APPS.HZ_EXTRACT_PERSON_BO_PVT SQL Statements

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

Line: 57

	SELECT HZ_EMPLOY_HIST_BO(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		EMPLOYMENT_HISTORY_ID,
		PARTY_ID,
		BEGIN_DATE,
		END_DATE,
		EMPLOYMENT_TYPE_CODE,
		EMPLOYED_AS_TITLE_CODE,
		EMPLOYED_AS_TITLE,
		EMPLOYED_BY_NAME_COMPANY,
		EMPLOYED_BY_PARTY_ID,
		EMPLOYED_BY_DIVISION_NAME,
		SUPERVISOR_NAME,
		BRANCH,
		MILITARY_RANK,
		SERVED,
		STATION,
		RESPONSIBILITY,
		WEEKLY_WORK_HOURS,
		REASON_FOR_LEAVING,
		FACULTY_POSITION_FLAG,
		TENURE_CODE,
		FRACTION_OF_TENURE,
		COMMENTS,
		STATUS,
		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),
		CAST(MULTISET (
		SELECT HZ_WORK_CLASS_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		WORK_CLASS_ID,
		LEVEL_OF_EXPERIENCE,
		WORK_CLASS_NAME,
		EMPLOYMENT_HISTORY_ID,
		STATUS,
		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))
		FROM HZ_WORK_CLASS
		WHERE WORK_CLASS_ID = P_PERSON_ID
		AND WORK_CLASS_NAME = 'HZ_PARTIES') AS HZ_WORK_CLASS_OBJ_TBL))
	FROM HZ_EMPLOYMENT_HISTORY
	WHERE PARTY_ID = P_PERSON_ID;

