APPS.HZ_MERGE_ENTITY_ATTRI_PVT SQL Statements

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

Line: 25

		SELECT PP.PARTY_ID
		FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
		WHERE PP.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 sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
		AND DSP.DUP_SET_ID= p_merge_batch_id
		AND PP.CREATED_BY_MODULE = 'CTB_PERSON_REGISTRY_SERVICES'
		AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
		AND ROWNUM = 1;

Line: 46

		SELECT PP.PARTY_ID
		FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
		WHERE PP.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 sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
		AND DSP.DUP_SET_ID= p_merge_batch_id
		AND PP.INTERNAL_FLAG = 'Y'
		AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
		AND ROWNUM = 1;

Line: 57

		SELECT OP.PARTY_ID
		FROM HZ_ORGANIZATION_PROFILES OP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
		WHERE OP.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 sysdate between op.effective_start_date and nvl(op.effective_end_date,sysdate)
		AND DSP.DUP_SET_ID= p_merge_batch_id
		AND OP.INTERNAL_FLAG = 'Y'
		AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
		AND ROWNUM = 1;

Line: 94

		select attribute_name, attribute_value, attribute_type, attribute_party_id
		from HZ_MERGE_ENTITY_ATTRIBUTES
		where merge_to_party_id = p_merge_to_party_id
		and entity_name = 'HZ_ORGANIZATION_PROFILES'
		and merge_batch_id = p_merge_batch_id;

Line: 165

	/*	ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
			L_ORGANIZATION_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;

Line: 171

		ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
			L_ORGANIZATION_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;

Line: 173

		ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
			L_ORGANIZATION_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;

Line: 177

		ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
			L_ORGANIZATION_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE;

Line: 434

		--ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
		--	L_ORGANIZATION_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;

Line: 505

		select attribute_name, attribute_value, attribute_type
		from HZ_MERGE_ENTITY_ATTRIBUTES
		where merge_to_party_id = p_merge_to_party_id
		and entity_name = 'HZ_PERSON_PROFILES'
		and merge_batch_id = p_merge_batch_id;

Line: 539

		     IF (NVL(FND_PROFILE.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'N') ='Y') THEN  --Update HR sensitive columns

			IF L_ATTRIBUTE_NAME = 'GENDER' THEN
			   L_PERSON_REC.GENDER := L_ATTRIBUTE_VALUE;

Line: 619

		     --update other attributes
		     IF L_ATTRIBUTE_NAME = 'MARITAL_STATUS_EFFECTIVE_DATE' THEN
			L_PERSON_REC.MARITAL_STATUS_EFFECTIVE_DATE := l_attribute_date_value; --L_ATTRIBUTE_VALUE; -- Bug No:3729832

Line: 646

		     ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
			L_PERSON_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE; */

