295: --This cursor will get quota related information for the given resource id
296: CURSOR c_quota (p_resource_id NUMBER)
297: IS
298: SELECT resource_id, sequence_number, kpi_name, kpi_value
299: FROM ozf_dashb_daily_kpi
300: WHERE resource_id = p_resource_id
301: AND report_date = trunc(SYSDATE)
302: ORDER BY sequence_number;
303:
394: ytd_alert = NULL, back_order_alert = NULL, outstand_order_alert = NULL
395: WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
396:
397: FORALL i in l_resource_list.FIRST .. l_resource_list.LAST
398: UPDATE OZF_DASHB_DAILY_KPI SET alert_type = NULL
399: WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
400: END IF;
401:
402: FOR rule IN c_threshold_rules_cur
1505: END;
1506:
1507: ELSIF p_alert_for = 'QUOTA' THEN
1508: BEGIN
1509: l_sql_stmt := 'SELECT alert_type FROM OZF_DASHB_DAILY_KPI '
1510: || 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
1511: || 'AND sequence_number = :2';
1512:
1513: EXECUTE IMMEDIATE l_sql_stmt
1530:
1531: IF l_update THEN
1532:
1533: /* commented by kvattiku
1534: l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = ''' || l_new_alert_type;
1535: l_sql_str := l_sql_str || ''' WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id;
1536: l_sql_str := l_sql_str || ' AND sequence_number = ' || p_attribute2;
1537:
1538: FND_DSQL.init;
1547: l_ignore := DBMS_SQL.execute(l_ins_csr);
1548: */
1549:
1550: --added by kvattiku
1551: l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = :1';
1552: l_sql_str := l_sql_str || ' WHERE report_date = trunc(SYSDATE) AND resource_id = :2';
1553: l_sql_str := l_sql_str || ' AND sequence_number = :3';
1554:
1555: EXECUTE IMMEDIATE l_sql_str