[Home] [Help]
PACKAGE BODY: APPS.CS_CHG_SUB_REST_PKG
Source
1 PACKAGE BODY CS_CHG_SUB_REST_PKG AS
2 /* $Header: csxresttb.pls 115.0 2004/06/04 01:46:02 aseethep noship $ */
3
4 PROCEDURE INSERT_ROW (
5 PX_RESTRICTION_ID IN OUT NOCOPY NUMBER,
6 P_RESTRICTION_TYPE IN VARCHAR2,
7 P_CONDITION IN VARCHAR2,
8 P_VALUE_OBJECT_ID IN NUMBER,
9 P_VALUE_AMOUNT IN NUMBER,
10 P_CURRENCY_CODE IN VARCHAR2,
11 P_START_DATE_ACTIVE IN DATE,
12 P_END_DATE_ACTIVE IN DATE,
13 P_CREATION_DATE IN DATE,
14 P_CREATED_BY IN NUMBER,
15 P_LAST_UPDATE_DATE IN DATE,
16 P_LAST_UPDATED_BY IN NUMBER,
17 P_LAST_UPDATE_LOGIN IN NUMBER,
18 P_ATTRIBUTE1 IN VARCHAR2,
19 P_ATTRIBUTE2 IN VARCHAR2,
20 P_ATTRIBUTE3 IN VARCHAR2,
21 P_ATTRIBUTE4 IN VARCHAR2,
22 P_ATTRIBUTE5 IN VARCHAR2,
23 P_ATTRIBUTE6 IN VARCHAR2,
24 P_ATTRIBUTE7 IN VARCHAR2,
25 P_ATTRIBUTE8 IN VARCHAR2,
26 P_ATTRIBUTE9 IN VARCHAR2,
27 P_ATTRIBUTE10 IN VARCHAR2,
28 P_ATTRIBUTE11 IN VARCHAR2,
29 P_ATTRIBUTE12 IN VARCHAR2,
30 P_ATTRIBUTE13 IN VARCHAR2,
31 P_ATTRIBUTE14 IN VARCHAR2,
32 P_ATTRIBUTE15 IN VARCHAR2,
33 P_CONTEXT IN VARCHAR2,
34 P_OBJECT_VERSION_NUMBER IN NUMBER,
35 P_SECURITY_GROUP_ID IN NUMBER)
36 IS
37 cursor c1 is
38 select cs_chg_sub_restrictions_s.nextval
39 from dual;
40
41 BEGIN
42 if ( px_restriction_id IS NULL ) OR ( px_restriction_id = FND_API.G_MISS_NUM) THEN
43 open c1;
44 fetch c1 into px_restriction_id;
45 close c1;
46 end if;
47
48 insert into CS_CHG_SUB_RESTRICTIONS (
49 RESTRICTION_ID, RESTRICTION_TYPE, CONDITION,
50 VALUE_OBJECT_ID, VALUE_AMOUNT, CURRENCY_CODE,
51 START_DATE_ACTIVE, END_DATE_ACTIVE, CREATION_DATE,
52 CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN, ATTRIBUTE1, ATTRIBUTE2,
54 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
55 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
56 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
57 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
58 ATTRIBUTE15, CONTEXT, OBJECT_VERSION_NUMBER,
59 SECURITY_GROUP_ID)
60 VALUES (
61 PX_RESTRICTION_ID, P_RESTRICTION_TYPE, P_CONDITION,
62 P_VALUE_OBJECT_ID, P_VALUE_AMOUNT, P_CURRENCY_CODE,
63 P_START_DATE_ACTIVE, P_END_DATE_ACTIVE, P_CREATION_DATE,
64 P_CREATED_BY, P_LAST_UPDATE_DATE, P_LAST_UPDATED_BY,
65 P_LAST_UPDATE_LOGIN, P_ATTRIBUTE1, P_ATTRIBUTE2,
66 P_ATTRIBUTE3, P_ATTRIBUTE4, P_ATTRIBUTE5,
67 P_ATTRIBUTE6, P_ATTRIBUTE7, P_ATTRIBUTE8,
68 P_ATTRIBUTE9, P_ATTRIBUTE10, P_ATTRIBUTE11,
69 P_ATTRIBUTE12, P_ATTRIBUTE13, P_ATTRIBUTE14,
70 P_ATTRIBUTE15, P_CONTEXT, P_OBJECT_VERSION_NUMBER,
71 P_SECURITY_GROUP_ID);
72
73
74 END INSERT_ROW;
75
76
77 PROCEDURE LOCK_ROW (
78 P_RESTRICTION_ID IN NUMBER,
79 P_OBJECT_VERSION_NUMBER IN NUMBER)
80 IS
81 cursor c is
82 select 1
83 from cs_chg_sub_restrictions
84 where restriction_id = p_restriction_id
85 and object_version_number = p_object_version_number
86 for update nowait;
87
88 l_dummy number(3) := 0;
89 BEGIN
90 open c;
91 fetch c into l_dummy;
92 if (c%notfound) then
93 close c;
94 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
95 app_exception.raise_exception;
96 end if;
97 close c;
98
99 END LOCK_ROW;
100
101
102 PROCEDURE UPDATE_ROW (
103 P_RESTRICTION_ID IN NUMBER,
104 P_RESTRICTION_TYPE IN VARCHAR2,
105 P_CONDITION IN VARCHAR2,
106 P_VALUE_OBJECT_ID IN NUMBER,
107 P_VALUE_AMOUNT IN NUMBER,
108 P_CURRENCY_CODE IN VARCHAR2,
109 P_START_DATE_ACTIVE IN DATE,
110 P_END_DATE_ACTIVE IN DATE,
111 P_CREATION_DATE IN DATE,
112 P_CREATED_BY IN NUMBER,
113 P_LAST_UPDATE_DATE IN DATE,
114 P_LAST_UPDATED_BY IN NUMBER,
115 P_LAST_UPDATE_LOGIN IN NUMBER,
116 P_ATTRIBUTE1 IN VARCHAR2,
117 P_ATTRIBUTE2 IN VARCHAR2,
118 P_ATTRIBUTE3 IN VARCHAR2,
119 P_ATTRIBUTE4 IN VARCHAR2,
120 P_ATTRIBUTE5 IN VARCHAR2,
121 P_ATTRIBUTE6 IN VARCHAR2,
122 P_ATTRIBUTE7 IN VARCHAR2,
123 P_ATTRIBUTE8 IN VARCHAR2,
124 P_ATTRIBUTE9 IN VARCHAR2,
125 P_ATTRIBUTE10 IN VARCHAR2,
126 P_ATTRIBUTE11 IN VARCHAR2,
127 P_ATTRIBUTE12 IN VARCHAR2,
128 P_ATTRIBUTE13 IN VARCHAR2,
129 P_ATTRIBUTE14 IN VARCHAR2,
130 P_ATTRIBUTE15 IN VARCHAR2,
131 P_CONTEXT IN VARCHAR2,
132 P_OBJECT_VERSION_NUMBER IN NUMBER,
133 P_SECURITY_GROUP_ID IN NUMBER)
134 IS
135
136 BEGIN
137
138 UPDATE CS_CHG_SUB_RESTRICTIONS
139 SET RESTRICTION_ID = P_RESTRICTION_ID,
140 RESTRICTION_TYPE = P_RESTRICTION_TYPE,
141 CONDITION = P_CONDITION,
142 VALUE_OBJECT_ID = P_VALUE_OBJECT_ID,
143 VALUE_AMOUNT = P_VALUE_AMOUNT,
144 CURRENCY_CODE = P_CURRENCY_CODE,
145 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
146 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
147 CREATION_DATE = P_CREATION_DATE,
148 CREATED_BY = P_CREATED_BY,
149 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
152 ATTRIBUTE1 = P_ATTRIBUTE1,
153 ATTRIBUTE2 = P_ATTRIBUTE2,
154 ATTRIBUTE3 = P_ATTRIBUTE3,
155 ATTRIBUTE4 = P_ATTRIBUTE4,
156 ATTRIBUTE5 = P_ATTRIBUTE5,
157 ATTRIBUTE6 = P_ATTRIBUTE6,
158 ATTRIBUTE7 = P_ATTRIBUTE7,
159 ATTRIBUTE8 = P_ATTRIBUTE8,
160 ATTRIBUTE9 = P_ATTRIBUTE9,
161 ATTRIBUTE10 = P_ATTRIBUTE10,
162 ATTRIBUTE11 = P_ATTRIBUTE11,
163 ATTRIBUTE12 = P_ATTRIBUTE12,
164 ATTRIBUTE13 = P_ATTRIBUTE13,
165 ATTRIBUTE14 = P_ATTRIBUTE14,
166 ATTRIBUTE15 = P_ATTRIBUTE15,
167 CONTEXT = P_CONTEXT,
168 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
169 SECURITY_GROUP_ID = P_SECURITY_GROUP_ID
170 WHERE RESTRICTION_ID = P_RESTRICTION_ID;
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 END UPDATE_ROW;
177
178 PROCEDURE DELETE_ROW (
179 P_RESTRICTION_ID in NUMBER)
180 IS
181 BEGIN
182
183 DELETE FROM cs_chg_sub_restrictions
184 WHERE restriction_id = p_restriction_id;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189
190 END DELETE_ROW;
191
192 PROCEDURE LOAD_ROW (
193 P_RESTRICTION_ID IN NUMBER,
194 P_RESTRICTION_TYPE IN VARCHAR2,
195 P_CONDITION IN VARCHAR2,
196 P_VALUE_OBJECT_ID IN NUMBER,
197 P_VALUE_AMOUNT IN NUMBER,
198 P_CURRENCY_CODE IN VARCHAR2,
199 P_START_DATE_ACTIVE IN DATE,
200 P_END_DATE_ACTIVE IN DATE,
201 P_CREATION_DATE IN DATE,
202 P_CREATED_BY IN NUMBER,
203 P_LAST_UPDATE_DATE IN DATE,
204 P_LAST_UPDATED_BY IN NUMBER,
205 P_LAST_UPDATE_LOGIN IN NUMBER,
206 P_ATTRIBUTE1 IN VARCHAR2,
207 P_ATTRIBUTE2 IN VARCHAR2,
208 P_ATTRIBUTE3 IN VARCHAR2,
209 P_ATTRIBUTE4 IN VARCHAR2,
210 P_ATTRIBUTE5 IN VARCHAR2,
211 P_ATTRIBUTE6 IN VARCHAR2,
212 P_ATTRIBUTE7 IN VARCHAR2,
213 P_ATTRIBUTE8 IN VARCHAR2,
214 P_ATTRIBUTE9 IN VARCHAR2,
215 P_ATTRIBUTE10 IN VARCHAR2,
216 P_ATTRIBUTE11 IN VARCHAR2,
217 P_ATTRIBUTE12 IN VARCHAR2,
218 P_ATTRIBUTE13 IN VARCHAR2,
219 P_ATTRIBUTE14 IN VARCHAR2,
220 P_ATTRIBUTE15 IN VARCHAR2,
221 P_CONTEXT IN VARCHAR2,
222 P_OBJECT_VERSION_NUMBER IN NUMBER,
223 P_SECURITY_GROUP_ID IN NUMBER)
224 IS
225
226 -- Out local variables for the update / insert row procedures.
227 lx_object_version_number NUMBER := 0;
228 l_user_id NUMBER := 0;
229
230 -- needed to be passed as the parameter value for the insert's in/out
231 -- parameter.
232 l_RESTRICTION_ID NUMBER;
233
234 BEGIN
235
236 /* if ( p_owner = 'SEED' ) then
237 l_user_id := 1;
238 end if; */
239
240 l_RESTRICTION_ID := P_RESTRICTION_ID;
241
242 UPDATE_ROW(P_RESTRICTION_ID => l_RESTRICTION_ID,
243 P_RESTRICTION_TYPE => P_RESTRICTION_TYPE,
244 P_CONDITION => P_CONDITION,
245 P_VALUE_OBJECT_ID => P_VALUE_OBJECT_ID,
246 P_VALUE_AMOUNT => P_VALUE_AMOUNT,
247 P_CURRENCY_CODE => P_CURRENCY_CODE,
248 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
249 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
250 P_CREATION_DATE => P_CREATION_DATE,
251 P_CREATED_BY => P_CREATED_BY,
252 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
253 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
254 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
255 P_ATTRIBUTE1 => P_ATTRIBUTE1,
256 P_ATTRIBUTE2 => P_ATTRIBUTE2,
257 P_ATTRIBUTE3 => P_ATTRIBUTE3,
258 P_ATTRIBUTE4 => P_ATTRIBUTE4,
259 P_ATTRIBUTE5 => P_ATTRIBUTE5,
260 P_ATTRIBUTE6 => P_ATTRIBUTE6,
261 P_ATTRIBUTE7 => P_ATTRIBUTE7,
262 P_ATTRIBUTE8 => P_ATTRIBUTE8,
263 P_ATTRIBUTE9 => P_ATTRIBUTE9,
264 P_ATTRIBUTE10 => P_ATTRIBUTE10,
265 P_ATTRIBUTE11 => P_ATTRIBUTE11,
266 P_ATTRIBUTE12 => P_ATTRIBUTE12,
267 P_ATTRIBUTE13 => P_ATTRIBUTE13,
268 P_ATTRIBUTE14 => P_ATTRIBUTE14,
269 P_ATTRIBUTE15 => P_ATTRIBUTE15,
270 P_CONTEXT => P_CONTEXT,
271 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
272 P_SECURITY_GROUP_ID => P_SECURITY_GROUP_ID);
273
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276
277 INSERT_ROW(PX_RESTRICTION_ID => l_RESTRICTION_ID,
278 P_RESTRICTION_TYPE => P_RESTRICTION_TYPE,
279 P_CONDITION => P_CONDITION,
280 P_VALUE_OBJECT_ID => P_VALUE_OBJECT_ID,
281 P_VALUE_AMOUNT => P_VALUE_AMOUNT,
282 P_CURRENCY_CODE => P_CURRENCY_CODE,
283 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
284 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
285 P_CREATION_DATE => P_CREATION_DATE,
286 P_CREATED_BY => P_CREATED_BY,
287 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
288 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
289 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
290 P_ATTRIBUTE1 => P_ATTRIBUTE1,
291 P_ATTRIBUTE2 => P_ATTRIBUTE2,
292 P_ATTRIBUTE3 => P_ATTRIBUTE3,
293 P_ATTRIBUTE4 => P_ATTRIBUTE4,
294 P_ATTRIBUTE5 => P_ATTRIBUTE5,
295 P_ATTRIBUTE6 => P_ATTRIBUTE6,
296 P_ATTRIBUTE7 => P_ATTRIBUTE7,
297 P_ATTRIBUTE8 => P_ATTRIBUTE8,
298 P_ATTRIBUTE9 => P_ATTRIBUTE9,
299 P_ATTRIBUTE10 => P_ATTRIBUTE10,
300 P_ATTRIBUTE11 => P_ATTRIBUTE11,
301 P_ATTRIBUTE12 => P_ATTRIBUTE12,
302 P_ATTRIBUTE13 => P_ATTRIBUTE13,
306 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
303 P_ATTRIBUTE14 => P_ATTRIBUTE14,
304 P_ATTRIBUTE15 => P_ATTRIBUTE15,
305 P_CONTEXT => P_CONTEXT,
307 P_SECURITY_GROUP_ID => P_SECURITY_GROUP_ID);
308
309 END LOAD_ROW;
310
311 END CS_CHG_SUB_REST_PKG;