APPS.HZ_EXTRACT_MERGE_EVENT_PKG SQL Statements

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

Line: 13

        SELECT contact_point_type
	FROM hz_contact_points
	WHERE contact_point_id = p_table_id;

Line: 77

    SELECT HZ_ACCT_MERGE_OBJ(
           p_customer_merge_header_id,
           mh.request_id,
           mh.created_by,
	   mh.creation_date,
	   mh.last_update_login,
	   mh.last_update_date,
	   mh.last_updated_by,
	   HZ_PARTY_ORIG_SYS_REF_OBJ(
           tp.party_id,
	   tp.party_number,
	   tp.party_name,
	   tp.party_type,
           HZ_ORIG_SYS_REF_OBJ_TBL()),
           HZ_PARTY_ORIG_SYS_REF_OBJ(
           fp.party_id,
           fp.party_number,
           fp.party_name,
           fp.party_type,
           HZ_ORIG_SYS_REF_OBJ_TBL()),
           HZ_ACCT_ORIG_SYS_REF_OBJ(
           ta.cust_account_id,
	   ta.account_name,
	   ta.account_number,
	   HZ_ORIG_SYS_REF_OBJ_TBL()),
	   CAST(MULTISET (
		SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
                     fa.cust_account_id,
                     fa.account_name,
		     fa.account_number,
                     HZ_ORIG_SYS_REF_OBJ_TBL())
		FROM hz_cust_accounts fa
                WHERE fa.cust_account_id = mh.duplicate_id
		) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL)
   )
   FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts fa, hz_parties fp
   WHERE mh.customer_merge_header_id = p_customer_merge_header_id
   AND   ta.cust_account_id = mh.customer_id
   AND   tp.party_id = ta.party_id
   AND   fa.cust_account_id = mh.duplicate_id
   AND   fp.party_id = fa.party_id
   AND   rownum = 1;

Line: 320

	SELECT  HZ_PARTY_MERGE_OBJ(
		mb.batch_id,
		mb.batch_name,
		mp.merge_type,
		db.automerge_flag,
		mb.created_by,
		mb.creation_date,
		mb.last_update_login,
		mb.last_update_date,
		mb.last_updated_by,
		HZ_PARTY_ORIG_SYS_REF_OBJ(
			tp.party_id,
			tp.party_number,
			tp.party_name,
			tp.party_type,
			HZ_ORIG_SYS_REF_OBJ_TBL()),
		CAST(MULTISET(
			SELECT HZ_PARTY_ORIG_SYS_REF_OBJ(
					fp.party_id,
					fp.party_number,
					fp.party_name,
					fp.party_type,
					HZ_ORIG_SYS_REF_OBJ_TBL())
			FROM hz_parties fp, hz_merge_parties mp1
			WHERE mp1.batch_id = p_batch_id
			AND   mp1.to_party_id = p_merge_to_party_id
			AND   fp.party_id = mp1.from_party_id
			) AS HZ_PARTY_ORIG_SYS_REF_OBJ_TBL),
	       HZ_PARTY_MERGE_DETAIL_OBJ_TBL()             --5093366
	)
	FROM hz_merge_batch mb,
             (SELECT DISTINCT merge_type from hz_merge_parties where batch_id = p_batch_id and to_party_id = p_merge_to_party_id) mp,
             hz_dup_batch db,
	     hz_dup_sets dset,
	     hz_parties tp
	WHERE mb.batch_id = p_batch_id
	AND   tp.party_id = p_merge_to_party_id
	AND   mb.batch_id = dset.dup_set_id (+)
        AND   db.dup_batch_id (+)= dset.dup_batch_id
        ORDER BY mp.merge_type;

