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