1 Reply Latest reply: Sep 16, 2010 12:07 PM by shan@rbc RSS

    query to get  customer details in oracle istore

    761597
      Hi all ,

      Please help me how to get the customer contact(bill to and ship to), customer name , and address, from database using query in oracle istore.


      Thanks
        • 1. Re: query to get  customer details in oracle istore
          shan@rbc
          Hey using this below query u can get the Shipto and bill to address for an customer in iStore page

          SELECT DISTINCT
          HCSUA.SITE_USE_ID AS SHIP_TO_ORG_ID,
          (HL.ADDRESS1 || CASE WHEN TRIM(HL.ADDRESS1) IS NULL THEN '' ELSE ', ' END ||
          HL.ADDRESS2 || CASE WHEN TRIM(HL.ADDRESS2) IS NULL THEN '' ELSE ', ' END ||
          HL.ADDRESS3 || CASE WHEN TRIM(HL.ADDRESS3) IS NULL THEN '' ELSE ', ' END ||
          HL.ADDRESS4 || CASE WHEN TRIM(HL.ADDRESS4) IS NULL THEN '' ELSE ', ' END ||
          HL.CITY || CASE WHEN TRIM(HL.CITY) IS NULL THEN '' ELSE ', ' END ||
          HL.POSTAL_CODE || CASE WHEN TRIM(HL.POSTAL_CODE) IS NULL THEN '' ELSE ', ' END ||
          HL.STATE) AS SHIP_TO_ADDRESS
          FROM
          HZ_LOCATIONS HL,HZ_CUST_SITE_USES_ALL HCSUA, HZ_CUST_ACCT_SITES_ALL HCASA,HZ_PARTY_SITES HPS
          WHERE
          HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID AND
          HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
          HPS.LOCATION_ID = HL.LOCATION_ID AND
          HCSUA.SITE_USE_CODE = 'SHIP_TO' AND
          HCASA.CUST_ACCOUNT_ID = 1140

          Thanks
          Shan