VIEW: APPS.OTA_CUSTOMER_CONTACTS_V

Source

View Text - Preformatted

SELECT ACCT_ROLE.CUST_ACCOUNT_ID, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID, substrb(PARTY.PERSON_FIRST_NAME,1,40), substrb(PARTY.PERSON_LAST_NAME,1,50), PARTY.PERSON_PRE_NAME_ADJUNCT TITLE, TIT.MEANING TITLE_MEANING, OTA_TDB_BUS.GET_FULL_NAME(substrb(PARTY.PERSON_LAST_NAME,1,50), TIT.MEANING,substrb(PARTY.PERSON_FIRST_NAME,1,40)), ACCT_ROLE.STATUS, ROL.RESPONSIBILITY_TYPE, ORG_CONT.MAIL_STOP , ACCT_ROLE.CUST_ACCT_SITE_ID, LOC.ADDRESS1||DECODE(LOC.ADDRESS1,NULL,'',', ')|| LOC.ADDRESS2|| DECODE(LOC.ADDRESS2,NULL,'',', ')|| LOC.ADDRESS3|| DECODE(LOC.ADDRESS3,NULL,'',', ')|| LOC.ADDRESS4|| DECODE(LOC.ADDRESS4,NULL,'',', ')|| LOC.CITY||DECODE(LOC.CITY,NULL, '',', ')|| LOC.STATE||DECODE(LOC.STATE,NULL,'',', ')|| LOC.PROVINCE|| DECODE(LOC.PROVINCE,NULL,'',', ')|| LOC.COUNTY|| DECODE(LOC.COUNTY,NULL,'',', ')|| LOC.POSTAL_CODE|| DECODE(LOC.POSTAL_CODE,NULL,'',', ')|| LOC.COUNTRY ADDRESS, NULL, NULL FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES PARTY, HZ_RELATIONSHIPS REL, HZ_ORG_CONTACTS ORG_CONT, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS ROLE_ACCT, AR_LOOKUPS TIT , HZ_ROLE_RESPONSIBILITY ROL , HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, HZ_PARTY_SITES PARTY_SITE WHERE PARTY.PERSON_PRE_NAME_ADJUNCT = TIT.LOOKUP_CODE(+) AND TIT.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND ROL.CUST_ACCOUNT_ROLE_ID (+) = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.PRIMARY_FLAG (+) = 'Y' AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = ACCT_ROLE.CUST_ACCT_SITE_ID AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+) AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID AND DECODE(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'), null, (NVL(ACCT_SITE.ORG_ID (+) , NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1) ,'  ', NULL, SUBSTRB( USERENV('CLIENT_INFO'),1,10))), -99))), 1 ) = DECODE(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID'),null, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV( 'CLIENT_INFO'),1,10))), -99)),1 )

View Text - HTML Formatted

SELECT ACCT_ROLE.CUST_ACCOUNT_ID
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, PARTY.PERSON_PRE_NAME_ADJUNCT TITLE
, TIT.MEANING TITLE_MEANING
, OTA_TDB_BUS.GET_FULL_NAME(SUBSTRB(PARTY.PERSON_LAST_NAME
, 1
, 50)
, TIT.MEANING
, SUBSTRB(PARTY.PERSON_FIRST_NAME
, 1
, 40))
, ACCT_ROLE.STATUS
, ROL.RESPONSIBILITY_TYPE
, ORG_CONT.MAIL_STOP
, ACCT_ROLE.CUST_ACCT_SITE_ID
, LOC.ADDRESS1||DECODE(LOC.ADDRESS1
, NULL
, ''
, '
, ')|| LOC.ADDRESS2|| DECODE(LOC.ADDRESS2
, NULL
, ''
, '
, ')|| LOC.ADDRESS3|| DECODE(LOC.ADDRESS3
, NULL
, ''
, '
, ')|| LOC.ADDRESS4|| DECODE(LOC.ADDRESS4
, NULL
, ''
, '
, ')|| LOC.CITY||DECODE(LOC.CITY
, NULL
, ''
, '
, ')|| LOC.STATE||DECODE(LOC.STATE
, NULL
, ''
, '
, ')|| LOC.PROVINCE|| DECODE(LOC.PROVINCE
, NULL
, ''
, '
, ')|| LOC.COUNTY|| DECODE(LOC.COUNTY
, NULL
, ''
, '
, ')|| LOC.POSTAL_CODE|| DECODE(LOC.POSTAL_CODE
, NULL
, ''
, '
, ')|| LOC.COUNTRY ADDRESS
, NULL
, NULL
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT
, AR_LOOKUPS TIT
, HZ_ROLE_RESPONSIBILITY ROL
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE PARTY.PERSON_PRE_NAME_ADJUNCT = TIT.LOOKUP_CODE(+)
AND TIT.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND ROL.CUST_ACCOUNT_ROLE_ID (+) = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.PRIMARY_FLAG (+) = 'Y'
AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = ACCT_ROLE.CUST_ACCT_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(ACCT_SITE.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )