View: ARFV_CUSTOMER_SITES

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

View Text

SELECT /* UNIQUE ATTRIBUTES */ SUA.SITE_USE_ID
, /* REGULAR ATTRIBUTES */ '_LA:SUA.SITE_USE_CODE:AR_LOOKUPS:SITE_USE_CODE:MEANING'
, SUA.LOCATION
, '_LA:SUA.PRIMARY_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:SUA.STATUS:AR_LOOKUPS:CODE_STATUS:MEANING'
, SUA.TAX_CODE
, SUA.TAX_REFERENCE
, SUA.SIC_CODE
, '_LA:SUA.FOB_POINT:AR_LOOKUPS:FOB:MEANING'
, '_LA:SUA.SHIP_PARTIAL:AR_LOOKUPS:YES/NO:MEANING'
, SUA.SHIP_VIA
, '_LA:SUA.GSA_INDICATOR:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:SUA.DEMAND_CLASS_CODE:FND_COMMON_LOOKUPS:DEMAND_CLASS:MEANING'
, PRL.NAME
, '_LA:SUA.FREIGHT_TERM:OE_LOOKUPS:FREIGHT_TERMS:MEANING'
, '_LA:SUA.TAX_CLASSIFICATION:AR_LOOKUPS:AR_TAX_CLASSIFICATION:MEANING'
, '_LA:SUA.TAX_HEADER_LEVEL_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:SUA.TAX_ROUNDING_RULE:AR_LOOKUPS:AR_TAX_ROUNDING_RULE:MEANING'
, AOU.NAME
, ADA.ADDRESS1 || DECODE(ADA.ADDRESS1
, NULL
, NULL
, ' ') || ADA.ADDRESS2 || DECODE(ADA.ADDRESS2
, NULL
, NULL
, ' ') || ADA.ADDRESS3 || DECODE(ADA.ADDRESS3
, NULL
, NULL
, ' ') || ADA.ADDRESS4 || DECODE(ADA.ADDRESS4
, NULL
, NULL
, '
, ') || ADA.CITY || DECODE(ADA.CITY
, NULL
, NULL
, '
, ') || ADA.STATE || DECODE(ADA.STATE
, NULL
, NULL
, ' ') || ADA.POSTAL_CODE || DECODE(FTERR.TERRITORY_SHORT_NAME
, NULL
, NULL
, '
, ') || FTERR.TERRITORY_SHORT_NAME
, TER.NAME
, CON.FIRST_NAME ||' '||CON.LAST_NAME
, ORD.ORGANIZATION_NAME
, TRI.NAME
, ORT.NAME
, SAL.NAME
, '_DF:JG:JG_RA_SITE_USES:SUA'
, '_DF:AR:RA_SITE_USES_HZ:SUA'
, /* IDS */ SUA.ORG_ID
, SUA.CUST_ACCT_SITE_ID
, SUA.PAYMENT_TERM_ID
, SUA.CONTACT_ID
, SUA.WAREHOUSE_ID
, SUA.TERRITORY_ID
, SUA.ORDER_TYPE_ID
, SUA.PRICE_LIST_ID
, SUA.PRIMARY_SALESREP_ID
, /* WHO COLUMNS */ SUA.LAST_UPDATE_DATE
, SUA.LAST_UPDATED_BY
, SUA.CREATION_DATE
, SUA.CREATED_BY
FROM SO_PRICE_LISTS PRL
, RA_TERMS TER
, ORG_ORGANIZATION_DEFINITIONS ORD
, HR_ALL_ORGANIZATION_UNITS AOU
, RA_SALESREPS SAL
, SO_ORDER_TYPES ORT
, RA_TERRITORIES TRI
, /* RA_CONTACTS CON
, */ (SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS ROLE_ACCT WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) CON
, /* RA_ADDRESSES_ALL ADA
, */ (SELECT LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.STATE STATE
, LOC.POSTAL_CODE POSTAL_CODE
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, LOC.COUNTRY COUNTRY
FROM HZ_PARTY_SITES PARTY_SITE
, /*REMOVING USE OF HZ_LOC_ASSIGNMENTS AS PART OF BUG FIX 6889187*/ 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 /*REMOVING USE OF HZ_LOC_ASSIGNMENTS AS PART OF BUG FIX 6889187*/ ) ADA
, FND_TERRITORIES_VL FTERR
, HZ_CUST_SITE_USES SUA
WHERE SUA.PRICE_LIST_ID = PRL.PRICE_LIST_ID(+)
AND SUA.ORG_ID = AOU.ORGANIZATION_ID (+)
AND SUA.CONTACT_ID = CON.CONTACT_ID(+)
AND SUA.PAYMENT_TERM_ID = TER.TERM_ID (+)
AND SUA.WAREHOUSE_ID = ORD.ORGANIZATION_ID (+)
AND SUA.TERRITORY_ID = TRI.TERRITORY_ID (+)
AND SUA.ORDER_TYPE_ID = ORT.ORDER_TYPE_ID (+)
AND SUA.PRIMARY_SALESREP_ID = SAL.SALESREP_ID (+)
AND ((SUA.PRIMARY_SALESREP_ID IS NULL) OR (SUA.PRIMARY_SALESREP_ID IS NOT NULL
AND NVL(SUA.ORG_ID
, -12345) = NVL(SAL.ORG_ID
, -12345)))
AND SUA.CUST_ACCT_SITE_ID = ADA.ADDRESS_ID (+)
AND ADA.COUNTRY = FTERR.TERRITORY_CODE (+) WITH READ ONLY

Columns