[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HZ_CUST_ACCT_CONTACT_BO(
P_ACTION_TYPE,
NULL, --COMMON_OBJ_ID
CAR.CUST_ACCOUNT_ROLE_ID, --CUST_ACCT_CONTACT_ID,
NULL, --ORIG_SYSTEM,
NULL, --ORIG_SYSTEM_REFERENCE,
CAR.CUST_ACCOUNT_ID,
CAR.CUST_ACCT_SITE_ID,
CAR.PRIMARY_FLAG,
CAR.ROLE_TYPE,
CAR.SOURCE_CODE,
CAR.ATTRIBUTE_CATEGORY,
CAR.ATTRIBUTE1,
CAR.ATTRIBUTE2,
CAR.ATTRIBUTE3,
CAR.ATTRIBUTE4,
CAR.ATTRIBUTE5,
CAR.ATTRIBUTE6,
CAR.ATTRIBUTE7,
CAR.ATTRIBUTE8,
CAR.ATTRIBUTE9,
CAR.ATTRIBUTE10,
CAR.ATTRIBUTE11,
CAR.ATTRIBUTE12,
CAR.ATTRIBUTE13,
CAR.ATTRIBUTE14,
CAR.ATTRIBUTE15,
CAR.ATTRIBUTE16,
CAR.ATTRIBUTE17,
CAR.ATTRIBUTE18,
CAR.ATTRIBUTE19,
CAR.ATTRIBUTE20,
CAR.ATTRIBUTE21,
CAR.ATTRIBUTE22,
CAR.ATTRIBUTE23,
CAR.ATTRIBUTE24,
CAR.ATTRIBUTE25,
CAR.STATUS,
CAR.PROGRAM_UPDATE_DATE,
CAR.CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CAR.CREATED_BY),
CAR.CREATION_DATE,
CAR.LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CAR.LAST_UPDATED_BY),
R.SUBJECT_ID, --CONTACT_PERSON_ID,
NULL, --CONTACT_PERSON_OS,
NULL, --CONTACT_PERSON_OSR,
R.RELATIONSHIP_ID,
R.RELATIONSHIP_CODE,
R.RELATIONSHIP_TYPE,
R.START_DATE,
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 = CAR.CUST_ACCOUNT_ROLE_ID
AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNT_ROLES'
AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- acct contact ssm
CAST(MULTISET (
SELECT HZ_ORIG_SYS_REF_OBJ(
NULL, --P_ACTION_TYPE,
OSR.ORIG_SYSTEM_REF_ID,
OSR.ORIG_SYSTEM,
OSR.ORIG_SYSTEM_REFERENCE,
OSR.OWNER_TABLE_NAME,
OSR.OWNER_TABLE_ID,
OSR.STATUS,
OSR.REASON_CODE,
OSR.OLD_ORIG_SYSTEM_REFERENCE,
OSR.START_DATE_ACTIVE,
OSR.END_DATE_ACTIVE,
OSR.PROGRAM_UPDATE_DATE,
OSR.CREATED_BY_MODULE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OSR.CREATED_BY),
OSR.CREATION_DATE,
OSR.LAST_UPDATE_DATE,
HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OSR.LAST_UPDATED_BY),
OSR.ATTRIBUTE_CATEGORY,
OSR.ATTRIBUTE1,
OSR.ATTRIBUTE2,
OSR.ATTRIBUTE3,
OSR.ATTRIBUTE4,
OSR.ATTRIBUTE5,
OSR.ATTRIBUTE6,
OSR.ATTRIBUTE7,
OSR.ATTRIBUTE8,
OSR.ATTRIBUTE9,
OSR.ATTRIBUTE10,
OSR.ATTRIBUTE11,
OSR.ATTRIBUTE12,
OSR.ATTRIBUTE13,
OSR.ATTRIBUTE14,
OSR.ATTRIBUTE15,
OSR.ATTRIBUTE16,
OSR.ATTRIBUTE17,
OSR.ATTRIBUTE18,
OSR.ATTRIBUTE19,
OSR.ATTRIBUTE20)
FROM HZ_ORIG_SYS_REFERENCES OSR, HZ_RELATIONSHIPS R
WHERE OSR.OWNER_TABLE_ID = R.SUBJECT_ID
AND CAR.PARTY_ID = R.PARTY_ID
AND R.SUBJECT_TYPE = 'PERSON'
AND OSR.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND OSR.STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- contact person ssm
CAST(MULTISET (
SELECT HZ_ROLE_RESPONSIBILITY_OBJ(
P_ACTION_TYPE,
NULL, --COMMON_OBJ_ID
RESPONSIBILITY_ID,
CUST_ACCOUNT_ROLE_ID,
RESPONSIBILITY_TYPE,
PRIMARY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
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_ROLE_RESPONSIBILITY
WHERE CUST_ACCOUNT_ROLE_ID = CAR.CUST_ACCOUNT_ROLE_ID) AS HZ_ROLE_RESPONSIBILITY_OBJ_TBL))
FROM HZ_CUST_ACCOUNT_ROLES CAR, HZ_CUST_ACCOUNTS CA, HZ_RELATIONSHIPS R
WHERE
R.PARTY_ID = CAR.PARTY_ID
AND CA.PARTY_ID = R.OBJECT_ID
AND CAR.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND ((P_CUST_ACCT_CONTACT_ID IS NULL AND CAR.CUST_ACCOUNT_ID = P_PARENT_ID)
OR (P_CUST_ACCT_CONTACT_ID IS NOT NULL
AND CAR.CUST_ACCOUNT_ROLE_ID = P_CUST_ACCT_CONTACT_ID));