APPS.CN_SCA_DENORM_RULES_PVT SQL Statements

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

Line: 40

   SELECT mv.sca_rule_attribute_id,
          ra.src_column_name
     FROM cn_sca_rule_cond_vals_mv mv,
          cn_sca_rule_attributes ra
    WHERE mv.sca_rule_attribute_id = ra.sca_rule_attribute_id
      AND mv.transaction_source = p_transaction_source
      AND EXISTS(
          SELECT 'X'
            FROM cn_sca_denorm_rules dr
           WHERE mv.sca_credit_rule_id = dr.sca_credit_rule_id
	     AND dr.transaction_source = p_transaction_source
	     AND dr.org_id = p_org_id)
    GROUP BY mv.sca_rule_attribute_id, ra.src_column_name;

Line: 55

   SELECT rule_attr_comb_value
     FROM cn_sca_denorm_rules
    WHERE transaction_source = p_transaction_source
      AND org_id = p_org_id
    GROUP BY rule_attr_comb_value;

Line: 62

   SELECT mv.operator_id
     FROM cn_sca_rule_cond_vals_mv mv
    WHERE mv.sca_rule_attribute_id = l_rule_attr_id
    GROUP BY mv.operator_id;

Line: 77

   l_update_flag		VARCHAR2(1) := 'Y';

Line: 78

   l_delete_flag		VARCHAR2(1) := 'N';

Line: 108

   UPDATE cn_sca_denorm_rules
      SET rule_attr_comb_value = 1
    WHERE transaction_source = p_transaction_source
      AND org_id = p_org_id;

Line: 122

	    --+ For the first occurance, I need to update the rule_attr_comb_value
	    --+ in cn_sca_denorm_rules table.
	    --+

	    IF (l_update_flag = 'Y') THEN

	       UPDATE cn_sca_denorm_rules
                  SET rule_attr_comb_value = 1
                WHERE transaction_source = p_transaction_source
		  AND org_id = p_org_id;

Line: 134

	       l_update_flag := 'N';

Line: 140

	       UPDATE cn_sca_denorm_rules dr
	          SET dr.rule_attr_comb_value =
		      dr.rule_attr_comb_value * l_attr_prime_tbl(i).prime_number
		WHERE dr.sca_credit_rule_id IN(
		      SELECT idr.sca_credit_rule_id
		        FROM cn_sca_denorm_rules idr,
			     cn_sca_conditions c
		       WHERE idr.ancestor_rule_id = c.sca_credit_rule_id
		         AND c.sca_rule_attribute_id = rule_attr_rec.sca_rule_attribute_id
			 AND idr.transaction_source = p_transaction_source
			 AND idr.org_id = p_org_id)
	          AND dr.transaction_source = p_transaction_source;

Line: 157

	    l_delete_flag := 'Y';

Line: 167

   IF (l_delete_flag = 'Y') THEN
      DELETE FROM cn_sca_combinations
       WHERE transaction_source = p_transaction_source
         AND org_id = p_org_id;

Line: 174

         INSERT INTO cn_sca_combinations(
	     sca_rule_attribute_id,
	     rule_attr_comb_value,
	     transaction_source,
	     created_by,
             creation_date,
             last_updated_by,
             last_update_date,
             last_update_login,
	     org_id)
         SELECT sca_rule_attribute_id,
	        get_attr_rec.rule_attr_comb_value,
	        p_transaction_source,
                fnd_global.user_id,
                SYSDATE,
                fnd_global.user_id,
                SYSDATE,
                fnd_global.login_id,
                p_org_id
           FROM cn_sca_rule_cond_vals_mv mv
          WHERE mv.sca_credit_rule_id IN (
             SELECT sca_credit_rule_id
               FROM cn_sca_denorm_rules dr
              WHERE rule_attr_comb_value = get_attr_rec.rule_attr_comb_value
                AND dr.transaction_source = p_transaction_source
                AND dr.org_id = p_org_id
                AND rownum = 1)
          GROUP BY sca_rule_attribute_id;

Line: 259

   SELECT *
     FROM cn_sca_denorm_rules csdr
    WHERE transaction_source = p_txn_src
      AND org_id = p_org_id
      AND EXISTS (
          SELECT 'S'
            FROM cn_sca_credit_rules  csca
           WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id
             AND transaction_source = p_txn_src
             AND NVL(IS_DENORMED,'N') = 'N');

Line: 271

   SELECT csca.sca_credit_rule_id
     FROM cn_sca_credit_rules  csca
    WHERE transaction_source = p_txn_src
      AND org_id = p_org_id
      AND NVL(IS_DENORMED,'N') = 'N';

Line: 301

   SELECT count(1)
     INTO l_invalid_rules
     FROM cn_sca_conditions a,
          cn_sca_credit_rules b
    WHERE a.sca_credit_rule_id = b.sca_credit_rule_id
      AND b.org_id = p_org_id
      AND NOT EXISTS(
          SELECT 'X'
	    FROM cn_sca_rule_attributes c
	   WHERE a.sca_rule_attribute_id = c.sca_rule_attribute_id)
      AND b.transaction_source = p_txn_src;

Line: 327

      SELECT max(rank)
        INTO l_max_rank
	FROM cn_sca_credit_rules
       WHERE transaction_source = p_txn_src
         AND org_id = p_org_id;

