APPS.CN_API SQL Statements

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

Line: 56

   SELECT name INTO l_rs_name
     FROM cn_rate_schedules_all
     WHERE rate_schedule_id = p_rate_table_id;

Line: 77

   SELECT rate_schedule_id INTO l_rs_id
     FROM cn_rate_schedules_all
     WHERE name = p_rate_table_name
       AND org_id = p_org_id;

Line: 99

   SELECT period_name
     INTO l_period_name
     FROM cn_period_statuses_all
     WHERE period_id = p_period_id
     AND org_id = p_org_id
     AND period_status IN ('F', 'O');

Line: 122

   SELECT period_id
     INTO l_period_id
     FROM cn_period_statuses_all
     WHERE Upper(period_name) = Upper(p_period_name)
     AND org_id = p_org_id
     AND period_status IN ('F', 'O');

Line: 146

   SELECT revenue_class_id
     INTO l_rev_class_id
     FROM cn_revenue_classes_all
     WHERE name = p_rev_class_name
     AND org_id = p_org_id
     ;

Line: 168

   SELECT name
     INTO l_rev_class_name
     FROM cn_revenue_classes_all
     WHERE revenue_class_id = p_rev_class_id
     ;

Line: 189

     SELECT meaning
       INTO l_meaning
       FROM cn_lookups
       WHERE lookup_type = p_lkup_type
       AND   lookup_code = p_lkup_code
       ;

Line: 752

   SELECT name
     INTO l_comp_plan_name
     FROM cn_comp_plans_all
     WHERE comp_plan_id = p_comp_plan_id;

Line: 774

   SELECT comp_plan_id
     INTO l_comp_plan_id
     FROM cn_comp_plans_all
     WHERE name = p_comp_plan_name
       AND org_id = p_org_id;

Line: 797

   SELECT name
     INTO l_pmt_plan_name
     FROM cn_pmt_plans_all
     WHERE pmt_plan_id = p_pmt_plan_id;

Line: 819

   SELECT pmt_plan_id
     INTO l_pmt_plan_id
     FROM cn_pmt_plans_all
     WHERE name = p_pmt_plan_name
       AND org_id = p_org_id;

Line: 842

   SELECT name
     INTO l_salesrep_name
     FROM cn_salesreps
     WHERE salesrep_id = p_salesrep_id
       AND org_id = p_org_id;

Line: 882

      SELECT salesrep_id
	INTO l_salesrep_id
	FROM cn_salesreps
	WHERE employee_number IS NULL
	  AND org_id = p_org_id
	  AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));

Line: 890

      SELECT /*+ first_rows */ salesrep_id
	INTO l_salesrep_id
	FROM cn_salesreps
	WHERE upper(employee_number) = l_emp_num
	AND org_id = p_org_id
	AND type = p_type;

Line: 941

   SELECT salesrep_id
     INTO l_salesrep_id
     FROM cn_salesreps
     WHERE name = p_salesrep_name AND employee_number = p_emp_num
     AND org_id = p_org_id;

Line: 1199

   SELECT role_id
     INTO l_role_id
     FROM cn_roles
     WHERE Upper(name) = Upper(p_role_name) ;

Line: 1221

   SELECT name
     INTO l_role_name
     FROM cn_roles
     WHERE  role_id = p_role_id ;

Line: 1251

   SELECT srp_role_id
     FROM cn_srp_roles
     WHERE role_id = l_role_id AND
     salesrep_id = l_salesrep_id AND
     start_date = l_start_date AND
     org_id = p_org_id AND
     ((end_date = l_end_date) OR
      (end_date IS NULL AND l_end_date IS NULL));

Line: 1307

   SELECT role_plan_id
     FROM cn_role_plans
     WHERE role_id = l_role_id AND
     comp_plan_id = l_comp_plan_id AND
     start_date = l_start_date AND
     ((end_date = l_end_date) OR
      (end_date IS NULL AND l_end_date IS NULL));

Line: 1353

   SELECT role_pmt_plan_id
     FROM cn_role_pmt_plans
     WHERE role_id = l_role_id AND
     pmt_plan_id = l_pmt_plan_id AND
     start_date = l_start_date AND
     ((end_date = l_end_date) OR
      (end_date IS NULL AND l_end_date IS NULL));