Line: 269

	SELECT HZ_PERSON_BO(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		P.PARTY_ID,
		NULL, --ORIG_SYSTEM,
		NULL, --ORIG_SYSTEM_REFERENCE,
		P.PARTY_NUMBER,
		P.VALIDATED_FLAG,
		P.STATUS,
		P.CATEGORY_CODE,
		P.SALUTATION,
		P.ATTRIBUTE_CATEGORY,
		P.ATTRIBUTE1,
		P.ATTRIBUTE2,
		P.ATTRIBUTE3,
		P.ATTRIBUTE4,
		P.ATTRIBUTE5,
		P.ATTRIBUTE6,
		P.ATTRIBUTE7,
		P.ATTRIBUTE8,
		P.ATTRIBUTE9,
		P.ATTRIBUTE10,
		P.ATTRIBUTE11,
		P.ATTRIBUTE12,
		P.ATTRIBUTE13,
		P.ATTRIBUTE14,
		P.ATTRIBUTE15,
		P.ATTRIBUTE16,
		P.ATTRIBUTE17,
		P.ATTRIBUTE18,
		P.ATTRIBUTE19,
		P.ATTRIBUTE20,
		P.ATTRIBUTE21,
		P.ATTRIBUTE22,
		P.ATTRIBUTE23,
		P.ATTRIBUTE24,
		PRO.PERSON_PRE_NAME_ADJUNCT,
		PRO.PERSON_FIRST_NAME,
		PRO.PERSON_MIDDLE_NAME,
		PRO.PERSON_LAST_NAME,
		PRO.PERSON_NAME_SUFFIX,
		PRO.PERSON_TITLE,
		PRO.PERSON_ACADEMIC_TITLE,
		PRO.PERSON_PREVIOUS_LAST_NAME,
		PRO.PERSON_INITIALS,
		PRO.KNOWN_AS,
		PRO.KNOWN_AS2,
		PRO.KNOWN_AS3,
		PRO.KNOWN_AS4,
		PRO.KNOWN_AS5,
		PRO.PERSON_NAME_PHONETIC,
		PRO.PERSON_FIRST_NAME_PHONETIC,
		PRO.PERSON_LAST_NAME_PHONETIC,
		PRO.MIDDLE_NAME_PHONETIC,
		PRO.TAX_REFERENCE,
		PRO.JGZZ_FISCAL_CODE,
		PRO.PERSON_IDEN_TYPE,
		PRO.PERSON_IDENTIFIER,
		PRO.DATE_OF_BIRTH,
		PRO.PLACE_OF_BIRTH,
		PRO.DATE_OF_DEATH,
		PRO.DECEASED_FLAG,
		PRO.GENDER,
		PRO.DECLARED_ETHNICITY,
		PRO.MARITAL_STATUS,
		MARITAL_STATUS_EFFECTIVE_DATE,
		PRO.PERSONAL_INCOME,
		PRO.HEAD_OF_HOUSEHOLD_FLAG,
		PRO.HOUSEHOLD_INCOME,
		PRO.HOUSEHOLD_SIZE,
		PRO.RENT_OWN_IND,
		PRO.LAST_KNOWN_GPS,
		PRO.INTERNAL_FLAG,
		PRO.PROGRAM_UPDATE_DATE,
		PRO.CREATED_BY_MODULE,
		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.CREATED_BY),
		PRO.CREATION_DATE,
		PRO.LAST_UPDATE_DATE,
		HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.LAST_UPDATED_BY),
		PRO.ACTUAL_CONTENT_SOURCE,
		HZ_ORIG_SYS_REF_OBJ_TBL(),
 		HZ_EXT_ATTRIBUTE_OBJ_TBL(),
		HZ_PARTY_SITE_BO_TBL(),
		CAST(MULTISET (
		SELECT HZ_PARTY_PREF_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		PARTY_PREFERENCE_ID,
		HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
		PARTY_ID,
		CATEGORY,
		PREFERENCE_CODE,
		VALUE_VARCHAR2,
		VALUE_NUMBER,
		VALUE_DATE,
		VALUE_NAME,
		MODULE,
		ADDITIONAL_VALUE1,
		ADDITIONAL_VALUE2,
		ADDITIONAL_VALUE3,
		ADDITIONAL_VALUE4,
		ADDITIONAL_VALUE5,
		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))
		FROM HZ_PARTY_PREFERENCES
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_PARTY_PREF_OBJ_TBL),
		HZ_RELATIONSHIP_OBJ_TBL(),
		HZ_PHONE_CP_BO_TBL(),
		HZ_EMAIL_CP_BO_TBL(),
		HZ_WEB_CP_BO_TBL(),
		HZ_SMS_CP_BO_TBL(),
		CAST(MULTISET (
		SELECT HZ_CODE_ASSIGNMENT_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		CODE_ASSIGNMENT_ID,
		HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',OWNER_TABLE_ID),
		OWNER_TABLE_ID,
		CLASS_CATEGORY,
		CLASS_CODE,
		PRIMARY_FLAG,
		ACTUAL_CONTENT_SOURCE,
		START_DATE_ACTIVE,
		END_DATE_ACTIVE,
		STATUS,
		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),
		RANK)
	FROM HZ_CODE_ASSIGNMENTS
	WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
	AND OWNER_TABLE_ID = P_PERSON_ID) AS HZ_CODE_ASSIGNMENT_OBJ_TBL),
	CAST(MULTISET (
	SELECT HZ_PERSON_LANG_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		LANGUAGE_USE_REFERENCE_ID,
		LANGUAGE_NAME,
		PARTY_ID,
		NATIVE_LANGUAGE,
		PRIMARY_LANGUAGE_INDICATOR,
		READS_LEVEL,
		SPEAKS_LEVEL,
		WRITES_LEVEL,
		SPOKEN_COMPREHENSION_LEVEL,
		STATUS,
		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))
	FROM HZ_PERSON_LANGUAGE
	WHERE PARTY_ID = P_PERSON_ID) AS HZ_PERSON_LANG_OBJ_TBL),
	CAST(MULTISET (
	SELECT HZ_EDUCATION_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		EDUCATION_ID,
		PARTY_ID,
		COURSE_MAJOR,
		DEGREE_RECEIVED,
		START_DATE_ATTENDED,
		LAST_DATE_ATTENDED,
		SCHOOL_ATTENDED_NAME,
		SCHOOL_PARTY_ID,
		TYPE_OF_SCHOOL,
		STATUS,
		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))
		FROM HZ_EDUCATION
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_EDUCATION_OBJ_TBL),
		CAST(MULTISET (
		SELECT HZ_CITIZENSHIP_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		CITIZENSHIP_ID,
		PARTY_ID,
		BIRTH_OR_SELECTED,
		COUNTRY_CODE,
		DATE_RECOGNIZED,
		DATE_DISOWNED,
		END_DATE,
		DOCUMENT_TYPE,
		DOCUMENT_REFERENCE,
		STATUS,
		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))
		FROM HZ_CITIZENSHIP
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_CITIZENSHIP_OBJ_TBL),
		HZ_EMPLOY_HIST_BO_TBL(),
		CAST(MULTISET (
		SELECT HZ_PERSON_INTEREST_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		PERSON_INTEREST_ID,
		LEVEL_OF_INTEREST,
		PARTY_ID,
		LEVEL_OF_PARTICIPATION,
		INTEREST_TYPE_CODE,
		COMMENTS,
		SPORT_INDICATOR,
		SUB_INTEREST_TYPE_CODE,
		INTEREST_NAME,
		TEAM,
		SINCE,
		STATUS,
		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))
		FROM HZ_PERSON_INTEREST
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_PERSON_INTEREST_OBJ_TBL),
	CAST(MULTISET (
	SELECT HZ_CERTIFICATION_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		CERTIFICATION_ID,
		CERTIFICATION_NAME,
		HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
		PARTY_ID,
		CURRENT_STATUS,
		EXPIRES_ON_DATE,
		GRADE,
		ISSUED_BY_AUTHORITY,
		ISSUED_ON_DATE,
		--WH_UPDATE_DATE,
		STATUS,
		PROGRAM_UPDATE_DATE,
		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))
		FROM HZ_CERTIFICATIONS
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_CERTIFICATION_OBJ_TBL),
		CAST(MULTISET (
		SELECT HZ_FINANCIAL_PROF_OBJ(
		P_ACTION_TYPE,
                NULL, -- COMMON_OBJ_ID
		FINANCIAL_PROFILE_ID,
		ACCESS_AUTHORITY_DATE,
		ACCESS_AUTHORITY_GRANTED,
		BALANCE_AMOUNT,
		BALANCE_VERIFIED_ON_DATE,
		FINANCIAL_ACCOUNT_NUMBER,
		FINANCIAL_ACCOUNT_TYPE,
		FINANCIAL_ORG_TYPE,
		FINANCIAL_ORGANIZATION_NAME,
		HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
		PARTY_ID,
		--WH_UPDATE_DATE,
		STATUS,
		PROGRAM_UPDATE_DATE,
		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))
		FROM HZ_FINANCIAL_PROFILE
		WHERE PARTY_ID = P_PERSON_ID) AS HZ_FINANCIAL_PROF_OBJ_TBL),
		HZ_CONTACT_PREF_OBJ_TBL(),
		HZ_PARTY_USAGE_OBJ_TBL())
	FROM HZ_PERSON_PROFILES PRO, HZ_PARTIES P
	WHERE PRO.PARTY_ID = P.PARTY_ID
	AND PRO.PARTY_ID = P_PERSON_ID
	AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE);

