[Home] [Help]
PACKAGE BODY: APPS.CN_INVOICE_CHANGES_PKG
Source
1 PACKAGE BODY CN_INVOICE_CHANGES_PKG AS
2 /* $Header: cninvlnb.pls 115.3 2002/01/28 20:01:40 pkm ship $*/
3
4
5 -- * -------------------------------------------------------------------------*
6 -- Procedure Name
7 -- Insert_row
8 -- Purpose
9 -- Main insert procedure
10 -- Note
11 -- 1. Primary key should be populated from sequence before call
12 -- this procedure. No refernece to sequence in this procedure.
13 -- 2. All paramaters are IN parameter.
14 -- * -------------------------------------------------------------------------*
15 PROCEDURE insert_row
16 ( p_invoice_changes_all_rec IN INVOICE_CHANGES_ALL_REC_TYPE) IS
17
18 BEGIN
19
20 INSERT into CN_INVOICE_CHANGES_ALL
21 ( INVOICE_CHANGE_ID,
22 SALESREP_ID,
23 INVOICE_NUMBER,
24 LINE_NUMBER,
25 REVENUE_TYPE,
26 SPLIT_PCT,
27 ATTRIBUTE_CATEGORY,
28 ATTRIBUTE1,
29 ATTRIBUTE2,
30 ATTRIBUTE3,
31 ATTRIBUTE4,
32 ATTRIBUTE5,
33 ATTRIBUTE6,
34 ATTRIBUTE7,
35 ATTRIBUTE8,
36 ATTRIBUTE9,
37 ATTRIBUTE10,
38 ATTRIBUTE11,
39 ATTRIBUTE12,
40 ATTRIBUTE13,
41 ATTRIBUTE14,
42 ATTRIBUTE15,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 OBJECT_VERSION_NUMBER,
49 COMM_LINES_API_ID)
50 select
51 DECODE(p_invoice_changes_all_rec.INVOICE_CHANGE_ID, FND_API.G_MISS_NUM, NULL,
52 p_invoice_changes_all_rec.INVOICE_CHANGE_ID),
53 DECODE(p_invoice_changes_all_rec.SALESREP_ID, FND_API.G_MISS_NUM, NULL,
54 p_invoice_changes_all_rec.SALESREP_ID),
55 DECODE(p_invoice_changes_all_rec.INVOICE_NUMBER, FND_API.G_MISS_CHAR, NULL,
56 p_invoice_changes_all_rec.INVOICE_NUMBER),
57 DECODE(p_invoice_changes_all_rec.LINE_NUMBER, FND_API.G_MISS_NUM, NULL,
58 p_invoice_changes_all_rec.LINE_NUMBER),
59 DECODE(p_invoice_changes_all_rec.REVENUE_TYPE, FND_API.G_MISS_CHAR, NULL,
60 p_invoice_changes_all_rec.REVENUE_TYPE),
61 DECODE(p_invoice_changes_all_rec.SPLIT_PCT, FND_API.G_MISS_NUM, NULL,
62 p_invoice_changes_all_rec.SPLIT_PCT),
63 DECODE(p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,
64 p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY),
65 DECODE(p_invoice_changes_all_rec.ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,
66 p_invoice_changes_all_rec.ATTRIBUTE1),
67 DECODE(p_invoice_changes_all_rec.ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,
68 p_invoice_changes_all_rec.ATTRIBUTE2),
69 DECODE(p_invoice_changes_all_rec.ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,
70 p_invoice_changes_all_rec.ATTRIBUTE3),
71 DECODE(p_invoice_changes_all_rec.ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,
72 p_invoice_changes_all_rec.ATTRIBUTE4),
73 DECODE(p_invoice_changes_all_rec.ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,
74 p_invoice_changes_all_rec.ATTRIBUTE5),
75 DECODE(p_invoice_changes_all_rec.ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,
76 p_invoice_changes_all_rec.ATTRIBUTE6),
77 DECODE(p_invoice_changes_all_rec.ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,
78 p_invoice_changes_all_rec.ATTRIBUTE7),
79 DECODE(p_invoice_changes_all_rec.ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,
80 p_invoice_changes_all_rec.ATTRIBUTE8),
81 DECODE(p_invoice_changes_all_rec.ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,
82 p_invoice_changes_all_rec.ATTRIBUTE9),
83 DECODE(p_invoice_changes_all_rec.ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,
84 p_invoice_changes_all_rec.ATTRIBUTE10),
85 DECODE(p_invoice_changes_all_rec.ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,
86 p_invoice_changes_all_rec.ATTRIBUTE11),
87 DECODE(p_invoice_changes_all_rec.ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,
88 p_invoice_changes_all_rec.ATTRIBUTE12),
89 DECODE(p_invoice_changes_all_rec.ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,
90 p_invoice_changes_all_rec.ATTRIBUTE13),
91 DECODE(p_invoice_changes_all_rec.ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,
92 p_invoice_changes_all_rec.ATTRIBUTE14),
93 DECODE(p_invoice_changes_all_rec.ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,
94 p_invoice_changes_all_rec.ATTRIBUTE15),
95 Sysdate,
96 fnd_global.user_id,
97 Sysdate,
98 fnd_global.user_id,
99 fnd_global.login_id,
100 1,
101 DECODE(p_invoice_changes_all_rec.COMM_LINES_API_ID, FND_API.G_MISS_NUM, NULL,
102 p_invoice_changes_all_rec.COMM_LINES_API_ID)
103 from dual;
104
105 END insert_row;
106
107
108 -- * -------------------------------------------------------------------------*
109 -- Procedure Name
110 -- update_row
111 -- Purpose
112 -- Main update procedure
113 -- Note
114 -- 1. No object version checking, overwrite may happen
115 -- 2. Calling lock_update for object version checking
116 -- 3. All paramaters are IN parameter.
117 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
118 -- * -------------------------------------------------------------------------*
119 PROCEDURE update_row
120 ( p_invoice_changes_all_rec IN INVOICE_CHANGES_ALL_REC_TYPE) IS
121
122 BEGIN
123
124 UPDATE CN_INVOICE_CHANGES_ALL oldrec
125 SET
126 SALESREP_ID = DECODE(p_invoice_changes_all_rec.SALESREP_ID,
127 FND_API.G_MISS_NUM,
128 oldrec.SALESREP_ID,
129 p_invoice_changes_all_rec.SALESREP_ID),
130 INVOICE_NUMBER = DECODE(p_invoice_changes_all_rec.INVOICE_NUMBER,
131 FND_API.G_MISS_CHAR,
132 oldrec.INVOICE_NUMBER,
133 p_invoice_changes_all_rec.INVOICE_NUMBER),
134 LINE_NUMBER = DECODE(p_invoice_changes_all_rec.LINE_NUMBER,
135 FND_API.G_MISS_NUM,
136 oldrec.LINE_NUMBER,
137 p_invoice_changes_all_rec.LINE_NUMBER),
138 REVENUE_TYPE = DECODE(p_invoice_changes_all_rec.REVENUE_TYPE,
139 FND_API.G_MISS_CHAR,
140 oldrec.REVENUE_TYPE,
141 p_invoice_changes_all_rec.REVENUE_TYPE),
142 SPLIT_PCT = DECODE(p_invoice_changes_all_rec.SPLIT_PCT,
143 FND_API.G_MISS_NUM,
144 oldrec.SPLIT_PCT,
145 p_invoice_changes_all_rec.SPLIT_PCT),
146 ATTRIBUTE_CATEGORY = DECODE(p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY,
147 FND_API.G_MISS_CHAR,
148 oldrec.ATTRIBUTE_CATEGORY,
149 p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY),
150 ATTRIBUTE1 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE1,
151 FND_API.G_MISS_CHAR,
152 oldrec.ATTRIBUTE1,
153 p_invoice_changes_all_rec.ATTRIBUTE1),
154 ATTRIBUTE2 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE2,
155 FND_API.G_MISS_CHAR,
156 oldrec.ATTRIBUTE2,
157 p_invoice_changes_all_rec.ATTRIBUTE2),
158 ATTRIBUTE3 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE3,
159 FND_API.G_MISS_CHAR,
160 oldrec.ATTRIBUTE3,
161 p_invoice_changes_all_rec.ATTRIBUTE3),
162 ATTRIBUTE4 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE4,
163 FND_API.G_MISS_CHAR,
164 oldrec.ATTRIBUTE4,
165 p_invoice_changes_all_rec.ATTRIBUTE4),
166 ATTRIBUTE5 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE5,
167 FND_API.G_MISS_CHAR,
168 oldrec.ATTRIBUTE5,
169 p_invoice_changes_all_rec.ATTRIBUTE5),
170 ATTRIBUTE6 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE6,
171 FND_API.G_MISS_CHAR,
172 oldrec.ATTRIBUTE6,
173 p_invoice_changes_all_rec.ATTRIBUTE6),
174 ATTRIBUTE7 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE7,
175 FND_API.G_MISS_CHAR,
176 oldrec.ATTRIBUTE7,
177 p_invoice_changes_all_rec.ATTRIBUTE7),
178 ATTRIBUTE8 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE8,
179 FND_API.G_MISS_CHAR,
180 oldrec.ATTRIBUTE8,
181 p_invoice_changes_all_rec.ATTRIBUTE8),
182 ATTRIBUTE9 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE9,
183 FND_API.G_MISS_CHAR,
184 oldrec.ATTRIBUTE9,
185 p_invoice_changes_all_rec.ATTRIBUTE9),
186 ATTRIBUTE10 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE10,
187 FND_API.G_MISS_CHAR,
188 oldrec.ATTRIBUTE10,
189 p_invoice_changes_all_rec.ATTRIBUTE10),
190 ATTRIBUTE11 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE11,
191 FND_API.G_MISS_CHAR,
192 oldrec.ATTRIBUTE11,
193 p_invoice_changes_all_rec.ATTRIBUTE11),
194 ATTRIBUTE12 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE12,
195 FND_API.G_MISS_CHAR,
196 oldrec.ATTRIBUTE12,
197 p_invoice_changes_all_rec.ATTRIBUTE12),
198 ATTRIBUTE13 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE13,
199 FND_API.G_MISS_CHAR,
200 oldrec.ATTRIBUTE13,
201 p_invoice_changes_all_rec.ATTRIBUTE13),
202 ATTRIBUTE14 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE14,
203 FND_API.G_MISS_CHAR,
204 oldrec.ATTRIBUTE14,
205 p_invoice_changes_all_rec.ATTRIBUTE14),
206 ATTRIBUTE15 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE15,
207 FND_API.G_MISS_CHAR,
208 oldrec.ATTRIBUTE15,
209 p_invoice_changes_all_rec.ATTRIBUTE15),
210 LAST_UPDATE_DATE = Sysdate,
211 LAST_UPDATED_BY = fnd_global.user_id,
212 LAST_UPDATE_LOGIN = fnd_global.login_id,
213 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1,
214 COMM_LINES_API_ID = DECODE(p_invoice_changes_all_rec.COMM_LINES_API_ID,
215 FND_API.G_MISS_NUM,
216 oldrec.COMM_LINES_API_ID,
217 p_invoice_changes_all_rec.COMM_LINES_API_ID)
218 WHERE invoice_change_id = p_invoice_changes_all_rec.invoice_change_id;
219
220 IF (SQL%ROWCOUNT=0) THEN
221 RAISE NO_DATA_FOUND;
222 END IF;
223
224 END update_row;
225
226
227 -- * -------------------------------------------------------------------------*
228 -- Procedure Name
229 -- lock_update_row
230 -- Purpose
231 -- Main lcok and update procedure
232 -- Note
233 -- 1. Object version checking is performed before checking
234 -- 2. Calling update_row if you don not want object version checking
235 -- 3. All paramaters are IN parameter.
236 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
237 -- * -------------------------------------------------------------------------*
238 PROCEDURE lock_update_row
239 ( p_invoice_changes_all_rec IN INVOICE_CHANGES_ALL_REC_TYPE) IS
240
241 CURSOR c IS
242 SELECT object_version_number
243 FROM CN_INVOICE_CHANGES_ALL
244 WHERE invoice_change_id = p_invoice_changes_all_rec.invoice_change_id;
245
246 tlinfo c%ROWTYPE ;
247 BEGIN
248
249 open c;
250 fetch c into tlinfo;
251 if (c%notfound) then
252 close c;
253 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
254 fnd_msg_pub.add;
258
255 raise fnd_api.g_exc_unexpected_error;
256 end if;
257 close c;
259 if (tlinfo.object_version_number <> p_invoice_changes_all_rec.object_version_number) then
260 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
261 fnd_msg_pub.add;
262 raise fnd_api.g_exc_unexpected_error;
263 end if;
264 UPDATE CN_INVOICE_CHANGES_ALL oldrec
265 SET
266 SALESREP_ID = DECODE(p_invoice_changes_all_rec.SALESREP_ID,
267 FND_API.G_MISS_NUM,
268 oldrec.SALESREP_ID,
269 p_invoice_changes_all_rec.SALESREP_ID),
270 INVOICE_NUMBER = DECODE(p_invoice_changes_all_rec.INVOICE_NUMBER,
271 FND_API.G_MISS_CHAR,
272 oldrec.INVOICE_NUMBER,
273 p_invoice_changes_all_rec.INVOICE_NUMBER),
274 LINE_NUMBER = DECODE(p_invoice_changes_all_rec.LINE_NUMBER,
275 FND_API.G_MISS_NUM,
276 oldrec.LINE_NUMBER,
277 p_invoice_changes_all_rec.LINE_NUMBER),
278 REVENUE_TYPE = DECODE(p_invoice_changes_all_rec.REVENUE_TYPE,
279 FND_API.G_MISS_CHAR,
280 oldrec.REVENUE_TYPE,
281 p_invoice_changes_all_rec.REVENUE_TYPE),
282 SPLIT_PCT = DECODE(p_invoice_changes_all_rec.SPLIT_PCT,
283 FND_API.G_MISS_NUM,
284 oldrec.SPLIT_PCT,
285 p_invoice_changes_all_rec.SPLIT_PCT),
286 ATTRIBUTE_CATEGORY = DECODE(p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY,
287 FND_API.G_MISS_CHAR,
288 oldrec.ATTRIBUTE_CATEGORY,
289 p_invoice_changes_all_rec.ATTRIBUTE_CATEGORY),
290 ATTRIBUTE1 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE1,
291 FND_API.G_MISS_CHAR,
292 oldrec.ATTRIBUTE1,
293 p_invoice_changes_all_rec.ATTRIBUTE1),
294 ATTRIBUTE2 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE2,
295 FND_API.G_MISS_CHAR,
296 oldrec.ATTRIBUTE2,
297 p_invoice_changes_all_rec.ATTRIBUTE2),
298 ATTRIBUTE3 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE3,
299 FND_API.G_MISS_CHAR,
300 oldrec.ATTRIBUTE3,
301 p_invoice_changes_all_rec.ATTRIBUTE3),
302 ATTRIBUTE4 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE4,
303 FND_API.G_MISS_CHAR,
304 oldrec.ATTRIBUTE4,
305 p_invoice_changes_all_rec.ATTRIBUTE4),
306 ATTRIBUTE5 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE5,
307 FND_API.G_MISS_CHAR,
308 oldrec.ATTRIBUTE5,
309 p_invoice_changes_all_rec.ATTRIBUTE5),
310 ATTRIBUTE6 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE6,
311 FND_API.G_MISS_CHAR,
312 oldrec.ATTRIBUTE6,
313 p_invoice_changes_all_rec.ATTRIBUTE6),
314 ATTRIBUTE7 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE7,
315 FND_API.G_MISS_CHAR,
316 oldrec.ATTRIBUTE7,
317 p_invoice_changes_all_rec.ATTRIBUTE7),
318 ATTRIBUTE8 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE8,
319 FND_API.G_MISS_CHAR,
320 oldrec.ATTRIBUTE8,
321 p_invoice_changes_all_rec.ATTRIBUTE8),
322 ATTRIBUTE9 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE9,
323 FND_API.G_MISS_CHAR,
324 oldrec.ATTRIBUTE9,
325 p_invoice_changes_all_rec.ATTRIBUTE9),
326 ATTRIBUTE10 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE10,
327 FND_API.G_MISS_CHAR,
328 oldrec.ATTRIBUTE10,
329 p_invoice_changes_all_rec.ATTRIBUTE10),
330 ATTRIBUTE11 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE11,
331 FND_API.G_MISS_CHAR,
332 oldrec.ATTRIBUTE11,
333 p_invoice_changes_all_rec.ATTRIBUTE11),
334 ATTRIBUTE12 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE12,
335 FND_API.G_MISS_CHAR,
336 oldrec.ATTRIBUTE12,
337 p_invoice_changes_all_rec.ATTRIBUTE12),
338 ATTRIBUTE13 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE13,
339 FND_API.G_MISS_CHAR,
340 oldrec.ATTRIBUTE13,
341 p_invoice_changes_all_rec.ATTRIBUTE13),
342 ATTRIBUTE14 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE14,
343 FND_API.G_MISS_CHAR,
344 oldrec.ATTRIBUTE14,
345 p_invoice_changes_all_rec.ATTRIBUTE14),
346 ATTRIBUTE15 = DECODE(p_invoice_changes_all_rec.ATTRIBUTE15,
347 FND_API.G_MISS_CHAR,
348 oldrec.ATTRIBUTE15,
349 p_invoice_changes_all_rec.ATTRIBUTE15),
350 LAST_UPDATE_DATE = Sysdate,
351 LAST_UPDATED_BY = fnd_global.user_id,
352 LAST_UPDATE_LOGIN = fnd_global.login_id,
356 oldrec.COMM_LINES_API_ID,
353 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1,
354 COMM_LINES_API_ID = DECODE(p_invoice_changes_all_rec.COMM_LINES_API_ID,
355 FND_API.G_MISS_NUM,
357 p_invoice_changes_all_rec.COMM_LINES_API_ID)
358 WHERE invoice_change_id = p_invoice_changes_all_rec.invoice_change_id;
359
360 IF (SQL%ROWCOUNT=0) THEN
361 RAISE NO_DATA_FOUND;
362 END IF;
363
364 END lock_update_row;
365
366
367 -- * -------------------------------------------------------------------------*
368 -- Procedure Name
369 -- delete_row
370 -- Purpose
371 -- Main lcok and update procedure
372 -- Note
373 -- 1. All paramaters are IN parameter.
374 -- 2. Raise NO_DATA_FOUND exception if no reocrd deleted (??)
375 -- * -------------------------------------------------------------------------*
376 PROCEDURE delete_row
377 (
378 p_invoice_change_id NUMBER
379 ) IS
380
381 BEGIN
382
383 DELETE FROM CN_INVOICE_CHANGES_ALL
384 WHERE invoice_change_id = p_invoice_change_id;
385
386 IF (SQL%ROWCOUNT=0) THEN
387 RAISE NO_DATA_FOUND;
388 END IF;
389
390 END Delete_row;
391
392
393 END CN_INVOICE_CHANGES_PKG;