Line: 1397

	SELECT srp_payee_assign_id
	  FROM cn_srp_payee_assigns
	  WHERE payee_id     = p_payee_id
	  AND salesrep_id  = p_salesrep_id
	  AND org_id       = p_org_id
	  AND quota_id     = p_quota_id
	  AND start_date   = p_start_date
    AND end_date     = p_end_date ;

Line: 1407

	SELECT srp_payee_assign_id
	  FROM cn_srp_payee_assigns
	  WHERE payee_id     = p_payee_id
	  AND salesrep_id  = p_salesrep_id
	  AND org_id       = p_org_id
	  AND quota_id     = p_quota_id
	  AND start_date   = p_start_date
	  AND end_date     IS NULL ;

Line: 1444

      SELECT MAX(end_date)
        INTO l_next_end_date
        FROM cn_acc_period_statuses_v
       WHERE period_status IN ('F', 'O')
         AND org_id = p_org_id;

Line: 1452

        SELECT MIN(end_date)
          INTO l_next_end_date
          FROM cn_acc_period_statuses_v
         WHERE trunc(end_date) >= trunc(p_end_date)
           AND period_status IN ('F', 'O')
           AND org_id = p_org_id;

Line: 1477

   SELECT a.period_id
     INTO l_pay_period_id
     FROM cn_period_statuses_all a, cn_srp_pay_groups_all b, cn_pay_groups_all c
     WHERE a.period_set_id = c.period_set_id
     AND a.org_id = p_org_id
     AND a.period_type_id = c.period_type_id
     AND b.pay_group_id = c.pay_group_id
     AND b.org_id = p_org_id
     AND c.org_id = p_org_id
     AND p_date BETWEEN a.start_date AND least(a.end_date, nvl(b.end_date,a.end_date))
     AND p_salesrep_id = b.salesrep_id
     AND p_date BETWEEN b.start_date AND nvl(b.end_date, p_date);

Line: 1505

   SELECT itd_flag
     INTO l_itd_flag
     FROM cn_calc_formulas_all
     WHERE calc_formula_id = p_calc_formula_id ;

Line: 1526

   SELECT period_id
     INTO l_period_id
     FROM cn_acc_period_statuses_v
     WHERE period_name = p_period_name
     AND org_id = p_org_id;

Line: 1548

   SELECT period_name
     INTO l_period_name
     FROM cn_acc_period_statuses_v
     WHERE period_id = p_period_id
     AND org_id = p_org_id;

Line: 1570

   SELECT quota_assign_id INTO l_quota_assign_id
     FROM cn_quota_assigns_all
     WHERE quota_id = p_quota_id
   AND  comp_plan_id = p_comp_plan_id;

Line: 1712

      SELECT MAX(end_date)
	FROM cn_acc_period_statuses_v
       WHERE period_status IN ('F', 'O')
         AND org_id = p_org_id;

Line: 1765

      SELECT period_id
  FROM cn_acc_period_statuses_v
  WHERE p_date BETWEEN start_date AND end_date
  AND org_id = p_org_id;

Line: 1771

      SELECT MAX(period_id)
  FROM cn_acc_period_statuses_v
    WHERE period_status = 'O'
    AND org_id = p_org_id;

Line: 1815

      SELECT period_id
  FROM cn_acc_period_statuses_v
  WHERE p_date BETWEEN start_date AND end_date
  AND org_id = p_org_id;

Line: 1821

      SELECT MIN(period_id)
  FROM cn_acc_period_statuses_v
    WHERE period_status = 'O'
    AND org_id = p_org_id;

Line: 1880

      SELECT name, Nvl(p_rc_overlap,allow_rev_class_overlap)--,sum_trx_flag  -- commented for bug 7655423
  INTO l_comp_plan_name,l_rc_overlap--,l_sum_trx_flag
  FROM cn_comp_plans_all
  WHERE comp_plan_id = p_comp_plan_id;

Line: 1914

   SELECT name
     INTO l_comp_group_name
     FROM cn_comp_groups
     WHERE comp_group_id = p_comp_group_id;

Line: 1940

   SELECT nvl(booked_flag, 'N')
   INTO l_booked_flag
   FROM aso_i_oe_order_headers_v
   WHERE header_id = p_order_header_id;

