[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SQL_EXPS_PKG
Source
1 PACKAGE BODY CN_CALC_SQL_EXPS_PKG AS
2 /* $Header: cntcexpb.pls 120.2 2005/07/14 22:33:54 kjayapau ship $ */
3
4 procedure INSERT_ROW (
5 X_ORG_ID IN CN_CALC_SQl_EXPS.ORG_ID%TYPE,
6 X_CALC_SQL_EXP_ID IN OUT NOCOPY CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
7 X_NAME IN CN_CALC_SQL_EXPS.NAME%TYPE,
8 X_DESCRIPTION IN CN_CALC_SQL_EXPS.DESCRIPTION%TYPE := NULL,
9 X_STATUS IN CN_CALC_SQL_EXPS.STATUS%TYPE := NULL,
10 X_EXP_TYPE_CODE IN CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE := NULL,
11 X_EXPRESSION_DISP IN VARCHAR2 := NULL, -- CLOBs
12 X_SQL_SELECT IN VARCHAR2 := NULL,
13 X_SQL_FROM IN VARCHAR2 := NULL,
14 X_PIPED_SQL_SELECT IN VARCHAR2 := NULL,
15 X_PIPED_SQL_FROM IN VARCHAR2 := NULL,
16 X_PIPED_EXPRESSION_DISP IN VARCHAR2 := NULL,
17 X_ATTRIBUTE_CATEGORY IN CN_CALC_SQL_EXPS.ATTRIBUTE_CATEGORY%TYPE := NULL,
18 X_ATTRIBUTE1 IN CN_CALC_SQL_EXPS.ATTRIBUTE1%TYPE := NULL,
19 X_ATTRIBUTE2 IN CN_CALC_SQL_EXPS.ATTRIBUTE2%TYPE := NULL,
20 X_ATTRIBUTE3 IN CN_CALC_SQL_EXPS.ATTRIBUTE3%TYPE := NULL,
21 X_ATTRIBUTE4 IN CN_CALC_SQL_EXPS.ATTRIBUTE4%TYPE := NULL,
22 X_ATTRIBUTE5 IN CN_CALC_SQL_EXPS.ATTRIBUTE5%TYPE := NULL,
23 X_ATTRIBUTE6 IN CN_CALC_SQL_EXPS.ATTRIBUTE6%TYPE := NULL,
24 X_ATTRIBUTE7 IN CN_CALC_SQL_EXPS.ATTRIBUTE7%TYPE := NULL,
25 X_ATTRIBUTE8 IN CN_CALC_SQL_EXPS.ATTRIBUTE8%TYPE := NULL,
26 X_ATTRIBUTE9 IN CN_CALC_SQL_EXPS.ATTRIBUTE9%TYPE := NULL,
27 X_ATTRIBUTE10 IN CN_CALC_SQL_EXPS.ATTRIBUTE10%TYPE := NULL,
28 X_ATTRIBUTE11 IN CN_CALC_SQL_EXPS.ATTRIBUTE11%TYPE := NULL,
29 X_ATTRIBUTE12 IN CN_CALC_SQL_EXPS.ATTRIBUTE12%TYPE := NULL,
30 X_ATTRIBUTE13 IN CN_CALC_SQL_EXPS.ATTRIBUTE13%TYPE := NULL,
31 X_ATTRIBUTE14 IN CN_CALC_SQL_EXPS.ATTRIBUTE14%TYPE := NULL,
32 X_ATTRIBUTE15 IN CN_CALC_SQL_EXPS.ATTRIBUTE15%TYPE := NULL,
33 X_CREATION_DATE IN CN_CALC_SQL_EXPS.CREATION_DATE%TYPE := SYSDATE,
34 X_CREATED_BY IN CN_CALC_SQL_EXPS.CREATED_BY%TYPE := FND_GLOBAL.USER_ID,
35 X_LAST_UPDATE_DATE IN CN_CALC_SQL_EXPS.LAST_UPDATE_DATE%TYPE := SYSDATE,
36 X_LAST_UPDATED_BY IN CN_CALC_SQL_EXPS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
37 X_LAST_UPDATE_LOGIN IN CN_CALC_SQL_EXPS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID,
38 X_OBJECT_VERSION_NUMBER OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE
39 ) is
40 cursor C is select calc_sql_exp_id from CN_CALC_SQL_EXPS
41 where CALC_SQL_EXP_ID = x_calc_sql_exp_id;
42 CURSOR id IS SELECT cn_calc_sql_exps_s.NEXTVAL FROM dual;
43
44 x_return_status VARCHAR2(4000);
45 l_note_msg VARCHAR2(4000);
46 x_msg_data VARCHAR2(4000);
47 x_msg_count NUMBER;
48 x_note_id NUMBER;
49
50
51 BEGIN
52 IF (x_calc_sql_exp_id IS NULL) THEN
53 OPEN id;
54 FETCH id INTO x_calc_sql_exp_id;
55 IF (id%notfound) THEN
56 CLOSE id;
57 RAISE no_data_found;
58 END IF;
59 CLOSE id;
60 END IF;
61
62 X_OBJECT_VERSION_NUMBER := 0;
63
64 insert into CN_CALC_SQL_EXPS (
65 ORG_ID,
66 CALC_SQL_EXP_ID,
67 NAME,
68 DESCRIPTION,
69 STATUS,
70 EXP_TYPE_CODE,
71 EXPRESSION_DISP,
72 SQL_SELECT,
73 SQL_FROM,
74 PIPED_SQL_SELECT,
75 PIPED_SQL_FROM,
76 PIPED_EXPRESSION_DISP,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 ATTRIBUTE3,
81 ATTRIBUTE4,
82 ATTRIBUTE5,
83 ATTRIBUTE6,
84 ATTRIBUTE7,
85 ATTRIBUTE8,
86 ATTRIBUTE9,
87 ATTRIBUTE10,
88 ATTRIBUTE11,
89 ATTRIBUTE12,
90 ATTRIBUTE13,
91 ATTRIBUTE14,
92 ATTRIBUTE15,
93 CREATED_BY,
94 CREATION_DATE,
95 LAST_UPDATE_LOGIN,
96 LAST_UPDATE_DATE,
97 LAST_UPDATED_BY,
98 OBJECT_VERSION_NUMBER
99 ) VALUES (
100 X_ORG_ID,
101 X_CALC_SQL_EXP_ID,
102 X_NAME,
103 X_DESCRIPTION,
104 X_STATUS,
105 X_EXP_TYPE_CODE,
106 X_EXPRESSION_DISP,
107 X_SQL_SELECT,
108 X_SQL_FROM,
109 X_PIPED_SQL_SELECT,
110 X_PIPED_SQL_FROM,
111 X_PIPED_EXPRESSION_DISP,
112 X_ATTRIBUTE_CATEGORY,
113 X_ATTRIBUTE1,
114 X_ATTRIBUTE2,
115 X_ATTRIBUTE3,
116 X_ATTRIBUTE4,
117 X_ATTRIBUTE5,
118 X_ATTRIBUTE6,
119 X_ATTRIBUTE7,
120 X_ATTRIBUTE8,
121 X_ATTRIBUTE9,
122 X_ATTRIBUTE10,
123 X_ATTRIBUTE11,
124 X_ATTRIBUTE12,
125 X_ATTRIBUTE13,
126 X_ATTRIBUTE14,
127 X_ATTRIBUTE15,
128 X_CREATED_BY,
129 X_CREATION_DATE,
130 X_LAST_UPDATE_LOGIN,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_OBJECT_VERSION_NUMBER);
134
135 FND_MESSAGE.SET_NAME('CN', 'CN_EXPRESSION_CREATE');
136 FND_MESSAGE.SET_TOKEN('EXPRESSION_NAME', X_NAME);
137 l_note_msg := FND_MESSAGE.GET;
138
139 jtf_notes_pub.create_note
140 ( p_api_version => 1.0,
141 x_return_status => x_return_status,
142 x_msg_count => x_msg_count,
143 x_msg_data => x_msg_data,
144 p_source_object_id => X_CALC_SQL_EXP_ID,
145 p_source_object_code => 'CN_CALC_SQL_EXPS',
146 p_notes => l_note_msg,
147 p_notes_detail => l_note_msg,
148 p_note_type => 'CN_SYSGEN',
149 x_jtf_note_id => x_note_id
150 );
151
152 end INSERT_ROW;
153
154 procedure LOCK_ROW (
155 P_CALC_SQL_EXP_ID IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
156 P_OBJECT_VERSION_NUMBER IN CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE) IS
157
158 cursor c is
159 select object_version_number
160 from cn_calc_sql_exps
161 where calc_sql_exp_id = p_calc_sql_exp_id
162 for update of CALC_SQL_EXP_ID nowait;
163
164 tlinfo c%rowtype ;
165 BEGIN
166 open c;
167 fetch c into tlinfo;
168 if (c%notfound) then
169 close c;
170 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
171 fnd_msg_pub.add;
172 raise fnd_api.g_exc_unexpected_error;
173 end if;
174 close c;
175
176 if (tlinfo.object_version_number <> p_object_version_number) then
177 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
178 fnd_msg_pub.add;
179 raise fnd_api.g_exc_unexpected_error;
180 end if;
181
182 END LOCK_ROW;
183
184 procedure UPDATE_ROW (
185 X_ORG_ID IN CN_CALC_SQl_EXPS.ORG_ID%TYPE,
186 X_CALC_SQL_EXP_ID IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE,
187 X_NAME IN CN_CALC_SQL_EXPS.NAME%TYPE,
188 X_DESCRIPTION IN CN_CALC_SQL_EXPS.DESCRIPTION%TYPE := CN_API.G_MISS_CHAR,
189 X_STATUS IN CN_CALC_SQL_EXPS.STATUS%TYPE := CN_API.G_MISS_CHAR,
190 X_EXP_TYPE_CODE IN CN_CALC_SQL_EXPS.EXP_TYPE_CODE%TYPE := CN_API.G_MISS_CHAR,
191 X_EXPRESSION_DISP IN VARCHAR2 := CN_API.G_MISS_CHAR,
192 X_SQL_SELECT IN VARCHAR2 := CN_API.G_MISS_CHAR,
193 X_SQL_FROM IN VARCHAR2 := CN_API.G_MISS_CHAR,
194 X_PIPED_SQL_SELECT IN VARCHAR2 := CN_API.G_MISS_CHAR,
195 X_PIPED_SQL_FROM IN VARCHAR2 := CN_API.G_MISS_CHAR,
196 X_PIPED_EXPRESSION_DISP IN VARCHAR2 := CN_API.G_MISS_CHAR,
197 X_ATTRIBUTE_CATEGORY IN CN_CALC_SQL_EXPS.ATTRIBUTE_CATEGORY%TYPE := CN_API.G_MISS_CHAR,
198 X_ATTRIBUTE1 IN CN_CALC_SQL_EXPS.ATTRIBUTE1%TYPE := CN_API.G_MISS_CHAR,
199 X_ATTRIBUTE2 IN CN_CALC_SQL_EXPS.ATTRIBUTE2%TYPE := CN_API.G_MISS_CHAR,
200 X_ATTRIBUTE3 IN CN_CALC_SQL_EXPS.ATTRIBUTE3%TYPE := CN_API.G_MISS_CHAR,
201 X_ATTRIBUTE4 IN CN_CALC_SQL_EXPS.ATTRIBUTE4%TYPE := CN_API.G_MISS_CHAR,
202 X_ATTRIBUTE5 IN CN_CALC_SQL_EXPS.ATTRIBUTE5%TYPE := CN_API.G_MISS_CHAR,
203 X_ATTRIBUTE6 IN CN_CALC_SQL_EXPS.ATTRIBUTE6%TYPE := CN_API.G_MISS_CHAR,
204 X_ATTRIBUTE7 IN CN_CALC_SQL_EXPS.ATTRIBUTE7%TYPE := CN_API.G_MISS_CHAR,
205 X_ATTRIBUTE8 IN CN_CALC_SQL_EXPS.ATTRIBUTE8%TYPE := CN_API.G_MISS_CHAR,
206 X_ATTRIBUTE9 IN CN_CALC_SQL_EXPS.ATTRIBUTE9%TYPE := CN_API.G_MISS_CHAR,
207 X_ATTRIBUTE10 IN CN_CALC_SQL_EXPS.ATTRIBUTE10%TYPE := CN_API.G_MISS_CHAR,
208 X_ATTRIBUTE11 IN CN_CALC_SQL_EXPS.ATTRIBUTE11%TYPE := CN_API.G_MISS_CHAR,
209 X_ATTRIBUTE12 IN CN_CALC_SQL_EXPS.ATTRIBUTE12%TYPE := CN_API.G_MISS_CHAR,
210 X_ATTRIBUTE13 IN CN_CALC_SQL_EXPS.ATTRIBUTE13%TYPE := CN_API.G_MISS_CHAR,
211 X_ATTRIBUTE14 IN CN_CALC_SQL_EXPS.ATTRIBUTE14%TYPE := CN_API.G_MISS_CHAR,
212 X_ATTRIBUTE15 IN CN_CALC_SQL_EXPS.ATTRIBUTE15%TYPE := CN_API.G_MISS_CHAR,
213 X_LAST_UPDATE_DATE IN CN_CALC_SQL_EXPS.LAST_UPDATE_DATE%TYPE := SYSDATE,
214 X_LAST_UPDATED_BY IN CN_CALC_SQL_EXPS.LAST_UPDATED_BY%TYPE := FND_GLOBAL.USER_ID,
215 X_LAST_UPDATE_LOGIN IN CN_CALC_SQL_EXPS.LAST_UPDATE_LOGIN%TYPE := FND_GLOBAL.LOGIN_ID,
216 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY CN_CALC_SQL_EXPS.OBJECT_VERSION_NUMBER%TYPE) IS
217
218 BEGIN
219
220 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
221
222 update CN_CALC_SQL_EXPS 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 STATUS = Decode(X_STATUS, fnd_api.g_miss_char, status, x_status),
226 EXP_TYPE_CODE = Decode(X_EXP_TYPE_CODE, fnd_api.g_miss_char, exp_type_code, x_exp_type_code),
227 EXPRESSION_DISP = Decode(X_EXPRESSION_DISP, fnd_api.g_miss_char, dbms_lob.substr(expression_disp), x_expression_disp),
228 SQL_SELECT = Decode(X_SQL_SELECT, fnd_api.g_miss_char, dbms_lob.substr(sql_select), x_sql_select),
229 SQL_FROM = Decode(X_SQL_FROM, fnd_api.g_miss_char, dbms_lob.substr(sql_from), x_sql_from),
230 PIPED_SQL_SELECT = Decode(X_PIPED_SQL_SELECT, fnd_api.g_miss_char, dbms_lob.substr(piped_sql_select), x_piped_sql_select),
231 PIPED_SQL_FROM = Decode(X_PIPED_SQL_FROM, fnd_api.g_miss_char, dbms_lob.substr(piped_sql_from), x_piped_sql_from),
232 PIPED_EXPRESSION_DISP = Decode(X_PIPED_EXPRESSION_DISP, fnd_api.g_miss_char, dbms_lob.substr(piped_expression_disp), x_piped_expression_disp),
233 ATTRIBUTE_CATEGORY = Decode(X_ATTRIBUTE_CATEGORY, fnd_api.g_miss_char, attribute_category, x_attribute_category),
234 ATTRIBUTE1 = Decode(X_ATTRIBUTE1, fnd_api.g_miss_char, attribute1, x_attribute1),
235 ATTRIBUTE2 = Decode(X_ATTRIBUTE2, fnd_api.g_miss_char, attribute2, x_attribute2),
236 ATTRIBUTE3 = Decode(X_ATTRIBUTE3, fnd_api.g_miss_char, attribute3, x_attribute3),
237 ATTRIBUTE4 = Decode(X_ATTRIBUTE4, fnd_api.g_miss_char, attribute4, x_attribute4),
238 ATTRIBUTE5 = Decode(X_ATTRIBUTE5, fnd_api.g_miss_char, attribute5, x_attribute5),
239 ATTRIBUTE6 = Decode(X_ATTRIBUTE6, fnd_api.g_miss_char, attribute6, x_attribute6),
240 ATTRIBUTE7 = Decode(X_ATTRIBUTE7, fnd_api.g_miss_char, attribute7, x_attribute7),
241 ATTRIBUTE8 = Decode(X_ATTRIBUTE8, fnd_api.g_miss_char, attribute8, x_attribute8),
242 ATTRIBUTE9 = Decode(X_ATTRIBUTE9, fnd_api.g_miss_char, attribute9, x_attribute9),
243 ATTRIBUTE10 = Decode(X_ATTRIBUTE10, fnd_api.g_miss_char, attribute10, x_attribute10),
244 ATTRIBUTE11 = Decode(X_ATTRIBUTE11, fnd_api.g_miss_char, attribute11, x_attribute11),
245 ATTRIBUTE12 = Decode(X_ATTRIBUTE12, fnd_api.g_miss_char, attribute12, x_attribute12),
246 ATTRIBUTE13 = Decode(X_ATTRIBUTE13, fnd_api.g_miss_char, attribute13, x_attribute13),
247 ATTRIBUTE14 = Decode(X_ATTRIBUTE14, fnd_api.g_miss_char, attribute14, x_attribute14),
248 ATTRIBUTE15 = Decode(X_ATTRIBUTE15, fnd_api.g_miss_char, attribute15, x_attribute15),
249 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
250 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
251 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
252 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
253 where CALC_SQL_EXP_ID = x_calc_sql_exp_id;
254
255 if (sql%notfound) then
256 raise no_data_found;
257 end if;
258 end UPDATE_ROW;
259
260 procedure DELETE_ROW (
261 X_CALC_SQL_EXP_ID IN CN_CALC_SQL_EXPS.CALC_SQL_EXP_ID%TYPE) is
262 begin
263 delete from CN_CALC_SQL_EXPS
264 where CALC_SQL_EXP_ID = X_CALC_SQL_EXP_ID;
265
266 if (sql%notfound) then
267 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
268 fnd_msg_pub.add;
269 raise fnd_api.g_exc_unexpected_error;
270 end if;
271
272 end DELETE_ROW;
273
274 end CN_CALC_SQL_EXPS_PKG;