VIEW: APPS.CSI_T_PARTY_CONTACTS_V

Source

View Text - Preformatted

SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, CL.MEANING PARTY_SOURCE_MEANING, CIP.PARTY_SOURCE_ID PARTY_ID, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, CIP.CONTACT_FLAG CONTACT_FLAG, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID, CIP.PREFERRED_FLAG PREFERRED_FLAG, CIP.PRIMARY_FLAG PRIMARY_FLAG, CIP.ACTIVE_START_DATE ACTIVE_START_DATE, CIP.ACTIVE_END_DATE ACTIVE_END_DATE, CIP.CONTEXT CONTEXT, CIP.ATTRIBUTE1 ATTRIBUTE1, CIP.ATTRIBUTE2 ATTRIBUTE2, CIP.ATTRIBUTE3 ATTRIBUTE3, CIP.ATTRIBUTE4 ATTRIBUTE4, CIP.ATTRIBUTE5 ATTRIBUTE5, CIP.ATTRIBUTE6 ATTRIBUTE6, CIP.ATTRIBUTE7 ATTRIBUTE7, CIP.ATTRIBUTE8 ATTRIBUTE8, CIP.ATTRIBUTE9 ATTRIBUTE9, CIP.ATTRIBUTE10 ATTRIBUTE10, CIP.ATTRIBUTE11 ATTRIBUTE11, CIP.ATTRIBUTE12 ATTRIBUTE12, CIP.ATTRIBUTE13 ATTRIBUTE13, CIP.ATTRIBUTE14 ATTRIBUTE14, CIP.ATTRIBUTE15 ATTRIBUTE15, CIP.CREATED_BY CREATED_BY, CIP.CREATION_DATE CREATION_DATE, CIP.LAST_UPDATED_BY LAST_UPDATED_BY, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, HP.PARTY_NAME PARTY_NAME, HP.PARTY_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, DECODE( HCP_WP.PHONE_COUNTRY_CODE, NULL, NULL, HCP_WP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_WP.PHONE_AREA_CODE, NULL, NULL, '(' || HCP_WP.PHONE_AREA_CODE || ')' ) || HCP_WP.PHONE_NUMBER || DECODE( HCP_WP.PHONE_EXTENSION, NULL, NULL, 'x' || HCP_WP.PHONE_EXTENSION) WORK_PHONE_NUMBER, DECODE( HCP_HP.PHONE_AREA_CODE,NULL, NULL, HCP_HP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_HP.PHONE_AREA_CODE, NULL, NULL, '(' || HCP_HP.PHONE_AREA_CODE || ')' ) || HCP_HP.PHONE_NUMBER || DECODE( HCP_HP.PHONE_EXTENSION, NULL, NULL, 'x' || HCP_HP.PHONE_EXTENSION ) HOME_PHONE_NUMBER, HCP_EM.EMAIL_ADDRESS EMAIL_ADDRESS, HP.ADDRESS1 ADDRESS1, HP.ADDRESS2 ADDRESS2, HP.ADDRESS3 ADDRESS3, HP.ADDRESS4 ADDRESS4, HP.CITY CITY, HP.POSTAL_CODE POSTAL_CODE, HP.STATE STATE, HP.PROVINCE PROVINCE, HP.COUNTY COUNTY, HP.COUNTRY COUNTRY FROM CSI_T_PARTY_DETAILS CIP, HZ_PARTIES HP, CSI_IPA_RELATION_TYPES CIR, HZ_CONTACT_POINTS HCP_WP, HZ_CONTACT_POINTS HCP_HP, HZ_CONTACT_POINTS HCP_EM, CSI_LOOKUPS CL, HZ_RELATIONSHIPS HR , CSI_T_PARTY_DETAILS CIPO WHERE CIP.PARTY_SOURCE_ID = HR.SUBJECT_ID AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID AND CIPO.PARTY_SOURCE_ID = HR.OBJECT_ID AND HR.SUBJECT_ID = HP.PARTY_ID AND CIP.CONTACT_FLAG = 'Y' AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+) AND HCP_WP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND HCP_WP.OWNER_TABLE_ID (+) = HR.PARTY_ID AND HCP_WP.CONTACT_POINT_TYPE (+) = 'PHONE' AND HCP_WP.PRIMARY_FLAG (+) = 'Y' AND HCP_HP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND HCP_HP.OWNER_TABLE_ID (+) = HR.PARTY_ID AND HCP_HP.CONTACT_POINT_TYPE (+) = 'PHONE' AND HCP_HP.PRIMARY_FLAG (+) = 'Y' AND HCP_HP.CONTACT_POINT_PURPOSE (+) = 'PERSONAL' AND HCP_EM.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND HCP_EM.OWNER_TABLE_ID (+) = HR.PARTY_ID AND HCP_EM.CONTACT_POINT_TYPE (+) = 'EMAIL' AND HCP_EM.PRIMARY_FLAG (+) = 'Y' AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, CL.MEANING PARTY_SOURCE_MEANING, CIP.PARTY_source_ID PARTY_ID, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, CIP.CONTACT_FLAG CONTACT_FLAG, CIP.CONTACT_Party_ID CONTACT_PARTY_ID, CIP.PREFERRED_FLAG PREFERRED_FLAG, CIP.PRIMARY_FLAG PRIMARY_FLAG, CIP.ACTIVE_START_DATE ACTIVE_START_DATE, CIP.ACTIVE_END_DATE ACTIVE_END_DATE, CIP.CONTEXT CONTEXT, CIP.ATTRIBUTE1 ATTRIBUTE1, CIP.ATTRIBUTE2 ATTRIBUTE2, CIP.ATTRIBUTE3 ATTRIBUTE3, CIP.ATTRIBUTE4 ATTRIBUTE4, CIP.ATTRIBUTE5 ATTRIBUTE5, CIP.ATTRIBUTE6 ATTRIBUTE6, CIP.ATTRIBUTE7 ATTRIBUTE7, CIP.ATTRIBUTE8 ATTRIBUTE8, CIP.ATTRIBUTE9 ATTRIBUTE9, CIP.ATTRIBUTE10 ATTRIBUTE10, CIP.ATTRIBUTE11 ATTRIBUTE11, CIP.ATTRIBUTE12 ATTRIBUTE12, CIP.ATTRIBUTE13 ATTRIBUTE13, CIP.ATTRIBUTE14 ATTRIBUTE14, CIP.ATTRIBUTE15 ATTRIBUTE15, CIP.CREATED_BY CREATED_BY, CIP.CREATION_DATE CREATION_DATE, CIP.LAST_UPDATED_BY LAST_UPADTED_BY, CIP.LAST_UPDATE_DATE LAST_UPADTE_DATE, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, PAP.FULL_NAME PARTY_NAME, PAP.EMPLOYEE_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, PP.PHONE_NUMBER WORK_PHONE_NUMBER, NULL HOME_PHONE_NUMBER, PAP.EMAIL_ADDRESS EMAIL_ADDRESS, NULL ADDRESS1, NULL ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL POSTAL_CODE, NULL STATE, NULL PROVINCE, NULL COUNTY, NULL COUNTRY FROM CSI_T_PARTY_DETAILS CIP, CSI_T_PARTY_DETAILS CIPO, PER_PHONES PP, PER_ALL_PEOPLE_F PAP, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE CIP.CONTACT_FLAG = 'Y' AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID AND CIP.PARTY_SOURCE_ID = PAP.PERSON_ID AND PAP.EFFECTIVE_END_DATE >= SYSDATE AND CIP.PARTY_SOURCE_TABLE = 'EMPLOYEE' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+) AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F' AND PAP.PERSON_ID = PP.PARENT_ID(+) AND PP.PHONE_TYPE(+) = 'W1' AND SYSDATE BETWEEN PP.DATE_FROM(+) AND NVL(PP.DATE_TO(+), SYSDATE) AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, CL.MEANING PARTY_SOURCE_MEANING, CIP.PARTY_SOURCE_ID PARTY_ID, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, CIP.CONTACT_FLAG CONTACT_FLAG, CIP.CONTACT_Party_ID CONTACT_PARTY_ID , CIP.PREFERRED_FLAG PREFERRED_FLAG, CIP.PRIMARY_FLAG PRIMARY_FLAG, CIP.ACTIVE_START_DATE ACTIVE_START_DATE, CIP.ACTIVE_END_DATE ACTIVE_END_DATE, CIP.CONTEXT CONTEXT, CIP.ATTRIBUTE1 ATTRIBUTE1, CIP.ATTRIBUTE2 ATTRIBUTE2, CIP.ATTRIBUTE3 ATTRIBUTE3, CIP.ATTRIBUTE4 ATTRIBUTE4, CIP.ATTRIBUTE5 ATTRIBUTE5, CIP.ATTRIBUTE6 ATTRIBUTE6, CIP.ATTRIBUTE7 ATTRIBUTE7, CIP.ATTRIBUTE8 ATTRIBUTE8, CIP.ATTRIBUTE9 ATTRIBUTE9, CIP.ATTRIBUTE10 ATTRIBUTE10, CIP.ATTRIBUTE11 ATTRIBUTE11, CIP.ATTRIBUTE12 ATTRIBUTE12, CIP.ATTRIBUTE13 ATTRIBUTE13, CIP.ATTRIBUTE14 ATTRIBUTE14, CIP.ATTRIBUTE15 ATTRIBUTE15, CIP.CREATED_BY CREATED_BY, CIP.CREATION_DATE CREATION_DATE, CIP.LAST_UPDATED_BY LAST_UPDATED_BY, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, PVC.PREFIX || ' ' || PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' '|| PVC.LAST_NAME PARTY_NAME, NULL PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, PVC.AREA_CODE || ' ' || PVC.PHONE WORK_PHONE_NUMBER, NULL HOME_PHONE_NUMBER, PVC.MAIL_STOP EMAIL_ADDRESS, PVS.ADDRESS_LINE1 ADDRESS1, PVS.ADDRESS_LINE2 ADDRESS2, PVS.ADDRESS_LINE3 ADDRESS3, NULL ADDRESS4, PVS.CITY CITY, PVS.ZIP POSTAL_CODE, PVS.STATE STATE, PVS.PROVINCE PROVINCE, NULL COUNTY, PVS.COUNTRY COUNTRY FROM CSI_T_PARTY_DETAILS CIP, CSI_T_PARTY_DETAILS CIPO, PO_VENDOR_CONTACTS PVC, PO_VENDOR_SITES_ALL PVS, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE CIP.CONTACT_FLAG = 'Y' AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID AND CIP.PARTY_SOURCE_TABLE = 'PO_VENDORS' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+) AND CIP.PARTY_SOURCE_ID = PVC.VENDOR_CONTACT_ID AND PVS.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, CL.MEANING PARTY_SOURCE_MEANING, CIP.PARTY_SOURCE_ID PARTY_ID, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, CIP.CONTACT_FLAG CONTACT_FLAG, CIP.CONTACT_Party_ID CONTACT_PARTY_ID, CIP.PREFERRED_FLAG PREFERRED_FLAG, CIP.PRIMARY_FLAG PRIMARY_FLAG, CIP.ACTIVE_START_DATE ACTIVE_START_DATE, CIP.ACTIVE_END_DATE ACTIVE_END_DATE, CIP.CONTEXT CONTEXT, CIP.ATTRIBUTE1 ATTRIBUTE1, CIP.ATTRIBUTE2 ATTRIBUTE2, CIP.ATTRIBUTE3 ATTRIBUTE3, CIP.ATTRIBUTE4 ATTRIBUTE4, CIP.ATTRIBUTE5 ATTRIBUTE5, CIP.ATTRIBUTE6 ATTRIBUTE6, CIP.ATTRIBUTE7 ATTRIBUTE7, CIP.ATTRIBUTE8 ATTRIBUTE8, CIP.ATTRIBUTE9 ATTRIBUTE9, CIP.ATTRIBUTE10 ATTRIBUTE10, CIP.ATTRIBUTE11 ATTRIBUTE11, CIP.ATTRIBUTE12 ATTRIBUTE12, CIP.ATTRIBUTE13 ATTRIBUTE13, CIP.ATTRIBUTE14 ATTRIBUTE14, CIP.ATTRIBUTE15 ATTRIBUTE15, CIP.CREATED_BY CREATED_BY, CIP.CREATION_DATE CREATION_DATE, CIP.LAST_UPDATED_BY LAST_UPDATED_BY, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, JT.TEAM_NAME PARTY_NAME, JT.TEAM_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, NULL WORK_PHONE_NUMBER, NULL HOME_PHONE_NUMBER, JT.EMAIL_ADDRESS EMAIL_ADDRESS, NULL ADDRESS1, NULL ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL POSTAL_CODE, NULL STATE, NULL PROVINCE, NULL COUNTY, NULL COUNTRY FROM CSI_T_PARTY_DETAILS CIP, CSI_T_PARTY_DETAILS CIPO, JTF_RS_TEAMS_VL JT, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE CIP.CONTACT_FLAG = 'Y' AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID AND CIP.PARTY_SOURCE_ID = JT.TEAM_ID AND CIP.PARTY_SOURCE_TABLE = 'TEAM' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+) AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, CL.MEANING PARTY_SOURCE_MEANING, CIP.PARTY_SOURCE_ID PARTY_ID, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, CIP.CONTACT_FLAG CONTACT_FLAG, CIP.CONTACT_Party_ID CONTACT_PARTY_ID, CIP.PREFERRED_FLAG PREFERRED_FLAG, CIP.PRIMARY_FLAG PRIMARY_FLAG, CIP.ACTIVE_START_DATE ACTIVE_START_DATE, CIP.ACTIVE_END_DATE ACTIVE_END_DATE, CIP.CONTEXT CONTEXT, CIP.ATTRIBUTE1 ATTRIBUTE1, CIP.ATTRIBUTE2 ATTRIBUTE2, CIP.ATTRIBUTE3 ATTRIBUTE3, CIP.ATTRIBUTE4 ATTRIBUTE4, CIP.ATTRIBUTE5 ATTRIBUTE5, CIP.ATTRIBUTE6 ATTRIBUTE6, CIP.ATTRIBUTE7 ATTRIBUTE7, CIP.ATTRIBUTE8 ATTRIBUTE8, CIP.ATTRIBUTE9 ATTRIBUTE9, CIP.ATTRIBUTE10 ATTRIBUTE10, CIP.ATTRIBUTE11 ATTRIBUTE11, CIP.ATTRIBUTE12 ATTRIBUTE12, CIP.ATTRIBUTE13 ATTRIBUTE13, CIP.ATTRIBUTE14 ATTRIBUTE14, CIP.ATTRIBUTE15 ATTRIBUTE15, CIP.CREATED_BY CREATED_BY, CIP.CREATION_DATE CREATION_DATE, CIP.LAST_UPDATED_BY LAST_UPDATED_BY, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, JG.GROUP_NAME PARTY_NAME, JG.GROUP_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, NULL WORK_PHONE_NUMBER, NULL HOME_PHONE_NUMBER, JG.EMAIL_ADDRESS EMAIL_ADDRESS, NULL ADDRESS1, NULL ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL POSTAL_CODE, NULL STATE, NULL PROVINCE, NULL COUNTY, NULL COUNTRY FROM CSI_T_PARTY_DETAILS CIP, CSI_T_PARTY_DETAILS CIPO, JTF_RS_GROUPS_VL JG, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE CIP.CONTACT_FLAG = 'Y' AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID AND CIP.PARTY_SOURCE_ID = JG.GROUP_ID AND CIP.PARTY_SOURCE_TABLE = 'GROUP' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+) AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE'