Line: 769

	      /*     ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
			L_PERSON_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;

Line: 771

		     ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
			L_PERSON_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;

Line: 777

		     ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
			L_PERSON_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;

Line: 779

		     ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
			L_PERSON_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;

Line: 831

	select object_version_number,orig_system_reference
	from hz_parties
	where party_id = p_merge_to_party_id;

Line: 836

		select attribute_party_id
		from HZ_MERGE_ENTITY_ATTRIBUTES
		where merge_to_party_id = p_merge_to_party_id
		and entity_name = 'HZ_ORGANIZATION_PROFILES'
		and merge_batch_id = p_merge_batch_id
		and attribute_name = 'BUSINESS_REPORT';

Line: 844

		select nvl(db.automerge_flag,'N')
		from hz_dup_batch db, hz_dup_sets ds
		where db.dup_batch_id = ds.dup_batch_id
		and ds.dup_set_id = p_merge_batch_id
		and rownum=1;

Line: 868

		Hz_party_v2pub.update_organization (
			p_init_msg_list         => FND_API.G_FALSE,
			p_organization_rec      => l_org_rec,
			p_party_object_version_number =>l_object_version_number,
			x_profile_id            => l_profile_id,
			x_return_status         => x_return_status,
			x_msg_count             => l_msg_count,
			x_msg_data              =>l_msg_data
		);

Line: 882

		UPDATE hz_organization_profiles
	        SET business_report = (select business_report
					from hz_organization_profiles
					where party_id = l_attribute_party_id
			                and sysdate between effective_start_date and nvl(effective_end_date,sysdate)
					and rownum=1),
		    last_update_date = SYSDATE,
		    last_update_login = FND_GLOBAL.LOGIN_ID,
	            last_updated_by = FND_GLOBAL.USER_ID,
	            object_version_number = nvl(object_version_number,1)+1
		where party_id = p_merge_to_party_id
                and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 904

		Hz_party_v2pub.update_person (
			p_init_msg_list               => FND_API.G_FALSE,
			p_person_rec            => l_person_rec,
			p_party_object_version_number => l_object_version_number,
			x_profile_id                  => l_profile_id,
			x_return_status               => x_return_status,
			x_msg_count                   => l_msg_count,
			x_msg_data                    =>l_msg_data
		);

Line: 914

		-- update_org/person api doesn't allow update created_by_module
                -- per chris, call update directly.

		l_party_id :=null;

Line: 921

			update hz_person_profiles
			set created_by_module = 'CTB_PERSON_REGISTRY_SERVICES',
			    last_update_date = SYSDATE,
			    last_update_login = FND_GLOBAL.LOGIN_ID,
			    last_updated_by = FND_GLOBAL.USER_ID,
			    object_version_number = nvl(object_version_number,1)+1
			where party_id = p_merge_to_party_id
			and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 935

	    	update HZ_MERGE_ENTITY_ATTRIBUTES
	    	set  attribute_value =  'Y',
	    	attribute_party_id = l_party_id,
	    	last_update_date = SYSDATE,
	    	last_update_login = FND_GLOBAL.LOGIN_ID,
	    	last_updated_by = FND_GLOBAL.USER_ID,
	    	object_version_number = nvl(object_version_number,1)+1
	    	where attribute_name = 'INTERNAL_FLAG'
	    	and merge_batch_id = p_merge_batch_id
	    	and merge_to_party_id = p_merge_to_party_id;

Line: 967

		and (p_attribute_name not in ('LAST_UPDATED_BY', 'LAST_UPDATE_LOGIN')) -- bug 4726700
	then
		l_str := 'select to_char('||p_attribute_name||','||'''YYYY/MM/DD'')'||
		 ' from '||p_entity_name||
		 ' where party_id = :party_id'||
		 ' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';

Line: 977

		l_str := 'select to_char('||p_attribute_name||')'||
		 ' from '||p_entity_name||
		 ' where party_id = :party_id'||
		 ' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';

Line: 988

procedure update_group_attribute_value(p_attribute_name in varchar2,
					p_candidate_party_id in number,
					p_merge_batch_id        IN      NUMBER,
					p_merge_to_party_id     IN      NUMBER,
					p_derived_last_update_date IN DATE,
					p_entity_name           IN VARCHAR2,
					p_proc_type IN VARCHAR2 DEFAULT 'C') is

	-- get all attributes for the passing in group leader attribute name
	cursor get_group_attributes_csr(p_attribute_name varchar2, p_lookup_type varchar2) is
		select c1.lookup_code
		from ar_lookups c, ar_lookups t, ar_lookups c1
		where c.lookup_type = t.lookup_code
		and c1.lookup_type = t.lookup_code
		and substrb(c.externally_visible_flag,6,3) = substrb(c1.externally_visible_flag,6,3)
		and c.lookup_type = c1.lookup_type
		and t.lookup_type = p_lookup_type
		and c.lookup_code = p_attribute_name
		and c1.enabled_flag = 'Y';

Line: 1027

		update hz_merge_entity_attributes
		set    attribute_value = l_value, -- l_value has already formatted
		--attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
			attribute_party_id = p_candidate_party_id,
			derived_last_update_date = p_derived_last_update_date,
			last_update_date = SYSDATE,
			last_update_login = FND_GLOBAL.LOGIN_ID,
			last_updated_by = FND_GLOBAL.USER_ID,
			object_version_number = nvl(object_version_number,1)+1
		where attribute_name = l_group_attribute_name
		and merge_batch_id = p_merge_batch_id
		and entity_name = p_entity_name
		and merge_to_party_id =p_merge_to_party_id
		and attribute_value is null;

Line: 1042

	       	update hz_merge_entity_attributes
		set    attribute_value = l_value, -- l_value has already formatted
		--attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
			attribute_party_id = p_candidate_party_id,
			derived_last_update_date = p_derived_last_update_date,
			last_update_date = SYSDATE,
			last_update_login = FND_GLOBAL.LOGIN_ID,
			last_updated_by = FND_GLOBAL.USER_ID,
			object_version_number = nvl(object_version_number,1)+1
		where attribute_name = l_group_attribute_name
		and merge_batch_id = p_merge_batch_id
		and entity_name = p_entity_name
		and merge_to_party_id =p_merge_to_party_id;

Line: 1062

Default to selected party profile attribute values which are not null.
For nullable leader attribute values, default to candidate party
profile attribute values based on profile values, also, update master party
profile to leader attribute group values.*/