Line: 551

	select person_profile_id
	from HZ_PERSON_PROFILES
	where party_id = p_person_id
	AND sysdate between effective_start_date and nvl(effective_end_date,sysdate);

Line: 1055

The Get Persons Updated procedure is a service to retrieve all of the Person business objects whose updates have been
captured by the logical business event. Each Persons Updated business event signifies that one or more Person business
objects have been updated.
The caller provides an identifier for the Persons Update business event and the procedure returns database objects of
the type HZ_PERSON_BO for all of the Person business objects from the business event.
Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure
and returns them to the caller.
*/

 PROCEDURE get_persons_updated(
    p_init_msg_list       IN            VARCHAR2 := fnd_api.g_false,
    p_event_id            IN           	NUMBER,
    x_person_objs         OUT NOCOPY    HZ_PERSON_BO_TBL,
    x_return_status       OUT NOCOPY    VARCHAR2,
    x_msg_count           OUT NOCOPY    NUMBER,
    x_msg_data            OUT NOCOPY    VARCHAR2
  ) is

l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;

Line: 1089

        	hz_utility_v2pub.debug(p_message=>'get_persons_updated(+)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1108

	-- call event API get_organization_updated for each id.

   	x_person_objs := HZ_PERSON_BO_TBL();

Line: 1115

		get_person_updated(
    		p_init_msg_list => fnd_api.g_false,
		p_event_id => p_event_id,
    		p_person_id  => l_obj_root_ids(i),
    		x_person_obj  => x_person_objs(i),
		x_return_status => x_return_status,
		x_msg_count => x_msg_count,
		x_msg_data => x_msg_data);

Line: 1143

        	hz_utility_v2pub.debug(p_message=>'get_persons_updated (-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1166

        hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1185

        hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1208

        hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1222

	   SELECT
  		sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
  		CHILD_OPERATION_FLAG,
  		CHILD_BO_CODE,
  		CHILD_ENTITY_NAME,
  		CHILD_ID,
  		populated_flag
	 FROM HZ_BUS_OBJ_TRACKING
         where event_id = p_event_id
	 START WITH child_id = p_root_id
   		AND child_entity_name = 'HZ_PARTIES'
   		AND  PARENT_BO_CODE IS NULL
   		AND event_id = p_event_id
   		AND CHILD_BO_CODE = 'PERSON' --(or ORG, PERSON_CUST, ORG_CUST).
		and event_id = p_event_id
	CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
    	AND PARENT_ID = PRIOR CHILD_ID
    	AND parent_bo_code = PRIOR child_bo_code
	and event_id = PRIOR event_id;

Line: 1243

    	   select CHILD_ENTITY_NAME
	    FROM HZ_BUS_OBJ_TRACKING
	    where event_id = p_event_id
	    and populated_flag = 'N'
	    and CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES';

Line: 1271

		px_person_obj.action_type := 'UPDATED';

Line: 1272

	else px_person_obj.action_type := 'CHILD_UPDATED';

Line: 1285

		then l_action_type := 'UPDATED';

Line: 1505

					if px_person_obj.party_site_objs(i).location_obj.location_id = l_child_id and l_action_type = 'UPDATED'
					then px_person_obj.party_site_objs(i).location_obj.action_type := l_action_type;

Line: 1563

				    px_person_obj.party_site_objs(i).action_type := 'CHILD_UPDATED';

Line: 1599

				    px_person_obj.employ_hist_objs(i).action_type := 'CHILD_UPDATED';

Line: 1660

PROCEDURE get_person_updated(
    p_init_msg_list       IN            VARCHAR2 := fnd_api.g_false,
    p_event_id		  IN           	NUMBER,
    p_person_id           IN           NUMBER,
    x_person_obj          OUT NOCOPY    HZ_PERSON_BO,
    x_return_status       OUT NOCOPY    VARCHAR2,
    x_msg_count           OUT NOCOPY    NUMBER,
    x_msg_data            OUT NOCOPY    VARCHAR2
  ) is
l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;

Line: 1686

        	hz_utility_v2pub.debug(p_message=>'get_person_updated(+)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1715

	-- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'


	l_person_obj := x_person_obj;

Line: 1741

        	hz_utility_v2pub.debug(p_message=>'get_person_updated (-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1764

        hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1783

        hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);

Line: 1806

        hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
                               p_prefix=>l_debug_prefix,
                               p_msg_level=>fnd_log.level_procedure);