View Text - HTML Formatted

SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_SOURCE_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, DECODE( HCP_WP.PHONE_COUNTRY_CODE
, NULL
, NULL
, HCP_WP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_WP.PHONE_AREA_CODE
, NULL
, NULL
, '(' || HCP_WP.PHONE_AREA_CODE || ')' ) || HCP_WP.PHONE_NUMBER || DECODE( HCP_WP.PHONE_EXTENSION
, NULL
, NULL
, 'X' || HCP_WP.PHONE_EXTENSION) WORK_PHONE_NUMBER
, DECODE( HCP_HP.PHONE_AREA_CODE
, NULL
, NULL
, HCP_HP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_HP.PHONE_AREA_CODE
, NULL
, NULL
, '(' || HCP_HP.PHONE_AREA_CODE || ')' ) || HCP_HP.PHONE_NUMBER || DECODE( HCP_HP.PHONE_EXTENSION
, NULL
, NULL
, 'X' || HCP_HP.PHONE_EXTENSION ) HOME_PHONE_NUMBER
, HCP_EM.EMAIL_ADDRESS EMAIL_ADDRESS
, HP.ADDRESS1 ADDRESS1
, HP.ADDRESS2 ADDRESS2
, HP.ADDRESS3 ADDRESS3
, HP.ADDRESS4 ADDRESS4
, HP.CITY CITY
, HP.POSTAL_CODE POSTAL_CODE
, HP.STATE STATE
, HP.PROVINCE PROVINCE
, HP.COUNTY COUNTY
, HP.COUNTRY COUNTRY
FROM CSI_T_PARTY_DETAILS CIP
, HZ_PARTIES HP
, CSI_IPA_RELATION_TYPES CIR
, HZ_CONTACT_POINTS HCP_WP
, HZ_CONTACT_POINTS HCP_HP
, HZ_CONTACT_POINTS HCP_EM
, CSI_LOOKUPS CL
, HZ_RELATIONSHIPS HR
, CSI_T_PARTY_DETAILS CIPO
WHERE CIP.PARTY_SOURCE_ID = HR.SUBJECT_ID
AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID
AND CIPO.PARTY_SOURCE_ID = HR.OBJECT_ID
AND HR.SUBJECT_ID = HP.PARTY_ID
AND CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND HCP_WP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_WP.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_WP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP_WP.PRIMARY_FLAG (+) = 'Y'
AND HCP_HP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_HP.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_HP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP_HP.PRIMARY_FLAG (+) = 'Y'
AND HCP_HP.CONTACT_POINT_PURPOSE (+) = 'PERSONAL'
AND HCP_EM.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_EM.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_EM.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND HCP_EM.PRIMARY_FLAG (+) = 'Y'
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_SOURCE_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPADTED_BY
, CIP.LAST_UPDATE_DATE LAST_UPADTE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, PAP.FULL_NAME PARTY_NAME
, PAP.EMPLOYEE_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, PP.PHONE_NUMBER WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, PAP.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_T_PARTY_DETAILS CIP
, CSI_T_PARTY_DETAILS CIPO
, PER_PHONES PP
, PER_ALL_PEOPLE_F PAP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID
AND CIP.PARTY_SOURCE_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND CIP.PARTY_SOURCE_TABLE = 'EMPLOYEE'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PAP.PERSON_ID = PP.PARENT_ID(+)
AND PP.PHONE_TYPE(+) = 'W1'
AND SYSDATE BETWEEN PP.DATE_FROM(+)
AND NVL(PP.DATE_TO(+)
, SYSDATE)
AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_SOURCE_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, PVC.PREFIX || ' ' || PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' '|| PVC.LAST_NAME PARTY_NAME
, NULL PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, PVC.AREA_CODE || ' ' || PVC.PHONE WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, PVC.MAIL_STOP EMAIL_ADDRESS
, PVS.ADDRESS_LINE1 ADDRESS1
, PVS.ADDRESS_LINE2 ADDRESS2
, PVS.ADDRESS_LINE3 ADDRESS3
, NULL ADDRESS4
, PVS.CITY CITY
, PVS.ZIP POSTAL_CODE
, PVS.STATE STATE
, PVS.PROVINCE PROVINCE
, NULL COUNTY
, PVS.COUNTRY COUNTRY
FROM CSI_T_PARTY_DETAILS CIP
, CSI_T_PARTY_DETAILS CIPO
, PO_VENDOR_CONTACTS PVC
, PO_VENDOR_SITES_ALL PVS
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID
AND CIP.PARTY_SOURCE_TABLE = 'PO_VENDORS'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND CIP.PARTY_SOURCE_ID = PVC.VENDOR_CONTACT_ID
AND PVS.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_SOURCE_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, JT.TEAM_NAME PARTY_NAME
, JT.TEAM_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, NULL WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, JT.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_T_PARTY_DETAILS CIP
, CSI_T_PARTY_DETAILS CIPO
, JTF_RS_TEAMS_VL JT
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID
AND CIP.PARTY_SOURCE_ID = JT.TEAM_ID
AND CIP.PARTY_SOURCE_TABLE = 'TEAM'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE' UNION ALL SELECT CIP.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, CIP.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_SOURCE_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_PARTY_ID CONTACT_PARTY_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, JG.GROUP_NAME PARTY_NAME
, JG.GROUP_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, NULL WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, JG.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_T_PARTY_DETAILS CIP
, CSI_T_PARTY_DETAILS CIPO
, JTF_RS_GROUPS_VL JG
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.CONTACT_PARTY_ID = CIPO.TXN_PARTY_DETAIL_ID
AND CIP.PARTY_SOURCE_ID = JG.GROUP_ID
AND CIP.PARTY_SOURCE_TABLE = 'GROUP'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE'