1 PACKAGE BODY GLXJEENT_PKG as
2 /* $Header: glfjeenb.pls 120.8 2004/07/14 21:54:08 djogg ship $ */
3
4 PROCEDURE cache_data( acc_id NUMBER,
5 default_ledger_id NUMBER,
6 form_mode VARCHAR2,
7 default_je_source IN OUT NOCOPY VARCHAR2,
8 user_default_je_source IN OUT NOCOPY VARCHAR2,
9 journal_approval_flag IN OUT NOCOPY VARCHAR2,
10 default_je_category IN OUT NOCOPY VARCHAR2,
11 user_default_je_category IN OUT NOCOPY VARCHAR2,
12 default_rev_change_sign_flag IN OUT NOCOPY VARCHAR2,
13 default_reversal_period IN OUT NOCOPY VARCHAR2,
14 default_reversal_date IN OUT NOCOPY DATE,
15 default_reversal_start_date IN OUT NOCOPY DATE,
16 default_reversal_end_date IN OUT NOCOPY DATE,
17 default_period_name IN OUT NOCOPY VARCHAR2,
18 default_start_date IN OUT NOCOPY DATE,
19 default_end_date IN OUT NOCOPY DATE,
20 default_eff_date IN OUT NOCOPY DATE,
21 default_period_year IN OUT NOCOPY NUMBER,
22 default_period_num IN OUT NOCOPY NUMBER,
23 default_conversion_type IN OUT NOCOPY VARCHAR2,
24 user_default_conversion_type IN OUT NOCOPY VARCHAR2,
25 user_fixed_conversion_type IN OUT NOCOPY VARCHAR2,
26 start_active_date IN OUT NOCOPY DATE,
27 end_active_date IN OUT NOCOPY DATE) IS
28
29 period_status VARCHAR2(1);
30 period_year NUMBER;
31 period_num NUMBER;
32 effective_date_rule_code VARCHAR2(1);
33 frozen_source_flag VARCHAR2(1);
34 tax_precision NUMBER;
35 tax_mau NUMBER;
36 period_code VARCHAR2(30);
37 date_code VARCHAR2(30);
38 autorev_flag VARCHAR2(1);
39 autopst_flag VARCHAR2(1);
40 rev_code VARCHAR2(30);
41 BEGIN
42
43 -- Get the source information
44 IF (form_mode = 'A') THEN
45 default_je_source := 'Manual';
46 ELSE
47 default_je_source := 'Encumbrance';
48 END IF;
49 gl_je_sources_pkg.select_columns(default_je_source,
50 user_default_je_source,
51 effective_date_rule_code,
52 frozen_source_flag,
53 journal_approval_flag);
54
55 -- Get the period information
56 IF ( (form_mode IN ('A', 'E'))
57 AND (default_ledger_id IS NOT NULL)) THEN
58 default_period_name := gl_period_statuses_pkg.default_actual_period(
59 acc_id, default_ledger_id);
60 IF (default_period_name IS NOT NULL) THEN
61 gl_period_statuses_pkg.select_columns(
62 101,
63 default_ledger_id,
64 default_period_name,
65 period_status,
66 default_start_date,
67 default_end_date,
68 default_period_num,
69 default_period_year);
70 END IF;
71 END IF;
72
73 IF (default_period_name IS NOT NULL) THEN
74 IF (trunc(sysdate) <= default_start_date) THEN
75 default_eff_date := default_start_date;
76 ELSIF (trunc(sysdate) >= default_end_date) THEN
77 default_eff_date := default_end_date;
78 ELSE
79 default_eff_date := trunc(sysdate);
80 END IF;
81 END IF;
82
83 -- Get the category information
84 IF (default_je_category IS NOT NULL) THEN
85 gl_je_categories_pkg.select_columns(default_je_category,
86 user_default_je_category);
87
88 IF ( (default_period_name IS NOT NULL)
89 AND (default_ledger_id IS NOT NULL)) THEN
90 BEGIN
91 gl_autoreverse_date_pkg.get_reversal_period_date(
92 X_Ledger_Id => default_ledger_id,
93 X_Je_Category => default_je_category,
94 X_Je_Source => 'Manual',
95 X_Je_Period_Name => default_period_name,
96 X_Je_Date => default_eff_date,
97 X_Reversal_Method => default_rev_change_sign_flag,
98 X_Reversal_Period => default_reversal_period,
99 X_Reversal_Date => default_reversal_date);
100
101 IF (default_reversal_period IS NOT NULL) THEN
102 gl_period_statuses_pkg.select_columns(
103 x_application_id => 101,
104 x_ledger_id => default_ledger_id,
105 x_period_name => default_reversal_period,
106 x_closing_status => period_status,
107 x_start_date => default_reversal_start_date,
108 x_end_date => default_reversal_end_date,
109 x_period_num => period_num,
110 x_period_year => period_year);
111 END IF;
112 EXCEPTION
113 WHEN OTHERS THEN
114 default_rev_change_sign_flag := '';
115 default_reversal_period := 'FAILED DEFAULT';
116 default_reversal_date := '';
117 default_reversal_start_date := '';
118 default_reversal_end_date := '';
119 END;
120 END IF;
121
122 IF ( (default_rev_change_sign_flag IS NULL)
123 AND (default_ledger_id IS NOT NULL)) THEN
124
125 gl_autoreverse_date_pkg.get_default_reversal_method(
126 X_Ledger_Id => default_ledger_id,
127 X_Category_Name => default_je_category,
128 X_Reversal_Method_Code => rev_code);
129 default_rev_change_sign_flag := rev_code;
130
131 END IF;
132 END IF;
133
134 -- Get the conversion type information
135 default_conversion_type := 'User';
136 gl_daily_conv_types_pkg.select_columns(
137 default_conversion_type,
138 user_default_conversion_type);
139
140 -- Get the fixed conversion type information
141 gl_daily_conv_types_pkg.select_columns(
142 'EMU FIXED',
143 user_fixed_conversion_type);
144
145 -- Get the range of valid dates
146 IF (default_ledger_id IS NOT NULL) THEN
147 gl_period_statuses_pkg.get_journal_range(
148 default_ledger_id,
149 start_active_date,
150 end_active_date);
151 END IF;
152 END cache_data;
153
154 PROCEDURE get_period(x_lgr_id NUMBER,
155 x_accounting_date DATE,
156 x_period_name IN OUT NOCOPY VARCHAR2,
157 x_period_status IN OUT NOCOPY VARCHAR2,
158 x_start_date IN OUT NOCOPY DATE,
159 x_end_date IN OUT NOCOPY DATE) IS
160
161 acct_cal_name VARCHAR2(15);
162 acc_period_type VARCHAR2(15);
163
164 BEGIN
165
166 -- Get the ledger information
167 SELECT period_set_name, accounted_period_type
168 INTO acct_cal_name, acc_period_type
169 FROM gl_ledgers
170 WHERE ledger_id = x_lgr_id;
171
172 SELECT ps.period_name, ps.closing_status, ps.start_date, ps.end_date
173 INTO x_period_name, x_period_status, x_start_date, x_end_date
174 FROM gl_date_period_map map, gl_period_statuses ps
175 WHERE map.period_set_name = acct_cal_name
176 AND map.period_type = acc_period_type
177 AND map.accounting_date = x_accounting_date
178 AND ps.application_id = 101
179 AND ps.ledger_id = x_lgr_id
180 AND ps.period_name = map.period_name;
181
182 END get_period;
183
184 FUNCTION is_prior_period(x_period_name VARCHAR2,
185 x_arg_type VARCHAR2,
186 x_arg_id NUMBER) RETURN BOOLEAN IS
187 dummy NUMBER;
188 BEGIN
189 IF (x_arg_type = 'L') THEN
190 BEGIN
191 SELECT 1
192 INTO dummy
193 FROM gl_period_statuses ps1
194 WHERE ps1.application_id = 101
195 AND ps1.ledger_id = x_arg_id
196 AND ps1.period_name = x_period_name
197 AND EXISTS
198 (SELECT 'later open'
199 FROM gl_period_statuses ps2
200 WHERE ps2.application_id = 101
201 AND ps2.ledger_id = ps1.ledger_id
202 AND ps2.effective_period_num > ps1.effective_period_num
203 AND ps2.closing_status IN ('O', 'C', 'P'));
204
205 return(TRUE);
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN(FALSE);
209 END;
210 ELSE
211 BEGIN
212 SELECT 1
213 INTO dummy
214 FROM dual
215 WHERE EXISTS
216 (SELECT 'not latest'
217 FROM gl_period_statuses ps1, gl_period_statuses ps2
218 WHERE ps1.application_id = 101
219 AND ps1.ledger_id IN (SELECT ledger_id
220 FROM gl_je_headers
221 WHERE je_batch_id = x_arg_id)
222 AND ps1.period_name = x_period_name
223 AND ps2.application_id = 101
224 AND ps2.ledger_id = ps1.ledger_id
225 AND ps2.effective_period_num > ps1.effective_period_num
226 AND ps2.closing_status IN ('O', 'C', 'P'));
227
228 return(TRUE);
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN
231 RETURN(FALSE);
232 END;
233 END IF;
234 END is_prior_period;
235
236 FUNCTION default_still_good(x_access_set_id NUMBER,
237 x_ledger_id NUMBER,
238 x_period_name VARCHAR2,
239 x_average_journal_flag VARCHAR2
240 ) RETURN VARCHAR2 IS
241 dummy VARCHAR2(100);
242 BEGIN
243 SELECT 'default good'
244 INTO dummy
245 FROM gl_ledgers lgr, gl_period_statuses ps, gl_access_set_ledgers acc
246 WHERE lgr.ledger_id = x_ledger_id
247 AND ( (x_average_journal_flag = 'N')
248 OR (lgr.consolidation_ledger_flag = 'Y'))
249 AND ps.application_id = 101
250 AND ps.ledger_id = lgr.ledger_id
251 AND ps.period_name = x_period_name
252 AND ps.closing_status IN ('O', 'F')
253 AND acc.access_set_id = x_access_set_id
254 AND acc.ledger_id = lgr.ledger_id
255 AND acc.access_privilege_code IN ('B', 'F')
256 AND ps.end_date between nvl(acc.start_date, ps.end_date-1)
257 and nvl(acc.end_date, ps.end_date+1);
258
259 RETURN('Y');
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 RETURN('N');
263 END default_still_good;
264
265
266 PROCEDURE default_actual_period(x_period_set_name VARCHAR2,
267 x_period_type VARCHAR2,
268 x_je_batch_id NUMBER,
269 period_name IN OUT NOCOPY VARCHAR2,
270 start_date IN OUT NOCOPY DATE,
271 end_date IN OUT NOCOPY DATE,
272 period_year IN OUT NOCOPY NUMBER,
273 period_num IN OUT NOCOPY NUMBER) IS
274 CURSOR get_latest_opened IS
275 SELECT period_name, start_date, end_date, period_year, period_num
276 FROM gl_periods per
277 WHERE period_set_name = x_period_set_name
278 AND period_type = x_period_type
279 AND NOT EXISTS
280 (SELECT 'unopened ledger'
281 FROM gl_je_headers jeh,
282 gl_period_statuses ps
283 WHERE jeh.je_batch_id = x_je_batch_id
284 AND (jeh.display_alc_journal_flag IS NULL
285 or jeh.display_alc_journal_flag = 'Y')
286 AND ps.application_id = 101
287 AND ps.ledger_id = jeh.ledger_id
288 AND ps.period_name = per.period_name
289 AND ps.closing_status <> 'O')
290 ORDER BY period_year * 10000 + period_num DESC;
291
292 CURSOR get_earliest_future_ent IS
293 SELECT period_name, start_date, end_date, period_year, period_num
294 FROM gl_periods per
295 WHERE period_set_name = x_period_set_name
296 AND period_type = x_period_type
297 AND NOT EXISTS
298 (SELECT 'unopened ledger'
299 FROM gl_je_headers jeh,
300 gl_period_statuses ps
301 WHERE jeh.je_batch_id = x_je_batch_id
302 AND (jeh.display_alc_journal_flag IS NULL
303 or jeh.display_alc_journal_flag = 'Y')
304 AND ps.application_id = 101
305 AND ps.ledger_id = jeh.ledger_id
306 AND ps.period_name = per.period_name
307 AND ps.closing_status NOT IN ('O', 'F'))
308 ORDER BY period_year * 10000 + period_num ASC;
309 default_period VARCHAR2(15);
310 BEGIN
311 OPEN get_latest_opened;
312 FETCH get_latest_opened INTO period_name, start_date, end_date,
313 period_year, period_num;
314
315 IF get_latest_opened%FOUND THEN
316 CLOSE get_latest_opened;
317 return;
318 ELSE
319 CLOSE get_latest_opened;
320
321 OPEN get_earliest_future_ent;
322 FETCH get_earliest_future_ent INTO period_name, start_date, end_date,
323 period_year, period_num;
324
325 IF get_earliest_future_ent%FOUND THEN
326 CLOSE get_earliest_future_ent;
327 return;
328 ELSE
329 CLOSE get_earliest_future_ent;
330 return;
331 END IF;
332 END IF;
333
334 EXCEPTION
335 WHEN app_exceptions.application_exception THEN
336 RAISE;
337 WHEN OTHERS THEN
338 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
339 fnd_message.set_token('PROCEDURE',
340 'gl_period_statuses_pkg.default_actual_period');
341 RAISE;
342 END default_actual_period;
343
344 PROCEDURE set_find_window_state(w_state VARCHAR2) IS
345
346 PRAGMA AUTONOMOUS_TRANSACTION;
347 BEGIN
348 -- set the profile option
349 IF (fnd_profile.save_user('GL_MJE_FIND_WINDOW_STATE', w_state)) THEN
350 NULL;
351 END IF;
352
353 COMMIT;
354 END set_find_window_state;
355
356 END GLXJEENT_PKG;
[Home] [Help]