View: CSD_HZ_REL_V

Product:	CSD - Depot Repair
Description:	View for details of Related, Subject, Object party. Used in the Depot Repair Workbench.
Implementation/DBA Data:	APPS.CSD_HZ_REL_V

View Text

SELECT REL_PARTY.PARTY_ID REL_PARTY_ID
, REL_PARTY.PARTY_NUMBER REL_PARTY_NUMBER
, REL_PARTY.PARTY_NAME REL_PARTY_NAME
, REL_PARTY.PARTY_TYPE REL_PARTY_TYPE
, REL_PARTY.LAST_UPDATE_DATE REL_LAST_UPDATE_DATE
, REL_PARTY.PERSON_PRE_NAME_ADJUNCT REL_PERSON_PRE_NAME_ADJUNCT
, REL_PARTY.PERSON_FIRST_NAME REL_PERSON_FIRST_NAME
, REL_PARTY.PERSON_MIDDLE_NAME REL_PERSON_MIDDLE_NAME
, REL_PARTY.PERSON_LAST_NAME REL_PERSON_LAST_NAME
, REL_PARTY.PERSON_NAME_SUFFIX REL_PERSON_NAME_SUFFIX
, REL_PARTY.COUNTRY REL_COUNTRY
, REL_PARTY.ADDRESS1 REL_ADDRESS1
, REL_PARTY.ADDRESS2 REL_ADDRESS2
, REL_PARTY.ADDRESS3 REL_ADDRESS3
, REL_PARTY.ADDRESS4 REL_ADDRESS4
, REL_PARTY.CITY REL_CITY
, REL_PARTY.POSTAL_CODE REL_POSTAL_CODE
, REL_PARTY.STATE REL_STATE
, REL_PARTY.PROVINCE REL_PROVINCE
, REL_PARTY.STATUS REL_STATUS
, REL_PARTY.EMAIL_ADDRESS REL_EMAIL_ADDRESS
, REL_PARTY.ADDRESS1||DECODE(REL_PARTY.ADDRESS2
, NULL
, NULL
, ';'||REL_PARTY.ADDRESS2 || DECODE(REL_PARTY.ADDRESS3
, NULL
, NULL
, ';'||REL_PARTY.ADDRESS3|| DECODE(REL_PARTY.ADDRESS4
, NULL
, NULL
, ';'||REL_PARTY.ADDRESS4))) REL_ADDRESS
, REL_PARTY.LANGUAGE_NAME REL_LANGUAGE_NAME
, REL_PARTY.COUNTY REL_COUNTY
, SUB_PARTY.PARTY_ID SUB_PARTY_ID
, SUB_PARTY.PARTY_NUMBER SUB_PARTY_NUMBER
, SUB_PARTY.PARTY_NAME SUB_PARTY_NAME
, SUB_PARTY.PARTY_TYPE SUB_PARTY_TYPE
, SUB_PARTY.LAST_UPDATE_DATE SUB_LAST_UPDATE_DATE
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT SUB_PERSON_PRE_NAME_ADJUNCT
, SUB_PARTY.PERSON_FIRST_NAME SUB_PERSON_FIRST_NAME
, SUB_PARTY.PERSON_MIDDLE_NAME SUB_PERSON_MIDDLE_NAME
, SUB_PARTY.PERSON_LAST_NAME SUB_PERSON_LAST_NAME
, SUB_PARTY.PERSON_NAME_SUFFIX SUB_PERSON_NAME_SUFFIX
, SUB_PARTY.COUNTRY SUB_COUNTRY
, SUB_PARTY.ADDRESS1 SUB_ADDRESS1
, SUB_PARTY.ADDRESS2 SUB_ADDRESS2
, SUB_PARTY.ADDRESS3 SUB_ADDRESS3
, SUB_PARTY.ADDRESS4 SUB_ADDRESS4
, SUB_PARTY.CITY SUB_CITY
, SUB_PARTY.POSTAL_CODE SUB_POSTAL_CODE
, SUB_PARTY.STATE SUB_STATE
, SUB_PARTY.PROVINCE SUB_PROVINCE
, SUB_PARTY.STATUS SUB_STATUS
, SUB_PARTY.EMAIL_ADDRESS SUB_EMAIL_ADDRESS
, SUB_PARTY.ADDRESS1||DECODE(SUB_PARTY.ADDRESS2
, NULL
, NULL
, ';'||SUB_PARTY.ADDRESS2 || DECODE(SUB_PARTY.ADDRESS3
, NULL
, NULL
, ';'||SUB_PARTY.ADDRESS3|| DECODE(SUB_PARTY.ADDRESS4
, NULL
, NULL
, ';'||SUB_PARTY.ADDRESS4))) SUB_ADDRESS
, SUB_PARTY.LANGUAGE_NAME SUB_LANGUAGE_NAME
, SUB_PARTY.COUNTY SUB_COUNTY
, OBJ_PARTY.PARTY_ID OBJ_PARTY_ID
, OBJ_PARTY.PARTY_NUMBER OBJ_PARTY_NUMBER
, OBJ_PARTY.PARTY_NAME OBJ_PARTY_NAME
, OBJ_PARTY.PARTY_TYPE OBJ_PARTY_TYPE
, OBJ_PARTY.LAST_UPDATE_DATE OBJ_LAST_UPDATE_DATE
, OBJ_PARTY.PERSON_PRE_NAME_ADJUNCT OBJ_PERSON_PRE_NAME_ADJUNCT
, OBJ_PARTY.PERSON_FIRST_NAME OBJ_PERSON_FIRST_NAME
, OBJ_PARTY.PERSON_MIDDLE_NAME OBJ_PERSON_MIDDLE_NAME
, OBJ_PARTY.PERSON_LAST_NAME OBJ_PERSON_LAST_NAME
, OBJ_PARTY.PERSON_NAME_SUFFIX OBJ_PERSON_NAME_SUFFIX
, OBJ_PARTY.COUNTRY OBJ_COUNTRY
, OBJ_PARTY.ADDRESS1 OBJ_ADDRESS1
, OBJ_PARTY.ADDRESS2 OBJ_ADDRESS2
, OBJ_PARTY.ADDRESS3 OBJ_ADDRESS3
, OBJ_PARTY.ADDRESS4 OBJ_ADDRESS4
, OBJ_PARTY.CITY OBJ_CITY
, OBJ_PARTY.POSTAL_CODE OBJ_POSTAL_CODE
, OBJ_PARTY.STATE OBJ_STATE
, OBJ_PARTY.PROVINCE OBJ_PROVINCE
, OBJ_PARTY.STATUS OBJ_STATUS
, OBJ_PARTY.EMAIL_ADDRESS OBJ_EMAIL_ADDRESS
, OBJ_PARTY.ADDRESS1||DECODE(OBJ_PARTY.ADDRESS2
, NULL
, NULL
, ';'||OBJ_PARTY.ADDRESS2 || DECODE(OBJ_PARTY.ADDRESS3
, NULL
, NULL
, ';'||OBJ_PARTY.ADDRESS3|| DECODE(OBJ_PARTY.ADDRESS4
, NULL
, NULL
, ';'||OBJ_PARTY.ADDRESS4))) OBJ_ADDRESS
, OBJ_PARTY.LANGUAGE_NAME OBJ_LANGUAGE_NAME
, OBJ_PARTY.COUNTY OBJ_COUNTY
, RELATIONSHIP.RELATIONSHIP_ID
, RELATIONSHIP.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE
, AR.MEANING PARTY_RELATIONSHIP_MEANING
FROM HZ_PARTIES REL_PARTY
, HZ_PARTIES SUB_PARTY
, HZ_PARTIES OBJ_PARTY
, HZ_RELATIONSHIPS RELATIONSHIP
, AR_LOOKUPS AR
WHERE RELATIONSHIP.PARTY_ID = REL_PARTY.PARTY_ID AND RELATIONSHIP.SUBJECT_ID = SUB_PARTY.PARTY_ID AND RELATIONSHIP.OBJECT_ID = OBJ_PARTY.PARTY_ID AND RELATIONSHIP.STATUS = 'A' AND RELATIONSHIP.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND RELATIONSHIP.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND NVL(RELATIONSHIP.START_DATE
, SYSDATE-1) < SYSDATE AND NVL(RELATIONSHIP.END_DATE
, SYSDATE+1) > SYSDATE AND REL_PARTY.STATUS = 'A' AND SUB_PARTY.STATUS = 'A' AND OBJ_PARTY.STATUS = 'A' AND RELATIONSHIP.RELATIONSHIP_CODE = AR.LOOKUP_CODE(+) AND AR.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'

Columns