VIEW: APPS.AR_CMGT_PARTY_ACCOUNTS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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