1 package body CN_ATTAIN_TIERS_PKG as
2 /* $Header: cntattrb.pls 115.4 2002/11/21 21:09:07 hlchen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ATTAIN_TIER_ID in NUMBER,
6 X_ATTAIN_SCHEDULE_ID in NUMBER,
7 X_PERCENT in NUMBER,
8 X_ATTRIBUTE_CATEGORY in VARCHAR2,
9 X_ATTRIBUTE1 in VARCHAR2,
10 X_ATTRIBUTE2 in VARCHAR2,
11 X_ATTRIBUTE3 in VARCHAR2,
12 X_ATTRIBUTE4 in VARCHAR2,
13 X_ATTRIBUTE5 in VARCHAR2,
14 X_ATTRIBUTE6 in VARCHAR2,
15 X_ATTRIBUTE7 in VARCHAR2,
16 X_ATTRIBUTE8 in VARCHAR2,
17 X_ATTRIBUTE9 in VARCHAR2,
18 X_ATTRIBUTE10 in VARCHAR2,
19 X_ATTRIBUTE11 in VARCHAR2,
20 X_ATTRIBUTE12 in VARCHAR2,
21 X_ATTRIBUTE13 in VARCHAR2,
22 X_ATTRIBUTE14 in VARCHAR2,
23 X_ATTRIBUTE15 in VARCHAR2,
24 X_OBJECT_VERSION_NUMBER in NUMBER,
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_ATTAIN_TIERS
32 where ATTAIN_TIER_ID = X_ATTAIN_TIER_ID
33 ;
34 begin
35 insert into CN_ATTAIN_TIERS (
36 ATTAIN_TIER_ID,
37 ATTAIN_SCHEDULE_ID,
38 PERCENT,
39 ATTRIBUTE_CATEGORY,
40 ATTRIBUTE1,
41 ATTRIBUTE2,
42 ATTRIBUTE3,
43 ATTRIBUTE4,
44 ATTRIBUTE5,
45 ATTRIBUTE6,
46 ATTRIBUTE7,
47 ATTRIBUTE8,
48 ATTRIBUTE9,
49 ATTRIBUTE10,
50 ATTRIBUTE11,
51 ATTRIBUTE12,
52 ATTRIBUTE13,
53 ATTRIBUTE14,
54 ATTRIBUTE15,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN,
58 CREATION_DATE,
59 CREATED_BY,
60 OBJECT_VERSION_NUMBER
61 ) values
62 (
63 X_ATTAIN_TIER_ID,
64 X_ATTAIN_SCHEDULE_ID,
65 X_PERCENT,
66 X_ATTRIBUTE_CATEGORY,
67 X_ATTRIBUTE1,
68 X_ATTRIBUTE2,
69 X_ATTRIBUTE3,
70 X_ATTRIBUTE4,
71 X_ATTRIBUTE5,
72 X_ATTRIBUTE6,
73 X_ATTRIBUTE7,
74 X_ATTRIBUTE8,
75 X_ATTRIBUTE9,
76 X_ATTRIBUTE10,
77 X_ATTRIBUTE11,
78 X_ATTRIBUTE12,
79 X_ATTRIBUTE13,
80 X_ATTRIBUTE14,
81 X_ATTRIBUTE15,
82 X_LAST_UPDATE_DATE,
83 X_LAST_UPDATED_BY,
84 X_LAST_UPDATE_LOGIN,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_OBJECT_VERSION_NUMBER
88 );
89
90 open c;
91 fetch c into X_ROWID;
92 if (c%notfound) then
93 close c;
94 raise no_data_found;
95 end if;
96 close c;
97
98 end INSERT_ROW;
99
100 procedure LOCK_ROW (
101 X_ATTAIN_TIER_ID in NUMBER,
102 X_ATTAIN_SCHEDULE_ID in NUMBER,
103 X_PERCENT in NUMBER,
104 X_ATTRIBUTE_CATEGORY in VARCHAR2,
105 X_ATTRIBUTE1 in VARCHAR2,
106 X_ATTRIBUTE2 in VARCHAR2,
107 X_ATTRIBUTE3 in VARCHAR2,
108 X_ATTRIBUTE4 in VARCHAR2,
109 X_ATTRIBUTE5 in VARCHAR2,
110 X_ATTRIBUTE6 in VARCHAR2,
111 X_ATTRIBUTE7 in VARCHAR2,
112 X_ATTRIBUTE8 in VARCHAR2,
113 X_ATTRIBUTE9 in VARCHAR2,
114 X_ATTRIBUTE10 in VARCHAR2,
115 X_ATTRIBUTE11 in VARCHAR2,
116 X_ATTRIBUTE12 in VARCHAR2,
117 X_ATTRIBUTE13 in VARCHAR2,
118 X_ATTRIBUTE14 in VARCHAR2,
119 X_ATTRIBUTE15 in VARCHAR2,
120 X_OBJECT_VERSION_NUMBER in NUMBER
121 ) is
122 cursor c1 is select
123 ATTAIN_SCHEDULE_ID,
124 PERCENT,
125 ATTRIBUTE_CATEGORY,
126 ATTRIBUTE1,
127 ATTRIBUTE2,
128 ATTRIBUTE3,
129 ATTRIBUTE4,
130 ATTRIBUTE5,
131 ATTRIBUTE6,
132 ATTRIBUTE7,
133 ATTRIBUTE8,
134 ATTRIBUTE9,
135 ATTRIBUTE10,
136 ATTRIBUTE11,
137 ATTRIBUTE12,
138 ATTRIBUTE13,
139 ATTRIBUTE14,
140 ATTRIBUTE15,
141 OBJECT_VERSION_NUMBER
142 from CN_ATTAIN_TIERS
143 where ATTAIN_TIER_ID = X_ATTAIN_TIER_ID
144 for update of ATTAIN_TIER_ID nowait;
145 begin
146 for tlinfo in c1 loop
147 if ( (tlinfo.ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID)
148 AND (tlinfo.PERCENT = X_PERCENT)
149 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
150 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
151 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
152 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
153 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
154 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
155 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
156 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
157 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
158 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
159 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
160 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
161 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
162 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
163 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
164 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
165 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
166 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
167 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
168 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
169 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
170 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
171 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
172 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
173 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
174 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
175 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
176 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
177 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
178 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
179 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
180 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
181 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
182 ) then
183 null;
184 else
185 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186 app_exception.raise_exception;
187 end if;
188 end loop;
189 return;
190 end LOCK_ROW;
191
192 procedure UPDATE_ROW (
193 X_ATTAIN_TIER_ID in NUMBER,
194 X_ATTAIN_SCHEDULE_ID in NUMBER,
195 X_PERCENT in NUMBER,
196 X_ATTRIBUTE_CATEGORY in VARCHAR2,
197 X_ATTRIBUTE1 in VARCHAR2,
198 X_ATTRIBUTE2 in VARCHAR2,
199 X_ATTRIBUTE3 in VARCHAR2,
200 X_ATTRIBUTE4 in VARCHAR2,
201 X_ATTRIBUTE5 in VARCHAR2,
202 X_ATTRIBUTE6 in VARCHAR2,
203 X_ATTRIBUTE7 in VARCHAR2,
204 X_ATTRIBUTE8 in VARCHAR2,
205 X_ATTRIBUTE9 in VARCHAR2,
206 X_ATTRIBUTE10 in VARCHAR2,
207 X_ATTRIBUTE11 in VARCHAR2,
208 X_ATTRIBUTE12 in VARCHAR2,
209 X_ATTRIBUTE13 in VARCHAR2,
210 X_ATTRIBUTE14 in VARCHAR2,
211 X_ATTRIBUTE15 in VARCHAR2,
212 X_OBJECT_VERSION_NUMBER in NUMBER,
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 CN_ATTAIN_TIERS set
219 ATTAIN_SCHEDULE_ID = X_ATTAIN_SCHEDULE_ID,
220 PERCENT = X_PERCENT,
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 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
238 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
239 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
240 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
241 where ATTAIN_TIER_ID = X_ATTAIN_TIER_ID
242 ;
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247 end UPDATE_ROW;
248
249 procedure DELETE_ROW (
250 X_ATTAIN_TIER_ID in NUMBER
251 ) is
252 begin
253 delete from CN_ATTAIN_TIERS
254 where ATTAIN_TIER_ID = X_ATTAIN_TIER_ID;
255
256 if (sql%notfound) then
257 raise no_data_found;
258 end if;
259
260 end DELETE_ROW;
261
262 end CN_ATTAIN_TIERS_PKG;
[Home] [Help]