[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_CONSTR_DEFAULTS_PKG
Source
1 PACKAGE BODY PN_VAR_CONSTR_DEFAULTS_PKG AS
2 /* $Header: PNVRCDFB.pls 120.0 2007/10/03 14:28:29 rthumma noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 14-MAR-07 Pikhar o Bug 5930407. Commented call to CHECK_MAX_CONSTR
10 -------------------------------------------------------------------------------
11 procedure INSERT_ROW (
12 X_ROWID in out NOCOPY VARCHAR2,
13 X_CONSTR_DEFAULT_ID in out NOCOPY NUMBER,
14 X_CONSTR_DEFAULT_NUM in out NOCOPY NUMBER,
15 X_VAR_RENT_ID in NUMBER,
16 X_AGREEMENT_TEMPLATE_ID in NUMBER,
17 X_CONSTR_TEMPLATE_ID in NUMBER,
18 X_CONSTR_START_DATE in DATE,
19 X_CONSTR_END_DATE in DATE,
20 X_CONSTR_CAT_CODE in VARCHAR2,
21 X_TYPE_CODE in VARCHAR2,
22 X_AMOUNT in NUMBER,
23 X_CREATION_DATE in DATE,
24 X_CREATED_BY in NUMBER,
25 X_LAST_UPDATE_DATE in DATE,
26 X_LAST_UPDATED_BY in NUMBER,
27 X_LAST_UPDATE_LOGIN in NUMBER,
28 X_ORG_ID in NUMBER,
29 X_ATTRIBUTE_CATEGORY in VARCHAR2,
30 X_ATTRIBUTE1 in VARCHAR2,
31 X_ATTRIBUTE2 in VARCHAR2,
32 X_ATTRIBUTE3 in VARCHAR2,
33 X_ATTRIBUTE4 in VARCHAR2,
34 X_ATTRIBUTE5 in VARCHAR2,
35 X_ATTRIBUTE6 in VARCHAR2,
36 X_ATTRIBUTE7 in VARCHAR2,
37 X_ATTRIBUTE8 in VARCHAR2,
38 X_ATTRIBUTE9 in VARCHAR2,
39 X_ATTRIBUTE10 in VARCHAR2,
40 X_ATTRIBUTE11 in VARCHAR2,
41 X_ATTRIBUTE12 in VARCHAR2,
42 X_ATTRIBUTE13 in VARCHAR2,
43 X_ATTRIBUTE14 in VARCHAR2,
44 X_ATTRIBUTE15 in VARCHAR2
45 ) is
46
47 cursor C is
48 select ROWID
49 from PN_VAR_CONSTR_DEFAULTS_ALL
50 where CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID;
51
52 l_return_status VARCHAR2(30) := NULL;
53
54 begin
55
56 PNP_DEBUG_PKG.debug ('PN_VAR_CONSTR_DEFAULTS_PKG.INSERT_ROW (+)');
57
58 -------------------------------------------------------
59 -- We need to generate the line number
60 -------------------------------------------------------
61 select nvl(max(constr.CONSTR_DEFAULT_NUM),0)
62 into X_CONSTR_DEFAULT_NUM
63 from PN_VAR_CONSTR_DEFAULTS constr
64 where constr.VAR_RENT_ID = X_VAR_RENT_ID;
65
66 X_CONSTR_DEFAULT_NUM := X_CONSTR_DEFAULT_NUM + 1;
67
68 -------------------------------------------------------
69 -- Select the nextval for constraint default id
70 -------------------------------------------------------
71 IF ( X_CONSTR_DEFAULT_ID IS NULL) THEN
72
73 select PN_VAR_CONSTR_DEFAULTS_S.nextval
74 into X_CONSTR_DEFAULT_ID
75 from dual;
76 END IF;
77
78 -- Check for constraint range
79 /*l_return_status := NULL;
80
81 PN_VAR_CONSTR_DEFAULTS_PKG.CHECK_MAX_CONSTR
82 (
83 x_return_status => l_return_status,
84 x_constraint_default_id => x_constr_default_id,
85 x_constr_cat_code => x_constr_cat_code,
86 x_type_code => x_type_code,
87 x_var_rent_id => x_var_rent_id,
88 x_amount => x_amount);
89
90 IF (l_return_status IS NOT NULL) THEN
91 APP_EXCEPTION.Raise_Exception;
92 END IF;*/
93
94 insert into PN_VAR_CONSTR_DEFAULTS_ALL (
95 CONSTR_DEFAULT_ID,
96 CONSTR_DEFAULT_NUM,
97 VAR_RENT_ID,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 CREATION_DATE,
101 CREATED_BY,
102 LAST_UPDATE_LOGIN,
103 AGREEMENT_TEMPLATE_ID,
104 CONSTR_TEMPLATE_ID,
105 CONSTR_START_DATE,
106 CONSTR_END_DATE,
107 CONSTR_CAT_CODE,
108 TYPE_CODE,
109 AMOUNT,
110 ORG_ID,
111 ATTRIBUTE_CATEGORY,
112 ATTRIBUTE1,
113 ATTRIBUTE2,
114 ATTRIBUTE3,
115 ATTRIBUTE4,
116 ATTRIBUTE5,
117 ATTRIBUTE6,
118 ATTRIBUTE7,
119 ATTRIBUTE8,
120 ATTRIBUTE9,
121 ATTRIBUTE10,
122 ATTRIBUTE11,
123 ATTRIBUTE12,
124 ATTRIBUTE13,
125 ATTRIBUTE14,
126 ATTRIBUTE15
127 ) values (
128 X_CONSTR_DEFAULT_ID,
129 X_CONSTR_DEFAULT_NUM,
130 X_VAR_RENT_ID,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_CREATION_DATE,
134 X_CREATED_BY,
135 X_LAST_UPDATE_LOGIN,
136 X_AGREEMENT_TEMPLATE_ID,
137 X_CONSTR_TEMPLATE_ID,
138 X_CONSTR_START_DATE,
139 X_CONSTR_END_DATE,
140 X_CONSTR_CAT_CODE,
141 X_TYPE_CODE,
142 X_AMOUNT,
143 X_ORG_ID,
144 X_ATTRIBUTE_CATEGORY,
145 X_ATTRIBUTE1,
146 X_ATTRIBUTE2,
147 X_ATTRIBUTE3,
148 X_ATTRIBUTE4,
149 X_ATTRIBUTE5,
150 X_ATTRIBUTE6,
151 X_ATTRIBUTE7,
152 X_ATTRIBUTE8,
153 X_ATTRIBUTE9,
154 X_ATTRIBUTE10,
155 X_ATTRIBUTE11,
156 X_ATTRIBUTE12,
157 X_ATTRIBUTE13,
158 X_ATTRIBUTE14,
159 X_ATTRIBUTE15
160 );
161
162 open c;
163 fetch c into X_ROWID;
164 if (c%notfound) then
165 close c;
166 raise no_data_found;
167 end if;
168 close c;
169
170 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.INSERT_ROW (-)');
171
172 end INSERT_ROW;
173
174 -------------------------------------------------------------------------------
175 -- PROCDURE : LOCK_ROW
176 -- INVOKED FROM : LOCK_ROW procedure
177 -- PURPOSE : locks the row
178 -- HISTORY :
179
180 -------------------------------------------------------------------------------
181 procedure LOCK_ROW (
182 X_CONSTR_DEFAULT_ID in NUMBER,
183 X_CONSTR_DEFAULT_NUM in NUMBER,
184 X_VAR_RENT_ID in NUMBER,
185 X_AGREEMENT_TEMPLATE_ID in NUMBER,
186 X_CONSTR_TEMPLATE_ID in NUMBER,
187 X_CONSTR_START_DATE in DATE,
188 X_CONSTR_END_DATE in DATE,
189 X_CONSTR_CAT_CODE in VARCHAR2,
190 X_TYPE_CODE in VARCHAR2,
191 X_AMOUNT in NUMBER,
192 X_ATTRIBUTE_CATEGORY in VARCHAR2,
193 X_ATTRIBUTE1 in VARCHAR2,
194 X_ATTRIBUTE2 in VARCHAR2,
195 X_ATTRIBUTE3 in VARCHAR2,
196 X_ATTRIBUTE4 in VARCHAR2,
197 X_ATTRIBUTE5 in VARCHAR2,
198 X_ATTRIBUTE6 in VARCHAR2,
199 X_ATTRIBUTE7 in VARCHAR2,
200 X_ATTRIBUTE8 in VARCHAR2,
201 X_ATTRIBUTE9 in VARCHAR2,
202 X_ATTRIBUTE10 in VARCHAR2,
203 X_ATTRIBUTE11 in VARCHAR2,
204 X_ATTRIBUTE12 in VARCHAR2,
205 X_ATTRIBUTE13 in VARCHAR2,
206 X_ATTRIBUTE14 in VARCHAR2,
207 X_ATTRIBUTE15 in VARCHAR2
208 ) is
209 cursor c1 is select
210 *
211 from PN_VAR_CONSTR_DEFAULTS_ALL
212 where CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID
213 for update of CONSTR_DEFAULT_ID nowait;
214
215 begin
216
217 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.LOCK_ROW (+)');
218
219 for tlinfo in c1 loop
220 if ( (tlinfo.CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID)
221 AND (tlinfo.CONSTR_DEFAULT_NUM = X_CONSTR_DEFAULT_NUM)
222 AND (tlinfo.VAR_RENT_ID = X_VAR_RENT_ID)
223 AND ((tlinfo.AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID)
224 OR ((tlinfo.AGREEMENT_TEMPLATE_ID is null) AND (X_AGREEMENT_TEMPLATE_ID is null)))
225 AND ((tlinfo.CONSTR_TEMPLATE_ID = X_CONSTR_TEMPLATE_ID)
226 OR ((tlinfo.CONSTR_TEMPLATE_ID is null) AND (X_CONSTR_TEMPLATE_ID is null)))
227 AND ((tlinfo.CONSTR_START_DATE = X_CONSTR_START_DATE)
228 OR ((tlinfo.CONSTR_START_DATE is null) AND (X_CONSTR_START_DATE is null)))
229 AND ((tlinfo.CONSTR_END_DATE = X_CONSTR_END_DATE)
230 OR ((tlinfo.CONSTR_END_DATE is null) AND (X_CONSTR_END_DATE is null)))
231 AND (tlinfo.CONSTR_CAT_CODE = X_CONSTR_CAT_CODE)
232 AND ((tlinfo.TYPE_CODE = X_TYPE_CODE)
233 OR ((tlinfo.TYPE_CODE is null) AND (X_TYPE_CODE is null)))
234 AND ((tlinfo.AMOUNT = X_AMOUNT)
235 OR ((tlinfo.AMOUNT is null) AND (X_AMOUNT is null)))
236 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
237 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
238 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
239 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
240 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
241 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
242 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
243 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
244 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
245 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
246 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
247 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
248 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
249 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
250 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
251 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
252 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
253 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
254 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
255 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
256 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
257 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
258 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
259 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
260 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
261 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
262 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
263 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
264 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
265 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
266 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
267 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
268 ) then
269 null;
270
271
272 else
273 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
274 app_exception.raise_exception;
275 end if;
276 end loop;
277 return;
278
279 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.LOCK_ROW (-)');
280
281 end LOCK_ROW;
282
283 -------------------------------------------------------------------------------
284 -- PROCDURE : UPDATE_ROW
285 -- INVOKED FROM : UPDATE_ROW procedure
286 -- PURPOSE : updates the row
287 -- HISTORY :
288 -- 14-MAR-07 Pikhar o Bug 5930407. Commented call to CHECK_MAX_CONSTR
289 -------------------------------------------------------------------------------
290 procedure UPDATE_ROW (
291 X_CONSTR_DEFAULT_ID in NUMBER,
292 X_CONSTR_DEFAULT_NUM in NUMBER,
293 X_VAR_RENT_ID in NUMBER,
294 X_AGREEMENT_TEMPLATE_ID in NUMBER,
295 X_CONSTR_TEMPLATE_ID in NUMBER,
296 X_CONSTR_START_DATE in DATE,
297 X_CONSTR_END_DATE in DATE,
298 X_CONSTR_CAT_CODE in VARCHAR2,
299 X_TYPE_CODE in VARCHAR2,
300 X_AMOUNT in NUMBER,
301 X_LAST_UPDATE_DATE in DATE,
302 X_LAST_UPDATED_BY in NUMBER,
303 X_LAST_UPDATE_LOGIN in NUMBER,
304 X_ATTRIBUTE_CATEGORY in VARCHAR2,
305 X_ATTRIBUTE1 in VARCHAR2,
306 X_ATTRIBUTE2 in VARCHAR2,
307 X_ATTRIBUTE3 in VARCHAR2,
308 X_ATTRIBUTE4 in VARCHAR2,
309 X_ATTRIBUTE5 in VARCHAR2,
310 X_ATTRIBUTE6 in VARCHAR2,
311 X_ATTRIBUTE7 in VARCHAR2,
312 X_ATTRIBUTE8 in VARCHAR2,
313 X_ATTRIBUTE9 in VARCHAR2,
314 X_ATTRIBUTE10 in VARCHAR2,
315 X_ATTRIBUTE11 in VARCHAR2,
316 X_ATTRIBUTE12 in VARCHAR2,
317 X_ATTRIBUTE13 in VARCHAR2,
318 X_ATTRIBUTE14 in VARCHAR2,
319 X_ATTRIBUTE15 in VARCHAR2
320 ) is
321
322 l_return_status VARCHAR2(30) := NULL;
323
324 begin
325
326 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.UPDATE_ROW (+)');
327
328 -- Check for constraint range
329 /*l_return_status := NULL;
330 PN_VAR_CONSTR_DEFAULTS_PKG.CHECK_MAX_CONSTR
331 (
332 x_return_status => l_return_status,
333 x_constraint_default_id => x_constr_default_id,
334 x_constr_cat_code => x_constr_cat_code,
335 x_var_rent_id => x_var_rent_id,
336 x_type_code => x_type_code,
337 x_amount => x_amount
338 );
339
340 IF (l_return_status IS NOT NULL) THEN
341 APP_EXCEPTION.Raise_Exception;
342 END IF;*/
343
344 update PN_VAR_CONSTR_DEFAULTS_ALL set
345 CONSTR_DEFAULT_NUM = X_CONSTR_DEFAULT_NUM,
346 VAR_RENT_ID = X_VAR_RENT_ID,
347 AGREEMENT_TEMPLATE_ID = X_AGREEMENT_TEMPLATE_ID,
348 CONSTR_TEMPLATE_ID = X_CONSTR_TEMPLATE_ID,
349 CONSTR_START_DATE = X_CONSTR_START_DATE,
350 CONSTR_END_DATE = X_CONSTR_END_DATE,
351 CONSTR_CAT_CODE = X_CONSTR_CAT_CODE,
352 TYPE_CODE = X_TYPE_CODE,
353 AMOUNT = X_AMOUNT,
354 CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID,
355 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
356 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
357 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
358 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
359 ATTRIBUTE1 = X_ATTRIBUTE1,
360 ATTRIBUTE2 = X_ATTRIBUTE2,
361 ATTRIBUTE3 = X_ATTRIBUTE3,
362 ATTRIBUTE4 = X_ATTRIBUTE4,
363 ATTRIBUTE5 = X_ATTRIBUTE5,
364 ATTRIBUTE6 = X_ATTRIBUTE6,
365 ATTRIBUTE7 = X_ATTRIBUTE7,
366 ATTRIBUTE8 = X_ATTRIBUTE8,
367 ATTRIBUTE9 = X_ATTRIBUTE9,
368 ATTRIBUTE10 = X_ATTRIBUTE10,
369 ATTRIBUTE11 = X_ATTRIBUTE11,
370 ATTRIBUTE12 = X_ATTRIBUTE12,
371 ATTRIBUTE13 = X_ATTRIBUTE13,
372 ATTRIBUTE14 = X_ATTRIBUTE14,
373 ATTRIBUTE15 = X_ATTRIBUTE15
374 where CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID;
375
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379
380 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.UPDATE_ROW (-)');
381
382 end UPDATE_ROW;
383
384 -----------------------------------------------------------------------
385 -- PROCDURE : DELETE_ROW
386 -----------------------------------------------------------------------
387
388 procedure DELETE_ROW (
389 X_CONSTR_DEFAULT_ID in NUMBER
390 ) is
391
392 begin
393
394 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.DELETE_ROW (+)');
395
396 delete from PN_VAR_CONSTR_DEFAULTS_ALL
397 where CONSTR_DEFAULT_ID = X_CONSTR_DEFAULT_ID;
398
399 if (sql%notfound) then
400 raise no_data_found;
401 end if;
402
403 PNP_DEBUG_PKG.debug ('PN_CONSTR_DEFAULTS_PKG.DELETE_ROW (-)');
404
405 end DELETE_ROW;
406
407
408 -----------------------------------------------------------------------
409 -- PROCDURE : CHECK_MAX_CONSTR
410 -----------------------------------------------------------------------
411
412 PROCEDURE CHECK_MAX_CONSTR
413 (
414 x_return_status in out NOCOPY varchar2,
415 x_constraint_default_id in number,
416 x_constr_cat_code in varchar2,
417 x_var_rent_id in number,
418 x_type_code in varchar2,
419 x_amount in number
420 )
421 IS
422 l_dummy NUMBER;
423 BEGIN
424 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_MAX_CONSTR (+)');
425
426 IF x_type_code = 'MIN' THEN
427
428 select 1
429 into l_dummy
430 from dual
431 where not exists
432 (
433 select 1
434 from pn_var_constr_defaults_all constr
435 where constr.amount < (x_amount)
436 and ((x_constraint_default_id is null) or
437 (constr.constr_default_id <> x_constraint_default_id))
438 and constr.constr_cat_code = x_constr_cat_code
439 and constr.var_rent_id = x_var_rent_id
440 and constr.type_code = 'MAX'
441 );
442
443 END IF;
444
445 PNP_DEBUG_PKG.debug ('PN_VAR_RENTS_PKG.CHECK_MAX_CONSTR (-)');
446
447 EXCEPTION
448 when NO_DATA_FOUND then
449 fnd_message.set_name ('PN','PN_VAR_WRONG_RANGE');
450 --fnd_message.set_token('RENT_NUMBER',
451 --x_rent_num);
452 x_return_status := 'E';
453
454 END CHECK_MAX_CONSTR;
455
456 end PN_VAR_CONSTR_DEFAULTS_PKG;