Line: 1946

      SELECT end_date
      INTO l_booked_date
      FROM wf_item_activity_statuses
      WHERE item_type = OE_GLOBALS.G_WFI_HDR
         AND item_key = p_order_header_id
         AND process_activity IN (SELECT wpa.instance_id
                                 FROM  wf_process_activities wpa
                                 WHERE wpa.activity_item_type = OE_GLOBALS.G_WFI_HDR
                                       AND wpa.activity_name = 'BOOK_ORDER');

Line: 1971

      SELECT cust_acct_site_id
      FROM hz_cust_site_uses
   WHERE site_use_id = p_site_use_id
   AND org_id = p_org_id;

Line: 2009

      SELECT quota_flag
      FROM   aso_i_sales_credit_types_v
   WHERE  sales_credit_type_id = p_sales_credit_type_id;

Line: 2052

   SELECT PRECISION, EXTENDED_PRECISION
     INTO x_precision, x_ext_precision
     FROM cn_credit_types
     WHERE name = p_credit_type_name
     AND org_id = p_org_id;

Line: 2097

  SELECT start_date
    FROM cn_credit_conv_fcts_all
    WHERE from_credit_type_id = p_from_credit_type_id
    AND   to_credit_type_id = -1000
    AND   start_date <= p_conv_date
    AND org_id = p_org_id
    ORDER BY start_date DESC ;

Line: 2109

   SELECT monetary_flag into l_monetary_flag
     FROM cn_credit_types_all
     WHERE credit_type_id = p_from_credit_type_id
     AND org_id = p_org_id;

Line: 2116

   SELECT conversion_factor * Nvl(p_credit_unit,0)
     INTO l_conv_amount
     FROM cn_credit_conv_fcts_all
     WHERE from_credit_type_id = p_from_credit_type_id
     AND   to_credit_type_id = -1000
     AND org_id = p_org_id
     AND   p_conv_date between start_date and nvl(end_date, p_conv_date)
     ;

Line: 2134

         SELECT conversion_factor * Nvl(p_credit_unit,0)
     INTO l_conv_amount
     FROM cn_credit_conv_fcts_all
     WHERE from_credit_type_id = p_from_credit_type_id
     AND   to_credit_type_id = -1000
     AND org_id = p_org_id
     AND   l_date between start_date and nvl(end_date, l_date)
     ;

Line: 2146

      SELECT Nvl(p_credit_unit,0) INTO l_conv_amount
  FROM sys.dual;

Line: 2192

  SELECT start_date
    FROM cn_credit_conv_fcts_all
    WHERE from_credit_type_id = p_from_credit_type_id
    AND   to_credit_type_id = -1000
    AND   Trunc(start_date) <= Trunc(p_conv_date)
    AND org_id = p_org_id
    ORDER BY start_date DESC ;

Line: 2211

   SELECT monetary_flag into l_monetary_flag
     FROM cn_credit_types_all
     WHERE credit_type_id = p_from_credit_type_id
     AND org_id = p_org_id;

Line: 2220

   SELECT conversion_factor * Nvl(p_credit_unit,0)
     INTO l_conv_amount
     FROM cn_credit_conv_fcts_all
     WHERE from_credit_type_id = p_from_credit_type_id
     AND   to_credit_type_id = -1000
     AND org_id = p_org_id
     AND   p_conv_date between start_date and nvl(end_date, p_conv_date)
     ;

Line: 2242

         SELECT conversion_factor * Nvl(p_credit_unit,0)
     INTO l_conv_amount
     FROM cn_credit_conv_fcts_all
     WHERE from_credit_type_id = p_from_credit_type_id
     AND   to_credit_type_id = -1000
     AND org_id = p_org_id
     AND   l_date between start_date and nvl(end_date, l_date);

Line: 2254

      SELECT Nvl(p_credit_unit,0) INTO l_conv_amount
  FROM sys.dual;

Line: 2390

   SELECT chart_of_accounts_id
     INTO l_chart_of_accounts_id
     FROM gl_sets_of_books gsb,
     cn_repositories_all cr
     WHERE cr.set_of_books_id = gsb.set_of_books_id
     AND cr.org_id = p_org_id;

Line: 2404

   sql_stmt := 'SELECT ';

Line: 2479

   SELECT chart_of_accounts_id
     INTO l_chart_of_accounts_id
     FROM gl_sets_of_books gsb,
     cn_repositories_all cr
     WHERE cr.set_of_books_id = gsb.set_of_books_id
     AND org_id = p_org_id;

