1 package body CN_ATTAIN_SCHEDULES_PKG as
2 /* $Header: cntatshb.pls 115.4 2002/11/21 21:09:03 hlchen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ATTAIN_SCHEDULE_ID in NUMBER,
6 X_NAME in VARCHAR2,
7 X_ATTRIBUTE_CATEGORY in VARCHAR2,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE2 in VARCHAR2,
10 X_ATTRIBUTE3 in VARCHAR2,
11 X_ATTRIBUTE4 in VARCHAR2,
12 X_ATTRIBUTE5 in VARCHAR2,
13 X_ATTRIBUTE6 in VARCHAR2,
14 X_ATTRIBUTE7 in VARCHAR2,
15 X_ATTRIBUTE8 in VARCHAR2,
16 X_ATTRIBUTE9 in VARCHAR2,
17 X_ATTRIBUTE10 in VARCHAR2,
18 X_ATTRIBUTE11 in VARCHAR2,
19 X_ATTRIBUTE12 in VARCHAR2,
20 X_ATTRIBUTE13 in VARCHAR2,
21 X_ATTRIBUTE14 in VARCHAR2,
22 X_ATTRIBUTE15 in VARCHAR2,
23 X_OBJECT_VERSION_NUMBER in NUMBER,
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 CN_ATTAIN_SCHEDULES
31 where ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID
32 ;
33 begin
34 insert into CN_ATTAIN_SCHEDULES (
35 ATTAIN_SCHEDULE_ID,
36 NAME,
37 ATTRIBUTE_CATEGORY,
38 ATTRIBUTE1,
39 ATTRIBUTE2,
40 ATTRIBUTE3,
41 ATTRIBUTE4,
42 ATTRIBUTE5,
43 ATTRIBUTE6,
44 ATTRIBUTE7,
45 ATTRIBUTE8,
46 ATTRIBUTE9,
47 ATTRIBUTE10,
48 ATTRIBUTE11,
49 ATTRIBUTE12,
50 ATTRIBUTE13,
51 ATTRIBUTE14,
52 ATTRIBUTE15,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN,
56 CREATION_DATE,
57 CREATED_BY,
58 OBJECT_VERSION_NUMBER
59 ) values
60 (
61 X_ATTAIN_SCHEDULE_ID,
62 TRIM(X_NAME),
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_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN,
82 X_CREATION_DATE,
83 X_CREATED_BY,
84 X_OBJECT_VERSION_NUMBER
85 );
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_ATTAIN_SCHEDULE_ID in NUMBER,
99 X_NAME in VARCHAR2,
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 X_OBJECT_VERSION_NUMBER in NUMBER
117 ) is
118 cursor c1 is select
119 NAME,
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 OBJECT_VERSION_NUMBER
137 from CN_ATTAIN_SCHEDULES
138 where ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID
139 for update of ATTAIN_SCHEDULE_ID nowait;
140 begin
141 for tlinfo in c1 loop
142 if ( (tlinfo.NAME = X_NAME)
143 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
144 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
145 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
146 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
147 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
148 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
149 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
150 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
151 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
152 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
153 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
154 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
155 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
156 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
157 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
158 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
159 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
160 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
161 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
162 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
163 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
164 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
165 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
166 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
167 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
168 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
169 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
170 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
171 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
172 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
173 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
174 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
175 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
176 ) then
177 null;
178 else
179 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
180 app_exception.raise_exception;
181 end if;
182 end loop;
183 return;
184 end LOCK_ROW;
185
186 procedure UPDATE_ROW (
187 X_ATTAIN_SCHEDULE_ID in NUMBER,
188 X_NAME in VARCHAR2,
189 X_ATTRIBUTE_CATEGORY in VARCHAR2,
190 X_ATTRIBUTE1 in VARCHAR2,
191 X_ATTRIBUTE2 in VARCHAR2,
192 X_ATTRIBUTE3 in VARCHAR2,
193 X_ATTRIBUTE4 in VARCHAR2,
194 X_ATTRIBUTE5 in VARCHAR2,
195 X_ATTRIBUTE6 in VARCHAR2,
196 X_ATTRIBUTE7 in VARCHAR2,
197 X_ATTRIBUTE8 in VARCHAR2,
198 X_ATTRIBUTE9 in VARCHAR2,
199 X_ATTRIBUTE10 in VARCHAR2,
200 X_ATTRIBUTE11 in VARCHAR2,
201 X_ATTRIBUTE12 in VARCHAR2,
202 X_ATTRIBUTE13 in VARCHAR2,
203 X_ATTRIBUTE14 in VARCHAR2,
204 X_ATTRIBUTE15 in VARCHAR2,
205 X_OBJECT_VERSION_NUMBER in NUMBER,
206 X_LAST_UPDATE_DATE in DATE,
207 X_LAST_UPDATED_BY in NUMBER,
208 X_LAST_UPDATE_LOGIN in NUMBER
209 ) is
210 begin
211 update CN_ATTAIN_SCHEDULES set
212 NAME = TRIM(X_NAME),
213 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
214 ATTRIBUTE1 = X_ATTRIBUTE1,
215 ATTRIBUTE2 = X_ATTRIBUTE2,
216 ATTRIBUTE3 = X_ATTRIBUTE3,
217 ATTRIBUTE4 = X_ATTRIBUTE4,
218 ATTRIBUTE5 = X_ATTRIBUTE5,
219 ATTRIBUTE6 = X_ATTRIBUTE6,
220 ATTRIBUTE7 = X_ATTRIBUTE7,
221 ATTRIBUTE8 = X_ATTRIBUTE8,
222 ATTRIBUTE9 = X_ATTRIBUTE9,
223 ATTRIBUTE10 = X_ATTRIBUTE10,
224 ATTRIBUTE11 = X_ATTRIBUTE11,
225 ATTRIBUTE12 = X_ATTRIBUTE12,
226 ATTRIBUTE13 = X_ATTRIBUTE13,
227 ATTRIBUTE14 = X_ATTRIBUTE14,
228 ATTRIBUTE15 = X_ATTRIBUTE15,
229 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
230 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
231 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
233 where ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID
234 ;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239 end UPDATE_ROW;
240
241 procedure DELETE_ROW (
242 X_ATTAIN_SCHEDULE_ID in NUMBER
243 ) is
244 begin
245 delete from CN_ATTAIN_SCHEDULES
246 where ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID;
247
248 if (sql%notfound) then
249 raise no_data_found;
250 end if;
251
252 end DELETE_ROW;
253
254 end CN_ATTAIN_SCHEDULES_PKG;