The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT r.threshold_rule_id,
r.threshold_id
FROM ozf_threshold_rules_all r, ozf_thresholds_all_b t
WHERE r.threshold_id = t.threshold_id
AND t.threshold_type = 'QUOTA'
AND r.enabled_flag = 'Y'
AND r.start_date <= SYSDATE
AND r.end_date >= SYSDATE ;
SELECT a.fund_id budget_id,
a.parent_fund_id parent_budget_id,
a.owner owner,
c.value_limit value_limit,
c.operator_code operator_code,
c.start_date rule_start_date,
c.end_date rule_end_date,
c.threshold_id threshold_id,
c.threshold_rule_id threshold_rule_id,
c.percent_amount percent_amt,
c.base_line base_line,
c.frequency_period frequency_period,
c.repeat_frequency repeat_frequency,
c.comparison_type,
c.alert_type
FROM ozf_funds_all_b a,
ozf_thresholds_all_b b,
ozf_threshold_rules_all c
WHERE a.threshold_id = b.threshold_id
AND a.status_code = 'ACTIVE'
AND b.enable_flag = 'Y'
AND b.threshold_id = c.threshold_id
AND c.threshold_rule_id = p_threshold_rule_id
AND c.end_date >= SYSDATE;
SELECT DISTINCT a.owner owner
FROM ozf_funds_all_b a,
ozf_thresholds_all_b b,
ozf_threshold_rules_all c
WHERE a.threshold_id = b.threshold_id
AND a.status_code = 'ACTIVE'
AND b.enable_flag = 'Y'
AND b.threshold_id = c.threshold_id
AND b.threshold_type = 'QUOTA'
AND c.enabled_flag = 'Y'
AND c.start_date <= SYSDATE
AND c.end_date >= SYSDATE;
SELECT
p.item_id item_id,
p.item_type item_type,
sum(c.ptd_sales) mtd_sales,
sum(c.qtd_sales) qtd_sales,
sum(c.ytd_sales) ytd_sales,
sum(c.lysp_sales) lysp_sales,
sum(c.past_due_order_qty) outst_order,
sum(c.current_period_order_qty) current_order,
sum(c.backordered_qty) back_order,
sum(c.booked_for_future_qty) future_order,
sum(c.current_year_target) yearly_quota,
sum(c.current_period_target) monthly_quota,
sum(c.current_qtr_target) quarterly_quota,
sum(c.lysq_sales) lysq_sales,
sum(c.ly_sales) ly_sales
FROM ozf_product_allocations p, ozf_cust_daily_facts c
WHERE
p.item_type <> 'OTHERS' and
p.fund_id = p_budget_id and
p.item_id = c.product_attr_value and
p.item_type = c.product_attribute and
c.report_date = trunc(SYSDATE)
group by p.item_id, p.item_type;
SELECT
c.cust_account_id cust_account_id,
c.ship_to_site_use_id ship_to_site_use_id,
sum(c.ptd_sales) mtd_sales,
sum(c.qtd_sales) qtd_sales,
sum(c.ytd_sales) ytd_sales,
sum(c.lysp_sales) lysp_sales,
sum(c.past_due_order_qty) outst_order,
sum(c.current_period_order_qty) current_order,
sum(c.backordered_qty) back_order,
sum(c.booked_for_future_qty) future_order,
sum(c.current_year_target) yearly_quota,
sum(c.current_period_target) monthly_quota,
sum(c.current_qtr_target) quarterly_quota,
sum(c.lysq_sales) lysq_sales,
sum(c.ly_sales) ly_sales
FROM ozf_account_allocations a, ozf_cust_daily_facts c
WHERE
a.allocation_for = 'FUND' and
a.allocation_for_id = p_budget_id and
a.site_use_code = 'SHIP_TO' and
a.cust_account_id = c.cust_account_id and
a.site_use_id = c.ship_to_site_use_id and
c.report_date = trunc(SYSDATE)
group by c.cust_account_id, c.ship_to_site_use_id;
SELECT resource_id, sequence_number, kpi_name, kpi_value
FROM ozf_dashb_daily_kpi
WHERE resource_id = p_resource_id
AND report_date = trunc(SYSDATE)
ORDER BY sequence_number;
SELECT ams_act_logs_s.NEXTVAL
FROM DUAL;
SELECT ams_act_logs_transaction_id_s.NEXTVAL
FROM DUAL;
SELECT budget_id, log_message_text
FROM ams_act_logs
WHERE log_transaction_id = p_trans_id;
SELECT owner,parent_fund_id
FROM ozf_Funds_All_b
WHERE fund_id = p_budget_id;
SELECT owner
FROM ozf_Funds_All_b
WHERE fund_id = p_budget_id;
SELECT short_name
FROM ozf_fund_details_v
WHERE fund_id = p_budget_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_QUOTA_VALUE_LIMIT'
AND lookup_code = p_lkup_code;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_QUOTA_BASE_LINE'
AND lookup_code = p_lkup_code;
UPDATE OZF_QUOTA_ALERTS SET mtd_alert = NULL, qtd_alert = NULL,
ytd_alert = NULL, back_order_alert = NULL, outstand_order_alert = NULL
WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
UPDATE OZF_DASHB_DAILY_KPI SET alert_type = NULL
WHERE report_date = trunc(sysdate) and resource_id = l_resource_list(i);
update_alerts(l_api_version_number,
FND_API.G_FALSE,
l_Msg_Count,
l_Msg_Data,
l_return_status,
budget.owner,
'PROD',
product.item_type,
product.item_id,
budget.alert_type,
l_alert_str,
0);
update_alerts(l_api_version_number,
FND_API.G_FALSE,
l_Msg_Count,
l_Msg_Data,
l_return_status,
budget.owner,
'CUST',
NULL,
customer.ship_to_site_use_id,
budget.alert_type,
l_alert_str,
customer.cust_account_id);
update_alerts(l_api_version_number,
FND_API.G_FALSE,
l_Msg_Count,
l_Msg_Data,
l_return_status,
budget.owner,
'QUOTA',
NULL,
l_alert_no,
budget.alert_type,
NULL,
0);
select to_char(sysdate, 'dd-Mon-yyyy' ) into l_today_date from dual;
l_owner_table.delete;
SELECT Max(notification_creation_date)
FROM AMS_ACT_LOGS
WHERE arc_act_log_used_by = 'FTHO'
AND act_log_used_by_id = x_threshold_rule_id
AND budget_id = x_budget_id
AND threshold_id = x_threshold_id;
PROCEDURE update_alerts(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
x_Msg_Count OUT NOCOPY NUMBER,
x_Msg_Data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_resource_id IN NUMBER,
p_alert_for IN VARCHAR2,
p_product_attribute IN VARCHAR2,
p_attribute2 IN NUMBER, -- product_attr_value/ship_to_site_use_id/sequence_number
p_alert_type IN VARCHAR2,
p_select_attribute IN VARCHAR2,
p_cust_account_id IN NUMBER
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_alerts';
l_update BOOLEAN;
SELECT ozf_quota_alerts_s.NEXTVAL
FROM DUAL;
FND_FILE.PUT_LINE(FND_FILE.LOG,' p_select_attribute: '|| p_select_attribute);
l_update := FALSE;
/*l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
|| 'WHERE report_date = trunc(SYSDATE) AND resource_id = ' || p_resource_id
|| 'AND alert_for = ''PROD'' AND product_attribute = ''' || p_product_attribute
|| ''' AND product_attr_value = ' || p_attribute2 */
l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
|| 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
|| 'AND alert_for = ''PROD'' AND product_attribute = :2 '
|| 'AND product_attr_value = :3';
l_update := TRUE;
l_update := TRUE;
l_update := TRUE;
IF l_update THEN
/*
l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';
l_sql_str := l_sql_str || 'product_attribute, product_attr_value, ' || p_select_attribute;
l_sql_stmt := 'SELECT ' || p_select_attribute || ' FROM OZF_QUOTA_ALERTS '
|| 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
|| 'AND alert_for = ''CUST'' AND cust_account_id = :2 '
|| 'AND ship_to_site_use_id = :3';
l_update := TRUE;
l_update := TRUE;
l_update := TRUE;
IF l_update THEN
/* commented by kvattiku
l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;
l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';
l_sql_str := l_sql_str || 'cust_account_id, ship_to_site_use_id, ' || p_select_attribute;
l_sql_stmt := 'SELECT alert_type FROM OZF_DASHB_DAILY_KPI '
|| 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
|| 'AND sequence_number = :2';
l_update := TRUE;
l_update := TRUE;
l_update := TRUE;
IF l_update THEN
/* commented by kvattiku
l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = ''' || l_new_alert_type;
l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = :1';
FND_FILE.PUT_LINE(FND_FILE.LOG,'update_alerts EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
END update_alerts;