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