This discussion is archived
1 Reply Latest reply: Sep 16, 2010 10:07 AM by shan@rbc RSS

query to get  customer details in oracle istore

761597 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points