APPS.CN_API SQL Statements

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

Line: 78

   SELECT name INTO l_rs_name
     FROM cn_rate_schedules_all
     WHERE rate_schedule_id = p_rate_table_id;

Line: 99

   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: 121

   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: 144

   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: 168

   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: 190

   SELECT name
     INTO l_rev_class_name
     FROM cn_revenue_classes_all
     WHERE revenue_class_id = p_rev_class_id
     ;

Line: 211

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

Line: 774

   SELECT name
     INTO l_comp_plan_name
     FROM cn_comp_plans_all
     WHERE comp_plan_id = p_comp_plan_id;

Line: 796

   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: 819

   SELECT name
     INTO l_pmt_plan_name
     FROM cn_pmt_plans_all
     WHERE pmt_plan_id = p_pmt_plan_id;

Line: 841

   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: 864

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

Line: 904

      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: 912

      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: 963

   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: 1221

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

Line: 1243

   SELECT name
     INTO l_role_name
     FROM cn_roles
     WHERE  role_id = p_role_id ;

Line: 1273

   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: 1329

   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: 1375

   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: 1419

	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: 1429

	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: 1466

      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: 1474

        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: 1499

   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: 1527

   SELECT itd_flag
     INTO l_itd_flag
     FROM cn_calc_formulas_all
     WHERE calc_formula_id = p_calc_formula_id ;

Line: 1548

   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: 1570

   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: 1592

   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: 1734

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

Line: 1787

      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: 1793

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

Line: 1837

      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: 1843

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

Line: 1902

      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: 1936

   SELECT name
     INTO l_comp_group_name
     FROM cn_comp_groups
     WHERE comp_group_id = p_comp_group_id;

Line: 1962

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

Line: 1968

      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: 1993

      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: 2031

      SELECT quota_flag
      FROM   aso_i_sales_credit_types_v
   WHERE  sales_credit_type_id = p_sales_credit_type_id;

Line: 2074

   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: 2119

  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: 2131

   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: 2138

   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: 2156

         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: 2168

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

Line: 2214

  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: 2233

   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: 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   p_conv_date between start_date and nvl(end_date, p_conv_date)
     ;

Line: 2264

         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: 2276

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

Line: 2412

   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: 2426

   sql_stmt := 'SELECT ';

Line: 2501

   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: 2515

   sql_stmt := 'SELECT ';

Line: 2586

   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: 2600

   sql_stmt := 'SELECT ';

Line: 2654

     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: 2679

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

Line: 2706

          SELECT status, payrun_id
            FROM cn_payruns
            WHERE payrun_id = p_payrun_id;

Line: 2761

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

Line: 2815

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

Line: 2867

      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: 2881

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

Line: 2908

    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: 2953

    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: 2996

   SELECT element_name
          FROM pay_element_types_f
   WHERE element_type_id = p_element_type_id;

Line: 3026

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

Line: 3067

      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: 3085

      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: 3132

   SELECT DISTINCT t.role_name role_name,
         assign.start_date
   FROM cn_srp_periods srp  ,
        cn_srp_plan_assigns assign,
        JTF_RS_ROLES_TL T         ,
        JTF_RS_ROLES_B B
  WHERE assign.srp_plan_assign_id(+)= srp.srp_plan_assign_id
    AND srp.period_id                   = l_period_id
    AND assign.role_id                  = B.role_id(+)
    AND srp.ORG_ID                      = assign.ORG_ID
    AND srp.salesrep_id                 = l_salesrep_id
    AND B.ROLE_ID                       = T.ROLE_ID
    AND T.LANGUAGE                      = userenv('LANG')
      -- AND srp.credit_type_id = -1000
    AND srp.quota_id <> -1000
    ORDER BY assign.start_date;

Line: 3149

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

Line: 3197

		SELECT salesrep_id
		FROM cn_payment_worksheets cnw
		WHERE cnw.payrun_id 	  = p_payrun_id
		  AND cnw.org_id          = p_ORG_ID
		  AND cnw.quota_id       IS NULL ;

Line: 3207

	    SELECT /*+ leading(cnw,srp) use_nl(srp) */ DISTINCT
                   srp.salesrep_id ||'-' ||srp_plan_assign_id ||'-' ||srp.period_id
	    FROM cn_srp_periods srp,
	         (SELECT column_value SALESREP_ID FROM TABLE(CAST(l_cnw_salesrep_id AS JTF_NUMBER_TABLE))) cnw
	    WHERE srp.period_id       = p_period_id
	      AND srp.quota_id       <> -1000
	      AND srp.org_id          = p_ORG_ID
	      AND srp.salesrep_id     = cnw.SALESREP_ID;

