The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HZ_PHONE_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
PHONE_CALLING_CALENDAR,
LAST_CONTACT_DT_TIME,
TIMEZONE_ID,
PHONE_AREA_CODE,
PHONE_COUNTRY_CODE,
PHONE_NUMBER,
PHONE_EXTENSION,
PHONE_LINE_TYPE,
RAW_PHONE_NUMBER,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'PHONE',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'PHONE'
AND ((P_PHONE_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_PHONE_ID IS NOT NULL AND CONTACT_POINT_ID = P_PHONE_ID));
SELECT HZ_TELEX_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
TELEX_NUMBER,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
OWNER_TABLE_NAME,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'TLX',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'TLX'
AND ((P_TELEX_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_TELEX_ID IS NOT NULL AND CONTACT_POINT_ID = P_TELEX_ID));
SELECT HZ_EMAIL_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
EMAIL_FORMAT,
EMAIL_ADDRESS,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
OWNER_TABLE_NAME,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'EMAIL',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EMAIL'
AND ((P_EMAIL_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_EMAIL_ID IS NOT NULL AND CONTACT_POINT_ID = P_EMAIL_ID));
SELECT HZ_WEB_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
WEB_TYPE,
URL,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
OWNER_TABLE_NAME,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'WEB',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'WEB'
AND ((P_WEB_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_WEB_ID IS NOT NULL AND CONTACT_POINT_ID = P_WEB_ID));
SELECT HZ_EDI_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
EDI_TRANSACTION_HANDLING,
EDI_ID_NUMBER,
EDI_PAYMENT_METHOD,
EDI_PAYMENT_FORMAT,
EDI_REMITTANCE_METHOD,
EDI_REMITTANCE_INSTRUCTION,
EDI_TP_HEADER_ID,
EDI_ECE_TP_LOCATION_CODE,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
OWNER_TABLE_NAME,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'EDI',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EDI'
AND ((P_EDI_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_EDI_ID IS NOT NULL AND CONTACT_POINT_ID = P_EDI_ID));
SELECT HZ_EFT_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
EFT_TRANSMISSION_PROGRAM_ID,
EFT_PRINTING_PROGRAM_ID,
EFT_USER_NUMBER,
EFT_SWIFT_CODE,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'EFT',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EFT'
AND ((P_EFT_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_EFT_ID IS NOT NULL AND CONTACT_POINT_ID = P_EFT_ID));
SELECT HZ_SMS_CP_BO(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_POINT_ID,
NULL, -- ORIG_SYSTEM,
NULL, -- ORIG_SYSTEM_REFERENCE,
STATUS,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
OWNER_TABLE_ID,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CONTACT_POINT_PURPOSE,
PRIMARY_BY_PURPOSE,
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),
ACTUAL_CONTENT_SOURCE,
PHONE_CALLING_CALENDAR,
LAST_CONTACT_DT_TIME,
TIMEZONE_ID,
PHONE_AREA_CODE,
PHONE_COUNTRY_CODE,
PHONE_NUMBER,
PHONE_EXTENSION,
PHONE_LINE_TYPE,
RAW_PHONE_NUMBER,
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM,
ORIG_SYSTEM_REFERENCE,
OWNER_TABLE_NAME,
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 = CP.CONTACT_POINT_ID
AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
CAST(MULTISET (
SELECT HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
'SMS',
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_CONTACT_PREFERENCES CPREF
WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'SMS'
AND ((P_SMS_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
AND OWNER_TABLE_ID = P_PARENT_ID)
OR (P_SMS_ID IS NOT NULL AND CONTACT_POINT_ID = P_SMS_ID));
select HZ_CONTACT_PREF_OBJ(
P_ACTION_TYPE,
NULL, -- COMMON_OBJ_ID
CONTACT_PREFERENCE_ID,
HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(CONTACT_LEVEL_TABLE,CONTACT_LEVEL_TABLE_ID),
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
PREFERENCE_CODE,
PREFERENCE_TOPIC_TYPE,
PREFERENCE_TOPIC_TYPE_ID,
PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_DATE,
PREFERENCE_END_DATE,
PREFERENCE_START_TIME_HR,
PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,
PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACTIONS,
MAX_NO_OF_INTERACT_UOM_CODE,
REQUESTED_BY,
REASON_CODE,
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_contact_preferences
where contact_level_table = p_cont_level_table
and contact_level_table_id = p_cont_level_table_id
and contact_type = nvl(p_contact_type, contact_type);