VIEW: APPS.CS_SR_HZ_RELATIONSHIPS_V

Source

View Text - Preformatted

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 PARTY_RELATIONSHIP_ID , RELATIONSHIP.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE, AR.MEANING PARTY_RELATIONSHIP_MEANING, OBJ_PARTY.KNOWN_AS OBJ_KNOWN_AS 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 (trunc(SYSDATE) BETWEEN trunc(NVL(relationship.start_date, SYSDATE)) AND trunc(NVL(relationship.end_date,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'

View Text - HTML Formatted

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 PARTY_RELATIONSHIP_ID
, RELATIONSHIP.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE
, AR.MEANING PARTY_RELATIONSHIP_MEANING
, OBJ_PARTY.KNOWN_AS OBJ_KNOWN_AS
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 (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(RELATIONSHIP.START_DATE
, SYSDATE))
AND TRUNC(NVL(RELATIONSHIP.END_DATE
, 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'