Line: 3220

	    SELECT /*+ leading(cnw_srp,assign) use_nl(assign,rl) */ DISTINCT
                   cnw_srp.SALESREP_ID, cnw_srp.PERIOD_ID, RL.ROLE_NAME, ASSIGN.START_DATE
	    FROM CN_SRP_PLAN_ASSIGNS assign ,
                 JTF_RS_ROLES_VL rl ,
	         (SELECT TO_NUMBER(SUBSTR(column_value, 1, INSTR(column_value, '-', 1, 1) - 1 ) ) SALESREP_ID,
	                 TO_NUMBER(SUBSTR(column_value , INSTR(column_value, '-', 1, 1) + 1,
                                    (INSTR(column_value, '-', 1, 2) - INSTR(column_value, '-', 1, 1))-1) ) PLANASSIGNID,
                         TO_NUMBER(SUBSTR(column_value , INSTR(column_value, '-', 1, 2) + 1 ,LENGTH(column_value) )) PERIOD_ID
	            FROM TABLE(CAST(l_cnw_srp_salrep_id AS JTF_VARCHAR2_TABLE_2000))
                 ) cnw_srp
	    WHERE assign.ORG_ID                = p_ORG_ID
	      AND assign.srp_plan_assign_id(+) = cnw_srp.PLANASSIGNID
	      AND assign.salesrep_id(+)        = cnw_srp.SALESREP_ID
	      AND rl.role_id(+)                = assign.ROLE_ID
	      AND rl.role_type_code            = 'SALES_COMP'
	      ORDER BY assign.start_date;

Line: 3296

	      g_salesrep_info_cache.DELETE;

Line: 3342

     SELECT DISTINCT re2.user_id
      FROM jtf_rs_group_usages u2,
           jtf_rs_rep_managers m2,
           jtf_rs_resource_extns_vl re2,
           (SELECT DISTINCT m1.resource_id,
                            greatest(pr.start_date, m1.start_date_active) start_date,
                            least(pr.end_date, nvl(m1.end_date_active, pr.end_date)) end_date
              FROM jtf_rs_resource_extns re1,
                   cn_period_statuses    pr,
                   jtf_rs_group_usages   u1,
                   jtf_rs_rep_managers   m1
             WHERE re1.user_id = l_user_id --129941
               AND (pr.period_id, pr.org_id) =
                   (SELECT p.pay_period_id,
                           p.org_id
                      FROM cn_payruns p
                     WHERE p.payrun_id = l_payrun_id ) -- 852986
               AND u1.usage = 'COMP_PAYMENT'
               AND ((m1.start_date_active <= pr.end_date) AND
                   (pr.start_date <= nvl(m1.end_date_active, pr.start_date)))
               AND u1.group_id = m1.group_id
               AND m1.resource_id = re1.resource_id
               AND m1.parent_resource_id = m1.resource_id
               AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
               AND m1.category <> 'TBH') v3
     WHERE u2.usage = 'COMP_PAYMENT'
       AND u2.group_id = m2.group_id
       AND m2.parent_resource_id = v3.resource_id
       AND ((m2.start_date_active <= v3.end_date) AND
           (v3.start_date <= nvl(m2.end_date_active, v3.start_date)))
       AND m2.category <> 'TBH'
       AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
       AND m2.resource_id = re2.resource_id;

Line: 3411

     SELECT DISTINCT re2.user_id
      FROM jtf_rs_group_usages u2,
           jtf_rs_rep_managers m2,
           jtf_rs_resource_extns_vl re2,
           (SELECT DISTINCT m1.resource_id,
                            greatest(pr.start_date, m1.start_date_active) start_date,
                            least(pr.end_date, nvl(m1.end_date_active, pr.end_date)) end_date
              FROM jtf_rs_resource_extns re1,
                   cn_period_statuses    pr,
                   jtf_rs_group_usages   u1,
                   jtf_rs_rep_managers   m1
             WHERE re1.user_id = l_user_id --129941
               AND (pr.period_id, pr.org_id) =
                   (SELECT p.pay_period_id,
                           p.org_id
                      FROM cn_payruns p
                     WHERE p.payrun_id = l_payrun_id ) -- 852986
               AND u1.usage = 'COMP_PAYMENT'
               AND ((m1.start_date_active <= pr.end_date) AND
                   (pr.start_date <= nvl(m1.end_date_active, pr.start_date)))
               AND u1.group_id = m1.group_id
               AND m1.resource_id = re1.resource_id
               AND m1.parent_resource_id = m1.resource_id
               AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
               AND m1.category <> 'TBH') v3
     WHERE u2.usage = 'COMP_PAYMENT'
       AND u2.group_id = m2.group_id
       AND m2.parent_resource_id = v3.resource_id
       AND ((m2.start_date_active <= v3.end_date) AND
           (v3.start_date <= nvl(m2.end_date_active, v3.start_date)))
       AND m2.category <> 'TBH'
       AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
       AND m2.resource_id = re2.resource_id;