1 Reply Latest reply: Sep 11, 2010 12:01 PM by azodpe 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
          azodpe
          I have not worked on istore, but you can access this information using TCA tables.
          These tables are under AR schema and start with HZ

          -- FOLLOWING IS A QUERY TO GET CUSTOMERS
          SELECT
          hp.party_id,
          hp.party_name,
          hp.party_number registry_id,
          hl.address1,
          hl.address2,
          hl.address3,
          hl.city,
          hl.county,
          hl.state,
          hl.postal_code,
          hl.country
          FROM
          hz_locations hl ,
          hz_party_sites hps ,
          hz_cust_accounts_all hca,
          hz_parties hp
          WHERE 1 = 1
          AND hl.location_id = hps.location_id
          and hca.party_id = hp.party_id
          and hp.party_id = hps.party_id
          and hps.identifying_address_flag = 'Y'
          and party_type='ORGANIZATION'

          following query is for contacts and you will have twik these queries as per your env

          SELECT 'a' sort,
          hp.party_id,
          hp.party_number "registry id",
          hp.party_name,
          HZ_PARTIES.party_name "contact name",
          HZ_ORG_CONTACTS.job_title,
          hz_org_contacts.job_title_code,
          RelationshipParty.email_address email,
          HzPuiRelationshipsEO.start_date,
          REL_GROUP_CODE,
          HzPuiRelationshipsEO.STATUS,
          decode(nvl(RelationshipParty.primary_phone_country_code, 'N'), 'N','',
          '+ '||RelationshipParty.primary_phone_country_code)||
          decode(nvl(RelationshipParty.primary_phone_area_code, 'N'), 'N', '', ' ('||RelationshipParty.primary_phone_area_code||') ')||
          RelationshipParty.primary_phone_number||
          decode(nvl(RelationshipParty.primary_phone_extension,'N'), 'N','', ' Ext. '||RelationshipParty.primary_phone_extension)||
          decode(nvl(RelationshipParty.primary_phone_line_type,'N'), 'N', '', ' ('||RelationshipParty.primary_phone_line_type||')') phone
          FROM HZ_RELATIONSHIPS HzPuiRelationshipsEO,
          HZ_RELATIONSHIP_GROUPINGS_V,
          HZ_PARTIES,
          HZ_PARTIES HP,
          HZ_PARTY_SITES,
          HZ_ORG_CONTACTS,
          HZ_PARTIES RelationshipParty,
          fnd_lookup_values fnd1
          WHERE 1=1
          AND HzPuiRelationshipsEO.relationship_type = HZ_RELATIONSHIP_GROUPINGS_V.relationship_type
          AND HzPuiRelationshipsEO.relationship_code = HZ_RELATIONSHIP_GROUPINGS_V.backward_rel_code
          AND HzPuiRelationshipsEO.directional_flag = 'B'
          AND HZ_PARTIES.party_id = HzPuiRelationshipsEO.object_id
          AND HZ_PARTY_SITES.party_id(+) = HzPuiRelationshipsEO.subject_id
          AND HZ_PARTY_SITES.identifying_address_flag(+) = 'Y'
          AND hp.party_id = HzPuiRelationshipsEO.subject_id
          and hp.status = 'A'
          and hz_parties.status = 'A'
          AND HzPuiRelationshipsEO.relationship_id = hz_org_contacts.party_relationship_id(+)
          AND HzPuiRelationshipsEO.subject_id <> HzPuiRelationshipsEO.object_id
          AND HzPuiRelationshipsEO.object_type = HZ_RELATIONSHIP_GROUPINGS_V.subject_type
          AND HzPuiRelationshipsEO.subject_type = HZ_RELATIONSHIP_GROUPINGS_V.object_type
          AND HzPuiRelationshipsEO.party_id = RelationshipParty.party_id
          AND fnd1.view_application_id(+) = 222
          AND fnd1.language(+) = USERENV ('LANG')
          AND fnd1.lookup_type(+) = 'RESPONSIBILITY'
          AND HzPuiRelationshipsEO.end_date > sysdate
          AND fnd1.lookup_code(+) = HZ_ORG_CONTACTS.job_title_code
          and rel_group_code='PARTY_REL_GRP_CONTACTS'