1 package body CS_TP_FREETEXTS_PKG as
2 /* $Header: cstpftxb.pls 115.3 2002/12/04 03:07:30 wzli noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_FREETEXT_ID in NUMBER,
6 X_LOOKUP_ID in NUMBER,
7 X_FREETEXT_SIZE 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 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30 cursor C is select ROWID from CS_TP_FREETEXTS
31 where FREETEXT_ID = X_FREETEXT_ID
32 ;
33 begin
34 insert into CS_TP_FREETEXTS (
35 FREETEXT_ID,
36 LOOKUP_ID,
37 FREETEXT_SIZE,
38 ATTRIBUTE_CATEGORY,
39 ATTRIBUTE1,
40 ATTRIBUTE2,
41 ATTRIBUTE3,
42 ATTRIBUTE4,
43 ATTRIBUTE5,
44 ATTRIBUTE6,
45 ATTRIBUTE7,
46 ATTRIBUTE8,
47 ATTRIBUTE9,
48 ATTRIBUTE10,
49 ATTRIBUTE11,
50 ATTRIBUTE12,
51 ATTRIBUTE13,
52 ATTRIBUTE14,
53 ATTRIBUTE15,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN
59 ) values (
60 X_FREETEXT_ID,
61 X_LOOKUP_ID,
62 X_FREETEXT_SIZE,
63 X_ATTRIBUTE_CATEGORY,
64 X_ATTRIBUTE1,
65 X_ATTRIBUTE2,
66 X_ATTRIBUTE3,
67 X_ATTRIBUTE4,
68 X_ATTRIBUTE5,
69 X_ATTRIBUTE6,
70 X_ATTRIBUTE7,
71 X_ATTRIBUTE8,
72 X_ATTRIBUTE9,
73 X_ATTRIBUTE10,
74 X_ATTRIBUTE11,
75 X_ATTRIBUTE12,
76 X_ATTRIBUTE13,
77 X_ATTRIBUTE14,
78 X_ATTRIBUTE15,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATED_BY,
83 X_LAST_UPDATE_LOGIN
84 );
85
86 open c;
87 fetch c into X_ROWID;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 X_FREETEXT_ID in NUMBER,
98 X_LOOKUP_ID in NUMBER,
99 X_FREETEXT_SIZE in NUMBER,
100 X_ATTRIBUTE_CATEGORY in VARCHAR2,
101 X_ATTRIBUTE1 in VARCHAR2,
102 X_ATTRIBUTE2 in VARCHAR2,
103 X_ATTRIBUTE3 in VARCHAR2,
104 X_ATTRIBUTE4 in VARCHAR2,
105 X_ATTRIBUTE5 in VARCHAR2,
106 X_ATTRIBUTE6 in VARCHAR2,
107 X_ATTRIBUTE7 in VARCHAR2,
108 X_ATTRIBUTE8 in VARCHAR2,
109 X_ATTRIBUTE9 in VARCHAR2,
110 X_ATTRIBUTE10 in VARCHAR2,
111 X_ATTRIBUTE11 in VARCHAR2,
112 X_ATTRIBUTE12 in VARCHAR2,
113 X_ATTRIBUTE13 in VARCHAR2,
114 X_ATTRIBUTE14 in VARCHAR2,
115 X_ATTRIBUTE15 in VARCHAR2
116 ) is
117 cursor c is select
118 LOOKUP_ID,
119 FREETEXT_SIZE,
120 ATTRIBUTE_CATEGORY,
121 ATTRIBUTE1,
122 ATTRIBUTE2,
123 ATTRIBUTE3,
124 ATTRIBUTE4,
125 ATTRIBUTE5,
126 ATTRIBUTE6,
127 ATTRIBUTE7,
128 ATTRIBUTE8,
129 ATTRIBUTE9,
130 ATTRIBUTE10,
131 ATTRIBUTE11,
132 ATTRIBUTE12,
133 ATTRIBUTE13,
134 ATTRIBUTE14,
135 ATTRIBUTE15
136 from CS_TP_FREETEXTS
137 where FREETEXT_ID = X_FREETEXT_ID
138 for update of FREETEXT_ID nowait;
139 recinfo c%rowtype;
140 begin
141 open c;
142 fetch c into recinfo;
143 if (c%notfound) then
144 close c;
145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
146 app_exception.raise_exception;
147 end if;
148 close c;
149 if ( (recinfo.LOOKUP_ID = X_LOOKUP_ID)
150 AND ((recinfo.FREETEXT_SIZE = X_FREETEXT_SIZE)
151 OR ((recinfo.FREETEXT_SIZE is null) AND (X_FREETEXT_SIZE is null)))
152 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
153 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
154 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
155 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
156 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
157 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
158 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
159 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
160 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
161 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
162 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
163 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
164 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
165 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
166 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
167 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
168 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
169 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
170 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
171 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
172 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
173 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
174 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
175 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
176 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
177 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
178 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
179 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
180 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
181 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
182 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
183 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
184 ) then
185 null;
186 else
187 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
188 app_exception.raise_exception;
189 end if;
190 return;
191 end LOCK_ROW;
192
193 procedure UPDATE_ROW (
194 X_FREETEXT_ID in NUMBER,
195 X_LOOKUP_ID in NUMBER,
196 X_FREETEXT_SIZE in NUMBER,
197 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
198 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
199 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
200 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
201 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
202 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
203 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
204 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
205 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
206 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
207 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
208 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
209 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
210 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
211 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
212 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
213 X_LAST_UPDATE_DATE in DATE,
214 X_LAST_UPDATED_BY in NUMBER,
215 X_LAST_UPDATE_LOGIN in NUMBER
216 ) is
217 begin
218 update CS_TP_FREETEXTS set
219 LOOKUP_ID = X_LOOKUP_ID,
220 FREETEXT_SIZE = X_FREETEXT_SIZE,
221 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
222 ATTRIBUTE1 = X_ATTRIBUTE1,
223 ATTRIBUTE2 = X_ATTRIBUTE2,
224 ATTRIBUTE3 = X_ATTRIBUTE3,
225 ATTRIBUTE4 = X_ATTRIBUTE4,
226 ATTRIBUTE5 = X_ATTRIBUTE5,
227 ATTRIBUTE6 = X_ATTRIBUTE6,
228 ATTRIBUTE7 = X_ATTRIBUTE7,
229 ATTRIBUTE8 = X_ATTRIBUTE8,
230 ATTRIBUTE9 = X_ATTRIBUTE9,
231 ATTRIBUTE10 = X_ATTRIBUTE10,
232 ATTRIBUTE11 = X_ATTRIBUTE11,
233 ATTRIBUTE12 = X_ATTRIBUTE12,
234 ATTRIBUTE13 = X_ATTRIBUTE13,
235 ATTRIBUTE14 = X_ATTRIBUTE14,
236 ATTRIBUTE15 = X_ATTRIBUTE15,
237 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
238 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
240 where FREETEXT_ID = X_FREETEXT_ID;
241
242 if (sql%notfound) then
243 raise no_data_found;
244 end if;
245 end UPDATE_ROW;
246
247 procedure DELETE_ROW (
248 X_FREETEXT_ID in NUMBER
249 ) is
250 begin
251 delete from CS_TP_FREETEXTS
252 where FREETEXT_ID = X_FREETEXT_ID;
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257 end DELETE_ROW;
258
259 end CS_TP_FREETEXTS_PKG;