[Home] [Help]
PACKAGE BODY: APPS.POA_BIS_ALERTS
Source
1 PACKAGE BODY POA_BIS_ALERTS AS
2 /* $Header: poaalrtb.pls 115.9 2003/03/15 00:22:54 jhou ship $ */
3
4 PROCEDURE get_target_value(
5 p_target_level_short_name IN VARCHAR2,
6 p_plan_short_name IN VARCHAR2,
7 p_time_period IN VARCHAR2,
8 p_org_id IN NUMBER,
9 p_found OUT NOCOPY BOOLEAN,
10 p_target_value OUT NOCOPY BIS_TARGET_PUB.Target_Rec_Type) IS
11 l_index NUMBER := 0;
12 CURSOR cr_target IS
13 SELECT tv.target_level_short_name
14 , tv.target_level_name
15 , tv.target_level_id
16 , tv.plan_short_name
17 , tv.plan_name
18 , tv.org_level_value_id
19 , tv.time_level_value_id
20 , tv.target
21 , tv.range1_low
22 , tv.range1_high
23 , tv.range2_low
24 , tv.range2_high
25 , tv.range3_low
26 , tv.range3_high
27 , tv.notify_resp1_id
28 , tv.notify_resp1_short_name
29 , tv.notify_resp2_id
30 , tv.notify_resp2_short_name
31 , tv.notify_resp3_id
32 , tv.notify_resp3_short_name
33 FROM BISFV_TARGETS tv
34 WHERE tv.target_level_short_name = p_target_level_short_name
35 AND tv.time_level_value_id = p_time_period
36 AND tv.org_level_value_id = TO_CHAR(p_org_id)
37 AND tv.plan_short_name = p_plan_short_name;
38 BEGIN
39 OPEN cr_target;
40 FETCH cr_target INTO
41 p_target_value.target_level_short_name,
42 p_target_value.target_level_name,
43 p_target_value.target_level_id,
44 p_target_value.plan_short_name,
45 p_target_value.plan_name,
46 p_target_value.org_level_value_id,
47 p_target_value.time_level_value_id,
48 p_target_value.target,
49 p_target_value.range1_low,
50 p_target_value.range1_high,
51 p_target_value.range2_low,
52 p_target_value.range2_high,
53 p_target_value.range3_low,
54 p_target_value.range3_high,
55 p_target_value.notify_resp1_id,
56 p_target_value.notify_resp1_short_name,
57 p_target_value.notify_resp2_id,
58 p_target_value.notify_resp2_short_name,
59 p_target_value.notify_resp3_id,
60 p_target_value.notify_resp3_short_name;
61 p_found := (cr_target%FOUND);
62 CLOSE cr_target;
63
64 EXCEPTION
65 WHEN others THEN
66 POA_LOG.put_line('Error getting target values:');
67 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
68 RAISE;
69 END get_target_value;
70
71 PROCEDURE get_target_orgs(
72 p_target_level_short_name IN VARCHAR2,
73 p_plan_short_name IN VARCHAR2,
74 p_target_orgs OUT NOCOPY BIS_TARGET_PUB.Target_Tbl_Type) IS
75 l_index NUMBER := 0;
76 CURSOR cr_target_org IS
77 SELECT distinct(org_level_value_id) org_id
78 FROM BISFV_TARGETS tv
79 WHERE tv.target_level_short_name = p_target_level_short_name
80 AND tv.plan_short_name = p_plan_short_name;
81 BEGIN
82
83 FOR cr IN cr_target_org LOOP
84 l_index := l_index + 1;
85 p_target_orgs(l_index).org_level_value_id := cr.org_id;
86 END LOOP;
87
88 EXCEPTION
89 WHEN others THEN
90 POA_LOG.put_line('Error getting organizations with targets defined:');
91 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
92 RAISE;
93 END get_target_orgs;
94
95 PROCEDURE get_gl_info(
96 p_org_id IN NUMBER,
97 p_period_set_name OUT NOCOPY VARCHAR2,
98 p_currency OUT NOCOPY VARCHAR2,
99 p_period_type OUT NOCOPY VARCHAR2) IS
100 l_sob_id NUMBER;
101 l_period_set_name VARCHAR2(15);
102 x_progress VARCHAR2(3);
103 BEGIN
104 x_progress := '001';
105
106 IF (p_org_id = -1) THEN
107 l_sob_id := fnd_profile.value('GL_SET_OF_BKS_ID');
108 ELSE
109 SELECT to_number(org_information1) INTO l_sob_id
110 FROM hr_organization_information
111 WHERE organization_id = p_org_id
112 AND org_information_context = 'Accounting Information';
113 END IF;
114
115 x_progress := '002';
116
117 SELECT period_set_name, currency_code, accounted_period_type
118 INTO p_period_set_name, p_currency, p_period_type
119 FROM gl_sets_of_books
120 WHERE set_of_books_id = l_sob_id;
121
122 EXCEPTION
123 WHEN others THEN
124 POA_LOG.put_line('Error getting GL information:');
125 POA_LOG.put_line(sqlcode || ': ' || sqlerrm || ': ' || x_progress);
126 RAISE;
127 END get_gl_info;
128
129 PROCEDURE get_period_info(
130 p_org_id IN NUMBER,
131 p_for_current_period IN BOOLEAN,
132 p_start_date OUT NOCOPY DATE,
133 p_end_date OUT NOCOPY DATE,
134 p_period_name OUT NOCOPY VARCHAR2,
135 p_period_set_name OUT NOCOPY VARCHAR2,
136 p_currency OUT NOCOPY VARCHAR2,
137 p_period_type OUT NOCOPY VARCHAR2) IS
138 BEGIN
139
140 IF p_for_current_period THEN
141 get_current_period_info(p_org_id, p_start_date, p_end_date,
142 p_period_name, p_period_set_name, p_currency,
143 p_period_type);
144 ELSE
145 get_previous_period_info(p_org_id, p_start_date, p_end_date,
146 p_period_name, p_period_set_name, p_currency,
147 p_period_type);
148 END IF;
149
150 EXCEPTION
151 WHEN others THEN
152 POA_LOG.put_line('Error getting period information:');
153 POA_LOG.put_line(sqlcode);
154 RAISE;
155 END get_period_info;
156
157 PROCEDURE get_current_period_info(
158 p_org_id IN NUMBER,
159 p_start_date OUT NOCOPY DATE,
160 p_end_date OUT NOCOPY DATE,
161 p_period_name OUT NOCOPY VARCHAR2,
162 p_period_set_name OUT NOCOPY VARCHAR2,
163 p_currency OUT NOCOPY VARCHAR2,
164 p_period_type OUT NOCOPY VARCHAR2) IS
165 l_sob_id NUMBER;
166 l_period_type VARCHAR2(15);
167 BEGIN
168
169 get_gl_info(p_org_id, p_period_set_name, p_currency, l_period_type);
170 p_period_type := l_period_type;
171
172 SELECT per.period_name, per.start_date, per.end_date
173 INTO p_period_name, p_start_date, p_end_date
174 FROM gl_periods per
175 WHERE per.period_set_name = p_period_set_name
176 AND per.period_type = l_period_type
177 AND per.adjustment_period_flag = 'N'
178 AND per.start_date <= TRUNC(sysdate)
179 AND per.end_date >= TRUNC(sysdate);
180
181 EXCEPTION
182 WHEN others THEN
183 POA_LOG.put_line('Error getting current GL period:');
184 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
185 RAISE;
186 END get_current_period_info;
187
188 PROCEDURE get_previous_period_info(
189 p_org_id IN NUMBER,
190 p_start_date OUT NOCOPY DATE,
191 p_end_date OUT NOCOPY DATE,
192 p_period_name OUT NOCOPY VARCHAR2,
193 p_period_set_name OUT NOCOPY VARCHAR2,
194 p_currency OUT NOCOPY VARCHAR2,
195 p_period_type OUT NOCOPY VARCHAR2) IS
196 l_sob_id NUMBER;
197 l_period_type VARCHAR2(15);
198 BEGIN
199
200 get_gl_info(p_org_id, p_period_set_name, p_currency, l_period_type);
201 p_period_type := l_period_type;
202
203 SELECT per.period_name, per.start_date, per.end_date
204 INTO p_period_name, p_start_date, p_end_date
205 FROM gl_periods per
206 WHERE per.period_set_name = p_period_set_name
207 AND per.period_type = l_period_type
208 AND per.adjustment_period_flag = 'N'
209 AND per.end_date = (SELECT max(end_date)
210 FROM gl_periods per2
211 WHERE per2.period_set_name = p_period_set_name
212 AND per2.period_type = l_period_type
213 AND per2.adjustment_period_flag = 'N'
214 AND per2.end_date < TRUNC(sysdate));
215
216 EXCEPTION
217 WHEN others THEN
218 POA_LOG.put_line('Error getting previous GL period:');
219 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
220 RAISE;
221 END get_previous_period_info;
222
223 PROCEDURE get_value(
224 p_label IN VARCHAR2,
225 p_value_tbl IN POA_Label_Value_Tbl,
226 p_index OUT NOCOPY NUMBER,
227 p_heading OUT NOCOPY VARCHAR2,
228 p_value OUT NOCOPY VARCHAR2) IS
229 l_index NUMBER;
230 BEGIN
231 FOR l_index IN 1..p_value_tbl.COUNT LOOP
232 IF p_value_tbl(l_index).label = p_label THEN
233 p_index := l_index;
234 p_heading := p_value_tbl(l_index).heading;
235 p_value := p_value_tbl(l_index).value;
236 RETURN;
237 END IF;
238 END LOOP;
239
240 p_index := to_number(NULL);
241 p_value := FND_API.G_MISS_CHAR;
242
243 EXCEPTION
244 WHEN others THEN
245 POA_LOG.put_line('Error in get_value procedure:');
246 POA_LOG.put_line(sqlcode);
247 RAISE;
248 END get_value;
249
250 PROCEDURE set_value(
251 p_label IN VARCHAR2,
252 p_heading IN VARCHAR2,
253 p_value IN VARCHAR2,
254 p_value_tbl IN OUT NOCOPY POA_Label_Value_Tbl) IS
255 l_index NUMBER;
256 BEGIN
257 FOR l_index IN 1..p_value_tbl.COUNT LOOP
258 IF p_value_tbl(l_index).label = p_label THEN
259 p_value_tbl(l_index).heading := p_heading;
260 p_value_tbl(l_index).value := p_value;
261 RETURN;
262 END IF;
263 END LOOP;
264 EXCEPTION
265 WHEN others THEN
266 POA_LOG.put_line('Error getting period information:');
267 POA_LOG.put_line(sqlcode);
268 RAISE;
269 END set_value;
270
271 PROCEDURE insert_row(
272 p_label IN VARCHAR2,
273 p_heading IN VARCHAR2,
274 p_value IN VARCHAR2,
275 p_value_tbl IN OUT NOCOPY POA_Label_Value_Tbl) IS
276 l_index NUMBER;
277 BEGIN
278 l_index := p_value_tbl.COUNT + 1;
279 p_value_tbl(l_index).label := p_label;
280 p_value_tbl(l_index).heading := p_heading;
281 p_value_tbl(l_index).value := p_value;
282 EXCEPTION
283 WHEN others THEN
284 POA_LOG.put_line('Error getting period information:');
285 POA_LOG.put_line(sqlcode);
286 RAISE;
287 END insert_row;
288
289 PROCEDURE get_actual_poactlkg_all_m(
290 p_start_date IN DATE,
291 p_end_date IN DATE,
292 p_currency IN VARCHAR2,
293 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
294 l_leakage_percent VARCHAR2(120);
295 l_total_purchases VARCHAR2(120);
296 l_leakage_amount VARCHAR2(120);
297 l_potential_savings VARCHAR2(120);
298 l_format_mask VARCHAR2(120);
299 BEGIN
300 l_format_mask := FND_CURRENCY.get_format_mask(p_currency, 25);
301
302 SELECT
303 TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
304 0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
305 SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
306 POA_BIS_ALERTS.g_percent_mask),
307 TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
308 l_format_mask),
309 TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
310 l_format_mask),
311 TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
312 l_format_mask)
313 INTO l_leakage_percent, l_total_purchases, l_potential_savings,
314 l_leakage_amount
315 FROM gl_daily_rates gl,
316 poa_bis_savings poa
317 WHERE gl.from_currency (+) = poa.currency_code
318 AND gl.to_currency (+) = p_currency
319 AND gl.conversion_date (+) = poa.rate_date
320 AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
321 AND poa.purchase_creation_date < p_end_date + 1
322 AND poa.purchase_creation_date >= p_start_date;
323
324 insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
325 l_leakage_percent, p_value_tbl);
326 insert_row('L_CHAR_ATTR1',
327 fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
328 l_total_purchases, p_value_tbl);
329 insert_row('L_CHAR_ATTR2',
330 fnd_message.get_string('POA', 'POA_LEAKAGE'),
331 l_leakage_amount, p_value_tbl);
332 insert_row('L_CHAR_ATTR3',
333 fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
334 l_potential_savings, p_value_tbl);
335 insert_row('L_CURRENCY',
336 fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);
337
338 EXCEPTION
339 WHEN others THEN
340 POA_LOG.put_line('Error getting Actual:');
341 POA_LOG.put_line('POACTLKG_ALL_M:' || sqlcode || ': ' || sqlerrm);
342 RAISE;
343 END get_actual_poactlkg_all_m;
344
345 PROCEDURE get_actual_poactlkg_ou_m(
346 p_org_id IN NUMBER,
347 p_start_date IN DATE,
348 p_end_date IN DATE,
349 p_currency IN VARCHAR2,
350 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
351 l_leakage_percent VARCHAR2(120);
352 l_total_purchases VARCHAR2(120);
353 l_leakage_amount VARCHAR2(120);
354 l_potential_savings VARCHAR2(120);
355 l_format_mask VARCHAR2(120);
356 BEGIN
357 l_format_mask := FND_CURRENCY.get_format_mask(p_currency, 25);
358
359 SELECT
360 TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
361 0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
362 SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
363 POA_BIS_ALERTS.g_percent_mask),
364 TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
365 l_format_mask),
366 TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
367 l_format_mask),
368 TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
369 l_format_mask)
370 INTO l_leakage_percent, l_total_purchases, l_potential_savings,
371 l_leakage_amount
372 FROM gl_daily_rates gl,
373 poa_bis_savings poa
374 WHERE gl.from_currency (+) = poa.currency_code
375 AND gl.to_currency (+) = p_currency
376 AND gl.conversion_date (+) = poa.rate_date
377 AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
378 AND poa.operating_unit_id = p_org_id
379 AND poa.purchase_creation_date < p_end_date + 1
380 AND poa.purchase_creation_date >= p_start_date;
381
382 insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
383 l_leakage_percent, p_value_tbl);
384 insert_row('L_CHAR_ATTR1',
385 fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
386 l_total_purchases, p_value_tbl);
387 insert_row('L_CHAR_ATTR2',
388 fnd_message.get_string('POA', 'POA_LEAKAGE'),
389 l_leakage_amount, p_value_tbl);
390 insert_row('L_CHAR_ATTR3',
391 fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
392 l_potential_savings, p_value_tbl);
393 insert_row('L_CURRENCY',
394 fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);
395
396 EXCEPTION
397 WHEN others THEN
398 POA_LOG.put_line('Error getting Actual:');
399 POA_LOG.put_line('POACTLKG_OU_M:' || sqlcode || ': ' || sqlerrm);
400 RAISE;
401 END get_actual_poactlkg_ou_m;
402
403 PROCEDURE get_actual_poactlkg_org_m(
404 p_org_id IN NUMBER,
405 p_start_date IN DATE,
406 p_end_date IN DATE,
407 p_currency IN VARCHAR2,
408 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
409 l_leakage_percent VARCHAR2(120);
410 l_total_purchases VARCHAR2(120);
411 l_leakage_amount VARCHAR2(120);
412 l_potential_savings VARCHAR2(120);
413 l_format_mask VARCHAR2(120);
414 BEGIN
415 l_format_mask := FND_CURRENCY.get_format_mask(p_currency, 25);
416
417 SELECT
418 TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
419 0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
420 SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
421 POA_BIS_ALERTS.g_percent_mask),
422 TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
423 l_format_mask),
424 TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
425 l_format_mask),
426 TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
427 l_format_mask)
428 INTO l_leakage_percent, l_total_purchases, l_potential_savings,
429 l_leakage_amount
430 FROM gl_daily_rates gl,
431 poa_bis_savings poa
432 WHERE gl.from_currency (+) = poa.currency_code
433 AND gl.to_currency (+) = p_currency
434 AND gl.conversion_date (+) = poa.rate_date
435 AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
436 AND poa.ship_to_organization_id = p_org_id
437 AND poa.purchase_creation_date < p_end_date + 1
438 AND poa.purchase_creation_date >= p_start_date;
439
440 insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
441 l_leakage_percent, p_value_tbl);
442 insert_row('L_CHAR_ATTR1',
443 fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
444 l_total_purchases, p_value_tbl);
445 insert_row('L_CHAR_ATTR2',
446 fnd_message.get_string('POA', 'POA_LEAKAGE'),
447 l_leakage_amount, p_value_tbl);
448 insert_row('L_CHAR_ATTR3',
449 fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
450 l_potential_savings, p_value_tbl);
451 insert_row('L_CURRENCY',
452 fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);
453
454 EXCEPTION
455 WHEN others THEN
456 POA_LOG.put_line('Error getting Actual:');
457 POA_LOG.put_line('POACTLKG_ORG_M:' || sqlcode || ': ' || sqlerrm);
458 RAISE;
459 END get_actual_poactlkg_org_m;
460
461 PROCEDURE get_actual_poaspsal_ou_m(
462 p_org_id IN NUMBER,
463 p_start_date IN DATE,
464 p_end_date IN DATE,
465 p_currency IN VARCHAR2,
466 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
467 l_actual VARCHAR2(120);
468 BEGIN
469 --The time dimension is set to a particular period
470 --and the org dimension is set to a particular organization
471 SELECT
472 TO_CHAR(decode(sum(poa.sales_amount), NULL, 0,0,0,
473 nvl(100*sum((poa.purchase_amount)*nvl(gl.conversion_rate,1))/
474 nvl(sum((poa.sales_amount)*nvl(gl.conversion_rate,1)),1),0)),
475 POA_BIS_ALERTS.g_percent_mask) Actual
476 INTO l_actual
477 FROM poa_purchase_sales_v poa,
478 gl_daily_rates gl
479 WHERE gl.from_currency (+) = poa.currency
480 and gl.to_currency (+) = p_currency
481 and gl.conversion_date (+) = poa.transaction_date
482 and gl.conversion_type (+) = 'Corporate'
483 and poa.transaction_date < p_end_date + 1
484 and poa.transaction_date >= p_start_date
485 and poa.ou_id = p_org_id;
486
487 insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
488 l_actual, p_value_tbl);
489 insert_row('L_CURRENCY',
490 fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);
491
492 EXCEPTION
493 WHEN others THEN
494 POA_LOG.put_line('Error getting Actual:');
495 POA_LOG.put_line('POASPSAL_OU_M:' || sqlcode || ': ' || sqlerrm);
496 RAISE;
497 END get_actual_poaspsal_ou_m;
498
499 PROCEDURE get_actual_poaspsal_all_m(
500 p_start_date IN DATE,
501 p_end_date IN DATE,
502 p_currency IN VARCHAR2,
503 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
504 l_actual VARCHAR2(120);
505 BEGIN
506 --The time dimension is set to a particular period
507 --and the org dimension is set to a particular organization
508 SELECT
509 TO_CHAR(decode(sum(poa.sales_amount), NULL, 0,0,0,
510 nvl(100*sum((poa.purchase_amount)*nvl(gl.conversion_rate,1))/
511 nvl(sum((poa.sales_amount)*nvl(gl.conversion_rate,1)),1),0)),
512 POA_BIS_ALERTS.g_percent_mask) Actual
513 INTO l_actual
514 FROM poa_purchase_sales_v poa,
515 gl_daily_rates gl
516 WHERE gl.from_currency (+) = poa.currency
517 and gl.to_currency (+) = p_currency
518 and gl.conversion_date (+) = poa.transaction_date
519 and gl.conversion_type (+) = 'Corporate'
520 and poa.transaction_date < p_end_date + 1
521 and poa.transaction_date >= p_start_date;
522
523 insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
524 l_actual, p_value_tbl);
525 insert_row('L_CURRENCY',
526 fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);
527
528 EXCEPTION
529 WHEN others THEN
530 POA_LOG.put_line('Error getting Actual:');
531 POA_LOG.put_line('POASPSAL_ALL_M:' || sqlcode || ': ' || sqlerrm);
532 RAISE;
533 END get_actual_poaspsal_all_m;
534
535 PROCEDURE get_actual(
536 p_target_level_short_name IN VARCHAR2,
537 p_org_id IN NUMBER,
538 p_start_date IN DATE,
539 p_end_date IN DATE,
540 p_currency IN VARCHAR2,
541 p_value_tbl OUT NOCOPY POA_Label_Value_Tbl) IS
542 BEGIN
543 IF (p_target_level_short_name = 'POACTLKG_ALL_M') THEN
544 get_actual_poactlkg_all_m(p_start_date, p_end_date, p_currency,
545 p_value_tbl);
546 ELSIF (p_target_level_short_name = 'POACTLKG_OU_M') THEN
547 get_actual_poactlkg_ou_m(p_org_id, p_start_date, p_end_date, p_currency,
548 p_value_tbl);
549 ELSIF (p_target_level_short_name = 'POACTLKG_ORG_M') THEN
550 get_actual_poactlkg_org_m(p_org_id, p_start_date, p_end_date,
551 p_currency, p_value_tbl);
552 ELSIF (p_target_level_short_name = 'POASPSAL_ALL_M') THEN
553 get_actual_poaspsal_all_m(p_start_date, p_end_date, p_currency,
554 p_value_tbl);
555 ELSIF (p_target_level_short_name = 'POASPSAL_OU_M') THEN
556 get_actual_poaspsal_ou_m(p_org_id, p_start_date, p_end_date, p_currency,
557 p_value_tbl);
558 ELSE
559 NULL;
560 END IF;
561
562 EXCEPTION
563 WHEN others THEN
564 POA_LOG.put_line('Error in get_actual procedure:');
565 POA_LOG.put_line(sqlcode);
566 RAISE;
567 END get_actual;
568
569 PROCEDURE get_report_param(
570 p_target_level_short_name IN VARCHAR2,
571 p_org_id IN NUMBER,
572 p_start_date IN DATE,
573 p_end_date IN DATE,
574 p_period_type IN VARCHAR2,
575 p_param OUT NOCOPY VARCHAR2) IS
576 l_default NUMBER := -9999;
577 BEGIN
578
579 IF (p_target_level_short_name = 'POACTLKG_ALL_M') THEN
580 p_param := 'p_comm_id=' || l_default ||
581 '*p_period_type=' || p_period_type ||
582 '*p_supp_id=' || l_default ||
583 '*p_supp_is_valid=Y' ||
584 '*p_org_id=' || l_default ||
585 '*p_oper_id=' || l_default ||
586 '*p_buyer_id=' || l_default ||
587 '*p_view_dim=1' ||
588 '*p_fdate=' || to_char(p_start_date, 'DD-MON-YYYY') ||
589 '*p_tdate=' || to_char(p_end_date, 'DD-MON-YYYY') ||
590 '*paramform=NO';
591 ELSIF (p_target_level_short_name = 'POACTLKG_OU_M') THEN
592 p_param := 'p_comm_id=' || l_default ||
593 '*p_period_type=' || p_period_type ||
594 '*p_supp_id=' || l_default ||
595 '*p_supp_is_valid=Y' ||
596 '*p_org_id=' || l_default ||
597 '*p_oper_id=' || to_char(p_org_id) ||
598 '*p_buyer_id=' || l_default ||
599 '*p_view_dim=1' ||
600 '*p_fdate=' || to_char(p_start_date, 'DD-MON-YYYY') ||
601 '*p_tdate=' || to_char(p_end_date, 'DD-MON-YYYY') ||
602 '*paramform=NO';
603 ELSIF (p_target_level_short_name = 'POACTLKG_ORG_M') THEN
604 p_param := 'p_comm_id=' || l_default ||
605 '*p_period_type=' || p_period_type ||
606 '*p_supp_id=' || l_default ||
607 '*p_supp_is_valid=Y' ||
608 '*p_org_id=' || to_char(p_org_id) ||
609 '*p_oper_id=' || l_default ||
610 '*p_buyer_id=' || l_default ||
611 '*p_view_dim=1' ||
612 '*p_fdate=' || to_char(p_start_date, 'DD-MON-YYYY') ||
613 '*p_tdate=' || to_char(p_end_date, 'DD-MON-YYYY') ||
614 '*paramform=NO';
615 ELSIF (p_target_level_short_name = 'POASPSAL_ALL_M') THEN
616 p_param := 'p_period_type=' || p_period_type ||
617 '*p_ou_id=' || l_default ||
618 '*p_view_dim=1' ||
619 '*p_fdate=' || to_char(p_start_date, 'DD-MON-YYYY') ||
620 '*p_tdate=' || to_char(p_end_date, 'DD-MON-YYYY') ||
621 '*paramform=NO';
622 ELSIF (p_target_level_short_name = 'POASPSAL_OU_M') THEN
623 p_param := 'p_period_type=' || p_period_type ||
624 '*p_ou_id=' || to_char(p_org_id) ||
625 '*p_view_dim=1' ||
626 '*p_fdate=' || to_char(p_start_date, 'DD-MON-YYYY') ||
627 '*p_tdate=' || to_char(p_end_date, 'DD-MON-YYYY') ||
628 '*paramform=NO';
629 END IF;
630
631 EXCEPTION
632 WHEN others THEN
633 POA_LOG.put_line('Error in get_report_param procedure:');
634 POA_LOG.put_line(sqlcode);
635 RAISE;
636 END get_report_param;
637
638 PROCEDURE get_org_name(
639 p_org_id IN NUMBER,
640 p_org_name OUT NOCOPY VARCHAR2) IS
641 BEGIN
642 IF (p_org_id = -1) THEN
643 -- All organizations
644 SELECT displayed_field INTO p_org_name
645 FROM po_lookup_codes
646 WHERE lookup_code = 'ALL'
647 AND lookup_type = 'POA BIS REPORT OPTION';
648 ELSE
649 SELECT name INTO p_org_name
650 FROM hr_organization_units
651 WHERE organization_id = p_org_id;
652 END IF;
653
654 EXCEPTION
655 WHEN others THEN
656 POA_LOG.put_line('Error getting Organization Name:');
657 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
658 p_org_name := NULL;
659 RAISE;
660 END get_org_name;
661
662 PROCEDURE post_actual(
663 p_target_level_short_name IN VARCHAR2,
664 p_plan_short_name IN VARCHAR2,
665 p_for_current_period IN BOOLEAN) IS
666 l_target_level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
667 l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;
668 l_index NUMBER;
669 l_return_status VARCHAR2(30);
670 l_msg_count NUMBER;
671 l_msg_data VARCHAR2(250);
672 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
673 l_period_name VARCHAR2(15);
674 l_period_type VARCHAR2(15);
675 l_period_set_name VARCHAR2(15);
676 l_currency VARCHAR2(20);
677 l_start_date DATE;
678 l_end_date DATE;
679 l_actual_rec BIS_ACTUAL_PUB.Actual_Rec_Type;
680 l_value_tbl POA_Label_Value_Tbl;
681 l_value VARCHAR2(120);
682 l_found_index NUMBER;
683 l_heading VARCHAR2(250);
684 BEGIN
685 POA_LOG.put_line('Posting actual values...');
686 l_target_level_rec.target_level_short_name := p_target_level_short_name;
687
688 BIS_ACTUAL_PUB.Retrieve_User_Selections
689 (p_api_version => 1.0,
690 p_Target_Level_Rec => l_target_level_rec,
691 x_Indicator_Region_Tbl => l_user_selection_tbl,
692 x_return_status => l_return_status,
693 x_msg_count => l_msg_count,
694 x_msg_data => l_msg_data,
695 x_error_Tbl => l_error_tbl);
696
697 /** DEBUG: Default 1 record for testing purposes **/
698 -- l_user_selection_tbl(1).org_level_value_id := 204;
699
700 /** DEBUG: Loop 1 record for testing purposes **/
701 FOR l_index IN 1..l_user_selection_tbl.COUNT LOOP
702 -- FOR l_index IN 1..1 LOOP
703 BEGIN
704 POA_LOG.debug_line('Processing ORG_ID: ' ||
705 l_user_selection_tbl(l_index).org_level_value_id);
706 get_period_info(
707 to_number(l_user_selection_tbl(l_index).org_level_value_id),
708 p_for_current_period, l_start_date, l_end_date, l_period_name,
709 l_period_set_name, l_currency, l_period_type);
710
711 POA_LOG.debug_line('Period: ' || l_period_name);
712 POA_LOG.debug_line('Currency: ' || l_currency);
713
714 get_actual(p_target_level_short_name,
715 to_number(l_user_selection_tbl(l_index).org_level_value_id),
716 l_start_date, l_end_date, l_currency, l_value_tbl);
717
718 get_value('L_ACTUAL', l_value_tbl, l_found_index, l_heading, l_value);
719 l_actual_rec.actual := to_number(l_value);
720 l_actual_rec.target_level_short_name := p_target_level_short_name;
721 l_actual_rec.org_level_value_id :=
722 l_user_selection_tbl(l_index).org_level_value_id;
723 l_actual_rec.time_level_value_id :=
724 l_period_set_name || '+' || l_period_name;
725
726 l_actual_rec.target_level_id := l_user_selection_tbl(l_index).target_level_id;
727
728 POA_LOG.debug_line('Actual: ' || l_actual_rec.actual);
729
730 poa_log.debug_line('p_Actual_Rec='|| 'actual_id='||l_actual_rec.actual_id || ' ' ||
731 'target_level_id='||l_actual_rec.target_level_id || ' ' ||
732 'target_level_name='||l_actual_rec.target_level_name ||' ' ||
733 'target_level_shortname='||l_actual_rec.Target_Level_Short_Name ||' ' ||
734 'org_level_id='||l_actual_rec.Org_Level_value_ID ||' ' ||
735 'time_level_id='||l_actual_rec.time_Level_value_ID ||' ' ||
736 'p_commit='|| FND_API.g_true ||' ' ||
737 'x_return_status='|| l_return_status ||' ' ||
738 'x_msg_count='|| l_msg_count
739 );
740
741 BIS_ACTUAL_PUB.Post_Actual(p_api_version => 1.0,
742 p_Actual_Rec => l_actual_rec,
743 p_commit => FND_API.G_TRUE,
744 x_return_status => l_return_status,
745 x_msg_count => l_msg_count,
746 x_msg_data => l_msg_data,
747 x_error_tbl => l_error_tbl);
748
749 POA_LOG.debug_line('Posted Actual: ' || l_return_status);
750 POA_LOG.debug_line('End Processing ORG_ID: ' ||
751 l_user_selection_tbl(l_index).org_level_value_id);
752 EXCEPTION
753 WHEN others THEN
754 POA_LOG.put_line('Failed to post actual for organization:');
755 POA_LOG.put_line(l_user_selection_tbl(l_index).org_level_value_id ||
756 sqlcode || ': ' || sqlerrm);
757 END;
758 END LOOP;
759
760 POA_LOG.put_line('Done.');
761
762 EXCEPTION
763 WHEN others THEN
764 POA_LOG.put_line('Error in post_actual procedure:');
765 POA_LOG.put_line(sqlcode);
766 RAISE;
767 END post_actual;
768
769 PROCEDURE compare_targets(
770 p_target_level_short_name IN VARCHAR2,
771 p_plan_short_name IN VARCHAR2,
772 p_for_current_period IN BOOLEAN) IS
773 l_workflow_item_type VARCHAR2(8);
774 l_workflow_process VARCHAR2(30);
775 l_target_level_short_name VARCHAR2(30);
776 l_measure_short_name VARCHAR2(30);
777 l_target_level_name VARCHAR2(80);
778 l_param VARCHAR2(1000);
779 l_target_orgs BIS_TARGET_PUB.Target_Tbl_Type;
780 l_target_rec BIS_TARGET_PUB.Target_Rec_Type;
781 l_actual NUMBER;
782 l_index NUMBER;
783 l_start_date DATE;
784 l_end_date DATE;
785 l_period_set_name VARCHAR2(15);
786 l_period_name VARCHAR2(15);
787 l_period_type VARCHAR2(15);
788 l_found BOOLEAN;
789 l_return_status VARCHAR2(30);
790 l_currency VARCHAR2(20);
791 l_value_tbl POA_Label_Value_Tbl;
792 l_value VARCHAR2(120);
793 l_found_index NUMBER;
794 l_index2 NUMBER;
795 l_range_low NUMBER;
796 l_range_high NUMBER;
797 l_org_name VARCHAR2(80);
798 l_app_name VARCHAR2(240);
799 l_heading VARCHAR2(250);
800 BEGIN
801 POA_LOG.put_line('Comparing actual against target values...');
802
803 -- Get Application Name
804 SELECT application_name INTO l_app_name
805 FROM fnd_application_vl
806 WHERE application_short_name = 'POA';
807
808 -- Get target level information
809 SELECT workflow_item_type,
810 workflow_process_short_name,
811 measure_short_name,
812 target_level_name
813 INTO l_workflow_item_type,
814 l_workflow_process,
815 l_measure_short_name,
816 l_target_level_name
817 FROM BISFV_TARGET_LEVELS
818 WHERE target_level_short_name = p_target_level_short_name;
819
820 -- Get all orgs with targets defined for the given target level
821 get_target_orgs(p_target_level_short_name, p_plan_short_name,
822 l_target_orgs);
823
824 FOR l_index IN 1..l_target_orgs.COUNT LOOP
825 BEGIN
826 POA_LOG.debug_line('Processing ORG_ID: ' ||
827 l_target_orgs(l_index).org_level_value_id);
828
829 get_period_info(
830 to_number(l_target_orgs(l_index).org_level_value_id),
831 p_for_current_period, l_start_date, l_end_date, l_period_name,
832 l_period_set_name, l_currency, l_period_type);
833
834 POA_LOG.debug_line('Period: '||l_period_set_name || '+' || l_period_name);
835 POA_LOG.debug_line('Currency: ' || l_currency);
836
837 get_target_value(p_target_level_short_name, p_plan_short_name,
838 l_period_set_name || '+' || l_period_name,
839 to_number(l_target_orgs(l_index).org_level_value_id),
840 l_found, l_target_rec);
841
842 POA_LOG.debug_line('Target: ' || l_target_rec.target);
843
844 IF (l_found) THEN
845 -- Clear value table
846 l_value_tbl.DELETE;
847
848 get_actual(p_target_level_short_name,
849 to_number(l_target_orgs(l_index).org_level_value_id),
850 l_start_date, l_end_date, l_currency, l_value_tbl);
851
852 -- get_actual returns a record with more than one value,
853 -- need to get the 'real' actual
854 get_value('L_ACTUAL', l_value_tbl, l_found_index, l_heading, l_value);
855 l_actual := TO_NUMBER(l_value);
856
857 POA_LOG.debug_line('Actual: ' || l_actual);
858
859 IF (l_target_rec.range1_low <> FND_API.g_miss_num AND
860 l_target_rec.range1_high <> FND_API.g_miss_num) THEN
861 l_range_low := l_target_rec.target*(1-l_target_rec.range1_low/100);
862 l_range_high := l_target_rec.target*(1+l_target_rec.range1_high/100);
863
864 IF (l_actual NOT BETWEEN l_range_low AND l_range_high) THEN
865 POA_LOG.debug_line('Outside Range 1');
866 insert_row('L_SUBJECT', fnd_message.get_string('POA',
867 'POA_' || UPPER(l_measure_short_name) || '_SUBJECT'), '(' ||
868 fnd_message.get_string('PO','POA_ACTUAL') || ': ' || l_value ||
869 ' ' || fnd_message.get_string('PO','POA_TARGET') || ': ' ||
870 to_char(l_target_rec.target,POA_BIS_ALERTS.g_percent_mask) || ')',
871 l_value_tbl);
872 insert_row('L_TARGET_LEVEL',
873 fnd_message.get_string('POA','POA_TARGET_LEVEL'),
874 l_target_level_name, l_value_tbl);
875 get_org_name(to_number(l_target_orgs(l_index).org_level_value_id),
876 l_org_name);
877 insert_row('L_ORG',
878 fnd_message.get_string('PO','POA_ORGANIZATION'), l_org_name,
879 l_value_tbl);
880 insert_row('L_PERIOD',
881 fnd_message.get_string('PO','POA_PERIOD'), l_period_name,
882 l_value_tbl);
883 insert_row('L_TARGET',
884 fnd_message.get_string('PO','POA_TARGET'),
885 to_char(l_target_rec.target, POA_BIS_ALERTS.g_percent_mask),
886 l_value_tbl);
887 insert_row('L_TARGET_RANGE',
888 fnd_message.get_string('POA','POA_TARGET_RANGE'),
889 to_char(l_range_low, POA_BIS_ALERTS.g_percent_mask) || ' - ' ||
890 to_char(l_range_high,POA_BIS_ALERTS.g_percent_mask),l_value_tbl);
891 insert_row('L_THANKS',
892 fnd_message.get_string('POA','POA_THANKS'), NULL, l_value_tbl);
893 insert_row('L_SENDER', l_app_name, NULL, l_value_tbl);
894 get_report_param(p_target_level_short_name,
895 to_number(l_target_orgs(l_index).org_level_value_id),
896 l_start_date, l_end_date, l_period_type, l_param);
897 insert_row('L_URL', fnd_profile.value('ICX_REPORT_LINK') ||
898 'OracleOASIS.RunReport?report='|| l_measure_short_name ||
899 '&' || 'parameters=' || l_param || '&' ||
900 'responsibility_id=' ||
901 l_target_rec.notify_resp1_id, NULL, l_value_tbl);
902 start_workflow(l_workflow_item_type, l_workflow_process,
903 l_target_rec.notify_resp1_short_name,l_value_tbl,l_return_status);
904 END IF;
905
906 END IF;
907
908 IF (l_target_rec.range2_low <> FND_API.g_miss_num AND
909 l_target_rec.range2_high <> FND_API.g_miss_num) THEN
910 l_range_low := l_target_rec.target*(1-l_target_rec.range2_low/100);
911 l_range_high := l_target_rec.target*(1+l_target_rec.range2_high/100);
912
913 IF (l_actual NOT BETWEEN l_range_low AND l_range_high) THEN
914 POA_LOG.debug_line('Outside Range 2');
915 set_value('L_TARGET_RANGE',
916 fnd_message.get_string('POA','POA_TARGET_RANGE'),
917 to_char(l_range_low, POA_BIS_ALERTS.g_percent_mask) || ' - ' ||
918 to_char(l_range_high,POA_BIS_ALERTS.g_percent_mask),l_value_tbl);
919 get_report_param(p_target_level_short_name,
920 to_number(l_target_orgs(l_index).org_level_value_id),
921 l_start_date, l_end_date, l_period_type, l_param);
922 set_value('L_URL', fnd_profile.value('ICX_REPORT_LINK') ||
923 'OracleOASIS.RunReport?report='|| l_measure_short_name ||
924 '&' || 'parameters=' || l_param || '&' ||
925 'responsibility_id=' ||
926 l_target_rec.notify_resp2_id, NULL, l_value_tbl);
927
928 start_workflow(l_workflow_item_type, l_workflow_process,
929 l_target_rec.notify_resp2_short_name,l_value_tbl,l_return_status);
930 END IF;
931
932 END IF;
933
934 IF (l_target_rec.range3_low <> FND_API.g_miss_num AND
935 l_target_rec.range3_high <> FND_API.g_miss_num) THEN
936 l_range_low := l_target_rec.target*(1-l_target_rec.range3_low/100);
937 l_range_high := l_target_rec.target*(1+l_target_rec.range3_high/100);
938
939 IF (l_actual NOT BETWEEN l_range_low AND l_range_high) THEN
940 POA_LOG.debug_line('Outside Range 3');
941 set_value('L_TARGET_RANGE',
942 fnd_message.get_string('POA','POA_TARGET_RANGE'),
943 to_char(l_range_low, POA_BIS_ALERTS.g_percent_mask) || ' - ' ||
944 to_char(l_range_high,POA_BIS_ALERTS.g_percent_mask),l_value_tbl);
945 get_report_param(p_target_level_short_name,
946 to_number(l_target_orgs(l_index).org_level_value_id),
947 l_start_date, l_end_date, l_period_type, l_param);
948 set_value('L_URL', fnd_profile.value('ICX_REPORT_LINK') ||
949 'OracleOASIS.RunReport?report='|| l_measure_short_name ||
950 '&' || 'parameters=' || l_param || '&' ||
951 'responsibility_id=' ||
952 l_target_rec.notify_resp3_id, NULL, l_value_tbl);
953 start_workflow(l_workflow_item_type, l_workflow_process,
954 l_target_rec.notify_resp3_short_name,l_value_tbl,l_return_status);
955 END IF;
956
957 END IF;
958
959 ELSE
960 POA_LOG.debug_line('No target defined');
961 END IF;
962
963 POA_LOG.debug_line('End Processing ORG_ID: ' ||
964 l_target_orgs(l_index).org_level_value_id);
965
966 EXCEPTION
967 WHEN others THEN
968 POA_LOG.put_line('Failed to compare target for organization:' ||
969 l_target_orgs(l_index).org_level_value_id);
970 POA_LOG.put_line(sqlcode || ': ' || sqlerrm);
971 END;
972 END LOOP;
973
974 POA_LOG.put_line('Done.');
975
976 END compare_targets;
977
978 PROCEDURE process_alert_current_period(
979 p_target_level_short_name IN VARCHAR2,
980 p_plan_short_name IN VARCHAR2) IS
981 BEGIN
982 process_alert(p_target_level_short_name, p_plan_short_name, TRUE);
983 END process_alert_current_period;
984
985 PROCEDURE process_alert_previous_period(
986 p_target_level_short_name IN VARCHAR2,
987 p_plan_short_name IN VARCHAR2) IS
988 BEGIN
989 process_alert(p_target_level_short_name, p_plan_short_name, FALSE);
990 END process_alert_previous_period;
991
992 PROCEDURE process_alert(
993 p_target_level_short_name IN VARCHAR2,
994 p_plan_short_name IN VARCHAR2,
995 p_for_current_period IN BOOLEAN) IS
996 x_progress VARCHAR2(3);
997 BEGIN
998 POA_LOG.setup('POAALRTS');
999 POA_LOG.debug_line('In Process Alerts');
1000
1001 post_actual(p_target_level_short_name, p_plan_short_name,
1002 p_for_current_period);
1003 compare_targets(p_target_level_short_name, p_plan_short_name,
1004 p_for_current_period);
1005
1006 POA_LOG.wrapup('SUCCESS');
1007
1008 EXCEPTION WHEN OTHERS THEN
1009 POA_LOG.put_line('Process Alerts: ' || sqlcode || ': ' ||
1010 sqlerrm || ': ' || x_progress);
1011 POA_LOG.put_line(' ');
1012 POA_LOG.wrapup('ERROR');
1013 END process_alert;
1014
1015 PROCEDURE start_workflow(
1016 p_wf_item_type IN VARCHAR2,
1017 p_wf_process IN VARCHAR2,
1018 p_role IN VARCHAR2,
1019 p_value_tbl IN POA_Label_Value_Tbl,
1020 x_return_status OUT NOCOPY VARCHAR2) IS
1021 l_index NUMBER;
1022 l_wf_item_key NUMBER;
1023 l_role_name VARCHAR2(80);
1024 l_string VARCHAR2(500);
1025 CURSOR c_role_name IS
1026 SELECT name
1027 FROM wf_roles
1028 WHERE name = p_role;
1029 BEGIN
1030 POA_LOG.debug_line('Starting Workflow Notification');
1031
1032 x_return_status := FND_API.G_RET_STS_SUCCESS;
1033 IF p_wf_process is NULL or p_role is NULL THEN
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 return;
1036 END IF;
1037
1038 OPEN c_role_name;
1039 FETCH c_role_name INTO l_role_name;
1040
1041 IF C_ROLE_NAME%NOTFOUND THEN
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 return;
1044 END IF;
1045
1046 POA_LOG.debug_line('Notifying: ' || l_role_name);
1047
1048 -- Generate workflow process key
1049 SELECT bis_excpt_wf_s.nextval
1050 INTO l_wf_item_key
1051 FROM dual;
1052
1053 -- Create workflow process
1054 wf_engine.CreateProcess(itemtype => p_wf_item_type,
1055 itemkey => l_wf_item_key,
1056 process => p_wf_process);
1057
1058 -- Set workflow process role
1059 wf_engine.SetItemAttrText(itemtype => p_wf_item_type,
1060 itemkey => l_wf_item_key,
1061 aname=> 'L_ROLE_NAME',
1062 avalue=> l_role_name);
1063
1064 -- Set other workflow attributes
1065 FOR l_index IN 1..p_value_tbl.COUNT LOOP
1066
1067 IF (p_value_tbl(l_index).value IS NULL) THEN
1068 l_string := nvl(p_value_tbl(l_index).heading, p_value_tbl(l_index).label);
1069 ELSE
1070 l_string := nvl(p_value_tbl(l_index).heading, p_value_tbl(l_index).label)
1071 || ': ' || p_value_tbl(l_index).value;
1072 END IF;
1073
1074 POA_LOG.debug_line('Label: ' || p_value_tbl(l_index).label);
1075 POA_LOG.debug_line('Value: ' || l_string);
1076
1077 wf_engine.SetItemAttrText(itemtype => p_wf_item_type,
1078 itemkey => l_wf_item_key,
1079 aname=> p_value_tbl(l_index).label,
1080 avalue=> l_string);
1081 END LOOP;
1082
1083 -- Start workflow process
1084 wf_engine.StartProcess(itemtype => p_wf_item_type,
1085 itemkey => l_wf_item_key);
1086
1087 COMMIT;
1088 POA_LOG.debug_line('Exit POA_STRT_WF_PROCESS');
1089
1090 EXCEPTION
1091 WHEN others THEN
1092 POA_LOG.put_line('Error in start_workflow procedure:');
1093 POA_LOG.put_line(sqlcode);
1094 RAISE;
1095 END start_workflow;
1096
1097 END POA_BIS_ALERTS;