procedure default_attribute_values(
        p_merge_batch_id        IN      NUMBER,
        p_merge_to_party_id     IN      NUMBER,
        p_entity_name           IN VARCHAR2) is

	-- get master party attributes where leader is null
	cursor get_leader_attribute_name_csr(p_lookup_type varchar2) is
		select attri.attribute_name
		from hz_merge_entity_attributes attri, ar_lookups c, ar_lookups t
		where attri.attribute_name = c.lookup_code
		and c.lookup_type = t.lookup_code
		and t.lookup_type = p_lookup_type
		and attribute_value is null and substrb(c.externally_visible_flag,10,1) = 'L'
		and merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and c.enabled_flag = 'Y';

Line: 1084

	-- get candidate parties with desc order for last update date
	cursor get_org_candidate_party_csr is
		select mp.from_party_id, pro.last_update_date
		from hz_organization_profiles pro, hz_merge_parties mp
		where pro.party_id = mp.from_party_id
		and mp.batch_id = p_merge_batch_id
		and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
		order by pro.last_update_date desc;

Line: 1095

		select mp.from_party_id, pro.last_update_date
		from hz_organization_profiles pro, hz_merge_parties mp
		where pro.party_id = mp.from_party_id
		and mp.batch_id = p_merge_batch_id
		and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
		order by pro.creation_date desc, pro.party_id desc;

Line: 1104

		select mp.from_party_id, pro.last_update_date
		from hz_organization_profiles pro, hz_merge_parties mp
		where pro.party_id = mp.from_party_id
		and mp.batch_id = p_merge_batch_id
		and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
		order by pro.creation_date, pro.party_id;

Line: 1111

	-- get candidate parties with desc order for last update date
        cursor get_per_candidate_party_csr is
                select mp.from_party_id, pro.last_update_date
                from hz_person_profiles pro, hz_merge_parties mp
                where pro.party_id = mp.from_party_id
                and mp.batch_id = p_merge_batch_id
                and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
                order by pro.last_update_date desc;

Line: 1122

                select mp.from_party_id, pro.last_update_date
                from hz_person_profiles pro, hz_merge_parties mp
                where pro.party_id = mp.from_party_id
                and mp.batch_id = p_merge_batch_id
                and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
                order by pro.creation_date desc, pro.party_id desc;

Line: 1131

                select mp.from_party_id, pro.last_update_date
                from hz_person_profiles pro, hz_merge_parties mp
                where pro.party_id = mp.from_party_id
                and mp.batch_id = p_merge_batch_id
                and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
                order by pro.creation_date, pro.party_id;

Line: 1141

l_last_update_date DATE;

Line: 1150

	l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');

Line: 1159

			/************* LATEST_UPDATE_DATE CASE **************/

			if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
			then
			   open get_org_candidate_party_csr;

Line: 1165

				fetch get_org_candidate_party_csr into l_party_id,l_last_update_date;

Line: 1180

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id =>p_merge_to_party_id,
					p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1190

			   end loop; -- for each candidate party desc on last update date

Line: 1192

			end if; -- latest update date case

Line: 1200

				fetch get_org_candidate_party_lc_csr into l_party_id,l_last_update_date;

Line: 1215

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id =>p_merge_to_party_id,
					p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1235

				fetch get_org_candidate_party_ec_csr into l_party_id,l_last_update_date;

Line: 1250

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id =>p_merge_to_party_id,
					p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1273

			/************* LATEST_UPDATE_DATE CASE **************/

			if (l_attri_def_next_prof = 'LATEST_UPDATE_DATE')
			then
			   open get_per_candidate_party_csr;

Line: 1279

				fetch get_per_candidate_party_csr into l_party_id,l_last_update_date;