Line: 2493

   sql_stmt := 'SELECT ';

Line: 2564

   SELECT chart_of_accounts_id
     INTO l_chart_of_accounts_id
     FROM gl_sets_of_books gsb,
     cn_repositories_all cr
     WHERE cr.set_of_books_id = gsb.set_of_books_id
     AND org_id = p_org_id;

Line: 2578

   sql_stmt := 'SELECT ';

Line: 2632

     SELECT descriptive_rule_attribute
       INTO l_desc
       FROM cn_rule_attributes_desc_v
       WHERE rule_id = p_rule_id
       AND   attribute_rule_id  = p_attribute_id ;

Line: 2657

     SELECT count(*)
       INTO l_count
       FROM cn_attribute_rules
       WHERE rule_id = p_rule_id;

Line: 2684

          SELECT status, payrun_id
            FROM cn_payruns
            WHERE payrun_id = p_payrun_id;

Line: 2739

         SELECT hold_payment, salesrep_id
           FROM cn_salesreps
           WHERE salesrep_id = p_salesrep_id
	     AND org_id      = p_org_id;

Line: 2793

         SELECT hold_payment, salesrep_id
           FROM cn_salesreps
           WHERE salesrep_id = p_salesrep_id
	     AND org_id = p_org_id;

Line: 2845

      SELECT pay_element_type_id
  FROM cn_quota_pay_elements p,
	cn_rs_salesreps s
	WHERE p.quota_id    = p_quota_id
	AND p_date between p.start_date and p.end_date
	AND s.salesrep_id = p_salesrep_id
	AND s.org_id      = p_org_id
	AND nvl(s.status,'A') =  p.status;

Line: 2859

    select nvl(payroll_flag,'N')
     into  l_payroll_flag
      from cn_repositories
     WHERE org_id = p_org_id;

Line: 2886

    SELECT 1
      FROM cn_payment_worksheets
      WHERE payrun_id = p_payrun_id
      AND salesrep_id = p_salesrep_id
      AND org_id      = p_org_id;

Line: 2931

    SELECT worksheet_status
      FROM cn_payment_worksheets
      WHERE payrun_id = p_payrun_id
      AND salesrep_id = p_salesrep_id
      AND org_id      = p_org_id
      AND quota_id   iS NULL;

Line: 2974

   SELECT element_name
          FROM pay_element_types_f
   WHERE element_type_id = p_element_type_id;

Line: 3004

     SELECT function_name
       FROM fnd_form_functions
       WHERE UPPER(web_html_call) like p_name;

Line: 3045

      SELECT 1 INTO l_exist  FROM dual WHERE EXISTS
	(SELECT 1
	 FROM cn_srp_payee_assigns cnspay, cn_period_statuses cnps
	 WHERE cnspay.payee_id = p_salesrep_id
	 AND cnps.period_id = p_period_id
	 AND cnps.org_id    = p_org_id
	 AND cnspay.org_id  = p_org_id
	 AND ((cnspay.start_date <= cnps.end_date)
	      AND (cnps.start_date <= Nvl(cnspay.end_date,cnps.start_date)))
	 );

Line: 3063

      SELECT 1 INTO l_exist  FROM dual WHERE EXISTS
        (SELECT 1 FROM cn_srp_roles sr, cn_period_statuses cnps
         WHERE sr.salesrep_id = p_salesrep_id
         AND sr.role_id = 54
	 AND sr.org_id = p_org_id
	 AND cnps.org_id = p_org_id
         AND cnps.period_id = p_period_id
         AND ((sr.start_date <= cnps.end_date)
        AND (cnps.start_date <= Nvl(sr.end_date,cnps.start_date)))
         );

Line: 3107

    SELECT distinct r.name role_name,assign.start_date
    FROM   cn_srp_periods srp,
       cn_srp_plan_assigns assign,
       cn_roles r
    WHERE assign.srp_plan_assign_id(+)= srp.srp_plan_assign_id
      AND srp.period_id	  = l_period_id
      AND assign.role_id  = r.role_id(+)
      AND srp.salesrep_id = l_salesrep_id
     -- AND srp.credit_type_id = -1000
      AND srp.quota_id <> -1000
    ORDER BY assign.start_date;