APPS.HZ_DUP_PVT SQL Statements

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

Line: 23

	-- party with last update date
	cursor get_def_master_lu_csr(cp_status varchar2) is
		select party_id, party_name
		from(
	 		select p.party_id party_id, p.party_name party_name,
			RANK() OVER (ORDER BY p.last_update_date desc ) rank
			from hz_parties p, hz_dup_set_parties mp
			where p.party_id = mp.dup_party_id
			and mp.dup_set_id = p_dup_set_id
			and p.status = cp_status
			and nvl(mp.merge_flag,'Y') = 'Y'
			)
		where rank = 1 and rownum=1;

Line: 40

		select party_id, party_name
		from(
	 		select p.party_id party_id,p.party_name party_name,
			RANK() OVER (ORDER BY p.creation_date desc, p.party_id desc ) rank
			from hz_parties p, hz_dup_set_parties mp
			where p.party_id = mp.dup_party_id
			and mp.dup_set_id = p_dup_set_id
			and p.status = cp_status
			and nvl(mp.merge_flag,'Y') = 'Y'
			)
		where rank = 1 and rownum=1;

Line: 54

		select party_id, party_name
		from(
	 		select p.party_id party_id,p.party_name party_name,
			RANK() OVER (ORDER BY p.creation_date, p.party_id) rank
			from hz_parties p, hz_dup_set_parties mp
			where p.party_id = mp.dup_party_id
			and mp.dup_set_id = p_dup_set_id
			and p.status = cp_status
			and nvl(mp.merge_flag,'Y') = 'Y'
			)
		where rank = 1 and rownum=1;

Line: 69

                select party_id, party_name
	        from
		(select party_id, party_name,rank() over (order by cnt desc) rank, last_update_date
	 	  from (
	 		SELECT
					ca.party_id party_id, party.party_name party_name,
					count(*) over (partition by ca.party_id) as cnt,
					ca.last_update_date
			from
					HZ_CUST_ACCOUNTS CA,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
					HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
			WHERE CA.PARTY_ID =DSP.DUP_PARTY_ID
				AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
				AND DS.DUP_SET_ID = DSP.DUP_SET_ID
				AND CA.PARTY_ID = PARTY.PARTY_ID
				AND DSP.DUP_SET_ID= p_dup_set_id
				AND nvl(dsp.merge_flag, 'Y') <> 'N'
				and party.status = cp_status
			)
			order by last_update_date desc )
		where rank = 1 and rownum=1;

Line: 93

        	select party_id, party_name
	        from
		(select party_id, party_name,rank() over (order by cnt desc) rank, last_update_date
	 	 from (
	 		SELECT
				ps.party_id party_id, party.party_name party_name,
				count(*) over (partition by ps.party_id) as cnt,
				ps.last_update_date
			from HZ_PARTY_SITES PS,HZ_PARTIES PARTY,
				HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
			WHERE PS.PARTY_ID =DSP.DUP_PARTY_ID
				AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
				AND DS.DUP_SET_ID = DSP.DUP_SET_ID
				AND PS.PARTY_ID = PARTY.PARTY_ID
				AND DSP.DUP_SET_ID= p_dup_set_id
				AND nvl(dsp.merge_flag, 'Y') <> 'N'
				and party.status = cp_status
		)
		order by last_update_date desc )
	        where rank = 1 and rownum=1;

Line: 116

                select party_id, party_name
	        from (
		    	select party_id, party_name, rank() over (order by cnt desc) rank,
				last_update_date
	 		from ( SELECT
				party.party_id party_id,
				party.party_name party_name,
				count(*) over (partition by party.party_id) as cnt,
				party.last_update_date
				from HZ_RELATIONSHIPS R,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
				HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
			      WHERE PARTY.PARTY_ID =DSP.DUP_PARTY_ID
				AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
				AND DS.DUP_SET_ID = DSP.DUP_SET_ID
				AND R.OBJECT_ID = PARTY.PARTY_ID
				AND DSP.DUP_SET_ID= p_dup_set_id
				AND nvl(dsp.merge_flag, 'Y') <> 'N'
				and party.status = cp_status
			)
			order by last_update_date desc )
	       where rank = 1 and rownum=1;