Line: 1285

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id => p_merge_to_party_id,
	                  		p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1293

			end loop; -- for each candidate party desc on last update date

Line: 1295

		    end if; -- person lastest update date case

Line: 1303

				fetch get_per_candidate_party_lc_csr into l_party_id,l_last_update_date;

Line: 1309

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id => p_merge_to_party_id,
	                  		p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1327

				fetch get_per_candidate_party_ec_csr into l_party_id,l_last_update_date;

Line: 1333

					update_group_attribute_value(p_attribute_name=>l_attribute_name,
					p_candidate_party_id => l_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id => p_merge_to_party_id,
	                  		p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name);

Line: 1355

		select 'x'
		from hz_organization_profiles
		where party_id = p_merge_to_party_id
		and business_report is not null
		and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 1361

	-- get candidate parties with desc order for last update date
	cursor get_business_report_party_csr is
		select mp.from_party_id, pro.last_update_date
		from hz_organization_profiles pro, hz_merge_parties mp
		where pro.party_id = mp.from_party_id
		and mp.batch_id = p_merge_batch_id
		and pro.business_report is not null
		and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
		order by pro.last_update_date desc;

Line: 1372

l_last_update_date date;

Line: 1381

			fetch get_business_report_party_csr into l_from_party_id, l_last_update_date;

Line: 1384

			update hz_merge_entity_attributes
			set     attribute_party_id = l_from_party_id,
				last_update_date = SYSDATE,
				last_update_login = FND_GLOBAL.LOGIN_ID,
				last_updated_by = FND_GLOBAL.USER_ID,
				object_version_number = nvl(object_version_number,1)+1,
				derived_last_update_date = l_last_update_date
			where attribute_name = 'BUSINESS_REPORT'
			and merge_batch_id = p_merge_batch_id
			and entity_name = 'HZ_ORGANIZATION_PROFILES'
			and merge_to_party_id =p_merge_to_party_id;

Line: 1398

			return; -- only need to get the first party_id which has latest update date.

Line: 1415

	select col.column_name, col.column_type
	from fnd_tables tbl, fnd_columns col
	where tbl.table_id = col.table_id
	and tbl.table_name = l_table_name
	-- adding the following clause for performance 4956759
	and tbl.application_id = 222
	and tbl.application_id = col.application_id;

Line: 1437

	l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');

Line: 1438

	if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
	then
		l_date_clause := 'LAST_UPDATE_DATE';

Line: 1441

		l_order_by_clause := ' order by LAST_UPDATE_DATE DESC';

Line: 1464

		l_str := 'select party_id,  decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value)  from '||
		'(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
		'(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
	' from HZ_ORGANIZATION_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
	'WHERE OP.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 OP.PARTY_ID = PARTY.PARTY_ID '||
	'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
	'AND OP.'||l_col_name||' is not null '||
        'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
	||' where rank = 1 and rownum=1' ;  --added rownum=1, in case same create/update date for rank = 1

Line: 1478

          	l_str := 'select party_id,  decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value)  from '||
		'(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
		'(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
	' from HZ_PERSON_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
	'WHERE OP.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 OP.PARTY_ID = PARTY.PARTY_ID '||
	'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
	'AND OP.'||l_col_name||' is not null '||
        'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
	||' where rank = 1 and rownum=1' ;  --added rownum=1, in case same create/update date for rank = 1

Line: 1505

		insert into hz_merge_entity_attributes(
				MERGE_BATCH_ID,
				MERGE_TO_PARTY_ID,
				ATTRIBUTE_NAME,
				ATTRIBUTE_VALUE,
				ATTRIBUTE_TYPE,
				ATTRIBUTE_PARTY_ID,
				ENTITY_NAME,
				DERIVED_LAST_UPDATE_DATE,
				OBJECT_VERSION_NUMBER,
				CREATED_BY ,
				CREATION_DATE ,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				LAST_UPDATE_DATE) values
				(p_merge_batch_id,
				p_merge_to_party_id,
				l_col_name,
				l_attri_value,
				l_col_type,
	                        l_attri_party_id,
				p_entity_name,
				l_derived_date,
                                1,
				nvl(fnd_global.user_id,-1),
				sysdate,
				nvl(fnd_global.user_id,-1),
				nvl(fnd_global.login_id,-1),
				sysdate);

