[Home] [Help]
PACKAGE BODY: APPS.WIP_BIS_UTZ_ALERT
Source
1 PACKAGE BODY WIP_BIS_UTZ_ALERT AS
2 /* $Header: wipbiuab.pls 115.22 2004/04/14 13:49:49 achandak ship $ */
3
4 /*
5 * PostActual
6 * Called by Alert_Check to post actuals to the BIS table.
7 * The posting is done by calling BIS API (BIS_ACTUAL_PUB).
8 */
9 PROCEDURE PostActual( target_level_id in number,
10 time_level_value in varchar2,
11 org_level_value in varchar2,
12 dimension1_level_value in varchar2,
13 dimension2_level_value in varchar2,
14 actual in number,
15 period_set_name in varchar2) IS
16 actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
17 x_return_status VARCHAR2(30);
18 x_msg_count NUMBER;
19 x_msg_data VARCHAR2(30);
20 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
21 BEGIN
22
23 actual_rec.Target_Level_ID := target_level_id;
24 if (period_set_name is not NULL) then
25 actual_rec.Time_Level_Value_ID := period_set_name || '+' ||
26 time_level_value;
27 end if;
28 actual_rec.Org_Level_value_ID := org_level_value;
29 actual_rec.Dim1_Level_Value_ID := dimension1_level_value;
30 actual_rec.Dim2_Level_Value_ID := dimension2_level_value;
31 actual_rec.Actual := actual;
32
33 BIS_ACTUAL_PUB.Post_Actual( p_api_version => 1.0,
34 p_commit => FND_API.G_TRUE,
35 p_Actual_Rec => actual_rec,
36 x_return_status => x_return_status,
37 x_msg_count => x_msg_count,
38 x_msg_data => x_msg_data,
39 x_error_Tbl => x_error_Tbl);
40
41 END PostActual;
42
43
44 /*
45 * PostLevelActuals
46 * Will post all actuals for the given dimension level combination.
47 * The dimension level should be 0 for ALL, and increasing for
48 * finer levels.
49 * e.g.
50 * time_level = 0 : TOTAL_TIME
51 * time_level = 1 : YEAR
52 * time_level = 2 : QUARTER
53 * time_level = 3 : MONTH
54 */
55 PROCEDURE PostLevelActuals( target_level_id in number,
56 time_level in number,
57 org_level in number,
58 dimension1_level in number,
59 dimension2_level in number) IS
60
61 /* the values retrieved */
62 v_actual NUMBER;
63 v_time VARCHAR2(80);
64 v_period_setname VARCHAR2(80);
65 v_org VARCHAR2(80);
66 v_geography VARCHAR2(80);
67 v_product VARCHAR2(80);
68
69
70 /* SELECT clause */
71 p_select_time VARCHAR2(1000) := 'to_char(NULL) ';
72 p_select_period_setname VARCHAR2(1000) := 'to_char(NULL) ';
73 p_select_org VARCHAR2(1000) := 'to_char(NULL) ';
74 p_select_geo VARCHAR2(1000) := 'to_char(NULL) ';
75 p_select_product VARCHAR2(1000) := 'to_char(NULL) ';
76
77 /* FROM clause */
78 p_from VARCHAR2(1000) := 'WIP_BIS_UTZ_NOCAT_V ';
79
80 /* GROUP BY clause */
81 p_groupby_time VARCHAR2(1000) := 'to_char(NULL) ';
82 p_groupby_org VARCHAR2(1000) := 'to_char(NULL) ';
83 p_groupby_geo VARCHAR2(1000) := 'to_char(NULL) ';
84 p_groupby_product VARCHAR2(1000) := 'to_char(NULL) ';
85
86
87 /* dynamic SQL stuff */
88 cursor_id INTEGER;
89 ignore INTEGER;
90 p_select_statement VARCHAR2(32767);
91
92 BEGIN
93
94 /* TIME */
95
96 if time_level = 1 then
97 p_select_time := 'TEMP.period_year ';
98 p_select_period_setname := 'TEMP.period_set_name';
99 elsif time_level = 2 then
100 p_select_time := 'TEMP.period_quarter ';
101 p_select_period_setname := 'TEMP.period_set_name';
102 elsif time_level = 3 then
103 p_select_time := 'TEMP.period_month ';
104 p_select_period_setname := 'TEMP.period_set_name';
105 end if;
106 p_groupby_time := p_select_time || ', ' || p_select_period_setname;
107
108
109
110 /* ORGANIZATION */
111
112 if org_level = 1 then
113 p_select_org := 'TEMP.set_of_books_id ';
114 elsif org_level = 2 then
115 p_select_org := 'TEMP.legal_entity_id ';
116 elsif org_level = 3 then
117 p_select_org := 'TEMP.operating_unit_id ';
118 elsif org_level = 4 then
119 p_select_org := 'TEMP.organization_id ';
120 end if;
121 p_groupby_org := p_select_org;
122
123
124 /* GEOGRAPHY */
125
126 if dimension1_level = 2 then
127 p_select_geo := 'TEMP.area_code ';
128 p_groupby_geo := p_select_geo;
129 elsif dimension1_level = 3 then
130 p_select_geo := 'TEMP.area_code || ''+'' ' ||
131 '|| TEMP.country_code ';
132 p_groupby_geo := 'TEMP.area_code, TEMP.country_code ';
133 /* James 7/8/99 */
134 elsif dimension1_level = 4 THEN
135 p_select_geo := 'TEMP.area_code || ''+'' ' ||
136 '|| TEMP.country_code || ''+'' || TEMP.region_code ';
137 p_groupby_geo := 'TEMP.area_code, TEMP.country_code, TEMP.region_code ';
138 /* end James */
139 end if;
140
141
142 /* PRODUCT */
143
144 if dimension2_level = 2 then
145 p_select_product := 'TEMP.category_id ';
146 p_from := 'wip_bis_utz_catnoitem_v';
147 elsif dimension2_level = 3 then
148 p_select_product := 'TEMP.inventory_item_id ';
149 p_from := 'wip_bis_utz_cat_v';
150 end if;
151 p_groupby_product := p_select_product;
152
153
154 cursor_id := DBMS_SQL.OPEN_CURSOR;
155
156 -- No literals to change to bind variables as per coding standard.
157 p_select_statement :=
158 'select ' ||
159 p_select_time || ', ' ||
160 p_select_period_setname || ', ' ||
161 p_select_org || ', ' ||
162 p_select_geo || ', ' ||
163 p_select_product || ', ' ||
164 ' (decode(sum(TEMP.available_hours), 0, 0, ' ||
165 ' sum(TEMP.actual_hours)/sum(TEMP.available_hours))*100) ' ||
166 'from ' ||
167 p_from || ' TEMP ' ||
168 'group by ' ||
169 p_groupby_time || ', ' ||
170 p_groupby_org || ', ' ||
171 p_groupby_geo || ', ' ||
172 p_groupby_product;
173
174 DBMS_SQL.PARSE( cursor_id, p_select_statement, DBMS_SQL.V7 );
175
176 DBMS_SQL.DEFINE_COLUMN( cursor_id, 1, v_time , 80 );
177 DBMS_SQL.DEFINE_COLUMN( cursor_id, 2, v_period_setname , 80 );
178 DBMS_SQL.DEFINE_COLUMN( cursor_id, 3, v_org , 80 );
179 DBMS_SQL.DEFINE_COLUMN( cursor_id, 4, v_geography , 80 );
180 DBMS_SQL.DEFINE_COLUMN( cursor_id, 5, v_product , 80 );
181 DBMS_SQL.DEFINE_COLUMN( cursor_id, 6, v_actual );
182 ignore := DBMS_SQL.EXECUTE( cursor_id );
183
184
185 LOOP
186 IF DBMS_SQL.FETCH_ROWS( cursor_id ) > 0 THEN
187 DBMS_SQL.COLUMN_VALUE( cursor_id, 1, v_time );
188 DBMS_SQL.COLUMN_VALUE( cursor_id, 2, v_period_setname );
189 DBMS_SQL.COLUMN_VALUE( cursor_id, 3, v_org );
190 DBMS_SQL.COLUMN_VALUE( cursor_id, 4, v_geography );
191 DBMS_SQL.COLUMN_VALUE( cursor_id, 5, v_product );
192 DBMS_SQL.COLUMN_VALUE( cursor_id, 6, v_actual );
193
194 WIP_BIS_UTZ_ALERT.PostActual(
195 target_level_id,
196 v_time,
197 v_org,
198 v_geography,
199 v_product,
200 v_actual,
201 v_period_setname );
202
203 ELSE
204 EXIT;
205 END IF;
206
207 END LOOP;
208
209 DBMS_SQL.CLOSE_CURSOR(cursor_id);
210
211 END PostLevelActuals;
212
213
214 /*
215 * StartFlow
216 * Start the workflow by calling WIP_Strt_Wf_Process.
217 * It will setup the notification messages for the attributes.
218 */
219 PROCEDURE StartFlow( time_level_value in varchar2,
220 start_date in date,
221 end_date in date,
222 org_level_value in varchar2,
223 dimension1_level_value in varchar2,
224 dimension2_level_value in varchar2,
225 prod_id in number,
226 sob in varchar2,
227 le in varchar2,
228 ou in varchar2,
229 org in varchar2,
230 area in varchar2,
231 country in varchar2,
232 country_id IN VARCHAR2, /* James 7/12/99 */
233 region IN VARCHAR2, /* James 7/12/99 */
234 prod in varchar2,
235 item in varchar2,
236 actual in number,
237 target in number,
238 plan_id in number,
239 plan_name in varchar2,
240 wf in varchar2,
241 resp_id in number,
242 resp_name in varchar2,
243 org_level in number,
244 dimension1_level in number,
245 dimension2_level in number) IS
246
247 l_subject VARCHAR2(240);
248 l_sob VARCHAR2(240);
249 l_le VARCHAR2(240);
250 l_ou VARCHAR2(240);
251 l_org VARCHAR2(240);
252 l_area VARCHAR2(240);
253 l_country VARCHAR2(240);
254 l_region VARCHAR2(240); /* James 7/8/99 */
255 l_prod_cat VARCHAR2(240);
256 l_prod VARCHAR2(240);
257 l_period VARCHAR2(240);
258 l_actual VARCHAR2(240);
259 l_target VARCHAR2(240);
260 status Varchar2(30);
261 l_param varchar2(2000);
262
263 l_org_level VARCHAR2(100);
264 l_sob_id VARCHAR2(100) := NULL;
265 l_le_id VARCHAR2(100) := NULL;
266 l_ou_id VARCHAR2(100) := NULL;
267 l_org_id VARCHAR2(100) := NULL;
268 l_geo_level VARCHAR2(100);
269 l_area_id VARCHAR2(100) := NULL;
270 l_country_id VARCHAR2(100) := NULL;
271 l_region_id VARCHAR2(100) := NULL;
272 l_prod_level VARCHAR2(100);
273 l_pg_id VARCHAR2(100) := NULL;
274 l_item_id VARCHAR2(100) := NULL;
275
276 l_unassigned VARCHAR2(100) :=
277 fnd_message.get_string('BOM', 'CST_UNASSIGNED_LABEL');
278
279 BEGIN
280
281 l_subject := fnd_message.get_string('WIP', 'RESOURCE_UTILIZATION') ||
282 ' ' || fnd_message.get_string('WIP', 'WIP_ALERT');
283 l_sob := fnd_message.get_string('WIP', 'SET_OF_BOOKS') || ': ' || sob;
284 l_le := fnd_message.get_string('WIP', 'WIP_LEGAL_ENTITY') || ': ' || le;
285 l_ou := fnd_message.get_string('BOM', 'CST_OPERATING_UNIT_LABEL') ||
286 ': ' || ou;
287 l_org := fnd_message.get_string('WIP', 'INVENTORY_ORGANIZATION') ||
288 ': ' || org;
289 l_area := fnd_message.get_string('BOM', 'CST_AREA_LABEL') || ': ' || area;
290 l_country := fnd_message.get_string('BOM', 'CST_COUNTRY_LABEL') ||
291 ': ' || country;
292 /* James 7/8/99 */
293 l_region := fnd_message.get_string('BOM', 'CST_REGION_LABEL') ||
294 ': ' || region;
295 /* end James */
296 l_prod_cat := fnd_message.get_string('WIP', 'WIP_PROD_CATEGORY')
297 || ': ' || prod;
298 l_prod := fnd_message.get_string('BOM', 'ITEM_CAP') ||
299 ': ' || item;
300 l_period := fnd_message.get_string('WIP', 'WIP_PERIOD')
301 || ': ' || time_level_value;
302 l_actual := fnd_message.get_string('WIP', 'WIP_ACTUAL')
303 || ': ' || round(actual, 2);
304 l_target := fnd_message.get_string('WIP', 'WIP_TARGET')
305 || ': ' || round(target, 2);
306
307 IF(org_level = 1) /* SOB */ THEN
308 l_org_level := 'SET OF BOOKS';
309 END IF;
310 IF(org_level >= 1) THEN
311 SELECT MAX(set_of_books_id) INTO l_sob_id
312 FROM gl_sets_of_books
313 WHERE name = sob;
314 END IF;
315 IF(org_level = 2) /* LE */ THEN
316 l_org_level := 'LEGAL ENTITY';
317 END IF;
318 IF(org_level >= 2) THEN
319 SELECT MAX(organization_id) INTO l_le_id
320 FROM hr_legal_entities
321 WHERE name = le;
322 END IF;
323 IF(org_level = 3) /* OU */ THEN
324 l_org_level := 'OPERATING UNIT';
325 END IF;
326 IF(org_level >= 3) THEN
327 SELECT MAX(organization_id) INTO l_ou_id
328 FROM hr_operating_units
329 WHERE name = ou;
330 END IF;
331 IF(org_level = 4) THEN
332 l_org_level := 'ORGANIZATION';
333 SELECT MAX(organization_id) INTO l_org_id
334 FROM org_organization_definitions
335 WHERE organization_name = org;
336 END IF;
337
338 IF(dimension1_level = 1) /* TOTAL GEOGRAPHY */ THEN
339 l_geo_level := 'TOTAL GEOGRAPHY';
340 END IF;
341 IF(dimension1_level = 2) /* AREA */ THEN
342 l_geo_level := 'AREA';
343 END IF;
344 IF(dimension1_level >= 2) THEN
345 SELECT MAX(id) INTO l_area_id
346 FROM bis_areas_v
347 WHERE name = area;
348 END IF;
349 IF(dimension1_level = 3) /* COUNTRY */ THEN
350 l_geo_level := 'COUNTRY';
351 END IF;
352 IF(dimension1_level >= 3) THEN
353 l_country_id := country_id;
354 END IF;
355 IF(dimension1_level = 4) /* REGION */ THEN
356 l_geo_level := 'REGION';
357 END IF;
358 IF(dimension1_level >= 4) THEN
359 SELECT MAX(region_code) INTO l_region_id
360 FROM bis_regions_v
361 WHERE area_code = l_area_id AND country_code = l_country_id
362 AND name = region;
363 END IF;
364
365 IF(dimension2_level = 1) /* TOTAL PRODUCTS */ THEN
366 l_prod_level := 'TOTAL PRODUCTS';
367 END IF;
368 IF(dimension2_level = 2) /* PRODUCT GROUP */ THEN
369 l_prod_level := 'PRODUCT GROUP';
370 END IF;
371 IF(dimension2_level >= 2) THEN
372 l_pg_id := To_char(prod_id);
373 END IF;
374 IF(dimension2_level = 3) /* ITEM */ THEN
375 l_prod_level := 'ITEM';
376 END IF;
377 IF(dimension2_level >= 3) THEN
378 SELECT MAX(inventory_item_id) INTO l_item_id
379 FROM wip_bis_utz_cat_v
380 WHERE category_name = prod AND inventory_item_name = item;
381 END IF;
382
383 l_param := 'P_PARAM_FROM_DATE=' ||
384 to_char(start_date, 'DD-MON-YYYY') ||
385 '*P_PARAM_TO_DATE=' ||
386 to_char(end_date, 'DD-MON-YYYY') ||
387 '*P_PARAM_ORG_LEVEL=' ||
388 REPLACE(l_org_level, ' ', '%20') ||
389 '*P_PARAM_SOB_ID=' || l_sob_id ||
390 '*P_PARAM_LE_ID=' || l_le_id ||
391 '*P_PARAM_OU_ID=' || l_ou_id ||
392 '*P_PARAM_ORG_ID=' || l_org_id ||
393 '*P_PARAM_GEO_LEVEL=' ||
394 REPLACE(l_geo_level, ' ', '%20') ||
395 '*P_PARAM_AREA_ID=' || l_area_id ||
396 '*P_PARAM_COUNTRY_ID=' || l_country_id ||
397 '*P_PARAM_REGION_ID=' || l_region_id ||
398 '*P_PARAM_PROD_LEVEL=' ||
399 REPLACE(l_prod_level, ' ', '%20') ||
400 '*P_PARAM_PG_ID=' || l_pg_id ||
401 '*P_PARAM_ITEM_ID=' || l_item_id ||
402 '*P_PARAM_PLAN_ID=' || plan_id ||
403 '*P_PARAM_VIEW_BY=TIME' ||
404 '*paramform=NO*';
405
406 WIP_Strt_Wf_Process(
410 p_ou => l_ou,
407 p_subject => l_subject,
408 p_sob => l_sob,
409 p_le => l_le,
411 p_org => l_org,
412 p_area => l_area,
413 p_country => l_country,
414 p_region => l_region,
415 p_prod_cat => l_prod_cat,
416 p_prod => l_prod,
417 p_period => l_period,
418 p_target => l_target,
419 p_actual => l_actual,
420 -- p_wf_process => 'WIP_UTZ_SEND_NOTIFICATION',
421 p_wf_process => wf,
422 -- p_role => 'DEVELOPER',
423 p_role => resp_name,
424 -- p_resp_id => 52344,
425 p_resp_id => resp_id,
426 p_report_name => 'WIPBIUTZ',
427 p_report_param => l_param,
428 x_return_status => status);
429
430 END StartFlow;
431
432
433 /*
434 * CompareLevelTarget
435 * Called by Alert_Check which
436 * compares all actuals against all targets defined for the
437 * given dimension level combination.
438 * This routine will be called with every combination of
439 * the 4 dimensions.
440 *
441 * Based on the range values (high/low) defined in the PMF,
442 * StartFlow will be called to start the workflow.
443 */
444 PROCEDURE CompareLevelTarget( target_level_id in number,
445 time_level in number,
446 org_level in number,
447 dimension1_level in number,
448 dimension2_level in number) IS
449 /* the values retrieved */
450 v_wf VARCHAR2(80);
451 v_plan_id NUMBER;
452 v_plan_name VARCHAR2(80);
453 v_range1_low NUMBER;
454 v_range1_high NUMBER;
455 v_range2_low NUMBER;
456 v_range2_high NUMBER;
457 v_range3_low NUMBER;
458 v_range3_high NUMBER;
459 v_resp1_id NUMBER;
460 v_resp2_id NUMBER;
461 v_resp3_id NUMBER;
462 v_resp1_name VARCHAR2(100);
463 v_resp2_name VARCHAR2(100);
464 v_resp3_name VARCHAR2(100);
465 v_target NUMBER;
466 v_actual NUMBER;
467 v_time VARCHAR2(80);
468 v_org VARCHAR2(80);
469 v_geography VARCHAR2(80);
470 v_product VARCHAR2(80);
471 v_prod_id NUMBER;
472 v_sob_name VARCHAR2(80);
473 v_le_name VARCHAR2(80);
474 v_ou_name VARCHAR2(80);
475 v_org_name HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
476 v_area_name VARCHAR2(80);
477 v_country_name VARCHAR2(80);
478 v_prod_name VARCHAR2(80);
479 v_item_name VARCHAR2(80);
480 v_start_date DATE;
481 v_end_date DATE;
482 v_region_name VARCHAR2(80); /* James 7/9/99 */
483 v_country_id VARCHAR2(80); /* James 7/12/99 */
484 all_text VARCHAR2(240);
485
486
487 /* SELECT clause */
488 p_select_time VARCHAR2(1000) := '-1 ';
489 p_select_start_date VARCHAR2(1000) := 'to_date(NULL) ';
490 p_select_end_date VARCHAR2(1000) := 'to_date(NULL) ';
491 p_select_org VARCHAR2(1000) := '-1 ';
492 p_select_geo VARCHAR2(1000) := '-1 ';
493 p_select_product VARCHAR2(1000) := '-1 ';
494 p_select_prod_id VARCHAR2(1000) := 'to_number(NULL) ';
495 p_select_sob_name VARCHAR2(1000) ;
496 p_select_le_name VARCHAR2(1000) ;
497 p_select_ou_name VARCHAR2(1000) ;
498 p_select_org_name VARCHAR2(1000) ;
499 p_select_area_name VARCHAR2(1000) ;
500 p_select_country_name VARCHAR2(1000) ;
501 p_select_country_id VARCHAR2(1000) := 'to_char(NULL) ';
502 p_select_prod_name VARCHAR2(1000) ;
503 p_select_item_name VARCHAR2(1000) ;
504 /* James 7/9/99 */
505 p_select_region_name VARCHAR2(1000) ;
506 /* end James */
507
508 /* FROM clause */
509 p_from VARCHAR2(1000) := 'WIP_BIS_UTZ_NOCAT_V ';
510 p_from_time VARCHAR2(1000) := 'sys.dual ';
511
512 /* WHERE for target clause */
513 p_where_trgt_time VARCHAR2(1000) := 'and 1=1 ';
514 p_where_trgt_org VARCHAR2(1000) := 'and 1=1 ';
515 p_where_trgt_geo VARCHAR2(1000) := 'and 1=1 ';
516 p_where_trgt_prod VARCHAR2(1000) := 'and 1=1 ';
517
518 /* GROUP BY clause */
519 p_groupby_time VARCHAR2(1000) := '-1 ';
520 p_groupby_org VARCHAR2(1000) := '-1 ';
521 p_groupby_geo VARCHAR2(1000) := '-1 ';
522 p_groupby_product VARCHAR2(1000) := '-1 ';
523
524 /* CONTEXT for target level */
525 p_context_time VARCHAR2(100) := 'TOTAL_TIME';
526 p_context_org VARCHAR2(100) := 'SET OF BOOKS';
527 p_context_prod VARCHAR2(100) := 'TOTAL_PRODUCT';
528 p_context_geo VARCHAR2(100) := 'TOTAL_GEOGRAPHY';
529
530 /* dynamic SQL stuff */
531 cursor_id INTEGER;
532 ignore INTEGER;
533 p_select_statement VARCHAR2(32767);
534
535 l_resp_id NUMBER;
536 l_resp_name VARCHAR2(100);
537 BEGIN
538
539 /* initialize all text */
540 all_text := '''' || FND_MESSAGE.get_string('WIP', 'WIP_ALL') || '''';
541
542 p_select_sob_name := all_text;
543 p_select_le_name := all_text;
544 p_select_ou_name := all_text;
548 p_select_prod_name := all_text;
545 p_select_org_name := all_text;
546 p_select_area_name := all_text;
547 p_select_country_name := all_text;
549 p_select_item_name := all_text;
550 p_select_region_name := all_text;
551
552 /* TIME */
553
554 if time_level = 0 then /* we do not checking total time for target */
555 return;
556 end if;
557
558 if time_level = 1 then
559 p_from_time := 'gl_periods ';
560 p_select_start_date := 'gl_p.start_date '; -- Bug 3554853
561 p_select_end_date := 'gl_p.end_date '; -- Bug 3554853
562 p_select_time := 'TEMP.period_year ';
563 p_context_time := 'YEAR';
564 p_where_trgt_time := 'and gl_p.period_set_name = TEMP.period_set_name ' || -- Bug 3554853
565 'and gl_p.period_name = TEMP.period_year ' || -- Bug 3554853
566 'and gl_p.start_date = (select max(gl_p.start_date) from gl_periods ' || -- Bug 3554853
567 'where period_set_name = TEMP.period_set_name ' ||
568 ' and period_type = ''Year'') ';
569 elsif time_level = 2 then
570 p_from_time := 'gl_periods ';
571 p_select_start_date := 'gl_p.start_date '; -- Bug 3554853
572 p_select_end_date := 'gl_p.end_date '; -- Bug 3554853
573 p_select_time := 'TEMP.period_quarter ';
574 p_context_time := 'QUARTER';
575 p_where_trgt_time := 'and gl_p.period_set_name = TEMP.period_set_name ' || -- Bug 3554853
576 'and gl_p.period_name = TEMP.period_quarter ' || -- Bug 3554853
577 'and gl_p.start_date = (select max(gl_p.start_date) from gl_periods ' || -- Bug 3554853
578 'where period_set_name = TEMP.period_set_name ' ||
579 ' and period_type = ''Quarter'') ';
580 elsif time_level = 3 then
581 p_from_time := 'gl_periods ';
582 p_select_start_date := 'gl_p.start_date '; -- Bug 3554853
583 p_select_end_date := 'gl_p.end_date '; -- Bug 3554853
584 p_select_time := 'TEMP.period_month ';
585 p_context_time := 'MONTH';
586 p_where_trgt_time := 'and gl_p.period_set_name = TEMP.period_set_name ' || -- Bug 3554853
587 'and gl_p.period_name = TEMP.period_month ' || -- Bug 3554853
588 'and gl_p.start_date = ' || -- Bug 3554853
589 ' (select max(gl_p.start_date) ' || -- Bug 3554853
590 ' from gl_periods p, gl_sets_of_books sob ' ||
591 ' where p.period_set_name = TEMP.period_set_name ' ||
592 ' and sob.SET_OF_BOOKS_ID = TEMP.set_of_books_id ' ||
593 ' and p.period_type = sob.ACCOUNTED_PERIOD_TYPE) ';
594 end if;
595 p_groupby_time := p_select_time || ', ' || p_select_start_date ||
596 ', ' || p_select_end_date;
597
598
599
600 /* ORGANIZATION */
601
602 if org_level = 1 then
603 p_select_org := 'TEMP.set_of_books_id ';
604 p_select_sob_name := 'TEMP.set_of_books_name';
605 p_context_org := 'SET OF BOOKS';
606 p_where_trgt_org := 'and trgt.org_level_value_id = to_char(TEMP.set_of_books_id) ';
607 elsif org_level = 2 then
608 p_select_org := 'TEMP.legal_entity_id ';
609 p_select_sob_name := 'TEMP.set_of_books_name';
610 p_select_le_name := 'TEMP.legal_entity_name';
611 p_context_org := 'LEGAL ENTITY';
612 p_where_trgt_org := 'and trgt.org_level_value_id = to_char(TEMP.legal_entity_id) ';
613 elsif org_level = 3 then
614 p_select_org := 'TEMP.operating_unit_id ';
615 p_select_sob_name := 'TEMP.set_of_books_name';
616 p_select_le_name := 'TEMP.legal_entity_name';
617 p_select_ou_name := 'TEMP.operating_unit_name';
618 p_context_org := 'OPERATING UNIT';
619 p_where_trgt_org := 'and trgt.org_level_value_id = to_char(TEMP.operating_unit_id) ';
620 elsif org_level = 4 then
621 p_select_org := 'TEMP.organization_id ';
622 p_select_sob_name := 'TEMP.set_of_books_name';
623 p_select_le_name := 'TEMP.legal_entity_name';
624 p_select_ou_name := 'TEMP.operating_unit_name';
625 p_select_org_name := 'TEMP.organization_name';
626 p_context_org := 'ORGANIZATION';
627 p_where_trgt_org := 'and trgt.org_level_value_id = to_char(TEMP.organization_id) ';
628 end if;
629
630 /* GEOGRAPHY */
631
632 if dimension1_level = 2 then /* area */
633 p_select_geo := 'TEMP.area_code ';
634 p_select_area_name := 'TEMP.area_name ';
635 p_context_geo := 'AREA';
636 p_where_trgt_geo := 'and trgt.dim1_level_value_id = ' ||
637 'TEMP.area_code ';
638 p_groupby_geo := p_select_geo || ', ' || p_select_area_name;
639 elsif dimension1_level = 3 then /* country */
640 p_select_geo := 'TEMP.country_code ' ;
641 p_select_area_name := 'TEMP.area_name ';
642 p_select_country_name := 'TEMP.country_name ';
643 p_context_geo := 'COUNTRY';
644 p_where_trgt_geo := 'and WIP_BIS_COMMON.get_segment(trgt.dim1_level_value_id, ''+'',' ||
645 ' 1) = TEMP.country_code ';
646 p_groupby_geo := p_select_area_name || ', ' ||
647 p_select_geo || ', ' ||
648 p_select_country_name;
649 /* James 7/9/99 */
650 ELSIF dimension1_level = 4 THEN /* region */
651 p_select_geo := 'TEMP.region_code ';
652 p_select_area_name := 'TEMP.area_name ';
653 p_select_country_name := 'TEMP.country_name ';
654 p_select_country_id := 'TEMP.country_code ';
655 p_select_region_name := 'TEMP.region_name ';
659 'and WIP_BIS_COMMON.get_segment(
656 p_context_geo := 'REGION';
657 p_where_trgt_geo := 'and WIP_BIS_COMMON.get_segment(
658 trgt.dim1_level_value_id, ''+'',' || ' 1) = TEMP.country_code ' ||
660 trgt.dim1_level_value_id, ''+'',' || ' 2) = TEMP.region_code ';
661 p_groupby_geo := p_select_area_name || ', ' ||
662 p_select_geo || ', ' ||
663 p_select_country_name || ', ' ||
664 p_select_country_id || ', ' || -- Added for bug 3570060
665 p_select_region_name;
666 /* end James */
667
668 end if;
669
670
671 /* PRODUCT */
672
673 if dimension2_level = 2 then /* prod cat */
674 p_select_product := 'TEMP.category_id ';
675 p_select_prod_name := 'TEMP.category_name ';
676 p_groupby_product := 'TEMP.category_id, TEMP.category_name';
677 p_from := 'wip_bis_utz_catnoitem_v';
678 p_context_prod := 'PRODUCT GROUP';
679 p_where_trgt_prod := 'and trgt.dim2_level_value_id = to_char(TEMP.category_id) ';
680 elsif dimension2_level = 3 then /* item */
681 p_select_product := 'TEMP.inventory_item_id ';
682 p_select_prod_id := 'TEMP.category_id ';
683 p_select_prod_name := 'TEMP.category_name ';
684 p_select_item_name := 'TEMP.inventory_item_name ';
685 p_groupby_product := 'TEMP.category_id, TEMP.inventory_item_id, ' ||
686 'TEMP.category_name, TEMP.inventory_item_name';
687 p_from := 'wip_bis_utz_cat_v';
688 p_context_prod := 'ITEM';
689 p_where_trgt_prod := 'and trgt.dim2_level_value_id = to_char(TEMP.inventory_item_id) ';
690 end if;
691
692
693 cursor_id := DBMS_SQL.OPEN_CURSOR;
694
695 -- Changing literals to bind variables as per coding standard.
696 p_select_statement :=
697 'select ' ||
698 p_select_time || ', ' ||
699 p_select_org || ', ' ||
700 p_select_geo || ', ' ||
701 p_select_product || ', ' ||
702 p_select_prod_id || ', ' ||
703 p_select_sob_name || ', ' ||
704 p_select_le_name || ', ' ||
705 p_select_ou_name || ', ' ||
706 p_select_org_name || ', ' ||
707 p_select_area_name || ', ' ||
708 p_select_country_name || ', ' ||
709 p_select_prod_name || ', ' ||
710 p_select_item_name || ', ' ||
711 ' (decode(sum(TEMP.available_hours), 0, 0, ' ||
712 ' sum(TEMP.actual_hours)/sum(TEMP.available_hours))*100), ' ||
713 ' trgt.target, ' ||
714 ' bbp.plan_id, ' ||
715 ' bbp.name, ' ||
716 ' btl.workflow_process_short_name, ' ||
717 ' min(trgt.range1_low), ' ||
718 ' min(trgt.range1_high), ' ||
719 ' min(trgt.range2_low), ' ||
720 ' min(trgt.range2_high), ' ||
721 ' min(trgt.range3_low), ' ||
722 ' min(trgt.range3_high), ' ||
723 ' min(trgt.notify_resp1_id), ' ||
724 ' min(trgt.notify_resp2_id), ' ||
725 ' min(trgt.notify_resp3_id), ' ||
726 ' min(trgt.notify_resp1_short_name), ' ||
727 ' min(trgt.notify_resp2_short_name), ' ||
728 ' min(trgt.notify_resp3_short_name), ' ||
729 p_select_start_date || ', ' ||
730 p_select_end_date || ', ' ||
731 p_select_region_name || ', ' || /* James */
732 p_select_country_id || /* James */
733
734 'from ' ||
735 ' bisbv_business_plans bbp, ' ||
736 ' bisbv_targets trgt, ' ||
737 ' bisbv_target_levels btl, ' ||
738 p_from_time || ' gl_p, ' || -- Bug 3554853
739 p_from || ' TEMP ' ||
740 'where btl.target_level_id = ' || ':target_level_id' ||
741 ' and trgt.target_level_id = btl.target_level_id ' ||
742 ' and bbp.plan_id = trgt.plan_id ' ||
743 p_where_trgt_time || ' ' ||
744 p_where_trgt_org || ' ' ||
745 p_where_trgt_geo || ' ' ||
746 p_where_trgt_prod || ' ' ||
747 'group by ' ||
748 p_groupby_time || ', ' ||
749 p_select_org || ', ' ||
750 p_select_sob_name || ', ' ||
751 p_select_le_name || ', ' ||
752 p_select_ou_name || ', ' ||
753 p_select_org_name || ', ' ||
754 p_groupby_org || ', ' ||
755 p_groupby_geo || ', ' ||
756 p_groupby_product || ', ' ||
757 'trgt.target, bbp.plan_id, bbp.name, ' ||
758 'btl.workflow_process_short_name ';
759
760 DBMS_SQL.PARSE( cursor_id, p_select_statement, DBMS_SQL.V7 );
761
762 DBMS_SQL.BIND_VARIABLE (cursor_id, ':target_level_id', target_level_id);
763
764 DBMS_SQL.DEFINE_COLUMN( cursor_id, 1, v_time , 80 );
765 DBMS_SQL.DEFINE_COLUMN( cursor_id, 2, v_org , 80 );
766 DBMS_SQL.DEFINE_COLUMN( cursor_id, 3, v_geography , 80 );
767 DBMS_SQL.DEFINE_COLUMN( cursor_id, 4, v_product , 80 );
768 DBMS_SQL.DEFINE_COLUMN( cursor_id, 5, v_prod_id );
769 DBMS_SQL.DEFINE_COLUMN( cursor_id, 6, v_sob_name , 80 );
770 DBMS_SQL.DEFINE_COLUMN( cursor_id, 7, v_le_name , 80 );
771 DBMS_SQL.DEFINE_COLUMN( cursor_id, 8, v_ou_name , 80 );
772 DBMS_SQL.DEFINE_COLUMN( cursor_id, 9, v_org_name , 240 );
773 DBMS_SQL.DEFINE_COLUMN( cursor_id, 10, v_area_name , 80 );
777 DBMS_SQL.DEFINE_COLUMN( cursor_id, 14, v_actual );
774 DBMS_SQL.DEFINE_COLUMN( cursor_id, 11, v_country_name , 80 );
775 DBMS_SQL.DEFINE_COLUMN( cursor_id, 12, v_prod_name , 80 );
776 DBMS_SQL.DEFINE_COLUMN( cursor_id, 13, v_item_name , 80 );
778 DBMS_SQL.DEFINE_COLUMN( cursor_id, 15, v_target );
779 DBMS_SQL.DEFINE_COLUMN( cursor_id, 16, v_plan_id );
780 DBMS_SQL.DEFINE_COLUMN( cursor_id, 17, v_plan_name , 80 );
781 DBMS_SQL.DEFINE_COLUMN( cursor_id, 18, v_wf , 80 );
782 DBMS_SQL.DEFINE_COLUMN( cursor_id, 19, v_range1_low );
783 DBMS_SQL.DEFINE_COLUMN( cursor_id, 20, v_range1_high );
784 DBMS_SQL.DEFINE_COLUMN( cursor_id, 21, v_range2_low );
785 DBMS_SQL.DEFINE_COLUMN( cursor_id, 22, v_range2_high );
786 DBMS_SQL.DEFINE_COLUMN( cursor_id, 23, v_range3_low );
787 DBMS_SQL.DEFINE_COLUMN( cursor_id, 24, v_range3_high );
788 DBMS_SQL.DEFINE_COLUMN( cursor_id, 25, v_resp1_id );
789 DBMS_SQL.DEFINE_COLUMN( cursor_id, 26, v_resp2_id );
790 DBMS_SQL.DEFINE_COLUMN( cursor_id, 27, v_resp3_id );
791 DBMS_SQL.DEFINE_COLUMN( cursor_id, 28, v_resp1_name , 100 );
792 DBMS_SQL.DEFINE_COLUMN( cursor_id, 29, v_resp2_name , 100 );
793 DBMS_SQL.DEFINE_COLUMN( cursor_id, 30, v_resp3_name , 100 );
794 DBMS_SQL.DEFINE_COLUMN( cursor_id, 31, v_start_date );
795 DBMS_SQL.DEFINE_COLUMN( cursor_id, 32, v_end_date );
796 dbms_sql.define_column( cursor_id, 33, v_region_name , 80 ); /* James */
797 dbms_sql.define_column( cursor_id, 34, v_country_id , 80 ); /* James */
798 ignore := DBMS_SQL.EXECUTE( cursor_id );
799
800
801 LOOP
802 IF DBMS_SQL.FETCH_ROWS( cursor_id ) > 0 THEN
803 DBMS_SQL.COLUMN_VALUE( cursor_id, 1, v_time );
804 DBMS_SQL.COLUMN_VALUE( cursor_id, 2, v_org );
805 DBMS_SQL.COLUMN_VALUE( cursor_id, 3, v_geography );
806 DBMS_SQL.COLUMN_VALUE( cursor_id, 4, v_product );
807 DBMS_SQL.COLUMN_VALUE( cursor_id, 5, v_prod_id );
808 DBMS_SQL.COLUMN_VALUE( cursor_id, 6, v_sob_name );
809 DBMS_SQL.COLUMN_VALUE( cursor_id, 7, v_le_name );
810 DBMS_SQL.COLUMN_VALUE( cursor_id, 8, v_ou_name );
811 DBMS_SQL.COLUMN_VALUE( cursor_id, 9, v_org_name );
812 DBMS_SQL.COLUMN_VALUE( cursor_id, 10, v_area_name );
813 DBMS_SQL.COLUMN_VALUE( cursor_id, 11, v_country_name );
814 DBMS_SQL.COLUMN_VALUE( cursor_id, 12, v_prod_name );
815 DBMS_SQL.COLUMN_VALUE( cursor_id, 13, v_item_name );
816 DBMS_SQL.COLUMN_VALUE( cursor_id, 14, v_actual );
817 DBMS_SQL.COLUMN_VALUE( cursor_id, 15, v_target );
818 DBMS_SQL.COLUMN_VALUE( cursor_id, 16, v_plan_id );
819 DBMS_SQL.COLUMN_VALUE( cursor_id, 17, v_plan_name );
820 DBMS_SQL.COLUMN_VALUE( cursor_id, 18, v_wf );
821 DBMS_SQL.COLUMN_VALUE( cursor_id, 19, v_range1_low );
822 DBMS_SQL.COLUMN_VALUE( cursor_id, 20, v_range1_high );
823 DBMS_SQL.COLUMN_VALUE( cursor_id, 21, v_range2_low );
824 DBMS_SQL.COLUMN_VALUE( cursor_id, 22, v_range2_high );
825 DBMS_SQL.COLUMN_VALUE( cursor_id, 23, v_range3_low );
826 DBMS_SQL.COLUMN_VALUE( cursor_id, 24, v_range3_high );
827 DBMS_SQL.COLUMN_VALUE( cursor_id, 25, v_resp1_id );
828 DBMS_SQL.COLUMN_VALUE( cursor_id, 26, v_resp2_id );
829 DBMS_SQL.COLUMN_VALUE( cursor_id, 27, v_resp3_id );
830 DBMS_SQL.COLUMN_VALUE( cursor_id, 28, v_resp1_name );
831 DBMS_SQL.COLUMN_VALUE( cursor_id, 29, v_resp2_name );
832 DBMS_SQL.COLUMN_VALUE( cursor_id, 30, v_resp3_name );
833 DBMS_SQL.COLUMN_VALUE( cursor_id, 31, v_start_date );
834 DBMS_SQL.COLUMN_VALUE( cursor_id, 32, v_end_date );
835 dbms_sql.column_value( cursor_id, 33, v_region_name ); /* James */
836 dbms_sql.column_value( cursor_id, 34, v_country_id ); /* James */
837
838 /* James 7/12/99 debugging */
839 /*INSERT INTO my_alert_test
840 VALUES(v_actual, v_target, v_range1_low, v_range1_high, v_range2_low,
841 v_range2_high, v_range3_low, v_range3_high, v_time, v_org,
842 v_geography, v_product);*/
843 /* end James debugging */
844
845
846 /* do the range checking */
847 if ( v_actual < v_target*(1-(v_range1_low/100)) OR
848 v_actual > v_target*(1+(v_range1_high/100)) ) then
849 WIP_BIS_UTZ_ALERT.StartFlow(
850 v_time, v_start_date, v_end_date,
851 v_org, v_geography, v_product, v_prod_id,
852 v_sob_name, v_le_name, v_ou_name, v_org_name,
853 v_area_name, v_country_name, v_country_id, v_region_name,
854 v_prod_name, v_item_name,
855 v_actual, v_target,
856 v_plan_id, v_plan_name, v_wf,
857 v_resp1_id, v_resp1_name,
858 org_level, dimension1_level, dimension2_level);
859 end if;
860
861 if ( v_actual < v_target*(1-(v_range2_low/100)) OR
862 v_actual > v_target*(1+(v_range2_high/100)) ) then
863 WIP_BIS_UTZ_ALERT.StartFlow(
864 v_time, v_start_date, v_end_date,
865 v_org, v_geography, v_product, v_prod_id,
866 v_sob_name, v_le_name, v_ou_name, v_org_name,
867 v_area_name, v_country_name, v_country_id, v_region_name,
868 v_prod_name, v_item_name,
869 v_actual, v_target,
870 v_plan_id, v_plan_name, v_wf,
874
871 v_resp2_id, v_resp2_name,
872 org_level, dimension1_level, dimension2_level);
873 end if;
875 if ( v_actual < v_target*(1-(v_range3_low/100)) OR
876 v_actual > v_target*(1+(v_range3_high/100)) ) then
877 WIP_BIS_UTZ_ALERT.StartFlow(
878 v_time, v_start_date, v_end_date,
879 v_org, v_geography, v_product, v_prod_id,
880 v_sob_name, v_le_name, v_ou_name, v_org_name,
881 v_area_name, v_country_name, v_country_id, v_region_name,
882 v_prod_name, v_item_name,
883 v_actual, v_target,
884 v_plan_id, v_plan_name, v_wf,
885 v_resp3_id, v_resp3_name,
886 org_level, dimension1_level, dimension2_level);
887 end if;
888 ELSE
889 EXIT;
890 END IF;
891
892 END LOOP;
893
894 DBMS_SQL.CLOSE_CURSOR(cursor_id);
895
896 END CompareLevelTarget;
897
898 /*
899 * WIP_Strt_Wf_Process
900 * This procedure starts the workflow process based on the passed
901 * in parameters
902 */
903 PROCEDURE WIP_Strt_Wf_Process(
904 p_subject IN varchar2,
905 p_sob IN varchar2,
906 p_le IN varchar2,
907 p_ou IN varchar2,
908 p_org IN varchar2,
909 p_area IN varchar2,
910 p_country IN varchar2,
911 p_region IN VARCHAR2, /* James 7/12/99 */
912 p_prod_cat IN varchar2,
913 p_prod IN varchar2,
914 p_period IN varchar2,
915 p_target IN varchar2,
916 p_actual IN varchar2,
917 p_wf_process IN varchar2,
918 p_role IN varchar2,
919 p_resp_id IN number,
920 p_report_name IN varchar2,
921 p_report_param IN varchar2,
922 x_return_status OUT NOCOPY varchar2
923 ) IS
924 l_wf_item_key Number;
925 l_item_type Varchar2(30) := 'WIPBISWF';
926 l_report_link Varchar2(500);
927 l_role_name Varchar2(80);
928 l_url1 Varchar2(2000);
929
930 cursor c_role_name is
931 select name from wf_roles
932 where name = p_role;
933
934 BEGIN
935 x_return_status := FND_API.G_RET_STS_SUCCESS;
936 if p_wf_process is null
937 or p_role is null then
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 return;
940 end if;
941
942 open c_role_name;
943 fetch c_role_name into l_role_name;
944 if c_role_name%NOTFOUND then
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 close c_role_name;
947 return;
948 end if;
949 close c_role_name;
950
951 select bis_excpt_wf_s.nextval
952 into l_wf_item_key
953 from dual;
954
955 l_report_link := FND_PROFILE.value('ICX_REPORT_LINK');
956
957 if p_report_name is not null then
958 l_url1 := l_report_link || 'OracleOASIS.RunReport?report='
959 || p_report_name|| '&Parameters=' || p_report_param
960 || '&responsibility_id=' || p_resp_id;
961 end if;
962
963 -- create a new workflow process
964 wf_engine.CreateProcess(itemtype=>l_item_type
965 ,itemkey =>l_wf_item_key
966 ,process =>p_wf_process);
967
968 -- set the workflow attributes
969 wf_engine.SetItemAttrText(itemtype=>l_item_type
970 ,itemkey =>l_wf_item_key
971 ,aname=>'L_ROLE_NAME'
972 ,avalue=>L_ROLE_NAME);
973
974 wf_engine.SetItemAttrText(itemtype=>l_item_type
975 ,itemkey =>l_wf_item_key
976 ,aname=>'L_SUBJECT'
977 ,avalue=>p_subject);
978 wf_engine.SetItemAttrText(itemtype=>l_item_type
979 ,itemkey =>l_wf_item_key
980 ,aname=>'L_SOB'
981 ,avalue=>p_sob);
982 wf_engine.SetItemAttrText(itemtype=>l_item_type
983 ,itemkey =>l_wf_item_key
984 ,aname=>'L_LEGAL_ENTITY'
985 ,avalue=>p_le);
986 wf_engine.SetItemAttrText(itemtype=>l_item_type
987 ,itemkey =>l_wf_item_key
988 ,aname=>'L_OU'
989 ,avalue=>p_ou);
990 wf_engine.SetItemAttrText(itemtype=>l_item_type
991 ,itemkey =>l_wf_item_key
992 ,aname=>'L_ORG'
993 ,avalue=>p_org);
994 wf_engine.SetItemAttrText(itemtype=>l_item_type
995 ,itemkey =>l_wf_item_key
996 ,aname=>'L_AREA'
997 ,avalue=>p_area);
998 wf_engine.SetItemAttrText(itemtype=>l_item_type
999 ,itemkey =>l_wf_item_key
1000 ,aname=>'L_COUNTRY'
1001 ,avalue=>p_country);
1002 /* James 7/12/99 */
1006 ,avalue=>p_region);
1003 wf_engine.setitemattrtext(itemtype=>l_item_type
1004 ,itemkey =>l_wf_item_key
1005 ,aname=>'L_REGION'
1007 /* end James */
1008 wf_engine.SetItemAttrText(itemtype=>l_item_type
1009 ,itemkey =>l_wf_item_key
1010 ,aname=>'L_PROD_CAT'
1011 ,avalue=>p_prod_cat);
1012 wf_engine.SetItemAttrText(itemtype=>l_item_type
1013 ,itemkey =>l_wf_item_key
1014 ,aname=>'L_PROD'
1015 ,avalue=>p_prod);
1016 wf_engine.SetItemAttrText(itemtype=>l_item_type
1017 ,itemkey =>l_wf_item_key
1018 ,aname=>'L_PERIOD'
1019 ,avalue=>p_period);
1020 wf_engine.SetItemAttrText(itemtype=>l_item_type
1021 ,itemkey =>l_wf_item_key
1022 ,aname=>'L_TARGET'
1023 ,avalue=>p_target);
1024 wf_engine.SetItemAttrText(itemtype=>l_item_type
1025 ,itemkey =>l_wf_item_key
1026 ,aname=>'L_ACTUAL'
1027 ,avalue=>p_actual);
1028 if l_url1 is not null then
1029 wf_engine.SetItemAttrText(itemtype=>l_item_type
1030 ,itemkey =>l_wf_item_key
1031 ,aname=>'L_URL1'
1032 ,avalue=>l_url1);
1033 end if;
1034
1035 -- start the process
1036 wf_engine.StartProcess(itemtype=>l_item_type
1037 ,itemkey => l_wf_item_key);
1038
1039 END WIP_Strt_Wf_Process;
1040
1041 /*
1042 * Alert_Check
1043 * This procedure loops through all the target levels defined for
1044 * Resource Utilization performance measure and call
1045 * 1) PostLevelActuals to post actuals to the BIS table
1046 * 2) CompareLevelTarget to compare actual against target
1047 */
1048 PROCEDURE Alert_Check IS
1049 target_level_id number := 0;
1050 time_level number := 0;
1051 org_level number := 0;
1052 geography_level number := 0;
1053 product_level number := 0;
1054
1055 CURSOR get_target_level IS
1056 select btl.target_level_id target_level_id,
1057 decode(tltime.dimension_level_short_name,
1058 'TOTAL_TIME', 0,
1059 'YEAR', 1,
1060 'QUARTER', 2,
1061 'MONTH', 3) time_value,
1062 decode(tlorg.dimension_level_short_name,
1063 'SET OF BOOKS', 1,
1064 'LEGAL ENTITY', 2,
1065 'OPERATING UNIT', 3,
1066 'ORGANIZATION', 4) org_value,
1067 decode(tlgeo.dimension_level_short_name,
1068 'TOTAL GEOGRAPHY', 1,
1069 'AREA', 2,
1070 'COUNTRY', 3,
1071 'REGION', 4) geo_value, /* James 7/8/99 */
1072 decode(tlcat.dimension_level_short_name,
1073 'TOTAL PRODUCTS', 1,
1074 'PRODUCT GROUP', 2,
1075 'ITEM', 3) prod_value
1076 from bisbv_performance_measures bpm,
1077 bisbv_target_levels btl,
1078 bisbv_dimension_levels tltime,
1079 bisbv_dimension_levels tlorg,
1080 bisbv_dimension_levels tlgeo,
1081 bisbv_dimension_levels tlcat
1082 where bpm.measure_short_name = 'WIPBIUZIND'
1083 and btl.measure_id = bpm.measure_id
1084 and btl.time_level_id = tltime.dimension_level_id
1085 and btl.org_level_id = tlorg.dimension_level_id
1086 and btl.dimension1_level_id = tlgeo.dimension_level_id
1087 and btl.dimension2_level_id = tlcat.dimension_level_id;
1088
1089 BEGIN
1090
1091 FOR tl_rec in get_target_level LOOP
1092
1093 WIP_BIS_UTZ_ALERT.PostLevelActuals(tl_rec.target_level_id,
1094 tl_rec.time_value,
1095 tl_rec.org_value, tl_rec.geo_value,
1096 tl_rec.prod_value);
1097 WIP_BIS_UTZ_ALERT.CompareLevelTarget(tl_rec.target_level_id,
1098 tl_rec.time_value, tl_rec.org_value,
1099 tl_rec.geo_value, tl_rec.prod_value);
1100 END LOOP;
1101
1102 END Alert_Check;
1103
1104 END WIP_BIS_UTZ_ALERT;