Line: 140

        	select party_id, party_name
	 	from(
	 		select p.party_id party_id, p.party_name party_name,
			RANK() OVER (ORDER BY p.certification_level, p.last_update_date desc ) rank
			from hz_parties p, hz_dup_set_parties mp
			where p.party_id = mp.dup_party_id
			and mp.dup_set_id = p_dup_set_id
			and p.status = cp_status
			and nvl(mp.merge_flag,'Y') = 'Y'
		)
		where rank = 1 and rownum=1;

Line: 153

		select count(*)
		from hz_parties p, hz_dup_set_parties mp
		where p.party_id = mp.dup_party_id
		and mp.dup_set_id = p_dup_set_id
		and p.status = 'A';

Line: 160

   		select object_version_number
   		from HZ_DUP_SETS
   		where dup_set_id = p_dup_set_id;

Line: 166

   		select count(*)
   		from HZ_DUP_SET_PARTIES dsp, hz_parties p
   		where dsp.dup_set_id = p_dup_set_id
   		and dsp.dup_party_id = p.party_id
		and dsp.dup_party_id = cp_party_id
		and p.party_name = cp_party_name;

Line: 175

		select p.party_id, p.party_name
		from hz_parties p, hz_dup_set_parties mp
		where p.party_id = mp.dup_party_id
		and mp.dup_set_id = p_dup_set_id
		and p.status = 'A'
		and rownum = 1;

Line: 204

	/* default to last updated party if other profile returns null master*/
	open get_def_master_lu_csr(l_status);

Line: 215

	elsif	l_prof_value = 'LATEST_UPDATE_DATE'
	then
		l_master_party_id := l_lu_master_party_id;

Line: 292

	 -- update the winner party id to have merge_flag = 'Y'

	update hz_dup_set_parties
        set merge_flag = 'Y'
        where dup_set_id = p_dup_set_id
        and dup_party_id = l_master_party_id;

Line: 299

	update_winner_party (
   	p_dup_set_id       => p_dup_set_id
  	,p_winner_party_id  => l_master_party_id
  	,px_set_obj_version_number  => l_set_obj_version_number
  	,x_return_status   => x_return_status
  	,x_msg_count    => x_msg_count
  	,x_msg_data   => x_msg_data );

Line: 453

   select party_type
   from HZ_PARTIES a, HZ_DUP_SETS b
   where a.party_id = b.winner_party_id
   and b.dup_set_id = l_dset_id;

Line: 459

   select party_type
   from HZ_PARTIES
   where party_id = l_party_id;

