VIEW: APPS.CSI_T_PARTY_DETAILS_V

Source

View Text - Preformatted

SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, TPD.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, TPD.PARTY_SOURCE_ID PARTY_ID, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, TPD.CONTACT_FLAG CONTACT_FLAG, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID, TPD.PRIMARY_FLAG PRIMARY_FLAG, TPD.PREFERRED_FLAG PREFERRED_FLAG, TPD.ACTIVE_START_DATE ACTIVE_START_DATE, TPD.ACTIVE_END_DATE ACTIVE_END_DATE, PV.VENDOR_NAME PARTY_NAME, PV.SEGMENT1 PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, CL.MEANING PARTY_TYPE FROM CSI_T_PARTY_DETAILS TPD, PO_VENDORS PV, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE TPD.PARTY_SOURCE_ID = PV.VENDOR_ID AND TPD.PARTY_SOURCE_TABLE = 'PO_VENDORS' AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, TPD.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, TPD.PARTY_SOURCE_ID PARTY_ID, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, TPD.CONTACT_FLAG CONTACT_FLAG, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID, TPD.PRIMARY_FLAG PRIMARY_FLAG, TPD.PREFERRED_FLAG PREFERRED_FLAG, TPD.ACTIVE_START_DATE ACTIVE_START_DATE, TPD.ACTIVE_END_DATE ACTIVE_END_DATE, PAP.FULL_NAME PARTY_NAME, PAP.EMPLOYEE_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, CL.MEANING PARTY_TYPE FROM CSI_T_PARTY_DETAILS TPD, PER_ALL_PEOPLE_F PAP, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE TPD.PARTY_SOURCE_ID = PAP.PERSON_ID AND PAP.EFFECTIVE_START_DATE <= SYSDATE AND PAP.EFFECTIVE_END_DATE >= SYSDATE AND TPD.PARTY_SOURCE_TABLE = 'EMPLOYEE' AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, TPD.PARTY_SOURCE_ID PARTY_ID, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, TPD.CONTACT_FLAG CONTACT_FLAG, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID, TPD.PRIMARY_FLAG PRIMARY_FLAG, TPD.PREFERRED_FLAG PREFERRED_FLAG, TPD.ACTIVE_START_DATE ACTIVE_START_DATE, TPD.ACTIVE_END_DATE CTIVE_END_DATE, HP.PARTY_NAME PARTY_NAME, HP.PARTY_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, CL.MEANING PARTY_TYPE FROM CSI_T_PARTY_DETAILS TPD, HZ_PARTIES HP, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE TPD.PARTY_SOURCE_ID = HP.PARTY_ID AND TPD.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, TPD.PARTY_SOURCE_ID PARTY_ID, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, TPD.CONTACT_FLAG CONTACT_FLAG, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID, TPD.PRIMARY_FLAG PRIMARY_FLAG, TPD.PREFERRED_FLAG PREFERRED_FLAG, TPD.ACTIVE_START_DATE ACTIVE_START_DATE, TPD.ACTIVE_END_DATE ACTIVE_END_DATE, JT.TEAM_NAME PARTY_NAME, JT.TEAM_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, CL.MEANING PARTY_TYPE FROM CSI_T_PARTY_DETAILS TPD, JTF_RS_TEAMS_VL JT, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE TPD.PARTY_SOURCE_ID = JT.TEAM_ID AND TPD.PARTY_SOURCE_TABLE = 'TEAM' AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE, TPD.PARTY_SOURCE_ID PARTY_ID, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, TPD.CONTACT_FLAG CONTACT_FLAG, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID, TPD.PRIMARY_FLAG PRIMARY_FLAG, TPD.PREFERRED_FLAG PREFERRED_FLAG, TPD.ACTIVE_START_DATE ACTIVE_START_DATE, TPD.ACTIVE_END_DATE ACTIVE_END_DATE, JG.GROUP_NAME PARTY_NAME, JG.GROUP_NUMBER PARTY_NUMBER, CIR.NAME RELATIONSHIP_NAME, CIR.DESCRIPTION RELATIONSHIP_DESCR, CL.MEANING PARTY_TYPE FROM CSI_T_PARTY_DETAILS TPD, JTF_RS_GROUPS_VL JG, CSI_IPA_RELATION_TYPES CIR, CSI_LOOKUPS CL WHERE TPD.PARTY_SOURCE_ID = JG.GROUP_ID AND TPD.PARTY_SOURCE_TABLE = 'GROUP' AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE

View Text - HTML Formatted

SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, TPD.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, TPD.PARTY_SOURCE_ID PARTY_ID
, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, TPD.CONTACT_FLAG CONTACT_FLAG
, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID
, TPD.PRIMARY_FLAG PRIMARY_FLAG
, TPD.PREFERRED_FLAG PREFERRED_FLAG
, TPD.ACTIVE_START_DATE ACTIVE_START_DATE
, TPD.ACTIVE_END_DATE ACTIVE_END_DATE
, PV.VENDOR_NAME PARTY_NAME
, PV.SEGMENT1 PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_T_PARTY_DETAILS TPD
, PO_VENDORS PV
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE TPD.PARTY_SOURCE_ID = PV.VENDOR_ID
AND TPD.PARTY_SOURCE_TABLE = 'PO_VENDORS'
AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE
AND CIR.PARTY_USE_FLAG = 'Y'
AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE'
AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, TPD.TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, TPD.PARTY_SOURCE_ID PARTY_ID
, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, TPD.CONTACT_FLAG CONTACT_FLAG
, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID
, TPD.PRIMARY_FLAG PRIMARY_FLAG
, TPD.PREFERRED_FLAG PREFERRED_FLAG
, TPD.ACTIVE_START_DATE ACTIVE_START_DATE
, TPD.ACTIVE_END_DATE ACTIVE_END_DATE
, PAP.FULL_NAME PARTY_NAME
, PAP.EMPLOYEE_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_T_PARTY_DETAILS TPD
, PER_ALL_PEOPLE_F PAP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE TPD.PARTY_SOURCE_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= SYSDATE
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND TPD.PARTY_SOURCE_TABLE = 'EMPLOYEE'
AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE
AND CIR.PARTY_USE_FLAG = 'Y'
AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE'
AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, TPD.PARTY_SOURCE_ID PARTY_ID
, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, TPD.CONTACT_FLAG CONTACT_FLAG
, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID
, TPD.PRIMARY_FLAG PRIMARY_FLAG
, TPD.PREFERRED_FLAG PREFERRED_FLAG
, TPD.ACTIVE_START_DATE ACTIVE_START_DATE
, TPD.ACTIVE_END_DATE CTIVE_END_DATE
, HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_T_PARTY_DETAILS TPD
, HZ_PARTIES HP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE TPD.PARTY_SOURCE_ID = HP.PARTY_ID
AND TPD.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE
AND CIR.PARTY_USE_FLAG = 'Y'
AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE'
AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, TPD.PARTY_SOURCE_ID PARTY_ID
, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, TPD.CONTACT_FLAG CONTACT_FLAG
, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID
, TPD.PRIMARY_FLAG PRIMARY_FLAG
, TPD.PREFERRED_FLAG PREFERRED_FLAG
, TPD.ACTIVE_START_DATE ACTIVE_START_DATE
, TPD.ACTIVE_END_DATE ACTIVE_END_DATE
, JT.TEAM_NAME PARTY_NAME
, JT.TEAM_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_T_PARTY_DETAILS TPD
, JTF_RS_TEAMS_VL JT
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE TPD.PARTY_SOURCE_ID = JT.TEAM_ID
AND TPD.PARTY_SOURCE_TABLE = 'TEAM'
AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE
AND CIR.PARTY_USE_FLAG = 'Y'
AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE'
AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE UNION ALL SELECT TPD.TXN_PARTY_DETAIL_ID TXN_PARTY_DETAIL_ID
, TPD. TXN_LINE_DETAIL_ID TXN_LINE_DETAIL_ID
, TPD.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, TPD.PARTY_SOURCE_ID PARTY_ID
, TPD.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, TPD.CONTACT_FLAG CONTACT_FLAG
, TPD.CONTACT_PARTY_ID CONTACT_PARTY_ID
, TPD.PRIMARY_FLAG PRIMARY_FLAG
, TPD.PREFERRED_FLAG PREFERRED_FLAG
, TPD.ACTIVE_START_DATE ACTIVE_START_DATE
, TPD.ACTIVE_END_DATE ACTIVE_END_DATE
, JG.GROUP_NAME PARTY_NAME
, JG.GROUP_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_T_PARTY_DETAILS TPD
, JTF_RS_GROUPS_VL JG
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE TPD.PARTY_SOURCE_ID = JG.GROUP_ID
AND TPD.PARTY_SOURCE_TABLE = 'GROUP'
AND TPD.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE
AND CIR.PARTY_USE_FLAG = 'Y'
AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE'
AND CL.LOOKUP_CODE = TPD.PARTY_SOURCE_TABLE