1 PACKAGE BODY gl_alloc_formulas_pkg AS
2 /* $Header: glimafmb.pls 120.6.12010000.2 2009/07/13 06:23:24 sommukhe 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 X_Validation_Status VARCHAR2
133 ) IS
134 CURSOR C IS SELECT rowid FROM GL_ALLOC_FORMULAS
135 WHERE allocation_formula_id = X_Allocation_Formula_Id;
136
137 BEGIN
138
139 -- Get batch id if it was not provided
140 IF (x_allocation_batch_id IS NULL) THEN
141 x_allocation_batch_id := gl_alloc_batches_pkg.get_unique_id;
142 END IF;
143
144 -- Get formula id if it was not provided
145 IF (x_allocation_formula_id IS NULL) THEN
146 x_allocation_formula_id := gl_alloc_formulas_pkg.get_unique_id;
147 END IF;
148
149 -- Make sure the user has completed all of the formula
150 -- lines.
151 IF (NOT gl_alloc_form_lines_pkg.complete_formula(
152 X_allocation_formula_id,
153 X_actual_flag)) THEN
154 IF (X_actual_flag = 'B') THEN
155 fnd_message.set_name('SQLGL', 'GL_COMPLETE_FOUR_FORMULA_LINES');
156 app_exception.raise_exception;
157 ELSE
158 fnd_message.set_name('SQLGL', 'GL_COMPLETE_ALL_FORMULA_LINES');
159 app_exception.raise_exception;
160 END IF;
161 END IF;
162
163 -- Make sure the ledger segment of target and offset lines are the same
164 check_ledger_selection(X_Allocation_Formula_Id);
165
166 -- Make sure there isn't a currency conflict
167 gl_alloc_form_lines_pkg.check_target_ledger(X_Allocation_Formula_Id);
168
169 INSERT INTO GL_ALLOC_FORMULAS(
170 allocation_formula_id,
171 allocation_batch_id,
172 name,
173 run_sequence,
174 je_category_name,
175 full_allocation_flag,
176 validation_status,
177 conversion_method_code,
178 currency_conversion_type,
179 last_update_date,
180 last_updated_by,
181 creation_date,
182 created_by,
183 last_update_login,
184 description
185 ) VALUES (
186 X_Allocation_Formula_Id,
187 X_Allocation_Batch_Id,
188 X_Name,
189 X_Run_Sequence,
190 X_Je_Category_Name,
191 X_Full_Allocation_Flag,
192 X_Validation_Status,
193 X_Conversion_Method_Code,
194 X_Currency_Conversion_Type,
195 X_Last_Update_Date,
196 X_Last_Updated_By,
197 X_Creation_Date,
198 X_Created_By,
199 X_Last_Update_Login,
200 X_Description
201 );
202
203 OPEN C;
204 FETCH C INTO X_Rowid;
205 if (C%NOTFOUND) then
206 CLOSE C;
207 RAISE NO_DATA_FOUND;
208 end if;
209 CLOSE C;
210 END Insert_Row;
211
212 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
213 X_Allocation_Formula_Id NUMBER,
214 X_Allocation_Batch_Id NUMBER,
215 X_Name VARCHAR2,
216 X_Run_Sequence NUMBER,
217 X_Je_Category_Name VARCHAR2,
218 X_Full_Allocation_Flag VARCHAR2,
219 X_Conversion_Method_Code VARCHAR2,
220 X_Currency_Conversion_Type VARCHAR2,
221 X_Description VARCHAR2,
222 X_Validation_Status VARCHAR2
223 ) IS
224 CURSOR C IS
225 SELECT *
226 FROM GL_ALLOC_FORMULAS
227 WHERE rowid = X_Rowid
228 FOR UPDATE of Allocation_Formula_Id NOWAIT;
229 Recinfo C%ROWTYPE;
230 BEGIN
231 OPEN C;
232 FETCH C INTO Recinfo;
233 if (C%NOTFOUND) then
234 CLOSE C;
235 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 end if;
238 CLOSE C;
239 if (
240 ( (Recinfo.allocation_formula_id = X_Allocation_Formula_Id)
241 OR ( (Recinfo.allocation_formula_id IS NULL)
242 AND (X_Allocation_Formula_Id IS NULL)))
243 AND ( (Recinfo.allocation_batch_id = X_Allocation_Batch_Id)
244 OR ( (Recinfo.allocation_batch_id IS NULL)
245 AND (X_Allocation_Batch_Id IS NULL)))
246 AND ( (Recinfo.name = X_Name)
247 OR ( (Recinfo.name IS NULL)
248 AND (X_Name IS NULL)))
249 AND ( (Recinfo.run_sequence = X_Run_Sequence)
250 OR ( (Recinfo.run_sequence IS NULL)
251 AND (X_Run_Sequence IS NULL)))
252 AND ( (Recinfo.je_category_name = X_Je_Category_Name)
253 OR ( (Recinfo.je_category_name IS NULL)
254 AND (X_Je_Category_Name IS NULL)))
255 AND ( (Recinfo.full_allocation_flag = X_Full_Allocation_Flag)
256 OR ( (Recinfo.full_allocation_flag IS NULL)
257 AND (X_Full_Allocation_Flag IS NULL)))
258 AND ( (Recinfo.validation_status = X_Validation_Status)
259 OR ( (Recinfo.validation_status IS NULL)
260 AND (X_Validation_Status IS NULL)))
261 AND ( (Recinfo.conversion_method_code = X_Conversion_Method_Code)
262 OR ( (Recinfo.conversion_method_code IS NULL)
263 AND (X_Conversion_Method_Code IS NULL)))
264 AND ( (Recinfo.currency_conversion_type = X_Currency_Conversion_Type)
265 OR ( (Recinfo.currency_conversion_type IS NULL)
266 AND (X_Currency_Conversion_Type IS NULL)))
267 AND ( (Recinfo.description = X_Description)
268 OR ( (Recinfo.description IS NULL)
269 AND (X_Description IS NULL)))
270 ) then
271 return;
272 else
273 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
274 APP_EXCEPTION.RAISE_EXCEPTION;
275 end if;
276 END Lock_Row;
277
278 PROCEDURE Update_Row(X_Rowid VARCHAR2,
279 X_Allocation_Formula_Id NUMBER,
280 X_Allocation_Batch_Id NUMBER,
281 X_Name VARCHAR2,
282 X_Run_Sequence NUMBER,
283 X_Je_Category_Name VARCHAR2,
284 X_Full_Allocation_Flag VARCHAR2,
285 X_Conversion_Method_Code VARCHAR2,
286 X_Currency_Conversion_Type VARCHAR2,
287 X_Last_Update_Date DATE,
288 X_Last_Updated_By NUMBER,
289 X_Last_Update_Login NUMBER,
290 X_Description VARCHAR2,
291 X_Actual_Flag VARCHAR2,
292 X_Transaction_Currency VARCHAR2,
293 Currency_Changed IN OUT NOCOPY VARCHAR2,
294 X_Validation_Status VARCHAR2
295 ) IS
296 BEGIN
297 -- Make sure the user has completed all of the formula
298 -- lines.
299 IF (NOT gl_alloc_form_lines_pkg.complete_formula(
300 X_allocation_formula_id,
301 X_actual_flag)) THEN
302 IF (X_actual_flag = 'B') THEN
303 fnd_message.set_name('SQLGL', 'GL_COMPLETE_FOUR_FORMULA_LINES');
304 app_exception.raise_exception;
305 ELSE
306 fnd_message.set_name('SQLGL', 'GL_COMPLETE_ALL_FORMULA_LINES');
307 app_exception.raise_exception;
308 END IF;
309 END IF;
310
311 -- If the user has changed the currency, then update the lines
312 IF (gl_alloc_form_lines_pkg.currency_changed(
313 X_Allocation_Formula_Id,
314 X_Transaction_Currency)
315 OR X_Conversion_Method_Code = 'CV') THEN
316 gl_alloc_form_lines_pkg.update_currency(
317 X_Allocation_Formula_id,
318 X_Transaction_Currency,
319 X_Conversion_Method_Code);
320 Currency_Changed := 'Y';
321 ELSE
322 Currency_Changed := 'N';
323 END IF;
324
325 -- Make sure the ledger segment of target and offset lines are the same
326 check_ledger_selection(X_Allocation_Formula_Id);
327
328 IF (Currency_Changed = 'Y') THEN
329 -- Make sure there isn't a currency conflict
330 gl_alloc_form_lines_pkg.check_target_ledger(X_Allocation_Formula_Id);
331 END IF;
332
333 UPDATE GL_ALLOC_FORMULAS
334 SET
335 allocation_formula_id = X_Allocation_Formula_Id,
336 allocation_batch_id = X_Allocation_Batch_Id,
337 name = X_Name,
338 run_sequence = X_Run_Sequence,
339 je_category_name = X_Je_Category_Name,
340 full_allocation_flag = X_Full_Allocation_Flag,
341 validation_status = X_Validation_Status,
342 conversion_method_code = X_Conversion_Method_Code,
343 currency_conversion_type = X_Currency_Conversion_Type,
344 last_update_date = X_Last_Update_Date,
345 last_updated_by = X_Last_Updated_By,
346 last_update_login = X_Last_Update_Login,
347 description = X_Description
348 WHERE rowid = X_rowid;
349
350 if (SQL%NOTFOUND) then
351 RAISE NO_DATA_FOUND;
352 end if;
353
354 END Update_Row;
355
356 PROCEDURE Delete_Row(Allocation_formula_id NUMBER, X_Rowid VARCHAR2) IS
357 BEGIN
358
359 -- Delete all of the associated formula lines
360 gl_alloc_form_lines_pkg.delete_rows(
361 allocation_formula_id);
362
363 -- Delete the formula
364 DELETE FROM GL_ALLOC_FORMULAS
365 WHERE rowid = X_Rowid;
366
367 if (SQL%NOTFOUND) then
368 RAISE NO_DATA_FOUND;
369 end if;
370 END Delete_Row;
371
372 END gl_alloc_formulas_pkg;