Line: 336

	    DELETE FROM cn_sca_denorm_rules
	     WHERE transaction_source = p_txn_src
	       AND org_id = p_org_id;

Line: 339

	    debugmsg('Rules Sync : Deleted denorm rule count : '||SQL%ROWCOUNT);

Line: 359

   DELETE FROM cn_sca_denorm_rules csdr
    WHERE transaction_source = p_txn_src
      AND org_id = p_org_id
      AND (EXISTS
          (SELECT 'S'
             FROM cn_sca_credit_rules  csca
            WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id
              AND NVL(IS_DENORMED,'N') = 'N')
       OR NOT EXISTS
         (SELECT 'S'
            FROM cn_sca_credit_rules  csca
           WHERE csca.sca_credit_rule_id = csdr.sca_credit_rule_id));

Line: 372

   debugmsg('Rules Sync : Total Rules Deleted - '||SQL%ROWCOUNT);

Line: 379

   INSERT INTO cn_sca_denorm_rules(
          sca_credit_rule_id,
	  ancestor_rule_id,
	  start_date,
	  end_date,
	  rank,
	  level_from_root,
	  relative_rank,
	  root_flag,
	  transaction_source,
          created_by,
	  creation_date,
	  last_updated_by,
	  last_update_date,
          last_update_login,
	  org_id)
   SELECT sca_credit_rule_id,
          sca_credit_rule_id,
	  start_date,
	  end_date,
	  rank,
	  level,
	  1/(NVL(DECODE(rank,0,0.1,rank),l_max_rank)*POWER(l_max_rank,level)), -- relative rank
	  DECODE(NVL(parent_rule_id,0),0,'Y','N'), -- root flag
          p_txn_src,
	  fnd_global.user_id,
	  SYSDATE,
	  fnd_global.user_id,
          SYSDATE,
	  fnd_global.login_id,
	  p_org_id
     FROM cn_sca_credit_rules cscr
    WHERE transaction_source = p_txn_src
      AND org_id = p_org_id
      AND NVL(IS_DENORMED,'N') = 'N'
  CONNECT BY PRIOR sca_credit_rule_id = parent_rule_id
    START WITH parent_rule_id IS NULL AND transaction_source = p_txn_src;

Line: 417

   debugmsg('Rules Sync : Parent Rules Insert Completed :'||SQL%ROWCOUNT);

Line: 428

      INSERT INTO cn_sca_denorm_rules(
             sca_credit_rule_id,
	     ancestor_rule_id,
	     start_date,
	     end_date,
	     rank,
	     level_from_root,
	     relative_rank,
	     root_flag,
	     transaction_source,
	     created_by,
	     creation_date,
	     last_updated_by,
	     last_update_date,
	     last_update_login,
	     org_id)
      SELECT c1_rec.sca_credit_rule_id,
             sca_credit_rule_id,
	     c1_rec.start_date,
             c1_rec.end_date,
	     c1_rec.rank,
	     c1_rec.level_from_root,
	     c1_rec.relative_rank,
	     c1_rec.root_flag,
	     p_txn_src,
             fnd_global.user_id,
	     SYSDATE,
	     fnd_global.user_id,
             SYSDATE,
	     fnd_global.login_id,
	     p_org_id
        FROM cn_sca_credit_rules
       WHERE sca_credit_rule_id <> c1_rec.sca_credit_rule_id
         AND transaction_source = p_txn_src
         AND org_id = p_org_id
     CONNECT BY PRIOR parent_rule_id = sca_credit_rule_id
       START WITH sca_credit_rule_id = c1_rec.sca_credit_rule_id
         AND transaction_source = p_txn_src;

Line: 471

      UPDATE cn_sca_denorm_rules
         SET calculated_rank = (
	 	SELECT SUM(r2.relative_rank)
	          FROM cn_sca_denorm_rules r1,
		       cn_sca_denorm_rules r2
		 WHERE r1.transaction_source = p_txn_src
		   AND r2.transaction_source = p_txn_src
		   AND r1.sca_credit_rule_id = c1_rec.sca_credit_rule_id
		   AND r2.sca_credit_rule_id = r1.ancestor_rule_id
		   AND r2.ancestor_rule_id = r1.ancestor_rule_id),
	     num_rule_attributes = (
	        SELECT count(distinct c.sca_rule_attribute_id)
		  FROM cn_sca_denorm_rules r,
		       cn_sca_credit_rules s,
		       cn_sca_conditions  c
		 WHERE r.transaction_source = p_txn_src
		   AND s.transaction_source = p_txn_src
		   AND r.sca_credit_rule_id = c1_rec.sca_credit_rule_id
		   AND r.ancestor_rule_id = s.sca_credit_rule_id
		   AND s.sca_credit_rule_id = c.sca_credit_rule_id)
       WHERE transaction_source = p_txn_src
         AND sca_credit_rule_id = c1_rec.sca_credit_rule_id
	 AND org_id = p_org_id;

Line: 499

      UPDATE cn_sca_credit_rules
         SET is_denormed     = 'Y'
       WHERE sca_credit_rule_id = c1_rec.sca_credit_rule_id
         AND transaction_source = p_txn_src
	 AND org_id = p_org_id;