VIEW: APPS.ARFV_CUSTOMER_SITES

Source

View Text - Preformatted

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

View Text - HTML Formatted

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