APPS.OZF_QUOTA_THRESHOLD_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 183

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 ;

Line: 196

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;

Line: 224

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;

Line: 240

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;

Line: 269

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;

Line: 298

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;

Line: 305

SELECT ams_act_logs_s.NEXTVAL
FROM DUAL;

Line: 309

SELECT ams_act_logs_transaction_id_s.NEXTVAL
FROM DUAL;

Line: 315

SELECT budget_id, log_message_text
FROM ams_act_logs
WHERE log_transaction_id = p_trans_id;

Line: 321

SELECT owner,parent_fund_id
FROM ozf_Funds_All_b
WHERE fund_id = p_budget_id;

Line: 327

SELECT owner
FROM ozf_Funds_All_b
WHERE fund_id = p_budget_id;

Line: 333

SELECT short_name
FROM ozf_fund_details_v
WHERE fund_id = p_budget_id;

Line: 339

SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_QUOTA_VALUE_LIMIT'
AND lookup_code = p_lkup_code;

Line: 346

SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_QUOTA_BASE_LINE'
AND lookup_code = p_lkup_code;

Line: 393

           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);

Line: 398

           UPDATE OZF_DASHB_DAILY_KPI SET alert_type = NULL
           WHERE report_date = trunc(sysdate) and resource_id  = l_resource_list(i);

Line: 497

                          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);

Line: 602

                          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);

Line: 723

                              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);

Line: 801

                    select to_char(sysdate, 'dd-Mon-yyyy' ) into l_today_date from dual;

Line: 852

      l_owner_table.delete;

Line: 1131

      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;

Line: 1243

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;

Line: 1259

l_api_name            CONSTANT VARCHAR2(30) := 'update_alerts';

Line: 1268

l_update               BOOLEAN;

Line: 1271

SELECT ozf_quota_alerts_s.NEXTVAL
FROM DUAL;

Line: 1297

    FND_FILE.PUT_LINE(FND_FILE.LOG,' p_select_attribute: '|| p_select_attribute);

Line: 1308

    l_update := FALSE;

Line: 1312

       /*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';

Line: 1325

            l_update := TRUE;

Line: 1329

                l_update := TRUE;

Line: 1334

                l_update := TRUE;

Line: 1338

        IF l_update THEN

        /*
        l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;

Line: 1360

              l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;

Line: 1376

              l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';

Line: 1378

              l_sql_str := l_sql_str || 'product_attribute, product_attr_value, ' || p_select_attribute;

Line: 1412

        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';

Line: 1421

            l_update := TRUE;

Line: 1425

                l_update := TRUE;

Line: 1430

                l_update := TRUE;

Line: 1434

        IF l_update THEN

        /* commented by kvattiku
              l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;

Line: 1456

        l_sql_str := 'UPDATE OZF_QUOTA_ALERTS SET ' || p_select_attribute;

Line: 1473

              l_sql_str := 'INSERT INTO OZF_QUOTA_ALERTS ';

Line: 1475

              l_sql_str := l_sql_str || 'cust_account_id, ship_to_site_use_id, ' || p_select_attribute;

Line: 1509

        l_sql_stmt := 'SELECT alert_type FROM OZF_DASHB_DAILY_KPI '
                  || 'WHERE report_date = trunc(SYSDATE) AND resource_id = :1 '
                  || 'AND sequence_number = :2';

Line: 1518

            l_update := TRUE;

Line: 1522

                l_update := TRUE;

Line: 1527

                l_update := TRUE;

Line: 1531

        IF l_update THEN

        /* commented by kvattiku
              l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = ''' || l_new_alert_type;

Line: 1551

        l_sql_str := 'UPDATE OZF_DASHB_DAILY_KPI SET alert_type = :1';

Line: 1603

     FND_FILE.PUT_LINE(FND_FILE.LOG,'update_alerts EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));

Line: 1604

END update_alerts;