[Home] [Help]
PACKAGE BODY: APPS.GL_BIS_ALERTS_PKG
Source
1 PACKAGE BODY GL_BIS_ALERTS_PKG as
2 /* $Header: glubisab.pls 120.7 2005/05/05 01:36:17 kvora ship $ */
3
4 ---
5 --- PRIVATE VARIABLES
6 ---
7
8 c_gl_revenue_pm VARCHAR2(30) := 'FIIGLREVENUE';
9 c_gl_company VARCHAR2(30) := 'GL COMPANY';
10 c_t_gl_companys VARCHAR2(30) := 'TOTAL GL COMPANIES';
11 c_gl_sm VARCHAR2(30) := 'GL SECONDARY MEASURE';
12 c_t_gl_sms VARCHAR2(30) := 'TOTAL GL SECONDARY MEASURES';
13
14 g_period_name VARCHAR2(15); -- period name
15 g_period_set_name VARCHAR2(15); -- accounting calendar name
16 g_period_num NUMBER(15); -- accounting period number
17 g_period_year NUMBER(15); -- accounting period year
18 g_period_pos NUMBER(15); -- relative period position
19
20 trg_select VARCHAR2(2000); -- Buffer for dynamic sql stmt
21
22 g_return_status VARCHAR2(1);
23 g_msg_count NUMBER;
24 g_msg_data VARCHAR2(250);
25 g_error_tbl BIS_UTILITIES_PUB.error_tbl_type;
26 g_msg_buf VARCHAR2(240);
27
28 g_old_org_id VARCHAR2(80); -- temp. storage of org id
29
30 -- Cursor to retrieve all target levels
31 CURSOR c_target_level IS
32 SELECT tl.target_level_id target_level_id,
33 tl.dimension1_level_id dim1_level_id,
34 tl.dimension2_level_id dim2_level_id,
35 decode(dl1.dimension_level_short_name,
36 c_t_gl_companys, -1,
37 c_gl_company, 0) dim1_level_code,
38 decode(dl2.dimension_level_short_name,
39 c_t_gl_sms, -1,
40 c_gl_sm, 0) dim2_level_code,
41 workflow_item_type wf_item_type,
42 workflow_process_short_name wf_process
43 FROM bis_indicators ind,
44 bisbv_target_levels tl,
45 bisbv_dimension_levels dl1,
46 bisbv_dimension_levels dl2
47 WHERE ind.short_name = c_gl_revenue_pm
48 AND tl.measure_id = ind.indicator_id
49 AND dl1.dimension_level_id = tl.dimension1_level_id
50 AND dl2.dimension_level_id = tl.dimension2_level_id
51 AND NOT (dl1.dimension_level_short_name = c_t_gl_companys
52 AND dl2.dimension_level_short_name = c_gl_sm)
53 ORDER BY tl.target_level_id;
54
55 ---
56 --- PRIVATE FUNCTIONS
57 ---
58
59 --
60 -- Procedure
61 -- get_period_info
62 -- Purpose
63 -- Get period info
64 -- History
65 -- 28-MAY-1999 K Vora Created
66 -- Arguments
67 -- pp_period_id Period set name + Period name
68 -- Example
69 -- gl_bis_alerts.get_period_info('Accounting+JAN-99');
70
71 PROCEDURE get_period_info(
72 pp_period_id VARCHAR2) IS
73 BEGIN
74
75 -- Get period name and period set name
76 SELECT substr(pp_period_id,
77 instr(pp_period_id, '+') + 1,
78 length(pp_period_id)),
79 substr(pp_period_id,
80 1, instr(pp_period_id, '+') -1)
81 INTO g_period_name,
82 g_period_set_name
83 FROM DUAL;
84
85 -- Get period number and period year
86 SELECT p.period_num,
87 p.period_year
88 INTO g_period_num,
89 g_period_year
90 FROM gl_periods p
91 WHERE period_set_name = g_period_set_name
92 AND period_name = g_period_name;
93
94 END get_period_info;
95
96
97 --
98 -- Procedure
99 -- build_sql_statement
100 -- Purpose
101 -- Build dynamic SQL statement for target cursor. Retrieve segment number
102 -- number information only if target is defined for a specific GL Company
103 -- or GL Secondary Measure.
104 -- History
105 -- 28-MAY-1999 K Vora Created
106 -- Arguments
107 -- p_dim1_level_code -1 - TOTAL GL COMPANYS
108 -- 0 - GL COMPANY
109 -- p_dim2_level_code -1 - TOTAL GL SECONDARY MEASURES
110 -- 0 - GL SECONDARY MEASURE
111 -- Example
112 -- gl_bis_alerts.build_sql_statement(
113 -- p_dim1_level_code => 0,
114 -- p_dim2_level_code => 0);
115
116 PROCEDURE build_sql_statement(
117 p_dim1_level_code NUMBER,
118 p_dim2_level_code NUMBER) IS
119 BEGIN
120
121 trg_select := 'SELECT ' ||
122 ' trg.target_id ' ||
123 ', bplan.plan_id ' ||
124 ', bplan.name ' ||
125 ', trg.org_level_value_id ' ||
126 ', sob.name ' ||
127 ', sob.chart_of_accounts_id ' ||
128 ', trg.time_level_value_id ' ||
129 ', trg.dim1_level_value_id ' ||
130 ', trg.dim2_level_value_id ' ||
131 ', trg.range1_low * -1' ||
132 ', trg.range1_high ' ||
133 ', trg.range2_low * -1' ||
134 ', trg.range2_high ' ||
135 ', trg.range3_low * -1' ||
136 ', trg.range3_high ' ||
137 ', trg.notify_resp1_id ' ||
138 ', trg.notify_resp1_short_name ' ||
139 ', trg.notify_resp2_id ' ||
140 ', trg.notify_resp2_short_name ' ||
141 ', trg.notify_resp3_id ' ||
142 ', trg.notify_resp3_short_name ';
143
144
145 IF (p_dim1_level_code = 0) THEN
146 trg_select := trg_select || ', sg1.segment_num ';
147 ELSE
148 trg_select := trg_select || ', -1 ';
149 END IF;
150
151 IF (p_dim2_level_code = 0) THEN
152 trg_select := trg_select || ', sg2.segment_num ';
153 ELSE
154 trg_select := trg_select || ', -1 ';
155 END IF;
156
157 trg_select := trg_select ||
158 'FROM ' ||
159 ' bisbv_targets trg ' ||
160 ', bisbv_business_plans bplan ' ||
161 ', bisbv_target_levels tl ' ||
162 ', gl_sets_of_books sob ';
163
164 IF (p_dim1_level_code = 0) THEN
165 trg_select := trg_select ||
166 ', bis_flex_mappings_v fm1 ' ||
167 ', fnd_id_flex_segments sg1 ';
168 END IF;
169
170 IF (p_dim2_level_code = 0) THEN
171 trg_select := trg_select ||
172 ', bis_flex_mappings_v fm2 ' ||
173 ', fnd_id_flex_segments sg2 ';
174 END IF;
175
176 trg_select := trg_select ||
177 'WHERE tl.target_level_id = :target_level_id ' ||
178 'AND trg.target_level_id = tl.target_level_id ' ||
179 'AND trg.time_level_value_id = :period_id ' ||
180 'AND bplan.plan_id = trg.plan_id ' ||
181 'AND sob.set_of_books_id = to_number(trg.org_level_value_id) ';
182
183 IF (p_dim1_level_code = 0) THEN
184 trg_select := trg_select ||
185 'AND fm1.level_id = :dim1_level_id ' ||
186 'AND fm1.application_id = 101 ' ||
187 'AND fm1.id_flex_code = ''GL#'' ' ||
188 'AND fm1.structure_num = sob.chart_of_accounts_id ' ||
189 'AND sg1.application_id = 101 ' ||
190 'AND sg1.id_flex_code = ''GL#'' ' ||
191 'AND sg1.id_flex_num = fm1.structure_num ' ||
192 'AND sg1.application_column_name = fm1.application_column_name ';
193 END IF;
194
195 IF (p_dim2_level_code = 0) THEN
196 trg_select := trg_select ||
197 'AND fm2.level_id = :dim2_level_id ' ||
198 'AND fm2.application_id = 101 ' ||
199 'AND fm2.id_flex_code = ''GL#'' ' ||
200 'AND fm2.structure_num = sob.chart_of_accounts_id ' ||
201 'AND sg2.application_id = 101 ' ||
202 'AND sg2.id_flex_code = ''GL#'' ' ||
203 'AND sg2.id_flex_num = fm2.structure_num ' ||
204 'AND sg2.application_column_name = fm2.application_column_name ' ||
205 'ORDER BY trg.org_level_value_id';
206 END IF;
207
208 END build_sql_statement;
209
210
211 --
212 -- Function
213 -- calculate_amount
214 -- Purpose
215 -- Retrieve actual revenue and planned revenue for the dimension values.
216 -- History
217 -- 28-MAY-1999 K Vora Created
218 -- Arguments
219 -- p_organization_id Set of books id
220 -- p_dim1_id Dimension1 value
221 -- p_dim1_segnum Mapped segment number
222 -- p_dim2_id Dimension2 value
223 -- p_dim2_segnum Mapped segment number
224 -- p_amount_type A - Retrieve actual revenue
225 -- B - Retrieve planned revenue
226 -- Returns
227 -- revenue or planned revenue
228 -- Example
229 -- l_actual := gl_bis_alerts.calculate_amount(
230 -- p_organization_id => '1491',
231 -- p_dim1_id => '01',
232 -- p_dim1_segnum => 1,
233 -- p_dim2_id => '110',
234 -- p_dim2_segnum => 2,
235 -- p_amount_type => 'A');
236 -- Notes
237 -- p_dim1_segnum and p_dim2_segnum hold the segment number of the segment
238 -- that particular dimension is mapped to, else the value is -1.
239 -- If the balancing segment is the first segment, then p_dim1_segnum holds
240 -- value 1. If the cost center segment is the fourth segment, and the
241 -- second dimension is mapped to the cost center segment, p_dim2_segnum
242 -- holds value 4. If the second dimension is not mapped, p_dim2_segnum
243 -- holds value -1.
244
245 FUNCTION calculate_amount(
246 p_organization_id VARCHAR2,
247 p_dim1_id VARCHAR2,
248 p_dim1_segnum NUMBER,
249 p_dim2_id VARCHAR2,
250 p_dim2_segnum NUMBER,
251 p_amount_type VARCHAR2
252 ) RETURN NUMBER IS
253
254 l_num_per_year NUMBER; -- Number of periods in a fiscal year
255 ret_amount NUMBER;
256
257 BEGIN
258
259 -- Calculate relative period position
260 IF p_organization_id <> g_old_org_id THEN
261
262 SELECT pt.number_per_fiscal_year
263 INTO l_num_per_year
264 FROM gl_sets_of_books sob,
265 gl_period_types pt
266 WHERE sob.set_of_books_id = to_number(p_organization_id)
267 AND pt.period_type = sob.accounted_period_type;
268
269 g_period_pos := g_period_year * l_num_per_year + g_period_num;
270 g_old_org_id := p_organization_id;
271
272 END IF;
273
274 -- Bug 1746807 - string literals in the 2 DECODE() statements are now
275 -- enclosed by single quotes to prevent "ORA-01722: invalid number" errors.
276
277 -- Calculate amount
278 --bug 3329868 - For revenue , the sign should be reversed when it
279 -- it is send to the PMF region.
280
281 BEGIN
282 SELECT -1*period_to_date
283 INTO ret_amount
284 FROM gl_oasis_summary_data
285 WHERE set_of_books_id = to_number(p_organization_id)
286 AND fin_item_id = 'REVENUE'
287 AND drilldown_segnum1 = p_dim1_segnum
288 AND drilldown_segnum2 = p_dim2_segnum
289 AND actual_flag = p_amount_type
290 AND drilldown_segval1 = decode(p_dim1_segnum, -1, '-1', p_dim1_id)
291 AND drilldown_segval2 = decode(p_dim2_segnum, -1, '-1', p_dim2_id)
292 AND relative_period_pos = g_period_pos;
293 EXCEPTION WHEN NO_DATA_FOUND THEN
294 NULL;
295 END;
296
297 RETURN(ret_amount);
298 END calculate_amount;
299
300
301 --
302 -- Procedure
303 -- post_actual
304 -- Purpose
305 -- Post revenue amounts to set performance measures on personal homepage
306 -- History
307 -- 28-MAY-1999 K Vora Created
308 -- Arguments
309 -- p_target_level_id Target level id
310 -- p_organization_id Set of books id
311 -- p_time_id Period name + Period set name
312 -- p_dim1_id Dimension1 value
313 -- p_dim2_id Dimension2 value
314 -- p_actual Actual amount
315 -- Example
316 -- gl_bis_alerts.post_actual(
317 -- p_target_level_id => 1533,
318 -- p_organization_id => '1491',
319 -- p_time_id => 'Accounting+July-99',
320 -- p_dim1_id => '01',
321 -- p_dim2_id => '110'
322 -- p_actual => 101345)
323
324 PROCEDURE post_actual(
325 p_target_level_id NUMBER,
326 p_organization_id VARCHAR2,
327 p_time_id VARCHAR2,
328 p_dim1_id VARCHAR2,
329 p_dim2_id VARCHAR2,
330 p_actual NUMBER
331 ) IS
332
333 -- Record containing actual info
334 v_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
335
336 BEGIN
337
338 -- Initialize actuals record
339 v_actual_rec.target_level_id := p_target_level_id;
340 v_actual_rec.org_level_value_id := p_organization_id;
341 v_actual_rec.time_level_value_id := p_time_id;
342 v_actual_rec.dim1_level_value_id := p_dim1_id;
343 v_actual_rec.dim2_level_value_id := p_dim2_id;
344
345 -- S.Bhattal, 31-JAN-2000, bug 1407747, reversed the sign of the actual.
346 -- So credit Revenue (negative amount) will be posted as a positive amount.
347 -- Debit Revenue (positive amount) will be posted as a negative amount.
348 -- This is required for display of Actuals on the BIS PHP.
349
350 -- No need to reverse the sign of the actual as it is done in
351 -- the procedure calculate_amount bug#3329868
352
353 v_actual_rec.actual := p_actual;
354
355 BIS_ACTUAL_PUB.post_actual(
356 p_api_version => 1.0,
357 p_commit => FND_API.G_TRUE,
358 p_actual_rec => v_actual_rec,
359 x_return_status => g_return_status,
360 x_msg_count => g_msg_count,
361 x_msg_data => g_msg_data,
362 x_error_tbl => g_error_tbl);
363
364 END post_actual;
365
366
367 --
368 -- Procedure
369 -- send_notification
370 -- Purpose
371 -- Start workflow notification process
372 -- History
373 -- 28-MAY-1999 K Vora Created
374 -- Arguments
375 -- p_type ABOVE or BELOW tolerance range
376 -- p_wf_item_type Workflow item type
377 -- p_wf_process Workflow process
378 -- p_resp_id Responsibility to be notified
379 -- p_resp Responsibility to be notified
380 -- p_sob_name Name of the set of books
381 -- p_dim2_id Dimension1 value
382 -- p_dim2_id Dimension2 value
383 -- p_actual Actual revenue
384 -- p_target Planned revenue
385 -- p_variance_percent Variance percent
386 -- Example
387 -- gl_bis_alerts.send_notification(
388 -- p_type => 'ABOVE',
389 -- p_wf_item_type => 'FIIBISWF',
390 -- p_wf_process => 'FII_REVENUE_NOTIFICATION',
394 -- p_dim1_id => '01',
391 -- p_resp => 'CFO Responsibility',
392 -- p_resp_id => 1002841,
393 -- p_sob_name => 'Vision Operations',
395 -- p_dim2_id => '110',
396 -- p_actual => 12000,
397 -- p_target => 11650,
398 -- p_variance_percent=> 3);
399
400 PROCEDURE send_notification(
401 p_type VARCHAR2,
402 p_wf_item_type VARCHAR2,
403 p_wf_process VARCHAR2,
404 p_resp_id NUMBER,
405 p_resp VARCHAR2,
406 p_sob_name VARCHAR2,
407 p_dim1_id VARCHAR2,
408 p_dim2_id VARCHAR2,
409 p_actual NUMBER,
410 p_target NUMBER,
411 p_variance_percent NUMBER,
412 p_range_low NUMBER,
413 p_range_high NUMBER) IS
414
415 l_report_name VARCHAR2(10);
416 l_subject VARCHAR2(500);
417 l_message VARCHAR2(1000);
418 l_param VARCHAR2(250);
419 l_variance_percent NUMBER;
420 l_dim1_value VARCHAR2(80);
421 l_dim2_value VARCHAR2(80);
422 l_dim1_name VARCHAR2(80);
423 l_dim2_name VARCHAR2(80);
424
425 send_notification_error EXCEPTION;
426
427 BEGIN
428
429 l_report_name := 'FIIGLFIR';
430
431 IF p_variance_percent = 9999 THEN
432 l_variance_percent := NULL;
433 ELSE
434 l_variance_percent := round(p_variance_percent, 3);
435 END IF;
436
437 --for bug 2166758: change -1 to '-1' to avoid
438 -- ORA-06502: PL/SQL: numeric or value error:
439 -- character to number conversion error
440 IF p_dim1_id = '-1' THEN
441 SELECT dl.dimension_level_name, dl.dimension_level_name
442 INTO l_dim1_value, l_dim1_name
443 FROM bisbv_dimension_levels dl
444 WHERE dl.dimension_level_short_name = c_t_gl_companys;
445 ELSE
446 l_dim1_value := p_dim1_id;
447 SELECT dl.dimension_level_name
448 INTO l_dim1_name
449 FROM bisbv_dimension_levels dl
450 WHERE dl.dimension_level_short_name = c_gl_company;
451 END IF;
452
453 --for bug 2166758: change -1 to '-1' to avoid
454 -- ORA-06502: PL/SQL: numeric or value error:
455 -- character to number conversion error
456 IF p_dim2_id = '-1' THEN
457 SELECT dl.dimension_level_name, dl.dimension_level_name
458 INTO l_dim2_value, l_dim2_name
459 FROM bisbv_dimension_levels dl
460 WHERE dl.dimension_level_short_name = c_t_gl_sms;
461 ELSE
462 l_dim2_value := p_dim2_id;
463 SELECT dl.dimension_level_name
464 INTO l_dim2_name
465 FROM bisbv_dimension_levels dl
466 WHERE dl.dimension_level_short_name = c_gl_sm;
467 END IF;
468
469 IF p_type = 'ABOVE' THEN
470 FND_MESSAGE.set_name('SQLGL', 'GL_BIS_REVPMF_ABOVE_SUBJECT');
471 FND_MESSAGE.set_token('PERIOD_NAME', g_period_name);
472 FND_MESSAGE.set_token('DIM1_NAME', l_dim1_name);
473 FND_MESSAGE.set_token('DIM2_NAME', l_dim2_name);
474 FND_MESSAGE.set_token('ACTUAL', abs(p_actual));
475 FND_MESSAGE.set_token('TARGET', abs(p_target));
476 l_subject := FND_MESSAGE.get;
477 FND_MESSAGE.set_name('SQLGL', 'GL_BIS_REVPMF_ABOVE_MESSAGE');
478
479 ELSE
480 FND_MESSAGE.set_name('SQLGL', 'GL_BIS_REVPMF_BELOW_SUBJECT');
481 FND_MESSAGE.set_token('PERIOD_NAME', g_period_name);
482 FND_MESSAGE.set_token('DIM1_NAME', l_dim1_name);
483 FND_MESSAGE.set_token('DIM2_NAME', l_dim2_name);
484 FND_MESSAGE.set_token('ACTUAL', abs(p_actual));
485 FND_MESSAGE.set_token('TARGET', abs(p_target));
486 l_subject := FND_MESSAGE.get;
487 FND_MESSAGE.set_name('SQLGL', 'GL_BIS_REVPMF_BELOW_MESSAGE');
488 END IF;
489
490 FND_MESSAGE.set_token('SET_OF_BOOKS_NAME', p_sob_name);
491 FND_MESSAGE.set_token('PERIOD_NAME', g_period_name);
492 FND_MESSAGE.set_token('DIM1_VALUE', l_dim1_value);
493 FND_MESSAGE.set_token('DIM2_VALUE', l_dim2_value);
494 FND_MESSAGE.set_token('ACTUAL', abs(p_actual));
495 FND_MESSAGE.set_token('TARGET', abs(p_target));
496 FND_MESSAGE.set_token('VARIANCE', (p_target - p_actual));
497 FND_MESSAGE.set_token('VPERCENT', l_variance_percent);
498 FND_MESSAGE.set_token('VRANGE_LOW', p_range_low);
499 FND_MESSAGE.set_token('VRANGE_HIGH', p_range_high);
500 l_message := FND_MESSAGE.get;
501
502 l_param := 'P_REPORT_ID=REVENUE*P_TARGET_PERIOD=' || g_period_name ||
503 '*P_DRILLDOWN_LEVEL=';
504 IF p_dim1_id = '-1' THEN
505 l_param := l_param || '0';
506 ELSE
507 IF p_dim2_id = '-1' THEN
508 l_param := l_param || '1';
509 ELSE
510 l_param := l_param || '2';
511 END IF;
512 END IF;
513
514 l_param := l_param || '*P_DRILLDOWN_SEGVAL1=' || p_dim1_id
518 p_exception_message => l_message,
515 || '*P_DRILLDOWN_SEGVAL2=' || p_dim2_id || '*';
516
517 BIS_UTIL.strt_wf_process(
519 p_msg_subject => l_subject,
520 p_exception_date => sysdate,
521 p_item_type => p_wf_item_type,
522 p_wf_process => p_wf_process,
523 p_notify_resp_name => p_resp,
524 p_report_name1 => l_report_name,
525 p_report_param1 => l_param,
526 p_report_resp1_id => p_resp_id,
527 x_return_status => g_return_status);
528 IF g_return_status <> FND_API.G_RET_STS_SUCCESS THEN
529 RAISE send_notification_error;
530 END IF;
531
532 EXCEPTION
533 WHEN send_notification_error THEN
534 g_msg_buf := FND_MESSAGE.get_string('SQLGL','GL_BIS_REVPMF_MESSAGE_ERROR');
535 -- dbms_output.put_line(g_msg_buf);
536 WHEN OTHERS THEN
537 -- dbms_output.put_line(g_msg_buf);
538 null;
539
540 END send_notification;
541
542
543 ---
544 --- PUBLIC FUNCTIONS
545 ---
546
547 --
548 -- Procedure
549 -- check_revenue
550 -- Purpose
551 -- Compares actual revenue versus planned revenue amounts
552 -- and sends notifications.
553 -- History
554 -- 28-MAY-1999 K Vora Created
555 -- Arguments
556 -- p_period_id Period set name + Period name
557 -- Example
558 -- gl_bis_alerts_pkg.check_revenue( 'Accounting+JAN-99');
559 --
560
561 PROCEDURE check_revenue(p_period_id IN VARCHAR2) IS
562
563 -- Define weak REF CURSOR type for dynamic SQL
564 TYPE Target_Rec_Type IS REF CURSOR;
565 c_target_rec Target_Rec_Type;
566 trg_rec Target_Info_Rec_Type;
567
568 l_actual NUMBER; -- revenue amount
569 l_target NUMBER; -- planned revenue amount
570 l_variance_percent NUMBER; -- percentage of difference
571 -- between actual and target
572 l_type VARCHAR2(5); -- ABOVE - actual > target
573 -- BELOW - actual < target
574
575 BEGIN
576
577 g_msg_buf := FND_MESSAGE.get_string('SQLGL', 'GL_BIS_REVPMF_START');
578 -- dbms_output.put_line(g_msg_buf);
579
580 g_old_org_id := '-1';
581 get_period_info(
582 pp_period_id => p_period_id);
583
584 -- Process each target level
585 FOR tl_rec IN c_target_level LOOP
586
587 -- Build target cursor statement
588 build_sql_statement(
589 p_dim1_level_code => tl_rec.dim1_level_code,
590 p_dim2_level_code => tl_rec.dim2_level_code);
591
592 -- Loop thru target records
593 IF ((tl_rec.dim1_level_code = -1) AND
594 (tl_rec.dim2_level_code = -1)) THEN
595 OPEN c_target_rec FOR trg_select USING
596 tl_rec.target_level_id, p_period_id;
597
598 ELSIF ((tl_rec.dim1_level_code = 0) AND
599 (tl_rec.dim2_level_code = -1)) THEN
600 OPEN c_target_rec FOR trg_select USING
601 tl_rec.target_level_id, p_period_id, tl_rec.dim1_level_id;
602
603 ELSIF ((tl_rec.dim1_level_code = 0) AND
604 (tl_rec.dim2_level_code = 0)) THEN
605 OPEN c_target_rec FOR trg_select USING
606 tl_rec.target_level_id, p_period_id,
607 tl_rec.dim1_level_id, tl_rec.dim2_level_id;
608 END IF;
609
610 LOOP
611 FETCH c_target_rec INTO trg_rec;
612 EXIT WHEN c_target_rec%NOTFOUND;
613
614 -- Calculate actual revenue amount
615 l_actual := calculate_amount(
616 p_organization_id => trg_rec.org_level_value_id,
617 p_dim1_id => trg_rec.dim1_level_value_id,
618 p_dim1_segnum => trg_rec.dim1_segnum,
619 p_dim2_id => trg_rec.dim2_level_value_id,
620 p_dim2_segnum => trg_rec.dim2_segnum,
621 p_amount_type => 'A');
622
623 -- Calculate planned revenue amount
624 l_target := calculate_amount(
625 p_organization_id => trg_rec.org_level_value_id,
626 p_dim1_id => trg_rec.dim1_level_value_id,
627 p_dim1_segnum => trg_rec.dim1_segnum,
628 p_dim2_id => trg_rec.dim2_level_value_id,
629 p_dim2_segnum => trg_rec.dim2_segnum,
630 p_amount_type => 'B');
631
632 -- Calculate difference
633 IF (l_actual IS NOT NULL) AND (l_target IS NOT NULL) THEN
634 IF (l_target <> 0) THEN
635 l_variance_percent := (((l_actual - l_target) / l_target) * 100);
636 ELSE
637 l_variance_percent := 9999;
638 END IF;
639 IF l_variance_percent < 0 THEN
640 l_type := 'BELOW';
641 ELSE
642 l_type := 'ABOVE';
643 END IF;
644 ELSE
645 g_msg_buf := FND_MESSAGE.get_string('SQLGL',
646 'GL_BIS_REVPMF_NO_REVENUE');
647 --dbms_output.put_line(g_msg_buf);
648 END IF;
649
653 -- Process range 3
650 -- Determine in which range the variance percentage falls in
651 IF (l_actual <> l_target) THEN
652
654 IF (((trg_rec.range3_low IS NOT NULL) AND
655 (l_variance_percent < trg_rec.range3_low)) OR
656 ((trg_rec.range3_high IS NOT NULL) AND
657 (l_variance_percent > trg_rec.range3_high))) THEN
658
659 IF trg_rec.notify_resp3_short_name IS NOT NULL THEN
660 send_notification(
661 p_type => l_type,
662 p_wf_item_type => tl_rec.wf_item_type,
663 p_wf_process => tl_rec.wf_process,
664 p_resp_id => trg_rec.notify_resp3_id,
665 p_resp => trg_rec.notify_resp3_short_name,
666 p_sob_name => trg_rec.sob_name,
667 p_dim1_id => trg_rec.dim1_level_value_id,
668 p_dim2_id => trg_rec.dim2_level_value_id,
669 p_actual => l_actual,
670 p_target => l_target,
671 p_variance_percent => l_variance_percent,
672 p_range_low => trg_rec.range3_low,
673 p_range_high => trg_rec.range3_high);
674 END IF;
675
676 -- Process range 2
677 ELSIF (((trg_rec.range2_low IS NOT NULL) AND
678 (l_variance_percent < trg_rec.range2_low)) OR
679 ((trg_rec.range2_high IS NOT NULL) AND
680 (l_variance_percent > trg_rec.range2_high))) THEN
681
682 IF trg_rec.notify_resp2_short_name IS NOT NULL THEN
683 send_notification(
684 p_type => l_type,
685 p_wf_item_type => tl_rec.wf_item_type,
686 p_wf_process => tl_rec.wf_process,
687 p_resp_id => trg_rec.notify_resp2_id,
688 p_resp => trg_rec.notify_resp2_short_name,
689 p_sob_name => trg_rec.sob_name,
690 p_dim1_id => trg_rec.dim1_level_value_id,
691 p_dim2_id => trg_rec.dim2_level_value_id,
692 p_actual => l_actual,
693 p_target => l_target,
694 p_variance_percent => l_variance_percent,
695 p_range_low => trg_rec.range2_low,
696 p_range_high => trg_rec.range2_high);
697 END IF;
698
699 -- Process range 1
700 ELSIF (((trg_rec.range1_low IS NOT NULL) AND
701 (l_variance_percent < trg_rec.range1_low)) OR
702 ((trg_rec.range1_high IS NOT NULL) AND
703 (l_variance_percent > trg_rec.range1_high))) THEN
704
705 IF trg_rec.notify_resp1_short_name IS NOT NULL THEN
706 send_notification(
707 p_type => l_type,
708 p_wf_item_type => tl_rec.wf_item_type,
709 p_wf_process => tl_rec.wf_process,
710 p_resp_id => trg_rec.notify_resp1_id,
711 p_resp => trg_rec.notify_resp1_short_name,
712 p_sob_name => trg_rec.sob_name,
713 p_dim1_id => trg_rec.dim1_level_value_id,
714 p_dim2_id => trg_rec.dim2_level_value_id,
715 p_actual => l_actual,
716 p_target => l_target,
717 p_variance_percent => l_variance_percent,
718 p_range_low => trg_rec.range1_low,
719 p_range_high => trg_rec.range1_high);
720 END IF;
721 END IF; -- process all ranges
722 END IF; -- l_actual <> l_target
723
724 END LOOP; -- loop through target records
725
726 CLOSE c_target_rec;
727
728 END LOOP; -- Loop through target levels
729 g_msg_buf := FND_MESSAGE.get_string('SQLGL', 'GL_BIS_REVPMF_SUCCESS');
730 -- dbms_output.put_line(g_msg_buf);
731
732 EXCEPTION WHEN OTHERS THEN
733 /* dbms_output.put_line('Error!!! -> ' || substrb(SQLERRM, 1, 230));
734 dbms_output.put_line(substrb(SQLERRM, 231, 230));
735 dbms_output.put_line('======= TRG_SELECT =======');
736 dbms_output.put_line(substrb(trg_select, 1, 245));
737 dbms_output.put_line(substrb(trg_select, 246, 245));
738 dbms_output.put_line(substrb(trg_select, 491, 245));
739 dbms_output.put_line('=========================='); */
740
741 g_msg_buf := FND_MESSAGE.get_string('SQLGL', 'GL_BIS_REVPMF_EXCEPTION');
742 -- dbms_output.put_line(g_msg_buf);
743
744 END check_revenue;
745
746
747 --
748 -- Function
749 -- set_performance_measures
750 -- Purpose
751 -- Set values for performance measures monitored on personal home page
752 -- History
753 -- 01-JUL-1999 K Vora Created
754 -- Arguments
755 -- p_sob_id Set of books id
756 -- p_mapped_segnum1 Segment number of segment mapped to GL COMPANY
760 -- FALSE - Performance Measure value was not set
757 -- p_mapped_segnum2 Segment number of segment mapped to GL SECONDARY MEASURE
758 -- Returns
759 -- TRUE - Performance Measure value was updated correctly
761 -- Example
762 -- ret_value := gl_bis_alerts_pkg.check_revenue( 1491, 1, 2);
763 --
764
765 FUNCTION set_performance_measures(p_sob_id IN NUMBER,
766 p_mapped_segnum1 IN NUMBER,
767 p_mapped_segnum2 IN NUMBER)
768 RETURN BOOLEAN IS
769
770 -- Record containing target level info
771 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
772
773 -- Table containing monitored indicators
774 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
775
776 l_period_id VARCHAR2(80); -- Period set name + Period name
777 l_dim1_segnum NUMBER; -- mapped segment number
778 l_dim2_segnum NUMBER; -- mapped segment number
779 l_actual NUMBER; -- revenue amount
780 i BINARY_INTEGER;
781
782 SET_PM_ERROR EXCEPTION;
783
784 BEGIN
785
786 g_old_org_id := '-1';
787
788 -- Get current period information
789 SELECT per.period_set_name || '+' || per.period_name
790 INTO l_period_id
791 FROM gl_sets_of_books sob,
792 gl_periods per
793 WHERE sob.set_of_books_id = p_sob_id
794 AND per.period_set_name = sob.period_set_name
795 AND per.period_type = sob.accounted_period_type
796 AND trunc(sysdate) between per.start_date and per.end_date
797 AND nvl(per.adjustment_period_flag,'N')='N'; --bug3457467
798
799 get_period_info(
800 pp_period_id => l_period_id);
801
802 -- Process each target level
803 FOR tl_rec IN c_target_level LOOP
804
805 -- Get indicators user wants to monitor
806 l_target_level_rec.target_level_id := tl_rec.target_level_id;
807
808 BIS_ACTUAL_PUB.retrieve_user_selections
809 (p_api_version => 1.0,
810 p_target_level_rec => l_target_level_rec,
811 x_indicator_region_tbl => l_user_selection_tbl,
812 x_return_status => g_return_status,
813 x_msg_count => g_msg_count,
814 x_msg_data => g_msg_data,
815 x_error_tbl => g_error_tbl);
816 IF g_return_status <> FND_API.G_RET_STS_SUCCESS THEN
817 RAISE SET_PM_ERROR;
818 END IF;
819
820 -- Loop through all the monitored indicators
821 i := l_user_selection_tbl.FIRST;
822 WHILE i IS NOT NULL LOOP
823
824 -- Process only those performance measures for sob id parameter
825 IF l_user_selection_tbl(i).org_level_value_id = to_char(p_sob_id) THEN
826
827 -- If dimension level is GL COMPANY
828 IF (tl_rec.dim1_level_code = 0) THEN
829 l_dim1_segnum := p_mapped_segnum1;
830 ELSE
831 l_dim1_segnum := -1;
832 END IF;
833
834 -- If dimension level is GL SECONDARY MEASURE
835 IF (tl_rec.dim2_level_code = 0) THEN
836 l_dim2_segnum := p_mapped_segnum2;
837 ELSE
838 l_dim2_segnum := -1;
839 END IF;
840
841 -- Calculate actual revenue amount
842 l_actual := calculate_amount(
843 p_organization_id => l_user_selection_tbl(i).org_level_value_id,
844 p_dim1_id => l_user_selection_tbl(i).dim1_level_value_id,
845 p_dim1_segnum => l_dim1_segnum,
846 p_dim2_id => l_user_selection_tbl(i).dim2_level_value_id,
847 p_dim2_segnum => l_dim2_segnum,
848 p_amount_type => 'A');
849
850 -- Post actual values
851 post_actual(
852 p_target_level_id => tl_rec.target_level_id,
853 p_organization_id => to_char(p_sob_id),
854 p_time_id => l_period_id,
855 p_dim1_id => l_user_selection_tbl(i).dim1_level_value_id,
856 p_dim2_id => l_user_selection_tbl(i).dim2_level_value_id,
857 p_actual => nvl(l_actual, 0));
858 IF g_return_status <> FND_API.G_RET_STS_SUCCESS THEN
859 RAISE SET_PM_ERROR;
860 END IF;
861 END IF;
862
863 i := l_user_selection_tbl.NEXT(i);
864
865 END LOOP; -- l_user_selection_tbl loop
866
867 END LOOP; -- Loop through target levels
868 RETURN TRUE;
869 EXCEPTION WHEN SET_PM_ERROR THEN
870 RETURN FALSE;
871 END set_performance_measures;
872
873 --
874 -- Function
875 -- get_ctarget
876 -- Purpose
877 -- Dummy function. Always returns -1.
878 -- History
879 -- 28-MAY-1999 K Vora Created
880 -- Arguments
881 -- p_target_rec Target information
882 -- Example
883 -- gl_bis_alerts_pkg.get_ctarget(p_target_rec =>_target_rec);
884 --
885 FUNCTION get_ctarget(
886 p_target_rec IN BIS_TARGET_PUB.Target_Rec_Type)
890 l_segment1_num NUMBER; -- mapped segment number
887 RETURN NUMBER IS
888
889 l_period_id VARCHAR2(80); -- Period set name + Period name
891 l_segment2_num NUMBER; -- mapped segment number
892 l_target NUMBER; -- planned revenue amount
893
894 CURSOR c_segment1_num IS
895 SELECT sg.segment_num
896 FROM bisbv_target_levels tl,
897 bisbv_dimension_levels dl,
898 gl_sets_of_books sob,
899 bis_flex_mappings_v fm,
900 fnd_id_flex_segments sg
901 WHERE tl.target_level_id = p_target_rec.target_level_id
902 AND dl.dimension_level_id = tl.dimension1_level_id
903 AND dl.dimension_level_short_name <> c_t_gl_companys
904 AND fm.level_id = tl.dimension1_level_id
905 AND fm.application_id = 101
906 AND fm.id_flex_code = 'GL#'
907 AND fm.structure_num = sob.chart_of_accounts_id
908 AND sg.application_id = 101
909 AND sg.id_flex_code = 'GL#'
910 AND sg.id_flex_num = fm.structure_num
911 AND sg.application_column_name = fm.application_column_name
912 AND sob.set_of_books_id = to_number(p_target_rec.org_level_value_id);
913
914 CURSOR c_segment2_num IS
915 SELECT sg.segment_num
916 FROM bisbv_target_levels tl,
917 bisbv_dimension_levels dl,
918 gl_sets_of_books sob,
919 bis_flex_mappings_v fm,
920 fnd_id_flex_segments sg
921 WHERE tl.target_level_id = p_target_rec.target_level_id
922 AND dl.dimension_level_id = tl.dimension1_level_id
923 AND dl.dimension_level_short_name <> c_t_gl_sms
924 AND fm.level_id = tl.dimension2_level_id
925 AND fm.application_id = 101
926 AND fm.id_flex_code = 'GL#'
927 AND fm.structure_num = sob.chart_of_accounts_id
928 AND sg.application_id = 101
929 AND sg.id_flex_code = 'GL#'
930 AND sg.id_flex_num = fm.structure_num
931 AND sg.application_column_name = fm.application_column_name
932 AND sob.set_of_books_id = to_number(p_target_rec.org_level_value_id);
933
934 BEGIN
935 g_old_org_id := '-1';
936
937 -- Get current period information
938 SELECT per.period_set_name || '+' || per.period_name
939 INTO l_period_id
940 FROM gl_sets_of_books sob,
941 gl_periods per
942 WHERE sob.set_of_books_id = to_number(p_target_rec.org_level_value_id)
943 AND per.period_set_name = sob.period_set_name
944 AND per.period_type = sob.accounted_period_type
945 AND trunc(sysdate) between per.start_date and per.end_date
946 AND nvl(per.adjustment_period_flag,'N')='N'; --bug3457467
947
948 get_period_info(
949 pp_period_id => l_period_id);
950
951 OPEN c_segment1_num;
952 FETCH c_segment1_num INTO l_segment1_num;
953 IF (c_segment1_num%NOTFOUND) THEN
954 l_segment1_num := -1;
955 END IF;
956 CLOSE c_segment1_num;
957
958 OPEN c_segment2_num;
959 FETCH c_segment2_num INTO l_segment2_num;
960 IF (c_segment2_num%NOTFOUND) THEN
961 l_segment2_num := -1;
962 END IF;
963 CLOSE c_segment2_num;
964
965 -- Calculate planned revenue amount
966 l_target := calculate_amount(
967 p_organization_id => p_target_rec.org_level_value_id,
968 p_dim1_id => p_target_rec.dim1_level_value_id,
969 p_dim1_segnum => l_segment1_num,
970 p_dim2_id => p_target_rec.dim2_level_value_id,
971 p_dim2_segnum => l_segment2_num,
972 p_amount_type => 'B');
973 RETURN(l_target);
974
975 EXCEPTION WHEN OTHERS THEN
976 RETURN(-1);
977 END get_ctarget;
978
979 END GL_BIS_ALERTS_PKG;