[Home] [Help]
PACKAGE BODY: APPS.GL_CONSOLIDATION_PKG
Source
1 PACKAGE BODY GL_CONSOLIDATION_PKG as
2 /* $Header: glicostb.pls 120.13 2005/05/05 01:06:18 kvora ship $ */
3 --
4 -- PRIVATE FUNCTIONS
5 --
6
7 PROCEDURE Check_Same_Currency(X_To_Ledger_Id NUMBER,
8 X_From_Ledger_Id NUMBER) IS
9 CURSOR C3 IS
10 SELECT 1
11 FROM GL_LEDGERS ledger1
12 WHERE ledger1.ledger_id = X_From_Ledger_Id
13 AND ledger1.currency_code = (SELECT ledger2.currency_code
14 FROM GL_LEDGERS ledger2
15 WHERE ledger2.ledger_id = X_To_Ledger_Id);
16
17 dummy NUMBER;
18
19 BEGIN
20 OPEN C3;
21 FETCH C3 INTO dummy;
22
23 IF C3%NOTFOUND THEN
24 CLOSE C3;
25 fnd_message.set_name('SQLGL','GL_SAME_CURRENCY');
26 app_exception.raise_exception;
27 END IF;
28
29 CLOSE C3;
30 END Check_Same_Currency;
31
32 --
33 -- PUBLIC FUNCTIONS
34 --
35
36 --** Added Security_Flag
37 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
38 X_Consolidation_Id IN OUT NOCOPY NUMBER,
39 X_Name VARCHAR2,
40 X_Coa_Mapping_Id NUMBER,
41 X_Last_Update_Date DATE,
42 X_Last_Updated_By NUMBER,
43 X_From_Ledger_Id NUMBER,
44 X_To_Ledger_Id NUMBER,
45 X_Creation_Date DATE,
46 X_Created_By NUMBER,
47 X_Last_Update_Login NUMBER,
48 X_Description VARCHAR2,
49 X_Method VARCHAR2,
50 X_From_Currency_Code VARCHAR2,
51 X_From_Location VARCHAR2,
52 X_From_Oracle_Id VARCHAR2,
53 X_Attribute1 VARCHAR2,
54 X_Attribute2 VARCHAR2,
55 X_Attribute3 VARCHAR2,
56 X_Attribute4 VARCHAR2,
57 X_Attribute5 VARCHAR2,
58 X_Context VARCHAR2,
59 X_Usage VARCHAR2,
60 X_Run_Journal_Import_Flag VARCHAR2,
61 X_Audit_Mode_Flag VARCHAR2,
62 X_Summarize_Lines_Flag VARCHAR2,
63 X_Run_Posting_Flag VARCHAR2,
64 X_Security_Flag VARCHAR2
65 ) IS
66 CURSOR C IS SELECT rowid FROM gl_consolidation
67 WHERE consolidation_id = X_Consolidation_Id;
68 BEGIN
69
70 -- Check that from and to ledgers have same funcional currency
71 if (X_Method = 'T') then
72 Check_Same_Currency(X_To_Ledger_Id, X_From_Ledger_Id);
73 end if;
74
75 INSERT INTO gl_consolidation(
76 consolidation_id,
77 name,
78 coa_mapping_id,
79 last_update_date,
80 last_updated_by,
81 from_ledger_id,
82 to_ledger_id,
83 creation_date,
84 created_by,
85 last_update_login,
86 description,
87 method,
88 from_currency_code,
89 from_location,
90 from_oracle_id,
91 attribute1,
92 attribute2,
93 attribute3,
94 attribute4,
95 attribute5,
96 context,
97 usage_code,
98 run_journal_import_flag,
99 audit_mode_flag,
100 summarize_lines_flag,
101 run_posting_flag,
102 security_flag
103 ) VALUES (
104 X_Consolidation_Id,
105 X_Name,
106 X_Coa_Mapping_Id,
107 X_Last_Update_Date,
108 X_Last_Updated_By,
109 X_From_Ledger_Id,
110 X_To_Ledger_Id,
111 X_Creation_Date,
112 X_Created_By,
113 X_Last_Update_Login,
114 X_Description,
115 X_Method,
116 X_From_Currency_Code,
117 X_From_Location,
118 X_From_Oracle_Id,
119 X_Attribute1,
120 X_Attribute2,
121 X_Attribute3,
122 X_Attribute4,
123 X_Attribute5,
124 X_Context,
125 X_Usage,
126 X_Run_Journal_Import_Flag,
127 X_Audit_Mode_Flag,
128 X_Summarize_Lines_Flag,
129 X_Run_Posting_Flag,
130 X_Security_Flag
131 );
132
133 OPEN C;
134 FETCH C INTO X_Rowid;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 RAISE NO_DATA_FOUND;
138 end if;
139 CLOSE C;
140 END Insert_Row;
141
142 --** Added Security_Flag
143 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
144 X_Consolidation_Id NUMBER,
145 X_Name VARCHAR2,
146 X_Coa_Mapping_Id NUMBER,
147 X_From_Ledger_Id NUMBER,
148 X_To_Ledger_Id NUMBER,
149 X_Description VARCHAR2,
150 X_Method VARCHAR2,
151 X_From_Currency_Code VARCHAR2,
152 X_From_Location VARCHAR2,
153 X_From_Oracle_Id VARCHAR2,
154 X_Attribute1 VARCHAR2,
155 X_Attribute2 VARCHAR2,
156 X_Attribute3 VARCHAR2,
157 X_Attribute4 VARCHAR2,
158 X_Attribute5 VARCHAR2,
159 X_Context VARCHAR2,
160 X_Usage VARCHAR2,
161 X_Security_Flag VARCHAR2
162 ) IS
163 CURSOR C IS
164 SELECT *
165 FROM gl_consolidation
166 WHERE rowid = X_Rowid
167 FOR UPDATE of Consolidation_Id NOWAIT;
168 Recinfo C%ROWTYPE;
169 BEGIN
170 OPEN C;
171 FETCH C INTO Recinfo;
172 if (C%NOTFOUND) then
173 CLOSE C;
174 RAISE NO_DATA_FOUND;
175 end if;
176 CLOSE C;
177 if (
178 ( (Recinfo.consolidation_id = X_Consolidation_Id)
179 OR ( (Recinfo.consolidation_id IS NULL)
180 AND (X_Consolidation_Id IS NULL)))
181 AND ( (Recinfo.name = X_Name)
182 OR ( (Recinfo.name IS NULL)
183 AND (X_Name IS NULL)))
184 AND ( (Recinfo.coa_mapping_id = X_Coa_Mapping_Id)
185 OR ( (Recinfo.coa_mapping_id IS NULL)
186 AND (X_Coa_Mapping_Id IS NULL)))
187 AND ( (Recinfo.from_ledger_id = X_From_Ledger_Id)
188 OR ( (Recinfo.from_ledger_id IS NULL)
189 AND (X_From_Ledger_Id IS NULL)))
190 AND ( (Recinfo.to_ledger_id = X_To_Ledger_Id)
191 OR ( (Recinfo.to_ledger_id IS NULL)
192 AND (X_To_Ledger_Id IS NULL)))
193 AND ( (Recinfo.description = X_Description)
194 OR ( (Recinfo.description IS NULL)
195 AND (X_Description IS NULL)))
196 AND ( (Recinfo.method = X_Method)
197 OR ( (Recinfo.method IS NULL)
198 AND (X_Method IS NULL)))
199 AND ( (Recinfo.from_currency_code = X_From_Currency_Code)
200 OR ( (Recinfo.from_currency_code IS NULL)
201 AND (X_From_Currency_Code IS NULL)))
202 AND ( (Recinfo.from_location = X_From_Location)
203 OR ( (Recinfo.from_location IS NULL)
204 AND (X_From_Location IS NULL)))
205 AND ( (Recinfo.from_oracle_id = X_From_Oracle_Id)
206 OR ( (Recinfo.from_oracle_id IS NULL)
207 AND (X_From_Oracle_Id IS NULL)))
208 AND ( (Recinfo.attribute1 = X_Attribute1)
209 OR ( (Recinfo.attribute1 IS NULL)
210 AND (X_Attribute1 IS NULL)))
211 AND ( (Recinfo.attribute2 = X_Attribute2)
212 OR ( (Recinfo.attribute2 IS NULL)
213 AND (X_Attribute2 IS NULL)))
214 AND ( (Recinfo.attribute3 = X_Attribute3)
215 OR ( (Recinfo.attribute3 IS NULL)
216 AND (X_Attribute3 IS NULL)))
217 AND ( (Recinfo.attribute4 = X_Attribute4)
218 OR ( (Recinfo.attribute4 IS NULL)
219 AND (X_Attribute4 IS NULL)))
220 AND ( (Recinfo.attribute5 = X_Attribute5)
221 OR ( (Recinfo.attribute5 IS NULL)
222 AND (X_Attribute5 IS NULL)))
223 AND ( (Recinfo.context = X_Context)
224 OR ( (Recinfo.context IS NULL)
225 AND (X_Context IS NULL)))
226 AND ( (Recinfo.usage_code = X_Usage)
227 OR ( (Recinfo.usage_code IS NULL)
228 AND (X_Usage IS NULL)))
229 AND ( (Recinfo.security_flag = X_Security_Flag)
230 OR ( (Recinfo.security_flag IS NULL)
231 AND (X_Security_Flag IS NULL)))
232 ) then
233 return;
234 else
235 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 end if;
238 END Lock_Row;
239
240 --** Added Security_Flag
241 PROCEDURE Update_Row(X_Rowid VARCHAR2,
242 X_Consolidation_Id NUMBER,
243 X_Name VARCHAR2,
244 X_Coa_Mapping_Id NUMBER,
245 X_Last_Update_Date DATE,
246 X_Last_Updated_By NUMBER,
247 X_From_Ledger_Id NUMBER,
248 X_To_Ledger_Id NUMBER,
249 X_Last_Update_Login NUMBER,
250 X_Description VARCHAR2,
251 X_Method VARCHAR2,
252 X_From_Currency_Code VARCHAR2,
253 X_From_Location VARCHAR2,
254 X_From_Oracle_Id VARCHAR2,
255 X_Attribute1 VARCHAR2,
256 X_Attribute2 VARCHAR2,
257 X_Attribute3 VARCHAR2,
258 X_Attribute4 VARCHAR2,
259 X_Attribute5 VARCHAR2,
260 X_Context VARCHAR2,
261 X_Usage VARCHAR2,
262 X_Run_Journal_Import_Flag VARCHAR2,
263 X_Audit_Mode_Flag VARCHAR2,
264 X_Summarize_Lines_Flag VARCHAR2,
265 X_Run_Posting_Flag VARCHAR2,
266 X_Security_Flag VARCHAR2
267 ) IS
268 BEGIN
269
270 -- Check that from and to ledgers have same funcional currency
271 if (X_Method = 'T') then
272 Check_Same_Currency(X_To_Ledger_Id, X_From_Ledger_Id);
273 end if;
274
275 UPDATE gl_consolidation
276 SET
277
278 consolidation_id = X_Consolidation_Id,
279 name = X_Name,
280 coa_mapping_id = X_Coa_Mapping_Id,
281 last_update_date = X_Last_Update_Date,
282 last_updated_by = X_Last_Updated_By,
283 from_ledger_id = X_From_Ledger_Id,
284 to_ledger_id = X_To_Ledger_Id,
285 last_update_login = X_Last_Update_Login,
286 description = X_Description,
287 method = X_Method,
288 from_currency_code = X_From_Currency_Code,
289 from_location = X_From_Location,
290 from_oracle_id = X_From_Oracle_Id,
291 attribute1 = X_Attribute1,
292 attribute2 = X_Attribute2,
293 attribute3 = X_Attribute3,
294 attribute4 = X_Attribute4,
295 attribute5 = X_Attribute5,
296 context = X_Context,
297 usage_code = X_Usage,
298 run_journal_import_flag = X_Run_Journal_Import_Flag,
299 audit_mode_flag = X_Audit_Mode_Flag,
300 summarize_lines_flag = X_Summarize_Lines_Flag,
301 run_posting_flag = X_Run_Posting_Flag,
302 security_flag = X_Security_Flag
303 WHERE rowid = X_rowid;
304
305 if (SQL%NOTFOUND) then
306 RAISE NO_DATA_FOUND;
307 end if;
308
309 END Update_Row;
310
311 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Consolidation_Id NUMBER) IS
312 BEGIN
313
314 DELETE FROM gl_consolidation
315 WHERE rowid = X_Rowid;
316
317 if (SQL%NOTFOUND) then
318 RAISE NO_DATA_FOUND;
319 end if;
320 END Delete_Row;
321
322 PROCEDURE Check_Unique_Name(X_Rowid VARCHAR2,
323 X_Name VARCHAR2) IS
324 CURSOR check_dups IS
325 SELECT 1
326 FROM GL_CONSOLIDATION glc
327 WHERE glc.name = X_Name
328 AND ( X_Rowid is NULL
329 OR glc.rowid <> X_Rowid);
330
331 dummy NUMBER;
332
333 BEGIN
334 OPEN check_dups;
335 FETCH check_dups INTO dummy;
336
337 IF check_dups%FOUND THEN
338 CLOSE check_dups;
339 fnd_message.set_name('SQLGL','GL_DUP_CONSOLIDATION_NAME');
340 app_exception.raise_exception;
341 END IF;
342
343 CLOSE check_dups;
344 END Check_Unique_Name;
345
346 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
347 X_Consolidation_Id NUMBER) IS
348 CURSOR C2 IS
349 SELECT 1
353 OR glc.rowid <> X_Rowid);
350 FROM GL_CONSOLIDATION glc
351 WHERE glc.consolidation_id = X_Consolidation_Id
352 AND ( X_Rowid is NULL
354
355 dummy NUMBER;
356
357 BEGIN
358 OPEN C2;
359 FETCH C2 INTO dummy;
360
361 IF C2%FOUND THEN
362 CLOSE C2;
363 fnd_message.set_name('SQLGL','GL_DUP_UNIQUE_ID');
364 fnd_message.set_token('TAB_S','GL_CONSOLIDATION_S');
365 app_exception.raise_exception;
366 END IF;
367
368 CLOSE C2;
369 END Check_Unique;
370
371 PROCEDURE Check_Mapping_Used_In_Sets( X_Consolidation_Id NUMBER,
372 X_Mapping_Used_In_Set IN OUT NOCOPY VARCHAR2) IS
373 CURSOR C4 IS
374 SELECT 'Y'
375 FROM DUAL
376 WHERE EXISTS
377 ( SELECT 'Mapping found in a mapping set'
378 FROM GL_CONS_SET_ASSIGNMENTS ASG
379 WHERE ASG.consolidation_id = X_Consolidation_Id
380 );
381
382 BEGIN
383 OPEN C4;
384 FETCH C4 INTO X_Mapping_Used_In_Set;
385
386 IF C4%FOUND THEN
387 X_Mapping_Used_In_Set := 'Y';
388 ELSE
389 X_Mapping_Used_In_Set := 'N';
390 END IF;
391
392 CLOSE C4;
393 END Check_Mapping_Used_In_Sets;
394
395 PROCEDURE Check_Mapping_Run( X_Consolidation_Id NUMBER,
396 X_Mapping_Has_Been_Run IN OUT NOCOPY VARCHAR2) IS
397 CURSOR C4 IS
398 SELECT 'Y'
399 FROM DUAL
400 WHERE EXISTS
401 ( SELECT 'Mapping has been run atleast once'
402 FROM GL_CONSOLIDATION_HISTORY COH
403 WHERE COH.consolidation_id = X_Consolidation_Id
404 );
405
406 BEGIN
407 OPEN C4;
408 FETCH C4 INTO X_Mapping_Has_Been_Run;
409
410 IF C4%FOUND THEN
411 X_Mapping_Has_Been_Run := 'Y';
412 ELSE
413 X_Mapping_Has_Been_Run := 'N';
414 END IF;
415
416 CLOSE C4;
417 END Check_Mapping_Run;
418
419 END GL_CONSOLIDATION_PKG;