Line: 1557

		select col.column_name, col.column_type
		from fnd_tables tbl, fnd_columns col
		where tbl.table_id = col.table_id
		and tbl.table_name = l_table_name
		-- adding the following clause for performance 4956759
	    and tbl.application_id = 222
	    and tbl.application_id = col.application_id;

Line: 1565

	-- get party with latest update date
	cursor get_party_id_lu_csr is
		select party_id
 		from(
 			select pro.party_id party_id,
			RANK() OVER (ORDER BY pro.last_update_date desc ) rank
			from hz_parties pro, hz_dup_set_parties mp
			where pro.party_id = mp.dup_party_id
			and mp.dup_set_id = p_merge_batch_id)
	        where rank = 1 and rownum=1;

Line: 1578

		select party_id
 		from(
 			select pro.party_id party_id,
			RANK() OVER (ORDER BY pro.creation_date desc, pro.party_id desc ) rank
			from hz_parties pro, hz_dup_set_parties mp
			where pro.party_id = mp.dup_party_id
			and mp.dup_set_id = p_merge_batch_id)
	        where rank = 1;

Line: 1591

		select party_id
 		from(
 			select pro.party_id party_id,
			RANK() OVER (ORDER BY pro.creation_date, pro.party_id ) rank
			from hz_parties pro, hz_dup_set_parties mp
			where pro.party_id = mp.dup_party_id
			and mp.dup_set_id = p_merge_batch_id)
	        where rank = 1;

Line: 1607

l_selected_party_id number;

Line: 1623

	then l_selected_party_id := p_merge_to_party_id;

Line: 1624

	elsif l_prof_value = 'LATEST_UPDATE_DATE'
	then
		open get_party_id_lu_csr;

Line: 1627

		fetch get_party_id_lu_csr into l_selected_party_id;

Line: 1632

		fetch get_party_id_lc_csr into l_selected_party_id;

Line: 1638

		fetch get_party_id_ec_csr into l_selected_party_id;

Line: 1672

			insert into hz_merge_entity_attributes(
				MERGE_BATCH_ID,
				MERGE_TO_PARTY_ID,
				ATTRIBUTE_NAME,
				ATTRIBUTE_VALUE,
				ATTRIBUTE_TYPE,
				ATTRIBUTE_PARTY_ID,
				ENTITY_NAME,
				DERIVED_LAST_UPDATE_DATE,
				OBJECT_VERSION_NUMBER,
				CREATED_BY ,
				CREATION_DATE ,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				LAST_UPDATE_DATE) select
				p_merge_batch_id,
				p_merge_to_party_id,
				'BUSINESS_REPORT',
				'CLOB',
				l_col_type,
	                        p_merge_to_party_id,
				p_entity_name,
				last_update_date,
                                1,
				nvl(fnd_global.user_id,-1),
				sysdate,
				nvl(fnd_global.user_id,-1),
				nvl(fnd_global.login_id,-1),
				sysdate
	                        from HZ_ORGANIZATION_PROFILES
	                        where party_id = l_selected_party_id
				and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 1710

			-- ex. select to_char(SIC_CODE) from hz_organization_profiles to get attribute_value

			str := 'insert into hz_merge_entity_attributes( '||
			'MERGE_BATCH_ID,'||
			'MERGE_TO_PARTY_ID,'||
			'ATTRIBUTE_NAME,'||
			'ATTRIBUTE_VALUE,'||
			'ATTRIBUTE_TYPE,'||
			'ATTRIBUTE_PARTY_ID,'||
			'ENTITY_NAME ,'||
			'DERIVED_LAST_UPDATE_DATE ,'||
			'OBJECT_VERSION_NUMBER,'||
			'CREATED_BY ,'||
			'CREATION_DATE ,'||
			'LAST_UPDATED_BY,'||
			'LAST_UPDATE_LOGIN,'||
			'LAST_UPDATE_DATE) '||
			'select '||
			p_merge_batch_id ||','||
			p_merge_to_party_id ||','||
			''''||l_col_name||''''||','||
			'decode('||''''||l_col_type||''''||',''D'','||'to_char('||l_col_name||','||'''YYYY/MM/DD'')'||','||'to_char('||l_col_name||')'||')'||','||

			''''||l_col_type||''''||','||
			l_selected_party_id ||','||
			''''||p_entity_name||''''||','||
			'last_update_date'||','||
			'1,'||
			'nvl(fnd_global.user_id,-1)'||','||
			'sysdate'||','||
			'nvl(fnd_global.user_id,-1)'||','||
			'nvl(fnd_global.login_id,-1)'||','||
			 'sysdate'||
			' from '|| p_entity_name ||
			' where party_id = '||':selected_party_id'||
			' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';

