FND Design Data [Home] [Help]

View: AR_CMGT_PARTY_ACCOUNTS

Product: AR - Receivables
Description:
Implementation/DBA Data: ViewAPPS.AR_CMGT_PARTY_ACCOUNTS
View Text

SELECT CA.ACCOUNT_NUMBER
, CA.ACCOUNT_NAME
, CA.PARTY_ID
, ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, LOC.COUNTRY ) CONCATENATED_ADDRESS
, ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL ) ADDRESS
, LOC.CITY CITY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.COUNTRY COUNTRY
, DECODE(CAS.BILL_TO_FLAG
, 'Y'
, 'BILL_TO'
, 'P'
, 'BILL_TO'
, DECODE(CAS.MARKET_FLAG
, 'Y'
, 'MARKET'
, 'P'
, 'MARKET'
, DECODE(CAS.SHIP_TO_FLAG
, 'Y'
, 'SHIP_TO'
, 'P'
, 'SHIP_TO'
, NULL))) SITE_USE
, 'SITE' DETAILS_LEVEL
, CAS.CUST_ACCT_SITE_ID
, CA.CUST_ACCOUNT_ID
, HZP.PARTY_NAME
, HZP.PARTY_NUMBER
, PARTY_SITE.PARTY_SITE_NUMBER
, CSU.SITE_USE_ID
, CSU.PRIMARY_SALESREP_ID
, CSU.ORG_ID
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS CA
, HZ_CUST_ACCT_SITES_ALL CAS
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES PARTY_SITE
, HZ_CUST_SITE_USES_ALL CSU
WHERE HZP.PARTY_ID = CA.PARTY_ID
AND CA.STATUS = 'A'
AND CAS.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CAS.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CAS.BILL_TO_FLAG IN ('Y'
, 'P')
AND CAS.STATUS = 'A'
AND CSU.SITE_USE_CODE = 'BILL_TO'
AND CSU.CUST_ACCT_SITE_ID(+) = CAS.CUST_ACCT_SITE_ID UNION ALL SELECT DISTINCT CA.ACCOUNT_NUMBER
, CA.ACCOUNT_NAME
, CA.PARTY_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'ACCOUNT' DETAILS_LEVEL
, -99
, CA.CUST_ACCOUNT_ID
, HZP.PARTY_NAME
, HZP.PARTY_NUMBER
, NULL
, -99
, CA.PRIMARY_SALESREP_ID
, -99
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS CA
, HZ_CUST_ACCT_SITES_ALL CAS
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES PARTY_SITE
WHERE HZP.PARTY_ID = CA.PARTY_ID
AND CAS.CUST_ACCOUNT_ID =CA.CUST_ACCOUNT_ID
AND CAS.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID

Columns

Name
ACCOUNT_NUMBER
ACCOUNT_NAME
PARTY_ID
CONCATENATED_ADDRESS
ADDRESS
CITY
COUNTY
STATE
PROVINCE
POSTAL_CODE
COUNTRY
SITE_USE
DETAILS_LEVEL
CUST_ACCT_SITE_ID
CUST_ACCOUNT_ID
PARTY_NAME
PARTY_NUMBER
PARTY_SITE_NUMBER
SITE_USE_ID
PRIMARY_SALESREP_ID
ORG_ID