View: ARFV_LOCATIONS

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.ARFV_LOCATIONS

View Text

SELECT /* UNIQUE ATTRIBUTES */ ADDRA.ADDRESS_ID
, /* REGULAR ATTRIBUTES */ '_LA:ADDRA.STATUS:AR_LOOKUPS:CODE_STATUS:MEANING'
, ADDRA.ADDRESS1 || DECODE(ADDRA.ADDRESS1
, NULL
, NULL
, ' ') || ADDRA.ADDRESS2 || DECODE(ADDRA.ADDRESS2
, NULL
, NULL
, ' ') || ADDRA.ADDRESS3 || DECODE(ADDRA.ADDRESS3
, NULL
, NULL
, ' ') || ADDRA.ADDRESS4
, ADDRA.CITY
, ADDRA.STATE
, ADDRA.POSTAL_CODE
, ADDRA.PROVINCE
, FTERR.TERRITORY_SHORT_NAME
, ADDRA.ORIG_SYSTEM_REFERENCE
, ADDRA.ECE_TP_LOCATION_CODE
, '_LA:DECODE(ADDRA.BILL_TO_FLAG
, ''P''
, ''Y''
, ''N''):AR_LOOKUPS:YES/NO:MEANING'
, /* RESOLVED FK RESOLUTIONS */ ALOU.NAME
, CUST.CUSTOMER_NAME
, TERR.NAME
, /* IDS */ ADDRA.ORG_ID
, ADDRA.CUSTOMER_ID
, ADDRA.LOCATION_ID
, ADDRA.TERRITORY_ID
, /* WHO COLUMNS */ ADDRA.LAST_UPDATE_DATE
, ADDRA.LAST_UPDATED_BY
, ADDRA.CREATION_DATE
, ADDRA.CREATED_BY
FROM HR_ALL_ORGANIZATION_UNITS ALOU
, FND_TERRITORIES_VL FTERR
, RA_TERRITORIES TERR
, /* RA_ADDRESSES_ALL ADDRA
, */ (SELECT ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_SITE.STATUS STATUS
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.STATE STATE
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.PROVINCE PROVINCE
, ACCT_SITE.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, ACCT_SITE.ECE_TP_LOCATION_CODE ECE_TP_LOCATION_CODE
, ACCT_SITE.BILL_TO_FLAG BILL_TO_FLAG
, ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
, LOC_ASSIGN.LOC_ID LOCATION_ID
, ACCT_SITE.TERRITORY_ID TERRITORY_ID
, ACCT_SITE.LAST_UPDATED_BY LAST_UPDATED_BY
, ACCT_SITE.CREATION_DATE CREATION_DATE
, ACCT_SITE.CREATED_BY CREATED_BY
, ACCT_SITE.ORG_ID ORG_ID
, LOC.COUNTRY COUNTRY
, ACCT_SITE.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM HZ_PARTY_SITES PARTY_SITE
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES ACCT_SITE
WHERE 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) ) ADDRA
, /* RA_CUSTOMERS CUST */ (SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) CUST
WHERE ADDRA.CUSTOMER_ID = CUST.CUSTOMER_ID (+)
AND ADDRA.ORG_ID = ALOU.ORGANIZATION_ID (+)
AND ADDRA.TERRITORY_ID = TERR.TERRITORY_ID (+)
AND ADDRA.COUNTRY = FTERR.TERRITORY_CODE (+) WITH READ ONLY

Columns