Line: 362

		       SELECT HZ_PARTY_MERGE_DETAIL_OBJ(
				get_object_type(md.entity_name, mph.from_entity_id),
		                mph.operation_type,
				mph.from_entity_id,
				CAST(MULTISET(
					SELECT HZ_ORIG_SYS_REF_OBJ(
						NULL,
						ORIG_SYSTEM_REF_ID,
						ORIG_SYSTEM,
						ORIG_SYSTEM_REFERENCE,
						HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
						OWNER_TABLE_ID,
						STATUS,
						REASON_CODE,
			          		OLD_ORIG_SYSTEM_REFERENCE,
			  			START_DATE_ACTIVE,
						END_DATE_ACTIVE,
						PROGRAM_UPDATE_DATE,
						CREATED_BY_MODULE,
						HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
				 		CREATION_DATE,
				 		LAST_UPDATE_DATE,
				 		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
						ATTRIBUTE_CATEGORY,
						ATTRIBUTE1,
						ATTRIBUTE2,
						ATTRIBUTE3,
						ATTRIBUTE4,
						ATTRIBUTE5,
						ATTRIBUTE6,
						ATTRIBUTE7,
						ATTRIBUTE8,
						ATTRIBUTE9,
						ATTRIBUTE10,
						ATTRIBUTE11,
						ATTRIBUTE12,
						ATTRIBUTE13,
						ATTRIBUTE14,
						ATTRIBUTE15,
						ATTRIBUTE16,
						ATTRIBUTE17,
						ATTRIBUTE18,
						ATTRIBUTE19,
						ATTRIBUTE20
					)
					FROM HZ_ORIG_SYS_REFERENCES
					WHERE OWNER_TABLE_ID = mph.from_entity_id
					AND OWNER_TABLE_NAME = md.entity_name

				     )AS HZ_ORIG_SYS_REF_OBJ_TBL),

				mph.to_entity_id,

			        CAST(MULTISET(
					SELECT HZ_ORIG_SYS_REF_OBJ(
						NULL,
						ORIG_SYSTEM_REF_ID,
						ORIG_SYSTEM,
						ORIG_SYSTEM_REFERENCE,
						HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
						OWNER_TABLE_ID,
						STATUS,
						REASON_CODE,
			          		OLD_ORIG_SYSTEM_REFERENCE,
			  			START_DATE_ACTIVE,
						END_DATE_ACTIVE,
						PROGRAM_UPDATE_DATE,
						CREATED_BY_MODULE,
						HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
				 		CREATION_DATE,
				 		LAST_UPDATE_DATE,
				 		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
						ATTRIBUTE_CATEGORY,
						ATTRIBUTE1,
						ATTRIBUTE2,
						ATTRIBUTE3,
						ATTRIBUTE4,
						ATTRIBUTE5,
						ATTRIBUTE6,
						ATTRIBUTE7,
						ATTRIBUTE8,
						ATTRIBUTE9,
						ATTRIBUTE10,
						ATTRIBUTE11,
						ATTRIBUTE12,
						ATTRIBUTE13,
						ATTRIBUTE14,
						ATTRIBUTE15,
						ATTRIBUTE16,
						ATTRIBUTE17,
						ATTRIBUTE18,
						ATTRIBUTE19,
						ATTRIBUTE20
					)
					FROM HZ_ORIG_SYS_REFERENCES
					WHERE OWNER_TABLE_ID = decode(mph.operation_type,'Copy',mph.from_entity_id,mph.to_entity_id)
					AND OWNER_TABLE_NAME = md.entity_name

				     )AS HZ_ORIG_SYS_REF_OBJ_TBL),

				mph.from_parent_entity_id,

				CAST(MULTISET(
					SELECT HZ_ORIG_SYS_REF_OBJ(
						NULL,
						ORIG_SYSTEM_REF_ID,
						ORIG_SYSTEM,
						ORIG_SYSTEM_REFERENCE,
						HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
						OWNER_TABLE_ID,
						STATUS,
						REASON_CODE,
			          		OLD_ORIG_SYSTEM_REFERENCE,
			  			START_DATE_ACTIVE,
						END_DATE_ACTIVE,
						PROGRAM_UPDATE_DATE,
						CREATED_BY_MODULE,
						HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
				 		CREATION_DATE,
				 		LAST_UPDATE_DATE,
				 		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
						ATTRIBUTE_CATEGORY,
						ATTRIBUTE1,
						ATTRIBUTE2,
						ATTRIBUTE3,
						ATTRIBUTE4,
						ATTRIBUTE5,
						ATTRIBUTE6,
						ATTRIBUTE7,
						ATTRIBUTE8,
						ATTRIBUTE9,
						ATTRIBUTE10,
						ATTRIBUTE11,
						ATTRIBUTE12,
						ATTRIBUTE13,
						ATTRIBUTE14,
						ATTRIBUTE15,
						ATTRIBUTE16,
						ATTRIBUTE17,
						ATTRIBUTE18,
						ATTRIBUTE19,
						ATTRIBUTE20
					)
					FROM HZ_ORIG_SYS_REFERENCES
					WHERE OWNER_TABLE_ID = 	mph.from_parent_entity_id
					AND OWNER_TABLE_NAME =  md.parent_entity_name

				     )AS HZ_ORIG_SYS_REF_OBJ_TBL),

				mph.to_parent_entity_id,


				CAST(MULTISET(
					SELECT HZ_ORIG_SYS_REF_OBJ(
						NULL,
						ORIG_SYSTEM_REF_ID,
						ORIG_SYSTEM,
						ORIG_SYSTEM_REFERENCE,
						HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
						OWNER_TABLE_ID,
						STATUS,
						REASON_CODE,
			          		OLD_ORIG_SYSTEM_REFERENCE,
			  			START_DATE_ACTIVE,
						END_DATE_ACTIVE,
						PROGRAM_UPDATE_DATE,
						CREATED_BY_MODULE,
						HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
				 		CREATION_DATE,
				 		LAST_UPDATE_DATE,
				 		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
						ATTRIBUTE_CATEGORY,
						ATTRIBUTE1,
						ATTRIBUTE2,
						ATTRIBUTE3,
						ATTRIBUTE4,
						ATTRIBUTE5,
						ATTRIBUTE6,
						ATTRIBUTE7,
						ATTRIBUTE8,
						ATTRIBUTE9,
						ATTRIBUTE10,
						ATTRIBUTE11,
						ATTRIBUTE12,
						ATTRIBUTE13,
						ATTRIBUTE14,
						ATTRIBUTE15,
						ATTRIBUTE16,
						ATTRIBUTE17,
						ATTRIBUTE18,
						ATTRIBUTE19,
						ATTRIBUTE20
					)
					FROM HZ_ORIG_SYS_REFERENCES
					WHERE OWNER_TABLE_ID = 	mph.to_parent_entity_id
					AND OWNER_TABLE_NAME =  md.parent_entity_name

				     )AS HZ_ORIG_SYS_REF_OBJ_TBL)

				)
		       FROM hz_merge_parties mp2,
			    hz_merge_party_history mph,
			    hz_merge_dictionary md
		       WHERE mp2.batch_id = p_batch_id
		       AND mph.batch_party_id = mp2.batch_party_id
		       AND md.merge_dict_id = mph.merge_dict_id
		       AND md.dict_application_id = 222
		       and md.entity_name like 'HZ%';