[Home] [Help]
PACKAGE BODY: APPS.BIS_GENERIC_PLANNER_PVT
Source
1 PACKAGE BODY BIS_GENERIC_PLANNER_PVT AS
2 /* $Header: BISVGPLB.pls 115.21 2003/01/27 13:35:23 mahrao ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVGPLB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for the Generic Planning Service
13 REM |
14 REM | NOTES |
15 REM | |
16 REM | HISTORY |
17 REM | APR-2000 irchen Creation |
18 REM | FEB-2002 sashaik Fix for 2193340. Actual value can be null. |
19 REM | 23-JAN-03 mahrao For having different local variables for IN and OUT
20 REM | parameters.
21 REM +=======================================================================+
22 */
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'BIS_GENERIC_PLANNER_PVT';
24
25 --
26 -- Procedures
27 --
28
29 Procedure Sync_All_Fields
30 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
31 , p_target_level_rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
32 , p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type
33 , p_target_owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
34 , p_actual_rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
35 , x_measure_instance IN OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
36 );
37
38 Procedure Service_Planner_Request
39 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
40 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
41 , p_alert_type IN VARCHAR2 := NULL
42 , p_alert_level IN VARCHAR2 := NULL
43 )
44 IS
45
46 l_measure_instance BIS_MEASURE_PUB.Measure_Instance_type;
47 l_dim_level_value_tbl BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type;
48 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
49 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
50 l_target_owners_rec BIS_TARGET_PUB.Target_Owners_Rec_Type;
51 l_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
52 l_comparison_result VARCHAR2(32000);
53
54 l_return_status VARCHAR2(32000);
55 l_msg_count NUMBER;
56 l_msg_data VARCHAR2(32000);
57 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
58 l_measure_instance_p BIS_MEASURE_PUB.Measure_Instance_type;
59
60 BEGIN
61
62 l_measure_instance := p_measure_instance;
63 l_dim_level_value_tbl := p_dim_level_value_tbl;
64
65 BIS_UTILITIES_PUB.put_line(p_text =>'Servicing planner request.');
66 BIS_UTILITIES_PUB.put_line(p_text =>'Target level id: '||l_measure_instance.Target_level_id);
67
68 /*
69 BIS_UTILITIES_PUB.put_line(p_text =>'VGPLB: org, id: '
70 ||l_dim_level_value_tbl(1).dimension_level_short_name||' - '
71 ||l_dim_level_value_tbl(1).dimension_level_value_id);
72
73 BIS_UTILITIES_PUB.put_line(p_text =>'VGPLB: time, id: '
74 ||l_dim_level_value_tbl(2).dimension_level_short_name||' - '
75 ||l_dim_level_value_tbl(2).dimension_level_value_id);
76
77 BIS_UTILITIES_PUB.put_line(p_text =>'VGPLB: dim1 id: '
78 ||l_dim_level_value_tbl(3).dimension_level_short_name||' - '
79 ||l_dim_level_value_tbl(3).dimension_level_value_id);
80
81 BIS_UTILITIES_PUB.put_line(p_text =>'VGPLB: dim2 id: '
82 ||l_dim_level_value_tbl(4).dimension_level_short_name||' - '
83 ||l_dim_level_value_tbl(4).dimension_level_value_id);
84 */
85
86 -- Retrieve Performance Target information
87 --
88 IF ((BIS_UTILITIES_PUB.Value_Missing(l_measure_instance.Target_level_id)
89 = FND_API.G_TRUE)
90 OR (BIS_UTILITIES_PUB.Value_Null(l_measure_instance.Target_level_id)
91 = FND_API.G_TRUE))
92 THEN
93 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Level
94 ( p_measure_instance => l_measure_instance
95 , p_dim_level_value_tbl => l_dim_level_value_tbl
96 , p_all_info => FND_API.G_FALSE
97 , x_target_level_rec => l_target_level_rec
98 );
99 l_measure_instance_p := l_measure_instance;
100 Sync_All_Fields
101 ( p_measure_instance => l_measure_instance_p
102 , p_target_level_rec => l_target_level_rec
103 , p_target_rec => l_Target_Rec
104 , p_target_owners_rec => l_Target_Owners_Rec
105 , p_actual_rec => l_Actual_Rec
106 , x_measure_instance => l_measure_instance
107 );
108 BIS_UTILITIES_PUB.put_line(p_text =>'Target level short name: '
109 ||l_measure_instance.Target_Level_Short_Name);
110 ELSE
111 BIS_PMF_DATA_SOURCE_PVT.Form_Target_Level_rec
112 ( p_measure_instance => l_measure_instance
113 , p_dim_level_value_tbl => l_dim_level_value_tbl
114 , x_target_level_rec => l_Target_Level_Rec
115 );
116 END IF;
117
118 -- Request target information
119 --
120 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target
121 ( p_measure_instance => l_measure_instance
122 , p_dim_level_value_tbl => l_dim_level_value_tbl
123 , p_all_info => FND_API.G_FALSE
124 , x_target_rec => l_target_rec
125 );
126 BIS_UTILITIES_PUB.put_line(p_text =>'Planner retrieved target: '||l_target_rec.target);
127
128 -- Retrieve Performance Actual information
129 --
130 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Actual
131 ( p_measure_instance => l_measure_instance
132 , p_dim_level_value_tbl => l_dim_level_value_tbl
133 , p_all_info => FND_API.G_FALSE
134 , x_actual_rec => l_actual_rec
135 );
136 BIS_UTILITIES_PUB.put_line(p_text =>'Planner retrieved actual: '||l_actual_rec.actual);
137
138 -- post actual
139 --
140 IF (BIS_UTILITIES_PUB.Value_Not_Missing(l_actual_rec.Actual) = FND_API.G_TRUE)
141 -- OR (BIS_UTILITIES_PUB.Value_Not_Null(l_actual_rec.Actual) = FND_API.G_TRUE))
142 THEN
143 BIS_ACTUAL_PUB.Post_Actual
144 ( p_api_version => 1.0
145 , p_commit => FND_API.G_TRUE
146 , p_Actual_Rec => l_actual_rec
147 , x_return_status => l_return_status
148 , x_msg_count => l_msg_count
149 , x_msg_data => l_msg_data
150 , x_error_Tbl => l_error_Tbl
151 );
152 BIS_UTILITIES_PUB.put_line(p_text =>'Actual posted: '||l_return_status);
153 END IF;
154
155 -- Comparison and notification
156 --
157 Compare_Values
158 ( p_target_rec => l_Target_Rec
159 , p_actual_rec => l_Actual_Rec
160 , x_comparison_result => l_comparison_result
161 );
162 BIS_UTILITIES_PUB.put_line(p_text =>'Target to actual comparison: out of range '||l_comparison_result);
163
164 -- Assume Exception mode alert compare and notify Target owner
165 -- when exceptions occur. No notification if result is normal.
166 -- Start corrective action only if exception occured
167 --
168 IF l_comparison_result = G_COMP_RESULT_NORMAL
169 THEN
170 RETURN;
171 ELSE
172 BIS_PMF_DATA_SOURCE_PUB.Retrieve_Target_Owners
173 ( p_measure_instance => l_measure_instance
174 , p_dim_level_value_tbl => l_dim_level_value_tbl
175 , p_all_info => FND_API.G_FALSE
176 , x_target_owners_rec => l_Target_Owners_Rec
177 );
178 l_measure_instance_p := l_measure_instance;
179 Sync_All_Fields
180 ( p_measure_instance => l_measure_instance_p
181 , p_target_level_rec => l_target_level_rec
182 , p_target_rec => l_Target_Rec
183 , p_target_owners_rec => l_Target_Owners_Rec
184 , p_actual_rec => l_Actual_Rec
185 , x_measure_instance => l_measure_instance
186 );
187 BIS_UTILITIES_PUB.put_line(p_text =>'Range 1 Owner: '||l_Target_Owners_Rec.Range1_Owner_Short_Name);
188 BIS_UTILITIES_PUB.put_line(p_text =>'Range 2 Owner: '||l_Target_Owners_Rec.Range2_Owner_Short_Name);
189 BIS_UTILITIES_PUB.put_line(p_text =>'Range 3 Owner: '||l_Target_Owners_Rec.Range3_Owner_Short_Name);
190
191 BIS_CORRECTIVE_ACTION_PUB.Send_Alert
192 ( p_measure_instance => l_measure_instance
193 , p_dim_level_value_tbl => l_dim_level_value_tbl
194 , p_comparison_result => l_comparison_result
195 );
196 -- Following is an Incorrect Log. Please don't uncomment the following line
197
198 -- BIS_UTILITIES_PUB.put_line(p_text =>'Send alert notification started');
199
200
201 IF p_alert_type = BIS_PMF_REG_SERVICE_PVT.G_TARGET_LEVEL THEN
202 BIS_CORRECTIVE_ACTION_PUB.Start_Corrective_Action
203 ( p_measure_instance => l_measure_instance
204 , p_dim_level_value_tbl => l_dim_level_value_tbl
205 , p_comparison_result => l_comparison_result
206 );
207 BIS_UTILITIES_PUB.put_line(p_text =>'Corrective action started');
208 END IF;
209
210 END IF;
211
212 RETURN;
213 EXCEPTION
214 when FND_API.G_EXC_ERROR then
215 RETURN;
216 when FND_API.G_EXC_UNEXPECTED_ERROR then
217 RETURN;
218 when others then
219 BIS_UTILITIES_PVT.Add_Error_Message
220 ( p_error_msg_id => SQLCODE
221 , p_error_description => SQLERRM
222 , p_error_proc_name => G_PKG_NAME||'.Service_planner_Request'
223 );
224 RETURN;
225 END Service_Planner_Request;
226
227 -- For future enhencements
228 -- Includes additional list of subscribers (enh. #1270301)
229 -- Public vs. private alerts (enh. #1270297)
230 --
231 Procedure Service_Planner_Request
232 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
233 , p_dim_level_value_tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
234 , P_notify_set IN VARCHAR2
235 , p_alert_type IN VARCHAR2
236 , p_alert_level IN VARCHAR2
237 )
238 IS
239
240 BEGIN
241
242 null;
243
244 EXCEPTION
245 when FND_API.G_EXC_ERROR then
246 RETURN;
247 when FND_API.G_EXC_UNEXPECTED_ERROR then
248 RETURN;
249 when others then
250 BIS_UTILITIES_PVT.Add_Error_Message
251 ( p_error_msg_id => SQLCODE
252 , p_error_description => SQLERRM
253 , p_error_proc_name => G_PKG_NAME||'.Service_Planner_Request'
254 );
255 RETURN;
256 END Service_Planner_Request;
257
258 Procedure Compare_Values
259 ( p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type
260 , p_actual_rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
261 , x_comparison_result OUT NOCOPY VARCHAR2
262 )
263 IS
264
265 l_actual BISBV_ACTUALS.ACTUAL_VALUE%TYPE;
266 l_target BISBV_TARGETS.TARGET%TYPE;
267 l_range1_low BISBV_TARGETS.RANGE1_LOW%TYPE;
268 l_range1_high BISBV_TARGETS.RANGE1_HIGH%TYPE;
269 l_range2_low BISBV_TARGETS.RANGE2_LOW%TYPE;
270 l_range2_high BISBV_TARGETS.RANGE2_HIGH%TYPE;
271 l_range3_low BISBV_TARGETS.RANGE3_LOW%TYPE;
272 l_range3_high BISBV_TARGETS.RANGE3_HIGH%TYPE;
273 l_comparison_result VARCHAR2(32000);
274
275 BEGIN
276
277 l_actual := NVL(p_actual_rec.actual,0);
278 l_target := NVL(p_target_rec.target,0);
279 l_range1_low := p_target_rec.range1_low;
280 l_range1_high := p_target_rec.range1_high;
281 l_range2_low := p_target_rec.range2_low;
282 l_range2_high := p_target_rec.range2_high;
283 l_range3_low := p_target_rec.range3_low;
284 l_range3_high := p_target_rec.range3_high;
285
286 -- Compute the min, max value of tolerance ranges in percentages
287 --
288 l_range1_low := l_target-((l_range1_low/100)*l_target);
289 l_range1_high := l_target+((l_range1_high/100)*l_target);
290 l_range2_low := l_target-((l_range2_low/100)*l_target);
291 l_range2_high := l_target+((l_range2_high/100)*l_target);
292 l_range3_low := l_target-((l_range3_low/100)*l_target);
293 l_range3_high := l_target+((l_range3_high/100)*l_target);
294
295 BIS_UTILITIES_PUB.put_line(p_text =>'Comparing values.');
296 BIS_UTILITIES_PUB.put_line(p_text =>'Actual: '||l_actual||', Target: '||l_target);
297 BIS_UTILITIES_PUB.put_line(p_text =>'Range 1 low: '||l_range1_low||', high: '||l_range1_high);
298 BIS_UTILITIES_PUB.put_line(p_text =>'Range 2 low: '||l_range2_low||', high: '||l_range2_high);
299 BIS_UTILITIES_PUB.put_line(p_text =>'Range 3 low: '||l_range3_low||', high: '||l_range3_high);
300
301 -- Check if actual not equal to target
302 --
303 IF (l_actual <> l_target)
304 THEN
305
306 -- Check if actual is within the first range
307 --
308 IF (l_range1_low IS NOT NULL OR l_range1_high IS NOT NULL)
309 AND (l_actual NOT BETWEEN
310 NVL(l_range1_low,l_target) AND NVL(l_range1_High,l_target))
311 AND (l_actual BETWEEN
312 NVL(l_range2_low,l_actual) AND NVL(l_range2_High,l_actual))
313 THEN
314 l_comparison_result := G_COMP_RESULT_OUT_OF_RANGE1;
315 -- Check if actual is within the second range
316 --
317 ELSIF (l_range2_low IS NOT NULL OR l_range2_high IS NOT NULL)
318 AND (l_actual NOT BETWEEN
319 NVL(l_range2_low,l_target) AND NVL(l_range2_High,l_target))
320 AND (l_actual BETWEEN
321 NVL(l_range3_low,l_actual) AND NVL(l_range3_High,l_actual))
322 THEN
323 l_comparison_result := G_COMP_RESULT_OUT_OF_RANGE2;
324
325 -- Check if actual is within the third range
326 --
327 ELSIF (l_range3_low IS NOT NULL OR l_range3_high IS NOT NULL)
328 AND (l_actual NOT BETWEEN
329 NVL(l_range3_low,l_target) AND NVL(l_range3_High,l_target))
330 THEN
331 l_comparison_result := G_COMP_RESULT_OUT_OF_RANGE3;
332
333 ELSE
334 -- within range
335 --
336 l_comparison_result := G_COMP_RESULT_NORMAL;
337 END IF;
338 ELSE
339 -- actual match target. send congratulation message
340 -- (enh # )
341 l_comparison_result := G_COMP_RESULT_NORMAL;
342 END IF;
343
344 x_comparison_result := l_comparison_result;
345
346 EXCEPTION
347 when FND_API.G_EXC_ERROR then
348 RETURN;
349 when FND_API.G_EXC_UNEXPECTED_ERROR then
350 RETURN;
351 when others then
352 BIS_UTILITIES_PVT.Add_Error_Message
353 ( p_error_msg_id => SQLCODE
354 , p_error_description => SQLERRM
355 , p_error_proc_name => G_PKG_NAME||'.Compare_Values'
356 );
357 RETURN;
358
359 END Compare_Values;
360
361 Procedure Sync_All_Fields
362 ( p_measure_instance IN BIS_MEASURE_PUB.Measure_Instance_type
363 , p_target_level_rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
364 , p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type
365 , p_target_owners_rec IN BIS_TARGET_PUB.Target_Owners_Rec_Type
366 , p_actual_rec IN BIS_ACTUAL_PUB.Actual_Rec_Type
367 , x_measure_instance IN OUT NOCOPY BIS_MEASURE_PUB.Measure_Instance_type
368 )
369 IS
370
371 l_measure_instance BIS_MEASURE_PUB.Measure_Instance_type;
372 l_measure_instance_p BIS_MEASURE_PUB.Measure_Instance_type;
373
374 BEGIN
375
376 l_measure_instance := p_measure_instance;
377
378 l_measure_instance.Measure_ID := p_target_level_rec.Measure_ID;
379 l_measure_instance.Measure_Short_Name
380 := p_target_level_rec.Measure_Short_Name;
381 l_measure_instance.Measure_Name := p_target_level_rec.Measure_Name;
382
383 l_measure_instance.Target_Level_ID := p_target_level_rec.Target_Level_ID;
384 l_measure_instance.Target_Level_Short_Name
385 := p_target_level_rec.Target_Level_Short_name;
386 l_measure_instance.Target_Level_Name
387 := p_target_level_rec.Target_Level_Name;
388
389 l_measure_instance.plan_id := p_target_rec.plan_id ;
390 l_measure_instance.plan_short_name := p_target_rec.plan_short_name;
391 l_measure_instance.plan_name := p_target_rec.plan_name;
392
393 l_measure_instance.Actual := p_actual_rec.actual;
394
395 l_measure_instance.Target_id := p_target_rec.target_id ;
396 l_measure_instance.Target := p_target_rec.target ;
397 l_measure_instance.range1_low := p_target_rec.range1_low ;
398 l_measure_instance.range1_high := p_target_rec.range1_high;
399 l_measure_instance.range2_low := p_target_rec.range2_low ;
400 l_measure_instance.range2_high := p_target_rec.range2_high;
401 l_measure_instance.range3_low := p_target_rec.range3_low ;
402 l_measure_instance.range3_high := p_target_rec.range3_high;
403 l_measure_instance_p := l_measure_instance;
404 BIS_PMF_DATA_SOURCE_PVT.Sync_Target_Measure_Owners
405 ( p_measure_instance => l_measure_instance_p
406 , p_Target_owners_rec => p_Target_owners_rec
407 , x_measure_instance => l_measure_instance
408 );
409
410 x_measure_instance := l_measure_instance;
411
412 EXCEPTION
413 when FND_API.G_EXC_ERROR then
414 RETURN;
415 when FND_API.G_EXC_UNEXPECTED_ERROR then
416 RETURN;
417 when others then
418 BIS_UTILITIES_PVT.Add_Error_Message
419 ( p_error_msg_id => SQLCODE
420 , p_error_description => SQLERRM
421 , p_error_proc_name => G_PKG_NAME||'.Sync_All_Fields'
422 );
423 RETURN;
424
425 END Sync_All_Fields;
426
427
428 END BIS_GENERIC_PLANNER_PVT;