Line: 512

   HZ_DUP_SET_PARTIES_PKG.Insert_Row(
      p_dup_party_id      => l_dup_set_party_id
     ,p_dup_set_id        => l_dup_set_id
     ,p_merge_flag        => 'Y'
     ,p_not_dup           => l_not_dup
     ,p_score             => l_score
     ,p_merge_seq_id      => l_merge_seq_id
     ,p_merge_batch_id    => l_merge_batch_id
     ,p_merge_batch_name  => l_merge_batch_name
     ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
     ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
     ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 580

   select party_type
   from HZ_PARTIES
   where party_id = l_party_id;

Line: 639

   HZ_DUP_BATCH_PKG.Insert_Row(
      px_dup_batch_id     => l_dup_batch_id
     ,p_dup_batch_name    => p_dup_batch_rec.dup_batch_name
     ,p_match_rule_id     => p_dup_batch_rec.match_rule_id
     ,p_application_id    => p_dup_batch_rec.application_id
     ,p_request_type      => p_dup_batch_rec.request_type
     ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
     ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
     ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 654

   HZ_DUP_SETS_PKG.Insert_Row(
      px_dup_set_id       => l_dup_set_id
     ,p_dup_batch_id      => l_dup_batch_id
     ,p_winner_party_id   => p_dup_set_rec.winner_party_id
     ,p_status            => 'SYSBATCH'
     ,p_assigned_to_user_id   => p_dup_set_rec.assigned_to_user_id
     ,p_merge_type        => p_dup_set_rec.merge_type
     ,p_object_version_number => 1
     ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
     ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
     ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 673

     HZ_DUP_SET_PARTIES_PKG.Insert_Row(
        p_dup_party_id      => l_party_id
       ,p_dup_set_id        => l_dup_set_id
       ,p_merge_flag        => 'Y'
       ,p_not_dup           => p_dup_party_tbl(i).not_dup
       ,p_score             => p_dup_party_tbl(i).score
       ,p_merge_seq_id      => p_dup_party_tbl(i).merge_seq_id
       ,p_merge_batch_id    => p_dup_party_tbl(i).merge_batch_id
       ,p_merge_batch_name  => p_dup_party_tbl(i).merge_batch_name
       ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
       ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
       ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
       ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
       ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 736

   select 'X'
   from HZ_DUP_BATCH
   where dup_batch_id = p_dup_set_rec.dup_batch_id;

Line: 773

   HZ_DUP_SETS_PKG.Insert_Row(
      px_dup_set_id       => l_dup_set_id
     ,p_dup_batch_id      => p_dup_set_rec.dup_batch_id
     ,p_winner_party_id   => p_dup_set_rec.winner_party_id
     ,p_status            => 'SYSBATCH'
     ,p_assigned_to_user_id   => p_dup_set_rec.assigned_to_user_id
     ,p_merge_type        => p_dup_set_rec.merge_type
     ,p_object_version_number => 1
     ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
     ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
     ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 792

     HZ_DUP_SET_PARTIES_PKG.Insert_Row(
        p_dup_party_id      => l_party_id
       ,p_dup_set_id        => l_dup_set_id
       ,p_merge_flag        => 'Y'
       ,p_not_dup           => p_dup_party_tbl(i).not_dup
       ,p_score             => p_dup_party_tbl(i).score
       ,p_merge_seq_id      => p_dup_party_tbl(i).merge_seq_id
       ,p_merge_batch_id    => p_dup_party_tbl(i).merge_batch_id
       ,p_merge_batch_name  => p_dup_party_tbl(i).merge_batch_name
       ,p_created_by        => HZ_UTILITY_V2PUB.CREATED_BY
       ,p_creation_date     => HZ_UTILITY_V2PUB.CREATION_DATE
       ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
       ,p_last_update_date  => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
       ,p_last_updated_by   => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   );

Line: 842

PROCEDURE update_winner_party (
   p_dup_set_id                IN      NUMBER
  ,p_winner_party_id           IN      NUMBER
  ,px_set_obj_version_number   IN OUT NOCOPY  NUMBER
  ,x_return_status             OUT NOCOPY     VARCHAR2
  ,x_msg_count                 OUT NOCOPY     NUMBER
  ,x_msg_data                  OUT NOCOPY     VARCHAR2
) IS

   l_temp                    VARCHAR2(1);

Line: 856

   select 'X'
   from HZ_DUP_SET_PARTIES
   where dup_set_id = p_dup_set_id
   and dup_party_id = p_winner_party_id
   and nvl(merge_flag,'Y') = 'Y';

Line: 863

   select winner_party_id, object_version_number
   from HZ_DUP_SETS
   where dup_set_id = p_dup_set_id;

Line: 869

   savepoint update_winner_party_pvt;

Line: 914

   UPDATE HZ_DUP_SET_PARTIES
   SET merge_flag = 'Y'
     , last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   WHERE dup_party_id = l_old_winner_party_id
   AND dup_set_id = p_dup_set_id;

Line: 923

   UPDATE HZ_DUP_SET_PARTIES
   SET last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
   WHERE dup_party_id = p_winner_party_id
   AND dup_set_id = p_dup_set_id;

Line: 932

     DELETE HZ_MERGE_PARTY_DETAILS
     WHERE batch_party_id
       in (select batch_party_id
           from HZ_MERGE_PARTIES mp
           where mp.batch_id = p_dup_set_id);

Line: 937

     DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;

Line: 938

     DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;

Line: 939

     DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;

Line: 951

   UPDATE HZ_DUP_SETS
   SET winner_party_id = p_winner_party_id
     , last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
     , object_version_number = px_set_obj_version_number
   WHERE dup_set_id = p_dup_set_id;

Line: 962

     ROLLBACK TO update_winner_party_pvt;

Line: 970

     ROLLBACK TO update_winner_party_pvt;

Line: 978

     ROLLBACK TO update_winner_party_pvt;

Line: 988

END update_winner_party;

Line: 990

PROCEDURE delete_dup_party (
   p_dup_set_id                IN      NUMBER
  ,p_dup_party_id              IN      NUMBER
  ,p_new_winner_party_id       IN      NUMBER
  ,px_set_obj_version_number   IN OUT NOCOPY  NUMBER
  ,x_return_status             OUT NOCOPY     VARCHAR2
  ,x_msg_count                 OUT NOCOPY     NUMBER
  ,x_msg_data                  OUT NOCOPY     VARCHAR2
) IS

  CURSOR get_dup_sets_info IS
  select winner_party_id, object_version_number
  from HZ_DUP_SETS
  where dup_set_id = p_dup_set_id;

Line: 1006

  select 'X'
  from HZ_DUP_SETS
  where winner_party_id = x_party_id
  and dup_set_id = p_dup_set_id;

Line: 1012

  select 'X'
  from HZ_DUP_SET_PARTIES
  where dup_set_id = p_dup_set_id
  and dup_party_id = x_party_id;

Line: 1023

   savepoint delete_dup_party_pvt;

Line: 1065

     UPDATE HZ_DUP_SETS
     set object_version_number = px_set_obj_version_number
       , last_update_date = hz_utility_v2pub.last_update_date
       , last_updated_by = hz_utility_v2pub.last_updated_by
       , last_update_login = hz_utility_v2pub.last_update_login
     where dup_set_id = p_dup_set_id;

Line: 1073

     UPDATE HZ_DUP_SET_PARTIES
     SET merge_flag = 'N'
       , last_update_date = hz_utility_v2pub.last_update_date
       , last_updated_by = hz_utility_v2pub.last_updated_by
       , last_update_login = hz_utility_v2pub.last_update_login
     WHERE dup_set_id = p_dup_set_id
     AND dup_party_id = p_dup_party_id;

Line: 1107

     UPDATE HZ_DUP_SETS
     set winner_party_id = p_new_winner_party_id
       , object_version_number = px_set_obj_version_number
       , last_update_date = hz_utility_v2pub.last_update_date
       , last_updated_by = hz_utility_v2pub.last_updated_by
       , last_update_login = hz_utility_v2pub.last_update_login
     where dup_set_id = p_dup_set_id;

Line: 1115

     UPDATE HZ_DUP_SET_PARTIES
     SET merge_flag = 'N'
       , last_update_date = hz_utility_v2pub.last_update_date
       , last_updated_by = hz_utility_v2pub.last_updated_by
       , last_update_login = hz_utility_v2pub.last_update_login
     WHERE dup_set_id = p_dup_set_id
     AND dup_party_id = p_dup_party_id;

Line: 1127

     DELETE HZ_MERGE_PARTY_DETAILS
     WHERE batch_party_id
       in (select batch_party_id
           from HZ_MERGE_PARTIES mp
           where mp.batch_id = p_dup_set_id);

Line: 1132

     DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;

Line: 1133

     DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;

Line: 1134

     DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;

Line: 1143

     ROLLBACK TO delete_dup_party_pvt;

Line: 1151

     ROLLBACK TO delete_dup_party_pvt;

Line: 1159

     ROLLBACK TO delete_dup_party_pvt;

Line: 1169

END delete_dup_party;

Line: 1191

  select winner_party_id, object_version_number
  from HZ_DUP_SETS
  where dup_set_id = p_dup_set_id;

Line: 1196

  select count(1)
  from HZ_DUP_SET_PARTIES
  where dup_set_id = p_dup_set_id;

Line: 1201

  select 'X'
  from HZ_DUP_SETS
  where winner_party_id =
  ( select dup_party_id
    from HZ_DUP_SET_PARTIES
    where dup_set_id = p_dup_set_id
    and nvl(merge_flag,'Y') <> 'N' )
  and dup_set_id = p_dup_set_id;

Line: 1258

     UPDATE HZ_DUP_SETS
     set merge_type = 'SAME_PARTY_MERGE'
       , object_version_number = px_set_obj_version_number
       , last_update_date = hz_utility_v2pub.last_update_date
       , last_updated_by = hz_utility_v2pub.last_updated_by
       , last_update_login = hz_utility_v2pub.last_update_login
     where dup_set_id = p_dup_set_id;

Line: 1266

     DELETE HZ_DUP_SET_PARTIES
     where dup_party_id not in
     ( select winner_party_id
       from HZ_DUP_SETS
       where dup_set_id = p_dup_set_id )
     and dup_set_id = p_dup_set_id;

Line: 1318

   select winner_party_id, object_version_number
   from HZ_DUP_SETS
   where dup_set_id = p_dup_set_id;

Line: 1359

     DELETE HZ_MERGE_PARTY_DETAILS
     WHERE batch_party_id
       in (select batch_party_id
           from HZ_MERGE_PARTIES mp
           where mp.batch_id = p_dup_set_id);

Line: 1364

     DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;

Line: 1365

     DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;

Line: 1366

     DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;

Line: 1372

   UPDATE HZ_DUP_SETS
   set status = 'REJECTED'
     , object_version_number = px_set_obj_version_number
     , last_update_date = hz_utility_v2pub.last_update_date
     , last_updated_by = hz_utility_v2pub.last_updated_by
     , last_update_login = hz_utility_v2pub.last_update_login
   where dup_set_id = p_dup_set_id;

Line: 1414

	select u.USER_ID
	from
		FND_USER u,
		wf_local_user_roles wur,
		FND_RESPONSIBILITY r,
		FND_MENU_ENTRIES m,
		FND_MENU_ENTRIES mp,
		FND_MENUS_VL mv
	where wur.user_name = u.user_name
		and wur.ROLE_ORIG_SYSTEM_ID  = r.responsibility_id
		and wur.role_orig_system = 'FND_RESP'
		and not wur.role_name like 'FND_RESP|%|ANY'
 		and wur.partition_id = 2
 		and ( ( ( wur.start_date is NULL )
    		or ( trunc ( sysdate ) >= trunc ( wur.start_date ) ) )
 		and ( ( wur.expiration_date is NULL )
    		or ( trunc ( sysdate ) < trunc ( wur.expiration_date ) ) )
 		and ( ( wur.user_start_date is NULL )
    		or ( trunc ( sysdate ) >= trunc ( wur.user_start_date ) ) )
 		and ( ( wur.user_end_date is NULL )
    		or ( trunc ( sysdate ) < trunc ( wur.user_end_date ) ) )
 		and ( ( wur.role_start_date is NULL )
    		or ( trunc ( sysdate ) >= trunc ( wur.role_start_date ) ) )
 		and ( ( wur.role_end_date is NULL )
    		or ( trunc ( sysdate ) < trunc ( wur.role_end_date ) ) ) )
		and r.menu_id = mp.menu_id
		and mp.sub_menu_id = m.menu_id
		and m.sub_menu_id = mv.menu_id
		and mv.menu_name = 'IMC_NG_DATA_QUALITY'
		and u.user_id = HZ_UTILITY_V2PUB.CREATED_BY
		and not exists (
		select 'X'
		from FND_RESP_FUNCTIONS rf, FND_MENUS m
		where rf.action_id = m.menu_id
		and r.responsibility_id = rf.responsibility_id
		and m.menu_name = 'IMC_NG_DATA_QUALITY'
		and rf.rule_type = 'M')
		and rownum = 1;

Line: 1463

		UPDATE HZ_DUP_SETS
   		SET assigned_to_user_id = l_user_id
     		, last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
     		, last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
     		, last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
     		, object_version_number = object_version_number+1
   		WHERE dup_set_id = p_dup_set_id;

Line: 1503

  SELECT status , request_id
  INTO  l_dup_status ,l_last_request_id
  FROM hz_dup_sets
  WHERE dup_set_id = p_dup_set_id;

Line: 1533

           UPDATE HZ_DUP_SETS
           SET status = 'PREPROCESS',
             request_id = l_request_id,
             LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
             LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
             LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
           WHERE  dup_set_id = p_dup_set_id;

Line: 1559

         UPDATE HZ_DUP_SETS
         SET status = 'PREPROCESS',
             request_id = l_request_id,
             LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
             LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
             LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
         WHERE  dup_set_id = p_dup_set_id;

Line: 1628

   select 'X'
   from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp
   where mb.batch_id = mp.batch_id
   and mb.batch_id = p_dup_set_id;

Line: 1634

   select nvl(object_version_number,-1)
   from HZ_DUP_SETS
   where dup_set_id = p_dup_set_id;

Line: 1641

   UPDATE HZ_DUP_SETS
   SET REQUEST_ID = hz_utility_v2pub.request_id
   WHERE dup_set_id = p_dup_set_id;

Line: 1742

       UPDATE HZ_DUP_SETS
       set status = 'REQUESTED'
         , last_update_date = hz_utility_v2pub.last_update_date
         , last_updated_by = hz_utility_v2pub.last_updated_by
         , last_update_login = hz_utility_v2pub.last_update_login
       where dup_set_id = l_dup_set_id;

Line: 1785

     UPDATE HZ_DUP_SETS
     SET STATUS = 'ERROR'
     WHERE DUP_SET_ID = p_dup_set_id;

Line: 1796

     UPDATE HZ_DUP_SETS
     SET STATUS = 'ERROR'
     WHERE DUP_SET_ID = p_dup_set_id;

Line: 1807

     UPDATE HZ_DUP_SETS
     SET STATUS = 'ERROR'
     WHERE DUP_SET_ID = p_dup_set_id;

Line: 1876

/*   OPEN c_cursor for 'select count(*) '||
		    ' from  HZ_PARTIES pa '||
		    ' where pa.certification_level = 100  '||
		    ' and   pa.party_id IN ('||p_party_ids||')';

Line: 1889

  open c_cursor FOR ' select distinct parent_id,child_id,nvl(top_parent_flag,''N'') top_parent_flag,level_number,hierarchy_type,'||
		    '                p1.party_number parent_reg_id,p2.party_number child_reg_id '||
		    ' from hz_hierarchy_nodes h,hz_parties p1,hz_parties p2 '||
		    ' where EXISTS (select 1 from hz_hierarchy_nodes h2 '||
		    '               where h2.parent_id = h.parent_id '||
		    '               and   h2.hierarchy_type = h.hierarchy_type '||
		    '	            and   nvl(h2.top_parent_flag,''N'') = ''Y'' '||
		    '	            and   h2.level_number = 0 '||
		    '              )'||
		    ' and   h.parent_id = p1.party_id'||
		    ' and   h.child_id  = p2.party_id'||
		    ' and   (nvl(top_parent_flag,''N'')=''N'' and level_number <> 0) '||
		    ' and    h.child_id IN ('|| p_party_ids ||')'||
		    ' and  sysdate between h.effective_start_date and h.effective_end_date'||
		    ' order by hierarchy_type,parent_id,nvl(top_parent_flag,''N'') desc,level_number';

Line: 1967

FUNCTION get_update_flag(x_dup_set_id NUMBER)
	RETURN VARCHAR2 IS
update_count NUMBER;

Line: 1970

CURSOR update_dupset(p_dup_set_id NUMBER) IS
select count(*) from hz_dup_set_parties where dup_set_id=p_dup_set_id and remove_flag is not null;

Line: 1973

 open update_dupset(x_dup_Set_id);

Line: 1974

 fetch update_dupset into update_count;

Line: 1975

 close update_dupset;

Line: 1977

  	If ( update_count>0) THEN
    	RETURN 'Y';

Line: 1985

			    x_dss_update_flag out nocopy varchar2,
			    x_return_status   OUT NOCOPY VARCHAR2,
  			    x_msg_count       OUT NOCOPY NUMBER,
  			    x_msg_data        OUT NOCOPY VARCHAR2 ) is

	cursor get_merge_parties_csr is
		select party_id
		from hz_merge_parties mp, hz_parties party
		where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
		and mp.batch_id = p_merge_batch_id;

Line: 2007

	x_dss_update_flag := 'Y';

Line: 2018

                  		p_operation_code     => 'UPDATE',
                  		p_db_object_name     => 'HZ_PARTIES',
                  		p_instance_pk1_value => l_party_id,
                  		p_user_name          => fnd_global.user_name,
                  		x_return_status      => dss_return_status,
                  		x_msg_count          => dss_msg_count,
                  		x_msg_data           => dss_msg_data);

Line: 2027

				x_dss_update_flag := 'N';

Line: 2042

				x_dss_update_flag := 'N';

Line: 2077

l_dss_update_flag varchar2(1);

Line: 2086

			    x_dss_update_flag => l_dss_update_flag,
			    x_return_status   => l_return_status,
  			    x_msg_count       => l_msg_count,
  			    x_msg_data        => l_msg_data);

Line: 2090

		if l_dss_update_flag = 'N'
		then return 'Y';

Line: 2108

		select count(*)
		from hz_dup_sets
		where status in ('REJECTED', 'COMPLETED','SUBMITTED')
		and dup_set_id = p_dup_set_id;

Line: 2132

     DELETE HZ_MERGE_PARTY_DETAILS
     WHERE batch_party_id
       in (select batch_party_id
           from HZ_MERGE_PARTIES mp
           where mp.batch_id = p_dup_set_id);

Line: 2137

     DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;

Line: 2138

     DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;

Line: 2139

     DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;

Line: 2184

		select count(*)
		from hz_dup_sets
		where status = 'SUBMITTED'
		and rownum = 1; /* as long as we have one submitted status */

Line: 2197

	  update hz_dup_sets
	  set status = 'ERROR'
	  where dup_set_id in
	  (select dup_set_id
		from HZ_MERGE_BATCH mb,
		HZ_DUP_SETS ds,
		Fnd_Concurrent_Requests r,
		FND_CONCURRENT_PROGRAMS cp
	where
 	mb.batch_id = ds.dup_set_id
 	and R.Program_Application_ID = 222
	and r.request_id = mb.request_id
	and cp.application_id = R.Program_Application_ID
	and cp.concurrent_program_id = r.concurrent_program_id
	and cp.concurrent_program_name = 'ARHPMERGE'
	and r.phase_code = 'C'
	and  ds.status ='SUBMITTED');

Line: 2216

	/* handle the case that requests have been deleted from the Fnd_Concurrent_Requests*/

	update hz_dup_sets
	  set status = 'ERROR'
	  where dup_set_id in
		(select dup_set_id
		from HZ_MERGE_BATCH mb,
		HZ_DUP_SETS ds,
		Fnd_Concurrent_Requests r
		where
		 mb.batch_id = ds.dup_set_id
		and r.request_id(+) = mb.request_id
		and  ds.status ='SUBMITTED'
		and r.request_id is null);

Line: 2242

		select match_score, nvl(auto_merge_score, 101)
	        from hz_match_rules_vl
	        where match_rule_id = p_match_rule_id;

Line: 2262

		SELECT party_id, score, party_name
   		FROM (SELECT mpg.party_id party_id, mpg.score, p.party_name,
    			RANK() OVER (ORDER BY score desc) rank
    	  		FROM hz_matched_parties_gt mpg, hz_parties p
          		WHERE mpg.party_id = p.party_id
            		AND mpg.search_context_id = p_search_ctx_id
			AND mpg.party_id <> p_new_party_id -- newly created id
    	    		ORDER BY p.last_update_date desc)
		WHERE rank = 1 and rownum = 1;

Line: 2286

		select to_party_id, mb.batch_id
		from hz_merge_batch mb, hz_merge_parties mp
		where mb.batch_id = mp.batch_id
			and mp.from_party_id = px_party_id
			and mp.to_party_id <> px_party_id
			and mb.batch_status <> 'COMPLETED' and rownum = 1;