Line: 1748

			EXECUTE IMMEDIATE str using l_selected_party_id;

Line: 1762

		update HZ_MERGE_ENTITY_ATTRIBUTES
		set  attribute_value =  'CTB_PERSON_REGISTRY_SERVICES',
		     attribute_party_id = l_party_id,
		     last_update_date = SYSDATE,
		     last_update_login = FND_GLOBAL.LOGIN_ID,
		     last_updated_by = FND_GLOBAL.USER_ID,
		     object_version_number = nvl(object_version_number,1)+1
		where attribute_name = 'CREATED_BY_MODULE'
		and merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id;

Line: 1778

    	update HZ_MERGE_ENTITY_ATTRIBUTES
    	set  attribute_value =  'Y',
    	attribute_party_id = l_party_id,
    	last_update_date = SYSDATE,
    	last_update_login = FND_GLOBAL.LOGIN_ID,
    	last_updated_by = FND_GLOBAL.USER_ID,
    	object_version_number = nvl(object_version_number,1)+1
    	where attribute_name = 'INTERNAL_FLAG'
    	and merge_batch_id = p_merge_batch_id
    	and merge_to_party_id = p_merge_to_party_id;

Line: 1826

		select 'X'
		from ar_lookups c,
		ar_lookups t
		where c.lookup_type = t.lookup_code
		and c.enabled_flag = 'Y'
		and t.lookup_type = p_lookup_type
		and substrb(c.externally_visible_flag,10,1) = 'L'
		and c.lookup_code = p_attribute_name;

Line: 1850

