1 PACKAGE BODY hri_bpl_asg_summarization AS
2 /* $Header: hribasum.pkb 120.0 2005/10/05 22:32:00 anmajumd noship $ */
3 --
4 c_summarization_rqd_ff_name CONSTANT VARCHAR2(30):= 'HRI_MAP_ASG_SUMMARIZATION';
5 --
6 g_summarization_rqd_ff_id NUMBER;
7 --
8 /* Type of caching record to store the output of fast formula, */
9 /* By using the outputs in this records, the number of fast formula */
10 /* calls will reduce */
11
12 TYPE ff_output_rec IS RECORD
13 (summarization_rqd VARCHAR2(1)
14 );
15
16 TYPE g_ff_ouptut_tab_type IS TABLE OF ff_output_rec INDEX BY VARCHAR2(480);
17
18 g_summarization_rqd_cache g_ff_ouptut_tab_type;
19
20 -- -----------------------------------------------------------------------------
21 -- Inserts row into concurrent program log
22 -- -----------------------------------------------------------------------------
23 --
24 PROCEDURE output(p_text VARCHAR2) IS
25 --
26 BEGIN
27 --
28 HRI_BPL_CONC_LOG.output(p_text);
29 --
30 END output;
31 -- -----------------------------------------------------------------------------
32 -- Inserts row into concurrent program log if debugging is enabled
33 -- -----------------------------------------------------------------------------
34 --
35 PROCEDURE dbg(p_text VARCHAR2) IS
36 --
37 BEGIN
38 --
39 HRI_BPL_CONC_LOG.dbg(p_text);
40 --
41 END dbg;
42 --
43 --
44 -- -------------------------------------------------------------------------
45 -- Checks that the fast formula exist in the proper business group and
46 -- is compiled
47 -- -------------------------------------------------------------------------
48 --
49 FUNCTION ff_exists_and_compiled(p_business_group_id IN NUMBER
50 ,p_date IN DATE
51 ,p_ff_name IN VARCHAR2)
52 RETURN NUMBER
53 IS
54 --
55 -- Cursor to fetch assignment summarization fast formula
56 --
57 CURSOR c_summarization_rqd_formula IS
58 SELECT fff.formula_id
59 FROM
60 ff_formulas_f fff
61 ,ff_formula_types fft
62 WHERE fft.formula_type_name = 'QuickPaint'
63 AND fff.formula_type_id = fft.formula_type_id
64 AND fff.business_group_id = p_business_group_id
65 AND p_date BETWEEN fff.effective_start_date AND fff.effective_end_date
66 AND fff.formula_name = p_ff_name;
67 --
68 l_ff_id NUMBER;
69 --
70 BEGIN
71 --
72 -- Check if the fast formula exists
73 --
74 OPEN c_summarization_rqd_formula;
75 FETCH c_summarization_rqd_formula INTO l_ff_id;
76 CLOSE c_summarization_rqd_formula;
77 --
78 -- If the fast formula is not available then return null
79 --
80 IF l_ff_id IS NULL THEN
81 --
82 RETURN NULL;
83 --
84 END IF;
85 --
86 hri_bpl_abv.CheckFastFormulaCompiled(p_formula_id => l_ff_id,
87 p_bgttyp => p_business_group_id);
88 --
89 -- If no exception is raised then return the fast formula
90 --
91 RETURN l_ff_id;
92 --
93 EXCEPTION
94 --
95 -- Handling the case when the fast formula is not compiled
96 --
97 WHEN hri_bpl_abv.ff_not_compiled THEN
98 --
99 RAISE;
100 --
101 END ff_exists_and_compiled;
102 --
103 -- ----------------------------------------------------------------------------
104 -- Returns the fast formula id
105 -- ----------------------------------------------------------------------------
106 --
107 FUNCTION get_summarization_rqd_ff_id
108 RETURN NUMBER
109 IS
110 --
111 BEGIN
112 --
113 -- Check if the formula_id is already cached
114 --
115 IF g_summarization_rqd_ff_id IS NULL THEN
116 --
117 g_summarization_rqd_ff_id := ff_exists_and_compiled
118 (p_business_group_id => 0
119 ,p_date => trunc(SYSDATE)
120 ,p_ff_name => c_summarization_rqd_ff_name
121 );
122 --
123 IF (g_summarization_rqd_ff_id IS NULL) AND (g_warning_flag = 'N') THEN
124 --
125 g_warning_flag := 'Y';
126 --
127 output('The fast formula' || ' ' || c_summarization_rqd_ff_name || ' ' || 'is not defined in business_group_id = 0');
128 --
129 RETURN g_summarization_rqd_ff_id;
130 --
131 END IF;
132 --
133 END IF;
134 --
135 RETURN g_summarization_rqd_ff_id;
136 --
137 END get_summarization_rqd_ff_id;
138 --
139 -- ----------------------------------------------------------------------------
140 -- Runs the fast formula and gets the result.
141 -- ----------------------------------------------------------------------------
142
143 PROCEDURE run_summarization_rqd_rule(p_business_group_id IN NUMBER,
144 p_assignment_id IN NUMBER,
145 p_effective_date IN DATE,
146 p_summarization_rqd OUT NOCOPY VARCHAR2
147 )
148 IS
149 --
150 l_ff_id NUMBER;
151 l_inputs FF_EXEC.INPUTS_T;
152 l_outputs FF_EXEC.OUTPUTS_T;
153 l_bg_name PER_BUSINESS_GROUPS.NAME%TYPE;
154 --
155 CURSOR c_bg_name IS
156 SELECT name
157 FROM per_business_groups
158 WHERE business_group_id = p_business_group_id;
159 --
160 BEGIN
161 --
162 -- Identify the formula to be executed
163 --
164 l_ff_id := get_summarization_rqd_ff_id;
165 --
166 -- In case a formula is not defined then return 'Y'
167 --
168 IF l_ff_id IS NULL THEN
169 --
170 p_summarization_rqd := 'Y';
171 --
172 RETURN;
173 --
174 END IF;
175 --
176 -- If the assignment id is available in the cache, then return the value stored in the
177 -- cache instead of calling fast formula
178 --
179 -- BEGIN
180 --
181 -- p_summarization_rqd := g_summarization_rqd_cache(p_assignment_id || p_effective_date).summarization_rqd;
182 --
183 -- RETURN;
184 --
185 -- EXCEPTION
186 --
187 -- WHEN OTHERS THEN
188 --
189 -- NULL;
190 --
191 -- END;
192 --
193 --
194 -- Initialize the formula input and output tables */
195 --
196 FF_Exec.Init_Formula
197 (l_ff_id
198 ,SYSDATE
199 ,l_inputs
200 ,l_outputs
201 );
202 --
203 -- Set the input values
204 --
205 IF l_inputs.count > 0 THEN
206 --
207 FOR l_loop_count IN l_inputs.FIRST..l_inputs.LAST LOOP
208 --
209 -- CODE the inputs here
210 --
211 IF l_inputs(l_loop_count).name = 'DATE_EARNED' THEN
212 --
213 --
214 l_inputs(l_loop_count).value := fnd_date.date_to_canonical(SYSDATE);
215 --
216 ELSIF upper(l_inputs(l_loop_count).name) = 'ASSIGNMENT_ID' THEN
217 --
218 l_inputs(l_loop_count).value := p_assignment_id;
219 --
220 ELSIF l_inputs(l_loop_count).name = 'EFFECTIVE_DATE' THEN
221 --
222 l_inputs(l_loop_count).value := fnd_date.date_to_canonical(p_effective_date);
223 --
224 ELSIF upper(l_inputs(l_loop_count).name) = 'BUSINESS_GROUP_NAME' THEN
225 --
226 OPEN c_bg_name;
227 FETCH c_bg_name into l_bg_name;
228 CLOSE c_bg_name;
229 --
230 l_inputs(l_loop_count).value := l_bg_name;
231 --
232 END IF;
233
234 --
235 END LOOP;
236 --
237 END IF;
238 --
239 -- Run the fast formula
240 --
241 FF_Exec.Run_Formula
242 (l_inputs
243 ,l_outputs
244 );
245 --
246 -- Get the output from the fast formula
247 --
248 IF l_outputs.count > 0 THEN
249 --
250 FOR l_loop_count IN l_outputs.FIRST..l_outputs.LAST LOOP
251 --
252 -- CODE the outputs here
253 --
254 IF upper(l_outputs(l_loop_count).name) = 'INCLUDE_IN_REPORTS' THEN
255 --
256 p_summarization_rqd := l_outputs(l_loop_count).value;
257 --
258 END IF;
259 --
260 END LOOP;
261 --
262 END IF;
263 --
264 -- Store the values in cache
265 --
266 -- g_summarization_rqd_cache(p_assignment_id || p_effective_date).summarization_rqd := NVL(p_summarization_rqd,'Y');
267 --
268 END run_summarization_rqd_rule;
269
270 --
271 -- ----------------------------------------------------------------------------
272 -- Retuns N, if summarization is not required, Else, returns Y
273 -- ----------------------------------------------------------------------------
274 --
275 FUNCTION is_summarization_rqd(p_assignment_id IN NUMBER,
276 p_effective_date IN DATE)
277 RETURN VARCHAR2
278 IS
279 --
280 l_summarization_rqd VARCHAR2(1);
281 --
282 BEGIN
283 --
284 -- Call to run the fast formula to know if the assignment needs to be summarized
285 --
286 run_summarization_rqd_rule
287 (p_business_group_id => 0,
288 p_assignment_id => p_assignment_id,
289 p_effective_date => p_effective_date,
290 p_summarization_rqd => l_summarization_rqd);
291 --
292 RETURN l_summarization_rqd;
293 --
294 END is_summarization_rqd;
295 --
296 END hri_bpl_asg_summarization;
[Home] [Help]