VIEW: APPS.ARFV_LOCATIONS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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