[Home] [Help]
PACKAGE BODY: APPS.GL_ALLOC_HISTORY_PKG
Source
1 PACKAGE BODY gl_alloc_history_pkg AS
2 /* $Header: glimahib.pls 120.5 2005/05/05 01:17:09 kvora ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE period_last_run(batch_id NUMBER,
9 access_set_id NUMBER,
10 period_name IN OUT NOCOPY VARCHAR2,
11 calculation_eff_date IN OUT NOCOPY DATE,
12 journal_eff_date IN OUT NOCOPY DATE) IS
13 CURSOR get_period IS
14 SELECT H1.to_period_name, H1.calculation_effective_date,
15 H1.journal_effective_date
16 FROM GL_ALLOC_HISTORY H1
17 WHERE H1.ALLOCATION_BATCH_ID = batch_id
18 AND H1.ACCESS_SET_ID = access_set_id
19 AND H1.LAST_UPDATE_DATE
20 = (SELECT MAX(H2.LAST_UPDATE_DATE)
21 FROM GL_ALLOC_HISTORY H2
22 WHERE H2.ALLOCATION_BATCH_ID
23 = batch_id
24 AND H2.ACCESS_SET_ID
25 = access_set_id
26 AND RUN_STATUS = 'C')
27 AND RUN_STATUS = 'C';
28 BEGIN
29 OPEN get_period;
30 FETCH get_period INTO period_name, calculation_eff_date, journal_eff_date;
31 CLOSE get_period;
32
33 EXCEPTION
34 WHEN app_exceptions.application_exception THEN
35 RAISE;
36 WHEN OTHERS THEN
37 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
38 fnd_message.set_token('PROCEDURE',
39 'gl_alloc_history_pkg.period_last_run');
40 RAISE;
41 END period_last_run;
42
43
44 FUNCTION set_random_ledger_id (x_allocation_batch_id NUMBER,
45 x_line_selection VARCHAR2,
46 x_ledger_override_id NUMBER) RETURN NUMBER IS
47 CURSOR random_abc_ledger IS
48 SELECT ldg.ledger_id
49 FROM gl_alloc_formulas af,
50 gl_alloc_formula_lines afl,
51 gl_ledger_set_assignments lsa,
52 gl_ledgers ldg
53 WHERE af.allocation_batch_id = x_allocation_batch_id
54 AND afl.allocation_formula_id = af.allocation_formula_id
55 AND afl.line_number IN (1, 2, 3)
56 AND lsa.ledger_set_id (+) = nvl(afl.ledger_id, x_ledger_override_id)
57 AND sysdate BETWEEN
58 nvl(trunc(lsa.start_date), sysdate - 1)
59 AND nvl(trunc(lsa.end_date), sysdate + 1)
60 AND ldg.ledger_id = nvl(lsa.ledger_id,
61 nvl(afl.ledger_id, x_ledger_override_id))
62 AND ldg.object_type_code = 'L';
63
64 CURSOR random_to_ledger IS
65 SELECT ldg.ledger_id
66 FROM gl_alloc_formulas af,
67 gl_alloc_formula_lines afl,
68 gl_ledger_set_assignments lsa,
69 gl_ledgers ldg
70 WHERE af.allocation_batch_id = x_allocation_batch_id
71 AND afl.allocation_formula_id = af.allocation_formula_id
72 AND afl.line_number IN (4, 5)
73 AND lsa.ledger_set_id (+) = nvl(afl.ledger_id, x_ledger_override_id)
74 AND sysdate BETWEEN
75 nvl(trunc(lsa.start_date), sysdate - 1)
76 AND nvl(trunc(lsa.end_date), sysdate + 1)
77 AND ldg.ledger_id = nvl(lsa.ledger_id,
78 nvl(afl.ledger_id, x_ledger_override_id))
79 AND ldg.object_type_code = 'L';
80
81 CURSOR null_ledger_in_t_o IS
82 SELECT 'has null ledger'
83 FROM gl_alloc_formulas af,
84 gl_alloc_formula_lines afl
85 WHERE af.allocation_batch_id = x_allocation_batch_id
86 AND af.allocation_formula_id = afl.allocation_formula_id
87 AND afl.line_number IN (4, 5)
88 AND afl.ledger_id IS NULL;
89
90 random_id NUMBER;
91 BEGIN
92
93 IF (x_line_selection = 'ABC') THEN
94 OPEN random_abc_ledger;
95 FETCH random_abc_ledger INTO random_id;
96 CLOSE random_abc_ledger;
97 ELSIF (x_line_selection = 'TO') THEN
98 OPEN random_to_ledger;
99 FETCH random_to_ledger INTO random_id;
100 CLOSE random_to_ledger;
101 ELSE
102 random_id := -1;
103 END IF;
104
105 RETURN random_id;
106 END set_random_ledger_id;
107
108
109 FUNCTION validate_calc_effective_date (x_allocation_batch_id NUMBER,
110 x_check_date DATE,
111 x_selected_ced DATE) RETURN BOOLEAN IS
112 CURSOR ledger_ids (formula_id NUMBER) IS
113 SELECT ldg.ledger_id
114 FROM gl_alloc_formula_lines afl,
115 gl_ledgers ldg,
116 gl_ledger_set_assignments lsa
117 WHERE afl.allocation_formula_id = formula_id
118 AND afl.line_number IN (1, 2, 3)
119 AND afl.ledger_id IS NOT NULL
120 AND lsa.ledger_set_id(+) = afl.ledger_id
121 AND sysdate BETWEEN
122 nvl(trunc(lsa.start_date),sysdate - 1)
123 AND nvl(trunc(lsa.end_date), sysdate + 1)
124 AND ldg.ledger_id = nvl(lsa.ledger_id, afl.ledger_id)
125 AND ldg.object_type_code = 'L';
126
127 CURSOR formulas (num NUMBER) IS
128 SELECT allocation_formula_id
129 FROM gl_alloc_formulas
130 WHERE allocation_batch_id = x_allocation_batch_id
131 AND rownum <= num;
132
133 num_formula NUMBER;
134 profile_buffer VARCHAR2(80);
135 start_date DATE;
136 end_date DATE;
137 BEGIN
138 -- get profile option
139 FND_PROFILE.GET('GL_ALLOC_NUMBER_OF_FORMULAS_TO_VALIDATE', profile_buffer);
140 num_formula := to_number(profile_buffer);
141 if (num_formula IS NULL) then
142 num_formula := DEFAULT_NUM_FORMULAS_TO_CHECK;
143 end if;
144
145 FOR next_formula IN formulas(num_formula) LOOP
146 FOR next_rec IN ledger_ids(next_formula.allocation_formula_id) LOOP
147
148 -- for each ledger id found, check if the selected calculation
149 -- effective date falls in its never opened period
150 gl_period_statuses_pkg.get_calendar_range(next_rec.ledger_id,
151 start_date,
152 end_date);
153 if ( x_selected_ced > end_date
154 OR x_selected_ced < start_date) then
155 RETURN FALSE;
156 end if;
157
158 END LOOP;
159 END LOOP;
160
161 RETURN TRUE;
162 END validate_calc_effective_date;
163
164
165 FUNCTION allow_average_usage(x_allocation_batch_id NUMBER,
166 x_period_end_date DATE,
167 x_ledger_override_id NUMBER) RETURN BOOLEAN IS
168 CURSOR non_cons_ledgers (formula_id NUMBER) IS
169 SELECT 'non consolidation ledger exist'
170 FROM gl_alloc_formula_lines afl,
171 gl_ledgers ldg,
172 gl_ledger_set_assignments lsa
173 WHERE afl.allocation_formula_id = formula_id
174 AND afl.line_number IN (4,5)
175 AND lsa.ledger_set_id(+) = nvl(afl.ledger_id, x_ledger_override_id)
176 AND sysdate BETWEEN
177 nvl(trunc(lsa.start_date), sysdate - 1)
178 AND nvl(trunc(lsa.end_date), sysdate + 1)
179 AND ldg.ledger_id = decode(lsa.ledger_id, null,
180 nvl(afl.ledger_id,x_ledger_override_id),
181 lsa.ledger_id)
182 AND ldg.consolidation_ledger_flag = 'N'
183 AND ldg.object_type_code = 'L'
184 AND rownum < 2;
185
186 CURSOR formulas (num NUMBER) IS
187 SELECT allocation_formula_id
188 FROM gl_alloc_formulas
189 WHERE allocation_batch_id = x_allocation_batch_id
190 AND rownum <= num;
191
192 num_formula NUMBER;
193 profile_buffer VARCHAR2(80);
194 fid NUMBER;
195 dummy VARCHAR2(40);
196 BEGIN
197 FND_PROFILE.GET('GL_ALLOC_NUMBER_OF_FORMULAS_TO_VALIDATE', profile_buffer);
198 num_formula := to_number(profile_buffer);
199 if (num_formula IS NULL) then
200 num_formula := DEFAULT_NUM_FORMULAS_TO_CHECK;
201 end if;
202
203 FOR formula_rec IN formulas(num_formula) LOOP
204
205 OPEN non_cons_ledgers(formula_rec.allocation_formula_id);
206 FETCH non_cons_ledgers INTO dummy;
207 if (non_cons_ledgers%FOUND) then
208 CLOSE non_cons_ledgers;
209 RETURN FALSE;
210 else
211 CLOSE non_cons_ledgers;
212 end if;
213
214 END LOOP;
215
216 RETURN TRUE;
217 END allow_average_usage;
218
219 END gl_alloc_history_pkg;