Oracle Customer Master Extract Query

SELECT GLL.NAME Ledger_name,
       HP.PARTY_NAME CUSTOMER_NAME,
       HP.PARTY_NUMBER CUSTOMER_NUMBER,
       HCA.ACCOUNT_NUMBER Customer_account_number,
        hps.party_site_name,
        hps.party_site_number,       
       (select TERRITORY_SHORT_NAME from FND_TERRITORIES_TL where TERRITORY_CODE =hl.COUNTRY and language = 'US') site_COUNTRY,       
        HL.ADDRESS1,
        HL.ADDRESS2,
        HL.ADDRESS3,
        HL.ADDRESS4,
        HL.CITY,
        hl.county,
        hl.state,
        hl.POSTAL_CODE,
        HROU.NAME OPERATING_UNIT,       
       hps.party_site_name,     
       hl.city site_name,
       HCSUB.SITE_USE_CODE BILL_TO_CODE,
        HCSUB.PRIMARY_FLAG BILL_TO_FLAG,
       HCSUB.LOCATION BILL_TO_LOCATION,     
       HCSUS.SITE_USE_CODE SHIP_TO_CODE,
       HCSUS.PRIMARY_FLAG SHIP_TO_FLAG,   
       HCSUS.LOCATION SHIP_TO_LOCATION
  FROM APPS.HZ_PARTIES HP,
       GL_LEDGERS GLL,
       APPS.HZ_PARTY_SITES HPS,
       APPS.HZ_LOCATIONS HL,
       APPS.HZ_CUST_ACCOUNTS_ALL HCA,
       APPS.HZ_CUST_ACCT_SITES_ALL HCSA,
       APPS.HZ_CUST_SITE_USES_ALL HCSUS,
       HZ_CUST_SITE_USES_ALL HCSUB,
       HR_OPERATING_UNITS HROU
 WHERE     HP.PARTY_ID = HPS.PARTY_ID
       AND HROU.SET_OF_BOOKS_ID = GLL.LEDGER_ID
       AND HPS.LOCATION_ID = HL.LOCATION_ID
       AND HCSA.ORG_ID = HROU.ORGANIZATION_ID
       AND HP.PARTY_ID = HCA.PARTY_ID
       AND HCSA.PARTY_SITE_ID(+) = HPS.PARTY_SITE_ID
       AND HCSUS.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID
       AND HCSUB.CUST_ACCT_SITE_ID(+) = HCSA.CUST_ACCT_SITE_ID
       AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
       AND HCSUS.SITE_USE_CODE(+) = 'SHIP_TO'
       AND HCSUB.SITE_USE_CODE(+) = 'BILL_TO'
--       and hp.party_name like 'BAY%SHORE%'
 --    AND GLL.NAME LIKE '%MCC%'