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