[Home] [Help]
PACKAGE BODY: APPS.GL_AUTOREVERSE_OPTIONS_PKG
Source
1 PACKAGE BODY GL_AUTOREVERSE_OPTIONS_PKG as
2 /* $Header: glistarb.pls 120.7 2004/10/14 23:08:09 spala ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7 --
8
9
10 --
11 -- PUBLIC FUNCTIONS
12 --
13
14 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
15 X_criteria_set_Id NUMBER,
16 X_Je_Category_Name VARCHAR2,
17 X_Method_Code VARCHAR2,
18 X_Reversal_Period_Code VARCHAR2,
19 X_Reversal_Date_Code VARCHAR2,
20 X_Autoreverse_Flag VARCHAR2,
21 X_Autopost_Reversal_Flag VARCHAR2,
22 X_Attribute1 VARCHAR2,
23 X_Attribute2 VARCHAR2,
24 X_Attribute3 VARCHAR2,
25 X_Attribute4 VARCHAR2,
26 X_Attribute5 VARCHAR2,
27 X_Attribute6 VARCHAR2,
28 X_Attribute7 VARCHAR2,
29 X_Attribute8 VARCHAR2,
30 X_Attribute9 VARCHAR2,
31 X_Attribute10 VARCHAR2,
32 X_Attribute11 VARCHAR2,
33 X_Attribute12 VARCHAR2,
34 X_Attribute13 VARCHAR2,
35 X_Attribute14 VARCHAR2,
36 X_Attribute15 VARCHAR2,
37 X_Context VARCHAR2
38 ) IS
39
40 CURSOR C IS
41 SELECT *
42 FROM gl_autoreverse_options
43 WHERE rowid = X_Rowid
44 FOR UPDATE of criteria_set_Id NOWAIT;
45
46 Recinfo C%ROWTYPE;
47
48 BEGIN
49 OPEN C;
50 FETCH C INTO Recinfo;
51 if (C%NOTFOUND) then
52 CLOSE C;
53 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
54 APP_EXCEPTION.Raise_Exception;
55 end if;
56 CLOSE C;
57 if (
58 (Recinfo.criteria_set_id = X_criteria_set_Id)
59 AND (Recinfo.je_category_name = X_Je_Category_Name)
60 AND (Recinfo.method_code = X_Method_Code)
61 AND (Recinfo.reversal_period_code = X_Reversal_Period_Code)
62 AND (Recinfo.reversal_date_code = X_Reversal_Date_Code
63 OR ( (Recinfo.reversal_date_code IS NULL)
64 AND (X_Reversal_Date_Code IS NULL)))
65 AND (Recinfo.autoreverse_flag = X_Autoreverse_Flag)
66 AND (Recinfo.autopost_reversal_flag = X_Autopost_Reversal_Flag)
67 AND ( (Recinfo.attribute1 = X_Attribute1)
68 OR ( (Recinfo.attribute1 IS NULL)
69 AND (X_Attribute1 IS NULL)))
70 AND ( (Recinfo.attribute2 = X_Attribute2)
71 OR ( (Recinfo.attribute2 IS NULL)
72 AND (X_Attribute2 IS NULL)))
73 AND ( (Recinfo.attribute3 = X_Attribute3)
74 OR ( (Recinfo.attribute3 IS NULL)
75 AND (X_Attribute3 IS NULL)))
76 AND ( (Recinfo.attribute4 = X_Attribute4)
77 OR ( (Recinfo.attribute4 IS NULL)
78 AND (X_Attribute4 IS NULL)))
79 AND ( (Recinfo.attribute5 = X_Attribute5)
80 OR ( (Recinfo.attribute5 IS NULL)
81 AND (X_Attribute5 IS NULL)))
82 AND ( (Recinfo.attribute6 = X_Attribute6)
83 OR ( (Recinfo.attribute6 IS NULL)
84 AND (X_Attribute6 IS NULL)))
85 AND ( (Recinfo.attribute7 = X_Attribute7)
86 OR ( (Recinfo.attribute7 IS NULL)
87 AND (X_Attribute7 IS NULL)))
88 AND ( (Recinfo.attribute8 = X_Attribute8)
89 OR ( (Recinfo.attribute8 IS NULL)
90 AND (X_Attribute8 IS NULL)))
91 AND ( (Recinfo.attribute9 = X_Attribute9)
92 OR ( (Recinfo.attribute9 IS NULL)
93 AND (X_Attribute9 IS NULL)))
94 AND ( (Recinfo.attribute10 = X_Attribute10)
95 OR ( (Recinfo.attribute10 IS NULL)
96 AND (X_Attribute10 IS NULL)))
97 AND ( (Recinfo.attribute11 = X_Attribute11)
98 OR ( (Recinfo.attribute11 IS NULL)
99 AND (X_Attribute11 IS NULL)))
100 AND ( (Recinfo.attribute12 = X_Attribute12)
101 OR ( (Recinfo.attribute12 IS NULL)
102 AND (X_Attribute12 IS NULL)))
103 AND ( (Recinfo.attribute13 = X_Attribute13)
104 OR ( (Recinfo.attribute13 IS NULL)
105 AND (X_Attribute13 IS NULL)))
106 AND ( (Recinfo.attribute14 = X_Attribute14)
107 OR ( (Recinfo.attribute14 IS NULL)
108 AND (X_Attribute14 IS NULL)))
109 AND ( (Recinfo.attribute15 = X_Attribute15)
110 OR ( (Recinfo.attribute15 IS NULL)
111 AND (X_Attribute15 IS NULL)))
112 AND ( (Recinfo.context = X_Context)
113 OR ( (Recinfo.context IS NULL)
114 AND (X_Context IS NULL)))
115 ) then
116 return;
117 else
118 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
119 APP_EXCEPTION.Raise_Exception;
120 end if;
121 END Lock_Row;
122
123
124 PROCEDURE Update_Row(X_Rowid VARCHAR2,
125 X_criteria_set_Id NUMBER,
126 X_Je_Category_Name VARCHAR2,
127 X_Method_Code VARCHAR2,
128 X_Reversal_Period_Code VARCHAR2,
129 X_Reversal_Date_Code VARCHAR2,
130 X_Autoreverse_Flag VARCHAR2,
131 X_Autopost_Reversal_Flag VARCHAR2,
132 X_Last_Update_Date DATE,
133 X_Last_Updated_By NUMBER,
134 X_Last_Update_Login NUMBER,
135 X_Attribute1 VARCHAR2,
136 X_Attribute2 VARCHAR2,
137 X_Attribute3 VARCHAR2,
138 X_Attribute4 VARCHAR2,
139 X_Attribute5 VARCHAR2,
140 X_Attribute6 VARCHAR2,
141 X_Attribute7 VARCHAR2,
142 X_Attribute8 VARCHAR2,
143 X_Attribute9 VARCHAR2,
144 X_Attribute10 VARCHAR2,
145 X_Attribute11 VARCHAR2,
146 X_Attribute12 VARCHAR2,
147 X_Attribute13 VARCHAR2,
148 X_Attribute14 VARCHAR2,
149 X_Attribute15 VARCHAR2,
150 X_Context VARCHAR2
151
152 ) IS
153 BEGIN
154 UPDATE gl_autoreverse_options
155 SET
156 criteria_set_id = X_criteria_set_Id,
157 je_category_name = X_Je_Category_Name,
158 method_code = X_Method_Code,
159 reversal_period_code = X_Reversal_Period_Code,
160 reversal_date_code = X_Reversal_Date_Code,
161 autoreverse_flag = X_Autoreverse_Flag,
162 autopost_reversal_flag = X_Autopost_Reversal_Flag,
163 last_update_date = X_Last_Update_Date,
164 last_updated_by = X_Last_Updated_By,
165 last_update_login = X_Last_Update_Login,
166 attribute1 = X_Attribute1,
167 attribute2 = X_Attribute2,
168 attribute3 = X_Attribute3,
169 attribute4 = X_Attribute4,
170 attribute5 = X_Attribute5,
171 attribute6 = X_Attribute6,
172 attribute7 = X_Attribute7,
173 attribute8 = X_Attribute8,
174 attribute9 = X_Attribute9,
175 attribute10 = X_Attribute10,
176 attribute11 = X_Attribute11,
177 attribute12 = X_Attribute12,
178 attribute13 = X_Attribute13,
179 attribute14 = X_Attribute14,
180 attribute15 = X_Attribute15,
181 context = X_Context
182 WHERE rowid = X_Rowid;
183
184 if (SQL%NOTFOUND) then
185 Raise NO_DATA_FOUND;
186 end if;
187 END Update_Row;
188
189 -- ************************************************************************
190 -- Called by Journal Category form
191 -- ************************************************************************
192 PROCEDURE insert_reversal_cat( x_je_category_name VARCHAR2,
193 x_created_by NUMBER,
194 x_last_updated_by NUMBER,
195 x_last_update_login NUMBER ) IS
196 CURSOR c_dup IS
197 SELECT 'Duplicate'
198 FROM gl_autoreverse_options
199 WHERE je_category_name = x_je_category_name ;
200 dummy VARCHAR2(100);
201
202 BEGIN
203 OPEN c_dup;
204 FETCH c_dup INTO dummy;
205
206 IF c_dup%NOTFOUND THEN
207 INSERT INTO gl_autoreverse_options (
208 criteria_set_id,
209 je_category_name,
210 method_code,
211 reversal_period_code,
212 autoreverse_flag,
213 autopost_reversal_flag,
214 last_update_date, last_updated_by,
215 created_by, creation_date,
216 last_update_login)
217 SELECT DISTINCT criteria_set_id,
218 x_je_category_name,
219 'S',
220 'NO_DEFAULT',
221 'N',
222 'N',
223 sysdate, x_last_updated_by,
224 x_created_by, sysdate,
225 x_last_update_login
226 FROM GL_AUTOREVERSE_OPTIONS;
227
228 END IF;
229
230 CLOSE c_dup;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
235 fnd_message.set_token('PROCEDURE',
236 'GL_JE_CATEGORIES_PKG.insert_reversal_cat');
237 RAISE;
238
239 END insert_reversal_cat;
240
241 -- ************************************************************************
242 -- Called from Ledger form
243 -- ************************************************************************
244
245 PROCEDURE insert_criteria_reversal_cat(
246 x_criteria_set_id NUMBER,
247 x_created_by NUMBER,
248 x_last_updated_by NUMBER,
249 x_last_update_login NUMBER ) IS
250 BEGIN
251 INSERT INTO gl_autoreverse_options (
252 criteria_set_id,
253 je_category_name,
254 method_code,
255 reversal_period_code,
256 reversal_date_code,
257 autoreverse_flag,
258 autopost_reversal_flag,
259 last_update_date, last_updated_by,
260 created_by, creation_date,
261 last_update_login)
262 SELECT x_criteria_set_id,
263 jc.je_category_name,
264 decode(jc.je_category_name,
265 'Revalue Profit/Loss', 'C',
266 'MRC Open Balances','C',
267 'Income Statement Close','C',
268 'Income Offset','C',
269 'S'),
270 decode(jc.je_category_name,
271 'Income Statement Close', 'SAME_PERIOD',
272 'Income Offset', 'SAME_PERIOD',
273 'Balance Sheet Close','NEXT_PERIOD',
274 'NO_DEFAULT'),
275 decode(jc.je_category_name,
276 'Income Statement Close', 'LAST_DAY',
277 'Income Offset', 'LAST_DAY',
278 'Balance Sheet Close','FIRST_DAY',
279 NULL) ,
280
281 'N',
282 'N',
283 sysdate, x_last_updated_by,
284 x_created_by, sysdate,
285 x_last_update_login
286 FROM GL_JE_CATEGORIES jc;
287
288
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
293 fnd_message.set_token('PROCEDURE',
294 'GL_AUTOREVERSE_OPTIONS_PKG.insert_ledger_reversal_cat');
295 RAISE;
296
297 END insert_criteria_reversal_cat;
298
299 END GL_AUTOREVERSE_OPTIONS_PKG;