1 PACKAGE BODY PN_NOTE_HEADERS_PKG AS
2 -- $Header: PNTNOTHB.pls 115.10 2004/05/26 07:03:43 abanerje ship $
3
4 ------------------------------------------------------------------
5 -- PROCEDURE : INSERT_ROW
6 ------------------------------------------------------------------
7 procedure INSERT_ROW
8 (
9 X_ROWID IN OUT NOCOPY VARCHAR2,
10 X_NOTE_HEADER_ID IN OUT NOCOPY NUMBER,
11 X_LEASE_ID IN NUMBER,
12 X_NOTE_TYPE_LOOKUP_CODE IN VARCHAR2,
13 X_NOTE_DATE IN DATE,
14 X_CREATION_DATE IN DATE,
15 X_CREATED_BY IN NUMBER,
16 X_LAST_UPDATE_DATE IN DATE,
17 X_LAST_UPDATED_BY IN NUMBER,
18 X_LAST_UPDATE_LOGIN IN NUMBER,
19 X_ATTRIBUTE_CATEGORY IN VARCHAR2, --3626177
20 X_ATTRIBUTE1 IN VARCHAR2,
21 X_ATTRIBUTE2 IN VARCHAR2,
22 X_ATTRIBUTE3 IN VARCHAR2,
23 X_ATTRIBUTE4 IN VARCHAR2,
24 X_ATTRIBUTE5 IN VARCHAR2,
25 X_ATTRIBUTE6 IN VARCHAR2,
26 X_ATTRIBUTE7 IN VARCHAR2,
27 X_ATTRIBUTE8 IN VARCHAR2,
28 X_ATTRIBUTE9 IN VARCHAR2,
29 X_ATTRIBUTE10 IN VARCHAR2,
30 X_ATTRIBUTE11 IN VARCHAR2,
31 X_ATTRIBUTE12 IN VARCHAR2,
32 X_ATTRIBUTE13 IN VARCHAR2,
33 X_ATTRIBUTE14 IN VARCHAR2,
34 X_ATTRIBUTE15 IN VARCHAR2
35 ) is
36 cursor C is
37 select ROWID
38 from PN_NOTE_HEADERS
39 where NOTE_HEADER_ID = X_NOTE_HEADER_ID;
40 BEGIN
41
42 IF (X_NOTE_HEADER_ID IS NULL) THEN
43 select PN_NOTE_HEADERS_S.nextval
44 into X_NOTE_HEADER_ID
45 from dual;
46 END IF;
47
48 insert into PN_NOTE_HEADERS
49 (
50 NOTE_HEADER_ID,
51 NOTE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 CREATED_BY,
56 CREATION_DATE,
57 NOTE_TYPE_LOOKUP_CODE,
58 LEASE_ID,
59 ATTRIBUTE_CATEGORY, --3626177
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 ATTRIBUTE11,
71 ATTRIBUTE12,
72 ATTRIBUTE13,
73 ATTRIBUTE14,
74 ATTRIBUTE15
75 )
76 values
77 (
78 X_NOTE_HEADER_ID,
79 X_NOTE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATE_LOGIN,
83 X_CREATED_BY,
84 X_CREATION_DATE,
85 X_NOTE_TYPE_LOOKUP_CODE,
86 X_LEASE_ID,
87 X_ATTRIBUTE_CATEGORY, --3626177
88 X_ATTRIBUTE1,
89 X_ATTRIBUTE2,
90 X_ATTRIBUTE3,
91 X_ATTRIBUTE4,
92 X_ATTRIBUTE5,
93 X_ATTRIBUTE6,
94 X_ATTRIBUTE7,
95 X_ATTRIBUTE8,
96 X_ATTRIBUTE9,
97 X_ATTRIBUTE10,
98 X_ATTRIBUTE11,
99 X_ATTRIBUTE12,
100 X_ATTRIBUTE13,
101 X_ATTRIBUTE14,
102 X_ATTRIBUTE15
103
104 );
105
106 open c;
107 fetch c into X_ROWID;
108 if (c%notfound) then
109 close c;
110 raise no_data_found;
111 end if;
112 close c;
113
114 END INSERT_ROW;
115
116 ------------------------------------------------------------------
117 -- PROCEDURE : LOCK_ROW
118 ------------------------------------------------------------------
119 PROCEDURE LOCK_ROW
120 (
121 X_NOTE_HEADER_ID in NUMBER,
122 X_LEASE_ID in NUMBER,
123 X_NOTE_DATE in DATE,
124 X_NOTE_TYPE_LOOKUP_CODE in VARCHAR2,
125 X_ATTRIBUTE_CATEGORY in VARCHAR2, --3626177
126 X_ATTRIBUTE1 in VARCHAR2,
127 X_ATTRIBUTE2 in VARCHAR2,
128 X_ATTRIBUTE3 in VARCHAR2,
129 X_ATTRIBUTE4 in VARCHAR2,
130 X_ATTRIBUTE5 in VARCHAR2,
131 X_ATTRIBUTE6 in VARCHAR2,
132 X_ATTRIBUTE7 in VARCHAR2,
133 X_ATTRIBUTE8 in VARCHAR2,
134 X_ATTRIBUTE9 in VARCHAR2,
135 X_ATTRIBUTE10 in VARCHAR2,
136 X_ATTRIBUTE11 in VARCHAR2,
137 X_ATTRIBUTE12 in VARCHAR2,
138 X_ATTRIBUTE13 in VARCHAR2,
139 X_ATTRIBUTE14 in VARCHAR2,
140 X_ATTRIBUTE15 in VARCHAR2
141 )
142 is
143 cursor c1 is
144 select *
145 from PN_NOTE_HEADERS
146 where NOTE_HEADER_ID = X_NOTE_HEADER_ID
147 for update of NOTE_HEADER_ID nowait;
148
149 tlinfo c1%rowtype;
150
151 BEGIN
152 open c1;
153 fetch c1 into tlinfo;
154 if (c1%notfound) then
155 close c1;
156 return;
157 end if;
158 close c1;
159 --3626177
160
161 IF NOT (tlinfo.NOTE_HEADER_ID = X_NOTE_HEADER_ID) THEN
162 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('NOTE_HEADER_ID',tlinfo.NOTE_HEADER_ID);
163 END IF;
164
165 IF NOT ((tlinfo.NOTE_TYPE_LOOKUP_CODE = X_NOTE_TYPE_LOOKUP_CODE)
166 OR ((tlinfo.NOTE_TYPE_LOOKUP_CODE is null) AND (X_NOTE_TYPE_LOOKUP_CODE is null))) THEN
167 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('NOTE_TYPE_LOOKUP_CODE',tlinfo.NOTE_TYPE_LOOKUP_CODE);
168 END IF;
169
170 IF NOT ((tlinfo.NOTE_DATE = X_NOTE_DATE)
171 OR ((tlinfo.NOTE_DATE is null) AND (X_NOTE_DATE is null))) THEN
172 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('NOTE_DATE',tlinfo.NOTE_DATE);
173 END IF;
174
175 IF NOT ((tlinfo.LEASE_ID = X_LEASE_ID)
176 OR ((tlinfo.LEASE_ID is null) AND (X_LEASE_ID is null))) THEN
177 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('LEASE_ID',tlinfo.LEASE_ID);
178 END IF;
179
180
181 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
182 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) THEN
183 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
184 END IF;
185
186 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
187 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) THEN
188 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
189 END IF;
190
191 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
192 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) THEN
193 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
194 END IF;
195
196 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
197 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) THEN
198 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
199 END IF;
200
201 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
202 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) THEN
203 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
204 END IF;
205
206 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
207 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) THEN
208 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
209 END IF;
210
211 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
212 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) THEN
213 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
214 END IF;
215
216 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
217 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) THEN
218 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
219 END IF;
220
221 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
222 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) THEN
223 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
224 END IF;
225
226 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
227 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) THEN
228 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
229 END IF;
230
231 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
232 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) THEN
233 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
234 END IF;
235
236 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
237 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) THEN
238 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
239 END IF;
240
241
242 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
243 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) THEN
244 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
245 END IF;
246
247 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
248 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) THEN
249 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
250 END IF;
251
252 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
253 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) THEN
254 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
255 END IF;
256
257
258 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
259 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) THEN
260 PN_VAR_RENT_PKG.LOCK_ROW_EXCEPTION('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
261 END IF;
262
263 RETURN;
264 END LOCK_ROW;
265
266 ------------------------------------------------------------------
267 -- PROCEDURE : UPDATE_ROW
268 ------------------------------------------------------------------
269 procedure UPDATE_ROW
270 (
271 X_NOTE_HEADER_ID in NUMBER,
272 X_LEASE_ID in NUMBER,
273 X_NOTE_TYPE_LOOKUP_CODE in VARCHAR2,
274 X_NOTE_DATE in DATE,
275 X_LAST_UPDATE_DATE in DATE,
276 X_LAST_UPDATED_BY in NUMBER,
277 X_LAST_UPDATE_LOGIN in NUMBER,
278 X_ATTRIBUTE_CATEGORY in VARCHAR2, --3626177
279 X_ATTRIBUTE1 in VARCHAR2,
280 X_ATTRIBUTE2 in VARCHAR2,
281 X_ATTRIBUTE3 in VARCHAR2,
282 X_ATTRIBUTE4 in VARCHAR2,
283 X_ATTRIBUTE5 in VARCHAR2,
284 X_ATTRIBUTE6 in VARCHAR2,
285 X_ATTRIBUTE7 in VARCHAR2,
286 X_ATTRIBUTE8 in VARCHAR2,
287 X_ATTRIBUTE9 in VARCHAR2,
288 X_ATTRIBUTE10 in VARCHAR2,
289 X_ATTRIBUTE11 in VARCHAR2,
290 X_ATTRIBUTE12 in VARCHAR2,
291 X_ATTRIBUTE13 in VARCHAR2,
292 X_ATTRIBUTE14 in VARCHAR2,
293 X_ATTRIBUTE15 in VARCHAR2
294 )
295 IS
296 BEGIN
297 update PN_NOTE_HEADERS
298 set LEASE_ID = X_LEASE_ID,
299 NOTE_DATE = X_NOTE_DATE,
300 NOTE_TYPE_LOOKUP_CODE = X_NOTE_TYPE_LOOKUP_CODE,
301 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
302 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
303 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
304 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY, --3626177
305 ATTRIBUTE1 = X_ATTRIBUTE1,
306 ATTRIBUTE2 = X_ATTRIBUTE2,
307 ATTRIBUTE3 = X_ATTRIBUTE3,
308 ATTRIBUTE4 = X_ATTRIBUTE4,
309 ATTRIBUTE5 = X_ATTRIBUTE5,
310 ATTRIBUTE6 = X_ATTRIBUTE6,
311 ATTRIBUTE7 = X_ATTRIBUTE7,
312 ATTRIBUTE8 = X_ATTRIBUTE8,
313 ATTRIBUTE9 = X_ATTRIBUTE9,
314 ATTRIBUTE10 = X_ATTRIBUTE10,
315 ATTRIBUTE11 = X_ATTRIBUTE11,
316 ATTRIBUTE12 = X_ATTRIBUTE12,
317 ATTRIBUTE13 = X_ATTRIBUTE13,
318 ATTRIBUTE14 = X_ATTRIBUTE14,
319 ATTRIBUTE15 = X_ATTRIBUTE15
320 where NOTE_HEADER_ID = X_NOTE_HEADER_ID;
321
322 IF (SQL%NOTFOUND) THEN
323 RAISE NO_DATA_FOUND;
324 END IF;
325
326 END UPDATE_ROW;
327
328 ------------------------------------------------------------------
329 -- PROCEDURE : DELETE_ROW
330 ------------------------------------------------------------------
331 PROCEDURE DELETE_ROW
332 (
333 X_NOTE_HEADER_ID in NUMBER
334 )
335 is
336 cursor c is
337 select note_detail_id
338 from pn_note_details
339 where note_header_id = X_NOTE_HEADER_ID
340 for update of note_detail_id nowait;
341 BEGIN
342 -- first we need to delete the note detail rows.
343 FOR i IN C LOOP
344 PN_NOTE_DETAILS_PKG.DELETE_ROW (X_NOTE_DETAIL_ID =>i.note_detail_id);
345 END LOOP;
346
347 delete from PN_NOTE_HEADERS where NOTE_HEADER_ID = X_NOTE_HEADER_ID;
348
349 if (sql%notfound) then
350 raise no_data_found;
351 end if;
352
353 END DELETE_ROW;
354
355 END PN_NOTE_HEADERS_PKG;
[Home] [Help]