1 package body CS_TP_TEMPLATE_ATTRIBUTE_PKG as
2 /* $Header: cstptatb.pls 115.7 2002/12/04 18:45:56 wzli noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TEMPLATE_ATTRIBUTE_ID in NUMBER,
6 X_TEMPLATE_ID in NUMBER,
7 X_OTHER_ID in NUMBER,
8 X_OBJECT_CODE in VARCHAR2,
9 X_START_THRESHOLD in NUMBER,
10 X_END_THRESHOLD in NUMBER,
11 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
12 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
13 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
14 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
15 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
16 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
17 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
18 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
19 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
20 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
21 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
22 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
23 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
24 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
25 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
26 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
27 X_CREATION_DATE in DATE,
28 X_CREATED_BY in NUMBER,
29 X_LAST_UPDATE_DATE in DATE,
30 X_LAST_UPDATED_BY in NUMBER,
31 X_LAST_UPDATE_LOGIN in NUMBER
32 ) is
33 cursor C is select ROWID from CS_TP_TEMPLATE_ATTRIBUTE
34 where TEMPLATE_ATTRIBUTE_ID = X_TEMPLATE_ATTRIBUTE_ID
35 ;
36 begin
37 insert into CS_TP_TEMPLATE_ATTRIBUTE (
38 TEMPLATE_ATTRIBUTE_ID,
39 TEMPLATE_ID,
40 OTHER_ID,
41 OBJECT_CODE,
42 START_THRESHOLD,
43 END_THRESHOLD,
44 ATTRIBUTE_CATEGORY,
45 ATTRIBUTE1,
46 ATTRIBUTE2,
47 ATTRIBUTE3,
48 ATTRIBUTE4,
49 ATTRIBUTE5,
50 ATTRIBUTE6,
51 ATTRIBUTE7,
52 ATTRIBUTE8,
53 ATTRIBUTE9,
54 ATTRIBUTE10,
55 ATTRIBUTE11,
56 ATTRIBUTE12,
57 ATTRIBUTE13,
58 ATTRIBUTE14,
59 ATTRIBUTE15,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN
65 ) values (
66 X_TEMPLATE_ATTRIBUTE_ID,
67 X_TEMPLATE_ID,
68 X_OTHER_ID,
69 X_OBJECT_CODE,
70 X_START_THRESHOLD,
71 X_END_THRESHOLD,
72 X_ATTRIBUTE_CATEGORY,
73 X_ATTRIBUTE1,
74 X_ATTRIBUTE2,
75 X_ATTRIBUTE3,
76 X_ATTRIBUTE4,
77 X_ATTRIBUTE5,
78 X_ATTRIBUTE6,
79 X_ATTRIBUTE7,
80 X_ATTRIBUTE8,
81 X_ATTRIBUTE9,
82 X_ATTRIBUTE10,
83 X_ATTRIBUTE11,
84 X_ATTRIBUTE12,
85 X_ATTRIBUTE13,
86 X_ATTRIBUTE14,
87 X_ATTRIBUTE15,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN
93 );
94
95 open c;
96 fetch c into X_ROWID;
97 if (c%notfound) then
98 close c;
99 raise no_data_found;
100 end if;
101 close c;
102
103 end INSERT_ROW;
104
105 procedure LOCK_ROW (
106 X_TEMPLATE_ATTRIBUTE_ID in NUMBER,
107 X_TEMPLATE_ID in NUMBER,
108 X_OTHER_ID in NUMBER,
109 X_OBJECT_CODE in VARCHAR2,
110 X_START_THRESHOLD in NUMBER,
111 X_END_THRESHOLD 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 c is
130 select
131 TEMPLATE_ID,
132 OTHER_ID,
133 OBJECT_CODE,
134 START_THRESHOLD,
135 END_THRESHOLD,
136 ATTRIBUTE_CATEGORY,
137 ATTRIBUTE1,
138 ATTRIBUTE2,
139 ATTRIBUTE3,
140 ATTRIBUTE4,
141 ATTRIBUTE5,
142 ATTRIBUTE6,
143 ATTRIBUTE7,
144 ATTRIBUTE8,
145 ATTRIBUTE9,
146 ATTRIBUTE10,
147 ATTRIBUTE11,
148 ATTRIBUTE12,
149 ATTRIBUTE13,
150 ATTRIBUTE14,
151 ATTRIBUTE15
152 from CS_TP_TEMPLATE_ATTRIBUTE
153 where TEMPLATE_ATTRIBUTE_ID = X_TEMPLATE_ATTRIBUTE_ID
154 for update of TEMPLATE_ATTRIBUTE_ID nowait;
155 recinfo c%rowtype;
156 begin
157 open c;
158 fetch c into recinfo;
159 if (c%notfound) then
160 close c;
161 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
162 app_exception.raise_exception;
163 end if;
164 close c;
165 if ( (recinfo.TEMPLATE_ID = X_TEMPLATE_ID)
166 AND (recinfo.OBJECT_CODE = X_OBJECT_CODE)
167 AND ((recinfo.OTHER_ID = X_OTHER_ID)
168 OR ((recinfo.OTHER_ID is null) AND (X_OTHER_ID is null)))
169 AND ((recinfo.START_THRESHOLD = X_START_THRESHOLD)
170 OR ((recinfo.START_THRESHOLD is null) AND (X_START_THRESHOLD is null)))
171 AND ((recinfo.END_THRESHOLD = X_END_THRESHOLD)
172 OR ((recinfo.END_THRESHOLD is null) AND (X_END_THRESHOLD is null)))
173 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
174 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
175 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
176 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
177 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
178 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
179 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
180 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
181 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
182 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
183 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
184 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
185 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
186 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
187 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
188 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
189 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
190 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
191 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
192 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
193 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
194 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
195 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
196 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
197 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
198 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
199 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
200 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
201 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
202 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
203 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
204 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
205 ) then
206 null;
207 else
208 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
209 app_exception.raise_exception;
210 end if;
211 return;
212 end LOCK_ROW;
213
214
215 procedure UPDATE_ROW (
216 X_TEMPLATE_ATTRIBUTE_ID in NUMBER,
217 X_TEMPLATE_ID in NUMBER,
218 X_OTHER_ID in NUMBER,
219 X_OBJECT_CODE in VARCHAR2,
220 X_START_THRESHOLD in NUMBER,
221 X_END_THRESHOLD in NUMBER,
222 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
223 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
224 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
225 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
226 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
227 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
228 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
229 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
230 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
231 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
232 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
233 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
234 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
235 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
236 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
237 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
238 X_LAST_UPDATE_DATE in DATE,
239 X_LAST_UPDATED_BY in NUMBER,
240 X_LAST_UPDATE_LOGIN in NUMBER
241 ) is
242 begin
243 update CS_TP_TEMPLATE_ATTRIBUTE set
244 TEMPLATE_ID = X_TEMPLATE_ID,
245 OTHER_ID = X_OTHER_ID,
246 OBJECT_CODE = X_OBJECT_CODE,
247 START_THRESHOLD = X_START_THRESHOLD,
248 END_THRESHOLD = X_END_THRESHOLD,
249 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
250 ATTRIBUTE1 = X_ATTRIBUTE1,
251 ATTRIBUTE2 = X_ATTRIBUTE2,
252 ATTRIBUTE3 = X_ATTRIBUTE3,
253 ATTRIBUTE4 = X_ATTRIBUTE4,
254 ATTRIBUTE5 = X_ATTRIBUTE5,
255 ATTRIBUTE6 = X_ATTRIBUTE6,
256 ATTRIBUTE7 = X_ATTRIBUTE7,
257 ATTRIBUTE8 = X_ATTRIBUTE8,
258 ATTRIBUTE9 = X_ATTRIBUTE9,
259 ATTRIBUTE10 = X_ATTRIBUTE10,
260 ATTRIBUTE11 = X_ATTRIBUTE11,
261 ATTRIBUTE12 = X_ATTRIBUTE12,
262 ATTRIBUTE13 = X_ATTRIBUTE13,
263 ATTRIBUTE14 = X_ATTRIBUTE14,
264 ATTRIBUTE15 = X_ATTRIBUTE15,
265 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
266 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
267 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
268 where TEMPLATE_ATTRIBUTE_ID = X_TEMPLATE_ATTRIBUTE_ID;
269
270 if (sql%notfound) then
271 raise no_data_found;
272 end if;
273 end UPDATE_ROW;
274
275 procedure DELETE_ROW (
276 X_TEMPLATE_ATTRIBUTE_ID in NUMBER
277 ) is
278 begin
279 delete from CS_TP_TEMPLATE_ATTRIBUTE
280 where TEMPLATE_ATTRIBUTE_ID = X_TEMPLATE_ATTRIBUTE_ID;
281
282 if (sql%notfound) then
283 raise no_data_found;
284 end if;
285 end DELETE_ROW;
286
287 end CS_TP_TEMPLATE_ATTRIBUTE_PKG;