PROCEDURE update_merge_attribute (
	p_merge_batch_id          IN      NUMBER,
        p_merge_to_party_id       IN      NUMBER,
	p_attribute_name	  IN	  VARCHAR2,
	p_attribute_value	  IN      VARCHAR2,
	p_attribute_party_id	  IN      NUMBER,
	p_entity_name		  IN      VARCHAR2,
	px_object_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_obj_version_number_csr is
		select object_version_number
		from HZ_MERGE_ENTITY_ATTRIBUTES
		where merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and entity_name = p_entity_name
		and attribute_name = p_attribute_name;

Line: 1870

	cursor get_org_last_update_date_csr is
		select last_update_date
		from hz_organization_profiles
		where party_id = p_attribute_party_id
		and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 1876

	cursor get_per_last_update_date_csr is
		select last_update_date
		from hz_person_profiles
		where party_id = p_attribute_party_id
		and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 1883

l_last_update_date date;

Line: 1888

	savepoint update_merge_attributes_pvt;

Line: 1901

		/* if pass in attribute name is not leading attri, no update action */
		return;

Line: 1924

		open get_org_last_update_date_csr;

Line: 1925

		fetch get_org_last_update_date_csr into l_last_update_date;

Line: 1926

		close get_org_last_update_date_csr;

Line: 1929

		open get_per_last_update_date_csr;

Line: 1930

		fetch get_per_last_update_date_csr into l_last_update_date;

Line: 1931

		close get_per_last_update_date_csr;

Line: 1935

	   update HZ_MERGE_ENTITY_ATTRIBUTES
	   set	attribute_value = p_attribute_value,
		attribute_party_id = p_attribute_party_id,
		derived_last_update_date = decode(p_attribute_party_id,null,SYSDATE,l_last_update_date),
		last_update_date = SYSDATE,
		last_update_login = FND_GLOBAL.LOGIN_ID,
		last_updated_by = FND_GLOBAL.USER_ID,
		object_version_number = px_object_version_number
	where attribute_name = p_attribute_name
		and merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and entity_name = p_entity_name;

Line: 1950

		update_group_attribute_value(p_attribute_name=>p_attribute_name,
					p_candidate_party_id => p_attribute_party_id,
					p_merge_batch_id    => p_merge_batch_id,
					p_merge_to_party_id =>p_merge_to_party_id,
					p_derived_last_update_date => l_last_update_date,
					p_entity_name       => p_entity_name,
					p_proc_type => 'U');

Line: 1957

		 update HZ_MERGE_ENTITY_ATTRIBUTES
		 set object_version_number = px_object_version_number
		 where attribute_name = p_attribute_name
		 and merge_batch_id = p_merge_batch_id
		 and merge_to_party_id = p_merge_to_party_id
		 and entity_name = p_entity_name;

Line: 1966

       UPDATE HZ_DUP_SETS
       SET STATUS = 'MAPPING',
           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,
	   object_version_number = nvl(object_version_number,1)+1
       WHERE DUP_SET_ID = p_merge_batch_id;

Line: 1977

     ROLLBACK TO update_merge_attributes_pvt;

Line: 1985

     ROLLBACK TO update_merge_attributes_pvt ;

Line: 1993

     ROLLBACK TO update_merge_attributes_pvt;

Line: 2003

end update_merge_attribute;

Line: 2084

	 cursor get_org_last_update_date_csr is
                select last_update_date
                from hz_organization_profiles
                where party_id = p_attribute_party_id
                and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 2090

	 cursor get_per_last_update_date_csr is
                select last_update_date
                from hz_person_profiles
                where party_id = p_attribute_party_id
                and sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 2095

l_last_update_date date;

Line: 2099

                open get_org_last_update_date_csr;

Line: 2100

                fetch get_org_last_update_date_csr into l_last_update_date;

Line: 2101

                close get_org_last_update_date_csr;

Line: 2104

                open get_per_last_update_date_csr;

Line: 2105

                fetch get_per_last_update_date_csr into l_last_update_date;

Line: 2106

                close get_per_last_update_date_csr;

Line: 2108

	return l_last_update_date;

Line: 2114

		  select 'x'
                from hz_merge_entity_attributes
                where merge_batch_id = p_merge_batch_id
		and attribute_name <> 'BUSINESS_REPORT'
                and attribute_party_id = l_attribute_party_id and rownum = 1
                and  derived_last_update_date <> (select last_update_date
                                                 from hz_organization_profiles
                                                 where party_Id =l_attribute_party_id
                                                and sysdate between effective_start_date and nvl(effective_end_date,sysdate));

Line: 2125

		select 'x'
		from hz_merge_entity_attributes
		where merge_batch_id = p_merge_batch_id
		and attribute_party_id = l_attribute_party_id and rownum = 1
		and  derived_last_update_date <> (select last_update_date
						 from hz_person_profiles
						 where party_Id = l_attribute_party_id
						and sysdate between effective_start_date and nvl(effective_end_date,sysdate));

Line: 2134

		select distinct attribute_party_id
		from hz_merge_entity_attributes
		where merge_batch_id = p_merge_batch_id;

Line: 2183

		select distinct attribute_party_id, derived_last_update_date
		from hz_merge_entity_attributes
		where merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and attribute_name <> 'BUSINESS_REPORT';

Line: 2190

		select attribute_name, entity_name
		from hz_merge_entity_attributes attri
		where merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and attribute_party_id = l_attribute_party_id
		and attribute_name <> 'BUSINESS_REPORT';

Line: 2198

		select count(distinct attribute_party_id)
		from hz_merge_entity_attributes
		where merge_batch_id = p_merge_batch_id
		and merge_to_party_id = p_merge_to_party_id
		and attribute_party_id <> p_merge_to_party_id;

Line: 2227

			delete from hz_merge_entity_attributes
			where merge_batch_id = p_merge_batch_id;

Line: 2237

		else      -- data has been updated by the user

			open get_attri_party_id_csr;

Line: 2255

					update hz_merge_entity_attributes
					set attribute_value = l_value, -- value formatted already
						derived_last_update_date = l_party_last_upd_date,
						last_update_date = SYSDATE,
						last_update_login = FND_GLOBAL.LOGIN_ID,
						last_updated_by = FND_GLOBAL.USER_ID,
						object_version_number = nvl(object_version_number,1)+1
					where attribute_name = l_attribute_name
					and merge_batch_id = p_merge_batch_id
					and entity_name = p_entity_name
					and merge_to_party_id =p_merge_to_party_id
					and attribute_party_id = l_attri_party_id;