[Home] [Help]
PACKAGE BODY: APPS.GL_RECURRING_LINES_PKG
Source
1 PACKAGE BODY GL_RECURRING_LINES_PKG as
2 /* $Header: glireclb.pls 120.3 2005/05/05 01:20:10 kvora ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9
10 PROCEDURE check_unique( x_rowid VARCHAR2,
11 x_line_num NUMBER,
12 x_header_id NUMBER ) IS
13 CURSOR c_dup IS
14 SELECT 'Duplicate'
15 FROM gl_recurring_lines l
16 WHERE l.recurring_line_num = x_line_num
17 AND l.recurring_header_id = x_header_id
18 AND ( x_rowid is NULL
19 OR
20 l.rowid <> x_rowid );
21
22 dummy VARCHAR2(100);
23
24 BEGIN
25 OPEN c_dup;
26 FETCH c_dup INTO dummy;
27
28 IF c_dup%FOUND THEN
29 CLOSE c_dup;
30 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_REC_LINE' );
31 app_exception.raise_exception;
32 END IF;
33
34 CLOSE c_dup;
35
36 EXCEPTION
37 WHEN app_exceptions.application_exception THEN
38 RAISE;
39 WHEN OTHERS THEN
40 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
41 fnd_message.set_token('PROCEDURE',
42 'GL_RECURRING_LINES_PKG.check_unique');
43 RAISE;
44
45 END check_unique;
46
47 PROCEDURE check_dup_budget_acct( x_rowid VARCHAR2,
48 x_ccid NUMBER,
49 x_batch_id NUMBER ) IS
50 CURSOR c_dup IS
51 SELECT 'Duplicate'
52 FROM dual
53 WHERE exists
54 ( SELECT 'x'
55 FROM gl_recurring_lines rl,
56 gl_recurring_headers rh,
57 gl_recurring_batches rb
58 WHERE
59 rb.recurring_batch_id = x_batch_id
60 AND rb.recurring_batch_id = rh.recurring_batch_id
61 AND rh.recurring_header_id = rl.recurring_header_id
62 AND rl.code_combination_id = x_ccid
63 AND ( x_rowid is NULL
64 OR
65 rl.rowid <> x_rowid ));
66
67 dummy VARCHAR2(100);
68
69 BEGIN
70 OPEN c_dup;
71 FETCH c_dup INTO dummy;
72
73 IF c_dup%FOUND THEN
74 CLOSE c_dup;
75 fnd_message.set_name( 'SQLGL', 'GL_DUPLICATE_BUD_LINE' );
76 app_exception.raise_exception;
77 END IF;
78
79 CLOSE c_dup;
80
81 EXCEPTION
82 WHEN app_exceptions.application_exception THEN
83 RAISE;
84 WHEN OTHERS THEN
85 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
86 fnd_message.set_token('PROCEDURE',
87 'GL_RECURRING_LINES_PKG.check_dup_budget_acct');
88 RAISE;
89
90 END check_dup_budget_acct;
91
92 -- **********************************************************************
93
94 PROCEDURE delete_rows( x_header_id NUMBER ) IS
95
96 BEGIN
97
98 DELETE
99 FROM GL_RECURRING_LINE_CALC_RULES
100 WHERE RECURRING_HEADER_ID = x_header_id;
101
102 DELETE
103 FROM GL_RECURRING_LINES
104 WHERE RECURRING_HEADER_ID = x_header_id;
105
106 EXCEPTION
107 WHEN app_exceptions.application_exception THEN
108 RAISE;
109 WHEN OTHERS THEN
110 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
111 fnd_message.set_token('PROCEDURE',
112 'GL_RECURRING_LINES_PKG.delete_rows');
113 RAISE;
114
115 END delete_rows;
116
117 -- **********************************************************************
118
119
120 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
121 X_Recurring_Header_Id NUMBER,
122 X_Recurring_Line_Num NUMBER,
123 X_Last_Update_Date DATE,
124 X_Last_Updated_By NUMBER,
125 X_Code_Combination_Id NUMBER,
126 X_Entered_Currency_Code VARCHAR2,
127 X_Creation_Date DATE,
128 X_Created_By NUMBER,
129 X_Last_Update_Login NUMBER,
130 X_Description VARCHAR2,
131 X_Entered_Dr NUMBER,
132 X_Entered_Cr NUMBER,
133 X_Attribute1 VARCHAR2,
134 X_Attribute2 VARCHAR2,
135 X_Attribute3 VARCHAR2,
136 X_Attribute4 VARCHAR2,
137 X_Attribute5 VARCHAR2,
138 X_Attribute6 VARCHAR2,
139 X_Attribute7 VARCHAR2,
140 X_Attribute8 VARCHAR2,
141 X_Attribute9 VARCHAR2,
142 X_Attribute10 VARCHAR2,
143 X_Context VARCHAR2,
144 X_Budget_Flag VARCHAR2,
145 X_Batch_Id NUMBER
146 ) IS
147 CURSOR C IS SELECT rowid FROM GL_RECURRING_LINES
148 WHERE recurring_header_id = X_Recurring_Header_Id
149 and recurring_line_num = X_Recurring_Line_Num;
150
151 BEGIN
152
153 -- Check for line uniqueness
154 check_unique(X_rowid, X_Recurring_Line_Num, X_Recurring_Header_Id );
155
156 IF (X_Budget_Flag = 'Y') THEN
157 check_dup_budget_acct( X_Rowid, X_Code_Combination_Id, X_Batch_Id);
158 END IF;
159
160 INSERT INTO GL_RECURRING_LINES(
161 recurring_header_id,
162 recurring_line_num,
163 last_update_date,
164 last_updated_by,
165 code_combination_id,
166 entered_currency_code,
167 creation_date,
168 created_by,
169 last_update_login,
170 description,
171 entered_dr,
172 entered_cr,
173 attribute1,
174 attribute2,
175 attribute3,
176 attribute4,
177 attribute5,
178 attribute6,
179 attribute7,
180 attribute8,
181 attribute9,
182 attribute10,
183 context
184 ) VALUES (
185
186 X_Recurring_Header_Id,
187 X_Recurring_Line_Num,
188 X_Last_Update_Date,
189 X_Last_Updated_By,
190 X_Code_Combination_Id,
191 X_Entered_Currency_Code,
192 X_Creation_Date,
193 X_Created_By,
194 X_Last_Update_Login,
195 X_Description,
196 X_Entered_Dr,
197 X_Entered_Cr,
198 X_Attribute1,
199 X_Attribute2,
200 X_Attribute3,
201 X_Attribute4,
202 X_Attribute5,
203 X_Attribute6,
204 X_Attribute7,
205 X_Attribute8,
206 X_Attribute9,
207 X_Attribute10,
208 X_Context
209
210 );
211
212 OPEN C;
213 FETCH C INTO X_Rowid;
214 if (C%NOTFOUND) then
215 CLOSE C;
216 Raise NO_DATA_FOUND;
217 end if;
218 CLOSE C;
219 END Insert_Row;
220
221
222 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
223 X_Recurring_Header_Id NUMBER,
224 X_Recurring_Line_Num NUMBER,
225 X_Code_Combination_Id NUMBER,
226 X_Entered_Currency_Code VARCHAR2,
227 X_Description VARCHAR2,
228 X_Entered_Dr NUMBER,
229 X_Entered_Cr NUMBER,
230 X_Attribute1 VARCHAR2,
231 X_Attribute2 VARCHAR2,
232 X_Attribute3 VARCHAR2,
233 X_Attribute4 VARCHAR2,
234 X_Attribute5 VARCHAR2,
235 X_Attribute6 VARCHAR2,
236 X_Attribute7 VARCHAR2,
237 X_Attribute8 VARCHAR2,
238 X_Attribute9 VARCHAR2,
239 X_Attribute10 VARCHAR2,
240 X_Context VARCHAR2
241 ) IS
242 CURSOR C IS
243 SELECT *
244 FROM GL_RECURRING_LINES
245 WHERE rowid = X_Rowid
246 FOR UPDATE of Recurring_Header_Id NOWAIT;
247 Recinfo C%ROWTYPE;
248
249
250 BEGIN
251 OPEN C;
252 FETCH C INTO Recinfo;
253 if (C%NOTFOUND) then
254 CLOSE C;
255 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
256 APP_EXCEPTION.Raise_Exception;
257 end if;
258 CLOSE C;
259 if (
260
261 (Recinfo.recurring_header_id = X_Recurring_Header_Id)
262 AND (Recinfo.recurring_line_num = X_Recurring_Line_Num)
263 AND (Recinfo.code_combination_id = X_Code_Combination_Id)
264 AND ( (Recinfo.entered_currency_code = X_Entered_Currency_Code)
265 OR ( (Recinfo.entered_currency_code IS NULL)
266 AND (X_Entered_Currency_Code IS NULL)))
267 AND ( (Recinfo.description = X_Description)
268 OR ( (Recinfo.description IS NULL)
269 AND (X_Description IS NULL)))
270 AND ( (Recinfo.entered_dr = X_Entered_Dr)
271 OR ( (Recinfo.entered_dr IS NULL)
272 AND (X_Entered_Dr IS NULL)))
273 AND ( (Recinfo.entered_cr = X_Entered_Cr)
274 OR ( (Recinfo.entered_cr IS NULL)
275 AND (X_Entered_Cr IS NULL)))
276 AND ( (Recinfo.attribute1 = X_Attribute1)
277 OR ( (Recinfo.attribute1 IS NULL)
278 AND (X_Attribute1 IS NULL)))
279 AND ( (Recinfo.attribute2 = X_Attribute2)
280 OR ( (Recinfo.attribute2 IS NULL)
281 AND (X_Attribute2 IS NULL)))
282 AND ( (Recinfo.attribute3 = X_Attribute3)
283 OR ( (Recinfo.attribute3 IS NULL)
284 AND (X_Attribute3 IS NULL)))
285 AND ( (Recinfo.attribute4 = X_Attribute4)
286 OR ( (Recinfo.attribute4 IS NULL)
287 AND (X_Attribute4 IS NULL)))
288 AND ( (Recinfo.attribute5 = X_Attribute5)
289 OR ( (Recinfo.attribute5 IS NULL)
290 AND (X_Attribute5 IS NULL)))
291 AND ( (Recinfo.attribute6 = X_Attribute6)
292 OR ( (Recinfo.attribute6 IS NULL)
293 AND (X_Attribute6 IS NULL)))
294 AND ( (Recinfo.attribute7 = X_Attribute7)
295 OR ( (Recinfo.attribute7 IS NULL)
296 AND (X_Attribute7 IS NULL)))
297 AND ( (Recinfo.attribute8 = X_Attribute8)
298 OR ( (Recinfo.attribute8 IS NULL)
299 AND (X_Attribute8 IS NULL)))
300 AND ( (Recinfo.attribute9 = X_Attribute9)
301 OR ( (Recinfo.attribute9 IS NULL)
302 AND (X_Attribute9 IS NULL)))
303 AND ( (Recinfo.attribute10 = X_Attribute10)
304 OR ( (Recinfo.attribute10 IS NULL)
305 AND (X_Attribute10 IS NULL)))
306 AND ( (Recinfo.context = X_Context)
307 OR ( (Recinfo.context IS NULL)
308 AND (X_Context IS NULL)))
309 ) then
310 return;
311 else
312 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
313 APP_EXCEPTION.Raise_Exception;
314 end if;
315 END Lock_Row;
316
317
318
319 PROCEDURE Update_Row(X_Rowid VARCHAR2,
320 X_Recurring_Header_Id NUMBER,
321 X_Recurring_Line_Num NUMBER,
322 X_Last_Update_Date DATE,
323 X_Last_Updated_By NUMBER,
324 X_Code_Combination_Id NUMBER,
325 X_Entered_Currency_Code VARCHAR2,
326 X_Last_Update_Login NUMBER,
327 X_Description VARCHAR2,
328 X_Entered_Dr NUMBER,
329 X_Entered_Cr NUMBER,
330 X_Attribute1 VARCHAR2,
331 X_Attribute2 VARCHAR2,
332 X_Attribute3 VARCHAR2,
333 X_Attribute4 VARCHAR2,
334 X_Attribute5 VARCHAR2,
335 X_Attribute6 VARCHAR2,
336 X_Attribute7 VARCHAR2,
337 X_Attribute8 VARCHAR2,
338 X_Attribute9 VARCHAR2,
339 X_Attribute10 VARCHAR2,
340 X_Context VARCHAR2,
341 X_Budget_Flag VARCHAR2,
342 X_Batch_Id NUMBER
343 ) IS
344 BEGIN
345 -- Check for line uniqueness
346 check_unique(X_rowid, X_Recurring_Line_Num, X_Recurring_Header_Id );
347
348 IF (X_Budget_Flag = 'Y') THEN
349 check_dup_budget_acct( X_Rowid, X_Code_Combination_Id, X_Batch_Id);
350 END IF;
351
352 UPDATE GL_RECURRING_LINES
353 SET
354 recurring_header_id = X_Recurring_Header_Id,
355 recurring_line_num = X_Recurring_Line_Num,
356 last_update_date = X_Last_Update_Date,
357 last_updated_by = X_Last_Updated_By,
358 code_combination_id = X_Code_Combination_Id,
359 entered_currency_code = X_Entered_Currency_Code,
360 last_update_login = X_Last_Update_Login,
361 description = X_Description,
362 entered_dr = X_Entered_Dr,
363 entered_cr = X_Entered_Cr,
364 attribute1 = X_Attribute1,
365 attribute2 = X_Attribute2,
366 attribute3 = X_Attribute3,
367 attribute4 = X_Attribute4,
368 attribute5 = X_Attribute5,
369 attribute6 = X_Attribute6,
370 attribute7 = X_Attribute7,
371 attribute8 = X_Attribute8,
372 attribute9 = X_Attribute9,
373 attribute10 = X_Attribute10,
374 context = X_Context
375 WHERE rowid = X_Rowid;
376
377 if (SQL%NOTFOUND) then
378 Raise NO_DATA_FOUND;
379 end if;
380 END Update_Row;
381 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
382 BEGIN
383 DELETE FROM GL_RECURRING_LINES
384 WHERE rowid = X_Rowid;
385
386 if (SQL%NOTFOUND) then
387 Raise NO_DATA_FOUND;
388 end if;
389 END Delete_Row;
390
391
392
393 -- **********************************************************************
394
395
396 END GL_RECURRING_LINES_PKG;