VIEW: APPS.AR_BILL_DRAWEE_ADDRESS_V

Source

View Text - Preformatted

SELECT su.rowid , substrb(party.party_name,1,50) customer_name , cust_acct.account_number customer_number , loc.ADDRESS1 , loc.ADDRESS2 , loc.CITY||' '|| NVL(loc.STATE, loc.PROVINCE) ||' ' || loc.POSTAL_CODE|| ' ' || T.TERRITORY_SHORT_NAME , loc.ADDRESS3 , SUBSTR(loc.ADDRESS1,1,25) ||' '|| SUBSTR(loc.ADDRESS2,1,25) ||' '|| SUBSTR(loc.ADDRESS3,1,25) ||' '|| SUBSTR(loc.ADDRESS4,1,25) ||' '|| loc.CITY|| ' ' || NVL(loc.STATE, loc.PROVINCE) ||' '|| T.TERRITORY_SHORT_NAME ADDRESS , SU.LOCATION LOCATION , SU.PRIMARY_FLAG PRIMARY_FLAG , arl.meaning , loc.CITY CITY , loc.COUNTY COUNTY , loc.STATE STATE , loc.PROVINCE PROVINCE , loc.COUNTRY COUNTRY , loc.POSTAL_CODE POSTAL_CODE , SU.TERRITORY_ID TERRITORY_ID , SU.STATUS SU_STATUS , ACCT_SITE.STATUS A_STATUS , SU.SITE_USE_ID SITE_USE_ID , ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID , ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID , loc_assign.LOC_ID LOCATION_ID ,SU.PRIMARY_SALESREP_ID ,acct_role.cust_account_role_id ,substrb(party_cont.person_last_name,1,50) ||' ' || substrb(party_cont.person_first_name,1,40) ,substrb(party_cont.person_first_name,1,40) ,substrb(party_cont.person_last_name,1,50) FROM FND_TERRITORIES_VL T, HZ_CUST_SITE_USES SU, hz_cust_acct_sites acct_site, hz_party_sites party_site, hz_locations loc, hz_loc_assignments loc_assign, hz_cust_accounts cust_acct, hz_parties party, ar_lookups arl, hz_cust_account_roles acct_role, hz_parties party_cont, hz_relationships rel WHERE ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND NVL(ACCT_SITE.ORG_ID , -99) = NVL(LOC_ASSIGN.ORG_ID,-99) AND loc.COUNTRY = T.TERRITORY_CODE AND SU.SITE_USE_CODE = 'DRAWEE' AND 'A' = SU.STATUS and cust_acct.cust_account_id = acct_site.cust_account_id and cust_acct.party_id = party.party_id and arl.lookup_type = 'YES/NO' and arl.lookup_code = su.primary_flag and su.contact_id = acct_role.cust_account_role_id (+) and acct_role.party_id = rel.party_id(+) and rel.subject_table_name(+) = 'HZ_PARTIES' and rel.object_table_name(+) = 'HZ_PARTIES' and rel.directional_flag(+) = 'F' and acct_role.role_type(+) = 'CONTACT' and rel.subject_id = party_cont.party_id(+)

View Text - HTML Formatted

SELECT SU.ROWID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.CITY||' '|| NVL(LOC.STATE
, LOC.PROVINCE) ||' ' || LOC.POSTAL_CODE|| ' ' || T.TERRITORY_SHORT_NAME
, LOC.ADDRESS3
, SUBSTR(LOC.ADDRESS1
, 1
, 25) ||' '|| SUBSTR(LOC.ADDRESS2
, 1
, 25) ||' '|| SUBSTR(LOC.ADDRESS3
, 1
, 25) ||' '|| SUBSTR(LOC.ADDRESS4
, 1
, 25) ||' '|| LOC.CITY|| ' ' || NVL(LOC.STATE
, LOC.PROVINCE) ||' '|| T.TERRITORY_SHORT_NAME ADDRESS
, SU.LOCATION LOCATION
, SU.PRIMARY_FLAG PRIMARY_FLAG
, ARL.MEANING
, LOC.CITY CITY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTRY COUNTRY
, LOC.POSTAL_CODE POSTAL_CODE
, SU.TERRITORY_ID TERRITORY_ID
, SU.STATUS SU_STATUS
, ACCT_SITE.STATUS A_STATUS
, SU.SITE_USE_ID SITE_USE_ID
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
, LOC_ASSIGN.LOC_ID LOCATION_ID
, SU.PRIMARY_SALESREP_ID
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50) ||' ' || SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50)
FROM FND_TERRITORIES_VL T
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS ARL
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99)
AND LOC.COUNTRY = T.TERRITORY_CODE
AND SU.SITE_USE_CODE = 'DRAWEE'
AND 'A' = SU.STATUS
AND CUST_ACCT.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND ARL.LOOKUP_TYPE = 'YES/NO'
AND ARL.LOOKUP_CODE = SU.PRIMARY_FLAG
AND SU.CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID(+)