APPS.HZ_POP_MRULE_SET_ATTR_V2PUB SQL Statements

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

Line: 30

PROCEDURE update_prim_display_order(p_mrule_set_id IN NUMBER);

Line: 31

PROCEDURE update_sec_display_order(p_mrule_set_id  IN NUMBER);

Line: 35

 CURSOR c_insert_prim IS ( SELECT p.ATTRIBUTE_ID FROM
 			   (SELECT  unique p1.ATTRIBUTE_ID
                            FROM   HZ_MATCH_RULE_PRIMARY p1
		            WHERE  p1.match_rule_id in (Select unique condition_match_rule_id
			                               from hz_match_rule_conditions
			  	  		       where match_rule_set_id = p_mrule_set_id)
                            UNION
			    (SELECT unique  attribute_id
			     from hz_match_rule_conditions
			     where match_rule_set_id = p_mrule_set_id
			     and attribute_id is not null
			    )
		           ) p
                          WHERE NOT EXISTS (
			   SELECT  1
                           FROM   HZ_MATCH_RULE_PRIMARY p1
		           WHERE  p1.match_rule_id = p_mrule_set_id
			   AND    p1.ATTRIBUTE_ID = p.ATTRIBUTE_ID
			  )
			 );

Line: 55

CURSOR c_delete_prim IS  SELECT primary_attribute_id
			 FROM HZ_MATCH_RULE_PRIMARY
			 WHERE match_rule_id = p_mrule_set_id
			 AND ATTRIBUTE_ID IN (SELECT  unique p1.ATTRIBUTE_ID
					      FROM   HZ_MATCH_RULE_PRIMARY p1
					      WHERE  p1.match_rule_id = p_mrule_set_id
					      MINUS
					       (SELECT  unique p1.ATTRIBUTE_ID
					         FROM   HZ_MATCH_RULE_PRIMARY p1
					         WHERE  p1.match_rule_id in (Select unique condition_match_rule_id
					   			          from hz_match_rule_conditions
									  where match_rule_set_id = p_mrule_set_id)
					          UNION
						  (SELECT unique  attribute_id
						     from hz_match_rule_conditions
						     where match_rule_set_id = p_mrule_set_id
						     and attribute_id is not null
						  )
					       )
					      );

Line: 75

CURSOR c_insert_sec IS ( SELECT  s1.ATTRIBUTE_ID
                         FROM   HZ_MATCH_RULE_SECONDARY s1
		         WHERE  s1.match_rule_id in (Select unique condition_match_rule_id
			                               from hz_match_rule_conditions
				  		       where match_rule_set_id = p_mrule_set_id)
                          AND NOT EXISTS(SELECT 1 FROM HZ_MATCH_RULE_SECONDARY s2
				         WHERE  s2.match_rule_id = p_mrule_set_id
					 AND    s2.ATTRIBUTE_ID = s1.ATTRIBUTE_ID
					 )
			 );

Line: 86

CURSOR c_delete_sec IS  SELECT secondary_attribute_id
			 FROM HZ_MATCH_RULE_SECONDARY
			 WHERE match_rule_id = p_mrule_set_id
			 AND ATTRIBUTE_ID IN (SELECT  unique s1.ATTRIBUTE_ID
					      FROM   HZ_MATCH_RULE_SECONDARY s1
					      WHERE  s1.match_rule_id = p_mrule_set_id
					      MINUS
					      SELECT  unique s1.ATTRIBUTE_ID
					      FROM   HZ_MATCH_RULE_SECONDARY s1
					      WHERE  s1.match_rule_id in (Select unique condition_match_rule_id
							  	          from hz_match_rule_conditions
									  where match_rule_set_id = p_mrule_set_id)
					     );

Line: 112

  OPEN c_insert_prim;

Line: 113

  FETCH c_insert_prim BULK COLLECT INTO v_attr_id_list;

Line: 114

  CLOSE c_insert_prim;

Line: 119

     HZ_MATCH_RULE_PRIMARY_PKG.INSERT_ROW(x_primary_attribute_id,               --px_PRIMARY_ATTRIBUTE_ID
			                  p_mrule_set_id,                       --p_MATCH_RULE_ID
					  v_attr_id_list(i),                    --p_ATTRIBUTE_ID
					  NULL,			                --p_ACTIVE_FLAG
					  NULL,			                --p_FILTER_FLAG
					  HZ_UTILITY_V2PUB.CREATED_BY,          --p_CREATED_BY
					  HZ_UTILITY_V2PUB.CREATION_DATE,       --p_CREATION_DATE
					  HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,   --p_LAST_UPDATE_LOGIN
					  HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,    --p_LAST_UPDATE_DATE
					  HZ_UTILITY_V2PUB.LAST_UPDATED_BY,     --p_LAST_UPDATED_BY
					  l_object_version_number               --p_OBJECT_VERSION_NUMBER
					  );

Line: 135

  /* Delete the unnecessary primary attributes */

  OPEN c_delete_prim;

Line: 138

  FETCH c_delete_prim BULK COLLECT INTO v_attr_id_list;

Line: 139

  CLOSE c_delete_prim;

Line: 143

     DELETE FROM HZ_MATCH_RULE_PRIMARY
     WHERE PRIMARY_ATTRIBUTE_ID = v_attr_id_list(i);

Line: 146

  /* Update the primary attributes display order */
  update_prim_display_order(p_mrule_set_id);

Line: 151

  OPEN c_insert_sec;

Line: 152

  FETCH c_insert_sec BULK COLLECT INTO v_attr_id_list;

Line: 153

  CLOSE c_insert_sec;

Line: 158

     HZ_MATCH_RULE_SECONDARY_PKG.INSERT_ROW(x_secondary_attribute_id,           --px_SECONDARY_ATTRIBUTE_ID
			                  p_mrule_set_id,                       --p_MATCH_RULE_ID
					  v_attr_id_list(i),                    --p_ATTRIBUTE_ID
					  0,			                --p_SCORE
					  NULL,			                --p_ACTIVE_FLAG
					  HZ_UTILITY_V2PUB.CREATED_BY,          --p_CREATED_BY
					  HZ_UTILITY_V2PUB.CREATION_DATE,       --p_CREATION_DATE
					  HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,   --p_LAST_UPDATE_LOGIN
					  HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,    --p_LAST_UPDATE_DATE
					  HZ_UTILITY_V2PUB.LAST_UPDATED_BY,     --p_LAST_UPDATED_BY
					  l_object_version_number               --p_OBJECT_VERSION_NUMBER
					  );

Line: 174

 /* Delete the unnecessary secondary attributes */

  OPEN c_delete_sec;

Line: 177

  FETCH c_delete_sec BULK COLLECT INTO v_attr_id_list;

Line: 178

  CLOSE c_delete_sec;

Line: 182

     DELETE FROM HZ_MATCH_RULE_SECONDARY
     WHERE SECONDARY_ATTRIBUTE_ID = v_attr_id_list(i);

Line: 186

 /* Update the secondary attributes display order */
  update_sec_display_order(p_mrule_set_id);

Line: 191

PROCEDURE update_prim_display_order(p_mrule_set_id NUMBER)
IS
l_g_miss_num NUMBER;

Line: 200

	SELECT unique  attribute_id,0 display_order
	from hz_match_rule_conditions
	where match_rule_set_id = p_mrule_set_id
	and attribute_id is not null
	UNION
	SELECT  p1.ATTRIBUTE_ID,min(nvl(p1.display_order,l_g_miss_num)) display_order
	FROM    HZ_MATCH_RULE_PRIMARY p1
	WHERE  EXISTS  (Select 1
			from  hz_match_rule_conditions cond1
			where cond1.match_rule_set_id = p_mrule_set_id
			and   cond1.condition_match_rule_id = p1.match_rule_id
		       )
	AND NOT EXISTS (SELECT 1 from hz_match_rule_conditions cond2
			where cond2.match_rule_set_id = p_mrule_set_id
			and  cond2.attribute_id = p1.attribute_id
			)
	group by p1.ATTRIBUTE_ID;

Line: 226

  UPDATE HZ_MATCH_RULE_PRIMARY
  SET   DISPLAY_ORDER = decode(v_attr_order_list(i),l_g_miss_num,null,v_attr_order_list(i))
  WHERE match_rule_id = p_mrule_set_id
  AND   attribute_id = v_attr_id_list(i);

Line: 234

PROCEDURE update_sec_display_order(p_mrule_set_id  IN NUMBER)
IS
l_g_miss_num NUMBER;

Line: 243

	SELECT s1.ATTRIBUTE_ID,min(nvl(s1.display_order,l_g_miss_num)) display_order
	FROM    HZ_MATCH_RULE_SECONDARY s1
	WHERE  EXISTS  (Select 1
			from  hz_match_rule_conditions cond1
			where cond1.match_rule_set_id = p_mrule_set_id
			and   cond1.condition_match_rule_id = s1.match_rule_id
		       )
	group by s1.ATTRIBUTE_ID;

Line: 261

  UPDATE HZ_MATCH_RULE_SECONDARY
  SET   DISPLAY_ORDER = decode(v_attr_order_list(i),l_g_miss_num,null,v_attr_order_list(i))
  WHERE match_rule_id = p_mrule_set_id
  AND   attribute_id  = v_attr_id_list(i);