1 PACKAGE BODY gl_alloc_formulas_pkg AS
2 /* $Header: glimafmb.pls 120.6 2005/05/05 01:16:56 kvora ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7 --
8 -- Procedure
9 -- check_ledger_selection
10 -- Purpose
11 -- Check if the target and offset lines' ledgers are the same.
12 -- History
13 -- 04-18-02 T Cheng Created
14 -- Arguments
15 -- X_Allocation_Formula_id the allocation formula id
16 -- Notes
17 --
18 PROCEDURE check_ledger_selection(X_Allocation_Formula_Id NUMBER) IS
19 CURSOR to_ledger_selection IS
20 SELECT count(DISTINCT nvl(ledger_id, -1))
21 FROM gl_alloc_formula_lines
22 WHERE allocation_formula_id = X_Allocation_Formula_Id
23 AND line_number in (4, 5);
24 counts NUMBER;
25 BEGIN
26 OPEN to_ledger_selection;
27 FETCH to_ledger_selection INTO counts;
28 IF (counts = 2) THEN
29 -- target and offset ledgers are both not null and are different
30 CLOSE to_ledger_selection;
31 fnd_message.set_name('SQLGL', 'GL_ALLOC_INTER_LEDGER');
32 app_exception.raise_exception;
33 END IF;
34 CLOSE to_ledger_selection;
35 EXCEPTION
36 WHEN app_exceptions.application_exception THEN
37 RAISE;
38 END check_ledger_selection;
39
40 --
41 -- PUBLIC FUNCTIONS
42 --
43
44 PROCEDURE check_unique(batch_id NUMBER, formula_name VARCHAR2,
45 row_id VARCHAR2) IS
46 CURSOR chk_duplicates is
47 SELECT 'Duplicate'
48 FROM GL_ALLOC_FORMULAS gaf
49 WHERE gaf.allocation_batch_id = batch_id
50 AND gaf.name = formula_name
51 AND ( row_id is null
52 OR gaf.rowid <> row_id);
53 dummy VARCHAR2(100);
54 BEGIN
55 OPEN chk_duplicates;
56 FETCH chk_duplicates INTO dummy;
57
58 IF chk_duplicates%FOUND THEN
59 CLOSE chk_duplicates;
60 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_ALLOC_FORMULA_NAM');
61 app_exception.raise_exception;
62 END IF;
63
64 CLOSE chk_duplicates;
65
66 EXCEPTION
67 WHEN app_exceptions.application_exception THEN
68 RAISE;
69 WHEN OTHERS THEN
70 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
71 fnd_message.set_token('PROCEDURE', 'gl_alloc_formulas_pkg.check_unique');
72 RAISE;
73 END check_unique;
74
75
76 FUNCTION get_unique_id RETURN NUMBER IS
77 CURSOR get_new_id IS
78 SELECT gl_alloc_formulas_s.NEXTVAL
79 FROM dual;
80 new_id number;
81 BEGIN
82 OPEN get_new_id;
83 FETCH get_new_id INTO new_id;
84
85 IF get_new_id%FOUND THEN
86 CLOSE get_new_id;
87 return(new_id);
88 ELSE
89 CLOSE get_new_id;
90 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
91 fnd_message.set_token('SEQUENCE', 'GL_ALLOC_FORMULAS_S');
92 app_exception.raise_exception;
93 END IF;
94
95 EXCEPTION
96 WHEN app_exceptions.application_exception THEN
97 RAISE;
98 WHEN OTHERS THEN
99 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
100 fnd_message.set_token('PROCEDURE',
101 'gl_alloc_formulas_pkg.get_unique_id');
102 RAISE;
103 END get_unique_id;
104
105 PROCEDURE delete_rows(batch_id NUMBER) IS
106 BEGIN
107 gl_alloc_form_lines_pkg.delete_batch(batch_id);
108
109 DELETE gl_alloc_formulas
110 WHERE allocation_batch_id = batch_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 null;
114 END delete_rows;
115
116 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
117 X_Allocation_Formula_Id IN OUT NOCOPY NUMBER,
118 X_Allocation_Batch_Id IN OUT NOCOPY NUMBER,
119 X_Name VARCHAR2,
120 X_Run_Sequence NUMBER,
121 X_Je_Category_Name VARCHAR2,
122 X_Full_Allocation_Flag VARCHAR2,
123 X_Conversion_Method_Code VARCHAR2,
124 X_Currency_Conversion_Type VARCHAR2,
125 X_Last_Update_Date DATE,
126 X_Last_Updated_By NUMBER,
127 X_Creation_Date DATE,
128 X_Created_By NUMBER,
129 X_Last_Update_Login NUMBER,
130 X_Description VARCHAR2,
131 X_Actual_Flag VARCHAR2
132 ) IS
133 CURSOR C IS SELECT rowid FROM GL_ALLOC_FORMULAS
134 WHERE allocation_formula_id = X_Allocation_Formula_Id;
135
136 BEGIN
137
138 -- Get batch id if it was not provided
139 IF (x_allocation_batch_id IS NULL) THEN
140 x_allocation_batch_id := gl_alloc_batches_pkg.get_unique_id;
141 END IF;
142
143 -- Get formula id if it was not provided
144 IF (x_allocation_formula_id IS NULL) THEN
145 x_allocation_formula_id := gl_alloc_formulas_pkg.get_unique_id;
146 END IF;
147
148 -- Make sure the user has completed all of the formula
149 -- lines.
150 IF (NOT gl_alloc_form_lines_pkg.complete_formula(
151 X_allocation_formula_id,
152 X_actual_flag)) THEN
153 IF (X_actual_flag = 'B') THEN
154 fnd_message.set_name('SQLGL', 'GL_COMPLETE_FOUR_FORMULA_LINES');
155 app_exception.raise_exception;
156 ELSE
157 fnd_message.set_name('SQLGL', 'GL_COMPLETE_ALL_FORMULA_LINES');
158 app_exception.raise_exception;
159 END IF;
160 END IF;
161
162 -- Make sure the ledger segment of target and offset lines are the same
163 check_ledger_selection(X_Allocation_Formula_Id);
164
165 -- Make sure there isn't a currency conflict
166 gl_alloc_form_lines_pkg.check_target_ledger(X_Allocation_Formula_Id);
167
168 INSERT INTO GL_ALLOC_FORMULAS(
169 allocation_formula_id,
170 allocation_batch_id,
171 name,
172 run_sequence,
173 je_category_name,
174 full_allocation_flag,
175 validation_status,
176 conversion_method_code,
177 currency_conversion_type,
178 last_update_date,
179 last_updated_by,
180 creation_date,
181 created_by,
182 last_update_login,
183 description
184 ) VALUES (
185 X_Allocation_Formula_Id,
186 X_Allocation_Batch_Id,
187 X_Name,
188 X_Run_Sequence,
189 X_Je_Category_Name,
190 X_Full_Allocation_Flag,
191 'N',
192 X_Conversion_Method_Code,
193 X_Currency_Conversion_Type,
194 X_Last_Update_Date,
195 X_Last_Updated_By,
196 X_Creation_Date,
197 X_Created_By,
198 X_Last_Update_Login,
199 X_Description
200 );
201
202 OPEN C;
203 FETCH C INTO X_Rowid;
204 if (C%NOTFOUND) then
205 CLOSE C;
206 RAISE NO_DATA_FOUND;
207 end if;
208 CLOSE C;
209 END Insert_Row;
210
211 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
212 X_Allocation_Formula_Id NUMBER,
213 X_Allocation_Batch_Id NUMBER,
214 X_Name VARCHAR2,
215 X_Run_Sequence NUMBER,
216 X_Je_Category_Name VARCHAR2,
217 X_Full_Allocation_Flag VARCHAR2,
218 X_Conversion_Method_Code VARCHAR2,
219 X_Currency_Conversion_Type VARCHAR2,
220 X_Description VARCHAR2
221 ) IS
222 CURSOR C IS
223 SELECT *
224 FROM GL_ALLOC_FORMULAS
225 WHERE rowid = X_Rowid
226 FOR UPDATE of Allocation_Formula_Id NOWAIT;
227 Recinfo C%ROWTYPE;
228 BEGIN
229 OPEN C;
230 FETCH C INTO Recinfo;
231 if (C%NOTFOUND) then
232 CLOSE C;
233 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 end if;
236 CLOSE C;
237 if (
238 ( (Recinfo.allocation_formula_id = X_Allocation_Formula_Id)
239 OR ( (Recinfo.allocation_formula_id IS NULL)
240 AND (X_Allocation_Formula_Id IS NULL)))
241 AND ( (Recinfo.allocation_batch_id = X_Allocation_Batch_Id)
242 OR ( (Recinfo.allocation_batch_id IS NULL)
243 AND (X_Allocation_Batch_Id IS NULL)))
244 AND ( (Recinfo.name = X_Name)
245 OR ( (Recinfo.name IS NULL)
246 AND (X_Name IS NULL)))
247 AND ( (Recinfo.run_sequence = X_Run_Sequence)
248 OR ( (Recinfo.run_sequence IS NULL)
249 AND (X_Run_Sequence IS NULL)))
250 AND ( (Recinfo.je_category_name = X_Je_Category_Name)
251 OR ( (Recinfo.je_category_name IS NULL)
252 AND (X_Je_Category_Name IS NULL)))
253 AND ( (Recinfo.full_allocation_flag = X_Full_Allocation_Flag)
254 OR ( (Recinfo.full_allocation_flag IS NULL)
255 AND (X_Full_Allocation_Flag IS NULL)))
256 AND ( (Recinfo.conversion_method_code = X_Conversion_Method_Code)
257 OR ( (Recinfo.conversion_method_code IS NULL)
258 AND (X_Conversion_Method_Code IS NULL)))
259 AND ( (Recinfo.currency_conversion_type = X_Currency_Conversion_Type)
260 OR ( (Recinfo.currency_conversion_type IS NULL)
261 AND (X_Currency_Conversion_Type IS NULL)))
262 AND ( (Recinfo.description = X_Description)
263 OR ( (Recinfo.description IS NULL)
264 AND (X_Description IS NULL)))
265 ) then
266 return;
267 else
268 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
269 APP_EXCEPTION.RAISE_EXCEPTION;
270 end if;
271 END Lock_Row;
272
273 PROCEDURE Update_Row(X_Rowid VARCHAR2,
274 X_Allocation_Formula_Id NUMBER,
275 X_Allocation_Batch_Id NUMBER,
276 X_Name VARCHAR2,
277 X_Run_Sequence NUMBER,
278 X_Je_Category_Name VARCHAR2,
279 X_Full_Allocation_Flag VARCHAR2,
280 X_Conversion_Method_Code VARCHAR2,
281 X_Currency_Conversion_Type VARCHAR2,
282 X_Last_Update_Date DATE,
283 X_Last_Updated_By NUMBER,
284 X_Last_Update_Login NUMBER,
285 X_Description VARCHAR2,
286 X_Actual_Flag VARCHAR2,
287 X_Transaction_Currency VARCHAR2,
288 Currency_Changed IN OUT NOCOPY VARCHAR2
289 ) IS
290 BEGIN
291 -- Make sure the user has completed all of the formula
292 -- lines.
293 IF (NOT gl_alloc_form_lines_pkg.complete_formula(
294 X_allocation_formula_id,
295 X_actual_flag)) THEN
296 IF (X_actual_flag = 'B') THEN
297 fnd_message.set_name('SQLGL', 'GL_COMPLETE_FOUR_FORMULA_LINES');
298 app_exception.raise_exception;
299 ELSE
300 fnd_message.set_name('SQLGL', 'GL_COMPLETE_ALL_FORMULA_LINES');
301 app_exception.raise_exception;
302 END IF;
303 END IF;
304
305 -- If the user has changed the currency, then update the lines
306 IF (gl_alloc_form_lines_pkg.currency_changed(
307 X_Allocation_Formula_Id,
308 X_Transaction_Currency)
309 OR X_Conversion_Method_Code = 'CV') THEN
310 gl_alloc_form_lines_pkg.update_currency(
311 X_Allocation_Formula_id,
312 X_Transaction_Currency,
313 X_Conversion_Method_Code);
314 Currency_Changed := 'Y';
315 ELSE
316 Currency_Changed := 'N';
317 END IF;
318
319 -- Make sure the ledger segment of target and offset lines are the same
320 check_ledger_selection(X_Allocation_Formula_Id);
321
322 IF (Currency_Changed = 'Y') THEN
323 -- Make sure there isn't a currency conflict
324 gl_alloc_form_lines_pkg.check_target_ledger(X_Allocation_Formula_Id);
325 END IF;
326
327 UPDATE GL_ALLOC_FORMULAS
328 SET
329 allocation_formula_id = X_Allocation_Formula_Id,
330 allocation_batch_id = X_Allocation_Batch_Id,
331 name = X_Name,
332 run_sequence = X_Run_Sequence,
333 je_category_name = X_Je_Category_Name,
334 full_allocation_flag = X_Full_Allocation_Flag,
335 conversion_method_code = X_Conversion_Method_Code,
336 currency_conversion_type = X_Currency_Conversion_Type,
337 last_update_date = X_Last_Update_Date,
338 last_updated_by = X_Last_Updated_By,
339 last_update_login = X_Last_Update_Login,
340 description = X_Description
341 WHERE rowid = X_rowid;
342
343 if (SQL%NOTFOUND) then
344 RAISE NO_DATA_FOUND;
345 end if;
346
347 END Update_Row;
348
349 PROCEDURE Delete_Row(Allocation_formula_id NUMBER, X_Rowid VARCHAR2) IS
350 BEGIN
351
352 -- Delete all of the associated formula lines
353 gl_alloc_form_lines_pkg.delete_rows(
354 allocation_formula_id);
355
356 -- Delete the formula
357 DELETE FROM GL_ALLOC_FORMULAS
358 WHERE rowid = X_Rowid;
359
360 if (SQL%NOTFOUND) then
361 RAISE NO_DATA_FOUND;
362 end if;
363 END Delete_Row;
364
365 END gl_alloc_formulas_pkg;
[Home] [Help]