1 PACKAGE BODY hri_bpl_fast_formula_util AS
2 /* $Header: hribuffl.pkb 120.1.12000000.2 2007/04/12 12:07:43 smohapat noship $ */
3
4 g_formula_type_id NUMBER;
5
6 -- -----------------------------------------------------------------------------
7 -- Sets Quickpaint FF type
8 -- -----------------------------------------------------------------------------
9 PROCEDURE set_ff_type_id(p_formula_type_name IN VARCHAR2) IS
10
11 BEGIN
12
13 SELECT formula_type_id INTO g_formula_type_id
14 FROM ff_formula_types
15 WHERE formula_type_name = p_formula_type_name;
16
17 END set_ff_type_id;
18
19 -- -----------------------------------------------------------------------------
20 -- Runs fast formula to determine event details
21 -- -----------------------------------------------------------------------------
22 PROCEDURE run_formula
23 (p_formula_id IN NUMBER,
24 p_input_tab IN formula_param_type,
25 p_output_tab OUT NOCOPY formula_param_type) IS
26
27 l_inputs FF_EXEC.INPUTS_T;
28 l_outputs FF_EXEC.OUTPUTS_T;
29 l_idx VARCHAR2(30);
30
31 BEGIN
32
33 -- Check formula exists
34 IF p_formula_id IS NOT NULL THEN
35
36 -- Run FF procedure initialization
37 FF_Exec.Init_Formula
38 (p_formula_id,
39 SYSDATE,
40 l_inputs,
41 l_outputs);
42
43 -- Populate input array
44 IF l_inputs.count > 0 THEN
45
46 FOR l_loop_count in l_inputs.first..l_inputs.last LOOP
47
48 -- Get input table index
49 l_idx := upper(l_inputs(l_loop_count).name);
50
51 -- If an input table entry exists, set the parameter accordingly
52 IF p_input_tab.EXISTS(l_idx) THEN
53 l_inputs(l_loop_count).value := p_input_tab(l_idx);
54 END IF;
55
56 END LOOP;
57
58 END IF;
59
60 -- Execute formula
61 FF_Exec.Run_Formula
62 (l_inputs,
63 l_outputs);
64
65 -- Translate results from output array to output table
66 IF l_outputs.count > 0 THEN
67
68 FOR l_loop_count in l_outputs.first..l_outputs.last LOOP
69
70 -- Set output table index
71 l_idx := upper(l_outputs(l_loop_count).name);
72
73 -- Translate result to table
74 p_output_tab(l_idx) := l_outputs(l_loop_count).value;
75
76 END LOOP;
77
78 END IF;
79
80 END IF;
81
82 END run_formula;
83
84 -- -----------------------------------------------------------------------------
85 -- Returns fast formula id from given business group
86 -- -----------------------------------------------------------------------------
87 FUNCTION fetch_bg_formula_id(p_formula_name IN VARCHAR2,
88 p_business_group_id IN NUMBER)
89 RETURN NUMBER IS
90
91 CURSOR bg_formula_csr IS
92 SELECT fff.formula_id
93 FROM ff_formulas_f fff
94 WHERE fff.formula_type_id = g_formula_type_id
95 AND fff.business_group_id = p_business_group_id
96 AND trunc(sysdate) BETWEEN fff.effective_start_date
97 AND fff.effective_end_date
98 AND fff.formula_name = p_formula_name;
99
100 CURSOR seeded_formula_csr IS
101 SELECT fff.formula_id
102 FROM ff_formulas_f fff
103 WHERE fff.formula_type_id = g_formula_type_id
104 AND fff.business_group_id IS NULL
105 AND trunc(sysdate) BETWEEN fff.effective_start_date
106 AND fff.effective_end_date
107 AND fff.formula_name = p_formula_name;
108
109 l_formula_id NUMBER;
110
111 BEGIN
112
113 IF (p_business_group_id IS NULL) THEN
114
115 OPEN seeded_formula_csr;
116 FETCH seeded_formula_csr INTO l_formula_id;
117 CLOSE seeded_formula_csr;
118
119 ELSE
120
121 OPEN bg_formula_csr;
122 FETCH bg_formula_csr INTO l_formula_id;
123 CLOSE bg_formula_csr;
124
125 END IF;
126
127 RETURN l_formula_id;
128
129 END fetch_bg_formula_id;
130
131 -- -----------------------------------------------------------------------------
132 -- Returns fast formula id from given business group
133 -- -----------------------------------------------------------------------------
134 FUNCTION fetch_bg_formula_id(p_formula_name IN VARCHAR2,
135 p_business_group_id IN NUMBER,
136 p_formula_type_name IN VARCHAR2)
137 RETURN NUMBER IS
138
139 l_formula_id NUMBER;
140
141 BEGIN
142
143 set_ff_type_id('Promotion');
144
145 l_formula_id := hri_bpl_fast_formula_util.fetch_bg_formula_id
146 (p_formula_name => p_formula_name,
147 p_business_group_id => p_business_group_id);
148
149 set_ff_type_id('QuickPaint');
150
151 RETURN l_formula_id;
152
153 END fetch_bg_formula_id;
154
155
156 -- -----------------------------------------------------------------------------
157 -- Returns fast formula id from setup business group
158 -- -----------------------------------------------------------------------------
159 FUNCTION fetch_setup_formula_id(p_formula_name IN VARCHAR2)
160 RETURN NUMBER IS
161
162 BEGIN
163
164 RETURN fetch_bg_formula_id
165 (p_formula_name => p_formula_name,
166 p_business_group_id => 0);
167
168 END fetch_setup_formula_id;
169
170 -- -----------------------------------------------------------------------------
171 -- Returns seeded fast formula id
172 -- -----------------------------------------------------------------------------
173 FUNCTION fetch_seeded_formula_id(p_formula_name IN VARCHAR2)
174 RETURN NUMBER IS
175
176 BEGIN
177
178 RETURN fetch_bg_formula_id
179 (p_formula_name => p_formula_name,
180 p_business_group_id => to_number(null));
181
182 END fetch_seeded_formula_id;
183
184 -- -----------------------------------------------------------------------------
185 -- Return fast formula id
186 -- -----------------------------------------------------------------------------
187 FUNCTION fetch_formula_id
188 (p_formula_name IN VARCHAR2,
189 p_business_group_id IN NUMBER,
190 p_bg_formula_name IN VARCHAR2 DEFAULT NULL,
191 p_setup_formula_name IN VARCHAR2 DEFAULT NULL,
192 p_seeded_formula_name IN VARCHAR2 DEFAULT NULL,
193 p_try_bg_formula IN VARCHAR2 DEFAULT 'Y',
194 p_try_setup_formula IN VARCHAR2 DEFAULT 'Y',
195 p_try_seeded_formula IN VARCHAR2 DEFAULT 'Y')
196 RETURN NUMBER IS
197
198 l_bg_formula_name VARCHAR2(30);
199 l_setup_formula_name VARCHAR2(30);
200 l_seeded_formula_name VARCHAR2(30);
201 l_formula_id NUMBER;
202
203 BEGIN
204
205 -- Set BG formula name
206 IF p_try_bg_formula = 'Y' AND p_bg_formula_name IS NULL THEN
207 l_bg_formula_name := p_formula_name;
208 ELSE
209 l_bg_formula_name := p_bg_formula_name;
210 END IF;
211
212 -- Set setup formula name
213 IF p_try_setup_formula = 'Y' AND p_setup_formula_name IS NULL THEN
214 l_setup_formula_name := p_formula_name;
215 ELSE
216 l_setup_formula_name := p_setup_formula_name;
217 END IF;
218
219 -- Set seeded formula name
220 IF p_try_seeded_formula = 'Y' AND p_seeded_formula_name IS NULL THEN
221 l_seeded_formula_name := p_formula_name;
222 ELSE
223 l_seeded_formula_name := p_seeded_formula_name;
224 END IF;
225
226 -- Test for BG formula
227 IF p_try_bg_formula = 'Y' AND
228 l_bg_formula_name IS NOT NULL THEN
229
230 l_formula_id := fetch_bg_formula_id
231 (p_formula_name => l_bg_formula_name,
232 p_business_group_id => p_business_group_id);
233 END IF;
234
235 -- If BG formula not found, try setup formula
236 IF l_formula_id IS NULL AND
237 p_try_setup_formula = 'Y' AND
238 l_setup_formula_name IS NOT NULL THEN
239
240 l_formula_id := fetch_setup_formula_id
241 (p_formula_name => l_setup_formula_name);
242
243 END IF;
244
245 -- If BG and setup formulas not found, try seeded formula
246 IF l_formula_id IS NULL AND
247 p_try_seeded_formula = 'Y' AND
248 l_seeded_formula_name IS NOT NULL THEN
249
250 l_formula_id := fetch_seeded_formula_id
251 (p_formula_name => l_seeded_formula_name);
252
253 END IF;
254
255 -- Return formula id
256 RETURN l_formula_id;
257
258 END fetch_formula_id;
259
260 -- -----------------------------------------------------------------------------
261 -- Initialize QuckPaint formula type
262 -- -----------------------------------------------------------------------------
263 BEGIN
264
265 set_ff_type_id('QuickPaint');
266
267 END hri_bpl_fast_formula_util;
[Home] [Help]