[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select r.subject_id, r.party_id
from hz_relationships r, hz_org_contacts oc
where r.relationship_id = oc.party_relationship_id
and oc.org_contact_id = p_org_cont_id
and r.subject_type = 'PERSON';
SELECT HZ_PERSON_PROFILE_OBJ(
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,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
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 OSR
WHERE
OSR.OWNER_TABLE_ID = PRO.PARTY_ID
AND OWNER_TABLE_NAME = 'HZ_PARTIES'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
HZ_EXT_ATTRIBUTE_OBJ_TBL())
FROM HZ_PERSON_PROFILES PRO, HZ_PARTIES P
WHERE PRO.PARTY_ID = P.PARTY_ID AND P.PARTY_ID = P_PERSON_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE);
SELECT HZ_ORG_CONTACT_ROLE_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
ORG_CONTACT_ROLE_ID,
NULL, --ORIG_SYSTEM,
NULL, --ORIG_SYSTEM_REFERENCE,
ROLE_TYPE,
PRIMARY_FLAG,
ORG_CONTACT_ID,
ROLE_LEVEL,
PRIMARY_CONTACT_PER_ROLE_TYPE,
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_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
'ORG_CONTACT', -- parent_object_type
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 OSR
WHERE
OSR.OWNER_TABLE_ID = OCR.ORG_CONTACT_ROLE_ID
AND OWNER_TABLE_NAME = 'HZ_ORG_CONTACT_ROLES'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL))
FROM HZ_ORG_CONTACT_ROLES OCR
WHERE ORG_CONTACT_ID = P_ORG_CONT_ID;
SELECT HZ_ORG_CONTACT_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
OC.ORG_CONTACT_ID,
P.PARTY_ID,
NULL, --ORIG_SYSTEM,
NULL, --ORIG_SYSTEM_REFERENCE,
OC.COMMENTS,
OC.CONTACT_NUMBER,
OC.DEPARTMENT_CODE,
OC.DEPARTMENT,
OC.TITLE,
OC.JOB_TITLE,
OC.DECISION_MAKER_FLAG,
OC.JOB_TITLE_CODE,
OC.REFERENCE_USE_FLAG,
OC.RANK,
OC.PARTY_SITE_ID,
OC.ATTRIBUTE_CATEGORY,
OC.ATTRIBUTE1,
OC.ATTRIBUTE2,
OC.ATTRIBUTE3,
OC.ATTRIBUTE4,
OC.ATTRIBUTE5,
OC.ATTRIBUTE6,
OC.ATTRIBUTE7,
OC.ATTRIBUTE8,
OC.ATTRIBUTE9,
OC.ATTRIBUTE10,
OC.ATTRIBUTE11,
OC.ATTRIBUTE12,
OC.ATTRIBUTE13,
OC.ATTRIBUTE14,
OC.ATTRIBUTE15,
OC.ATTRIBUTE16,
OC.ATTRIBUTE17,
OC.ATTRIBUTE18,
OC.ATTRIBUTE19,
OC.ATTRIBUTE20,
OC.ATTRIBUTE21,
OC.ATTRIBUTE22,
OC.ATTRIBUTE23,
OC.ATTRIBUTE24,
OC.PROGRAM_UPDATE_DATE,
OC.CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OC.CREATED_BY),
OC.CREATION_DATE,
OC.LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OC.LAST_UPDATED_BY),
R.RELATIONSHIP_CODE,
R.RELATIONSHIP_TYPE,
R.COMMENTS,
R.START_DATE,
R.END_DATE,
R.STATUS,
HZ_ORIG_SYS_REF_OBJ_TBL(),
NULL, --PERSON_PROFILE_OBJ,
HZ_ORG_CONTACT_ROLE_OBJ_TBL(),
HZ_PARTY_SITE_BO_TBL(),
HZ_PHONE_CP_BO_TBL(),
HZ_TELEX_CP_BO_TBL(),
HZ_EMAIL_CP_BO_TBL(),
HZ_WEB_CP_BO_TBL(),
HZ_SMS_CP_BO_TBL(),
HZ_CONTACT_PREF_OBJ_TBL())
FROM HZ_ORG_CONTACTS OC, HZ_PARTIES P, HZ_RELATIONSHIPS R
WHERE OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND R.SUBJECT_ID = P.PARTY_ID
AND R.OBJECT_TYPE = 'PERSON'
AND ((P_ORG_CONTACT_ID IS NULL AND P.PARTY_ID = P_ORGANIZATION_ID)
OR (P_ORG_CONTACT_ID IS NOT NULL AND OC.ORG_CONTACT_ID = P_ORG_CONTACT_ID));