1 PACKAGE BODY cn_calc_ext_tbl_dtls_pkg as
2 -- $Header: cntblmpb.pls 115.5 2002/02/05 00:25:40 pkm ship $
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_CALC_EXT_TBL_DTL_ID in NUMBER,
6 X_EXTERNAL_COLUMN_ID in NUMBER,
7 X_CALC_EXT_TABLE_ID in NUMBER,
8 X_INTERNAL_COLUMN_ID in NUMBER,
9 X_ATTRIBUTE_CATEGORY in VARCHAR2,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_CREATION_DATE in DATE,
26 X_CREATED_BY in NUMBER,
27 X_LAST_UPDATE_DATE in DATE,
28 X_LAST_UPDATED_BY in NUMBER,
29 X_LAST_UPDATE_LOGIN in NUMBER
30 ) is
31 cursor C is select ROWID from CN_CALC_EXT_TBL_DTLS
32 where CALC_EXT_TBL_DTL_ID = X_CALC_EXT_TBL_DTL_ID ;
33
34 l_calc_ext_tbl_dtl_id NUMBER;
35 BEGIN
36 IF (x_calc_ext_tbl_dtl_id IS NULL) THEN
37 SELECT cn_calc_ext_tbl_dtls_s.NEXTVAL
38 INTO l_calc_ext_tbl_dtl_id
39 FROM dual;
40 ELSE
41 l_calc_ext_tbl_dtl_id := x_calc_ext_tbl_dtl_id;
42 END IF;
43
44 insert into CN_CALC_EXT_TBL_DTLS (
45 EXTERNAL_COLUMN_ID,
46 CALC_EXT_TBL_DTL_ID,
47 CALC_EXT_TABLE_ID,
48 INTERNAL_COLUMN_ID,
49 ATTRIBUTE_CATEGORY,
50 ATTRIBUTE1,
51 ATTRIBUTE2,
52 ATTRIBUTE3,
53 ATTRIBUTE4,
54 ATTRIBUTE5,
55 ATTRIBUTE6,
56 ATTRIBUTE7,
57 ATTRIBUTE8,
58 ATTRIBUTE9,
59 ATTRIBUTE10,
60 ATTRIBUTE11,
61 ATTRIBUTE12,
62 ATTRIBUTE13,
63 ATTRIBUTE14,
64 ATTRIBUTE15,
65 CREATED_BY,
66 CREATION_DATE,
67 LAST_UPDATE_LOGIN,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY
70 ) VALUES (
71 X_EXTERNAL_COLUMN_ID,
72 l_CALC_EXT_TBL_DTL_ID,
73 X_CALC_EXT_TABLE_ID,
74 X_INTERNAL_COLUMN_ID,
75 X_ATTRIBUTE_CATEGORY,
76 X_ATTRIBUTE1,
77 X_ATTRIBUTE2,
78 X_ATTRIBUTE3,
79 X_ATTRIBUTE4,
80 X_ATTRIBUTE5,
81 X_ATTRIBUTE6,
82 X_ATTRIBUTE7,
83 X_ATTRIBUTE8,
84 X_ATTRIBUTE9,
85 X_ATTRIBUTE10,
86 X_ATTRIBUTE11,
87 X_ATTRIBUTE12,
88 X_ATTRIBUTE13,
89 X_ATTRIBUTE14,
90 X_ATTRIBUTE15,
91 X_CREATED_BY,
92 X_CREATION_DATE,
93 X_LAST_UPDATE_LOGIN,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY );
96
97 open c;
98 fetch c into X_ROWID;
99 if (c%notfound) then
100 close c;
101 raise no_data_found;
102 end if;
103 close c;
104
105 end INSERT_ROW;
106
107 procedure LOCK_ROW (
108 X_CALC_EXT_TBL_DTL_ID in NUMBER,
109 X_EXTERNAL_COLUMN_ID in NUMBER,
110 X_CALC_EXT_TABLE_ID in NUMBER,
111 X_INTERNAL_COLUMN_ID in NUMBER,
112 X_ATTRIBUTE_CATEGORY in VARCHAR2,
113 X_ATTRIBUTE1 in VARCHAR2,
114 X_ATTRIBUTE2 in VARCHAR2,
115 X_ATTRIBUTE3 in VARCHAR2,
116 X_ATTRIBUTE4 in VARCHAR2,
117 X_ATTRIBUTE5 in VARCHAR2,
118 X_ATTRIBUTE6 in VARCHAR2,
119 X_ATTRIBUTE7 in VARCHAR2,
120 X_ATTRIBUTE8 in VARCHAR2,
121 X_ATTRIBUTE9 in VARCHAR2,
122 X_ATTRIBUTE10 in VARCHAR2,
123 X_ATTRIBUTE11 in VARCHAR2,
124 X_ATTRIBUTE12 in VARCHAR2,
125 X_ATTRIBUTE13 in VARCHAR2,
126 X_ATTRIBUTE14 in VARCHAR2,
127 X_ATTRIBUTE15 in VARCHAR2
128 ) is
129 cursor c1 is select
130 ATTRIBUTE11,
131 ATTRIBUTE12,
132 ATTRIBUTE13,
133 ATTRIBUTE14,
134 ATTRIBUTE15,
135 ATTRIBUTE_CATEGORY,
136 ATTRIBUTE1,
137 ATTRIBUTE2,
138 ATTRIBUTE3,
139 ATTRIBUTE4,
140 ATTRIBUTE5,
141 ATTRIBUTE6,
142 ATTRIBUTE7,
143 ATTRIBUTE8,
144 ATTRIBUTE9,
145 ATTRIBUTE10,
146 EXTERNAL_COLUMN_ID,
147 CALC_EXT_TABLE_ID,
148 INTERNAL_COLUMN_ID
149 from CN_CALC_EXT_TBL_DTLS
150 where CALC_EXT_TBL_DTL_ID = X_CALC_EXT_TBL_DTL_ID
151 for update of CALC_EXT_TBL_DTL_ID nowait;
152
153 tlinfo c1%ROWTYPE;
154 BEGIN
155 OPEN c1;
156 FETCH c1 INTO tlinfo;
157 CLOSE c1;
158
159 if ( ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
160 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
161 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
162 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
163 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
164 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
165 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
166 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
167 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
168 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
169 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
170 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
171 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
172 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
173 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
174 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
175 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
176 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
177 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
178 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
179 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
180 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
181 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
182 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
183 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
184 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
185 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
186 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
187 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
188 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
189 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
190 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
191 AND (tlinfo.EXTERNAL_COLUMN_ID = X_EXTERNAL_COLUMN_ID)
192 AND (tlinfo.CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID)
193 AND (tlinfo.INTERNAL_COLUMN_ID = X_INTERNAL_COLUMN_ID)
194 ) then
195 null;
196 else
197 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
198 app_exception.raise_exception;
199 end if;
200
201 return;
202 end LOCK_ROW;
203
204 procedure UPDATE_ROW (
205 X_CALC_EXT_TBL_DTL_ID in NUMBER,
206 X_EXTERNAL_COLUMN_ID in NUMBER,
207 X_CALC_EXT_TABLE_ID in NUMBER,
208 X_INTERNAL_COLUMN_ID in NUMBER,
209 X_ATTRIBUTE_CATEGORY in VARCHAR2,
210 X_ATTRIBUTE1 in VARCHAR2,
211 X_ATTRIBUTE2 in VARCHAR2,
212 X_ATTRIBUTE3 in VARCHAR2,
213 X_ATTRIBUTE4 in VARCHAR2,
214 X_ATTRIBUTE5 in VARCHAR2,
215 X_ATTRIBUTE6 in VARCHAR2,
216 X_ATTRIBUTE7 in VARCHAR2,
217 X_ATTRIBUTE8 in VARCHAR2,
218 X_ATTRIBUTE9 in VARCHAR2,
219 X_ATTRIBUTE10 in VARCHAR2,
220 X_ATTRIBUTE11 in VARCHAR2,
221 X_ATTRIBUTE12 in VARCHAR2,
222 X_ATTRIBUTE13 in VARCHAR2,
223 X_ATTRIBUTE14 in VARCHAR2,
224 X_ATTRIBUTE15 in VARCHAR2,
225 X_LAST_UPDATE_DATE in DATE,
226 X_LAST_UPDATED_BY in NUMBER,
227 X_LAST_UPDATE_LOGIN in NUMBER
228 ) is
229 begin
230 update CN_CALC_EXT_TBL_DTLS set
231 ATTRIBUTE11 = X_ATTRIBUTE11,
232 ATTRIBUTE12 = X_ATTRIBUTE12,
233 ATTRIBUTE13 = X_ATTRIBUTE13,
234 ATTRIBUTE14 = X_ATTRIBUTE14,
235 ATTRIBUTE15 = X_ATTRIBUTE15,
236 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
237 ATTRIBUTE1 = X_ATTRIBUTE1,
238 ATTRIBUTE2 = X_ATTRIBUTE2,
239 ATTRIBUTE3 = X_ATTRIBUTE3,
240 ATTRIBUTE4 = X_ATTRIBUTE4,
241 ATTRIBUTE5 = X_ATTRIBUTE5,
242 ATTRIBUTE6 = X_ATTRIBUTE6,
243 ATTRIBUTE7 = X_ATTRIBUTE7,
244 ATTRIBUTE8 = X_ATTRIBUTE8,
245 ATTRIBUTE9 = X_ATTRIBUTE9,
246 ATTRIBUTE10 = X_ATTRIBUTE10,
247 EXTERNAL_COLUMN_ID = X_EXTERNAL_COLUMN_ID,
248 CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID,
249 INTERNAL_COLUMN_ID = X_INTERNAL_COLUMN_ID,
250 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
251 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
253 where CALC_EXT_TBL_DTL_ID = X_CALC_EXT_TBL_DTL_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_EXT_TBL_DTL_ID in NUMBER
262 ) is
263 begin
264 delete from CN_CALC_EXT_TBL_DTLS
265 where CALC_EXT_TBL_DTL_ID = X_CALC_EXT_TBL_DTL_ID;
266
267 if (sql%notfound) then
268 raise no_data_found;
269 end if;
270
271 end DELETE_ROW;
272
273
274 END cn_calc_ext_tbl_dtls_pkg;
[Home] [Help]