Query to get Customer Information in Oracle Fusion

 Below is the query to get customer information

 /*Query site reference xref information */

SELECT   e.owner_table_name,
         e.owner_table_id,
         b.party_name,
         a.orig_system_reference "Customer REF",
         d.site_use_code,
         d.orig_system_reference "Site REF",
         d.primary_flag,
         d.status
FROM     hz_cust_accounts a,
         hz_parties b ,
         hz_cust_acct_sites_all c ,
         hz_cust_site_uses_all d,
         hz_orig_sys_references e
WHERE    a.party_id = b.party_id
AND      a.cust_account_id = c.cust_account_id
AND      c.cust_acct_site_id = d.cust_acct_site_id
AND      d.orig_system_reference = e.orig_system_reference
         --and
         --    upper(party_name) like  upper('%comp%')
ORDER BY 2,
         4

------------------------------------------------------------------------------------

Below is the reference query to get customer site information in oracle fusion

/*Query Customer Site Reference information*/
SELECT b.party_name,
       a.orig_system_reference "Customer REF",
       d.site_use_code,
       d.orig_system_reference "Site REF",
       d.primary_flag,
       d.status,
       f.country,
       f.address1,
       f.address2,
       f.address3,
       f.address4,
       f.city,
       f.postal_code,
       f.state,
       f.province
FROM   hz_cust_accounts a,
       hz_parties b,
       hz_cust_acct_sites_all c,
       hz_cust_site_uses_all d,
       hz_party_sites e,
       hz_locations f
WHERE  a.party_id = b.party_id
       AND a.cust_account_id = c.cust_account_id
       AND c.cust_acct_site_id = d.cust_acct_site_id
       AND c.party_site_id = e.party_site_id
       AND e.location_id = f.location_id
       AND Upper(party_name) LIKE Upper('%computer service and rentals%')
ORDER  BY 2,
          3 

-------------------------------------------------------------------------------------

*/ Query to ger customer account details in oracle fusion */

SELECT b.party_name,
       b.party_type,
       a.cust_account_id,
       b.party_id,
       a.account_number,
       a.orig_system_reference,
       a.status,
       a.account_name
FROM   hz_cust_accounts a,
       hz_parties b
WHERE  a.party_id (+) = b.party_id
       AND ( b.party_id IN ( 300000005352190, 300000001469001, 300000005352164 )
              OR Upper(a.account_name) LIKE Upper('%comp%')
              OR Upper(b.party_name) LIKE Upper('comp%') ) 

-------------------------------------------------------------------------------------

/*Query Customer location*/

SELECT HZ.party_id,
       HZ.party_name,
       LOCATIONPEO.address_style,
       LOCATIONPEO.country
       ||','
       || LOCATIONPEO.address1
       ||','
       || LOCATIONPEO.city
       ||','
       || LOCATIONPEO.postal_code ADDRESS,
       CUSTOMERACCOUNTSITEPEO.status,
       CUSTOMERACCOUNTSITEPEO.bill_to_flag,
       CUSTOMERACCOUNTSITEPEO.ship_to_flag
FROM   hz_parties HZ,
       hz_party_sites PartySitePEO,
       hz_locations LocationPEO,
       hz_cust_acct_sites_all CustomerAccountSitePEO
WHERE  HZ.party_id = PARTYSITEPEO.party_id
       AND ( PARTYSITEPEO.location_id = LOCATIONPEO.location_id )
       AND ( PARTYSITEPEO.party_site_id =
             CUSTOMERACCOUNTSITEPEO.party_site_id(+) )
       AND Upper(HZ.party_name) LIKE Upper('Comp%') 

Customer Site Information

/* Query Customer Site Information - 1 */
SELECT hz.party_name ,
       hz.party_id ,
       partysitepeo.party_site_id ,
       locationpeo.location_id ,
       locationpeo.address_style ,
       locationpeo.country
              ||','
              || locationpeo.address1
              ||','
              || locationpeo.city
              ||','
              || locationpeo.postal_code address ,
       customeraccountsitepeo.status ,
       customeraccountsitepeo.bill_to_flag ,
       customeraccountsitepeo.ship_to_flag
FROM   hz_parties HZ ,
       hz_party_sites PartySitePEO ,
       hz_locations LocationPEO ,
       hz_cust_acct_sites_all CustomerAccountSitePEO
WHERE  hz.party_id = partysitepeo.party_id
AND    (
              partysitepeo.location_id = locationpeo.location_id)
AND    (
              partysitepeo.party_site_id = customeraccountsitepeo.party_site_id(+))
AND    upper(hz.party_name) LIKE upper('Comp%')

-------------------------------------------------------------------------------------

/* Customer Site Information – 2 */

SELECT   a.party_name ,
         a.party_id ,
         a.party_number ,
         a.party_type ,
         a.orig_system_reference ,
         a.country ,
         a.address1
                  ||','
                  || city
                  ||','
                  || postal_code
                  ||','
                  || state "ACCOUNT ADDRESS" ,
         a.status "Party Status" ,
         a.iden_addr_party_site_id ,
         a.iden_addr_location_id ,
         b.party_site_id ,
         b.location_id ,
         b.orig_system_reference "Party Site Orig ref" ,
         b.party_site_number ,
         b.identifying_address_flag ,
         b.status "Party Site Status" ,
         b.party_site_name ,
         b.overall_primary_flag ,
         c.party_site_use_id ,
         c.site_use_type ,
         c.primary_per_type ,
         c.status "Party Site Use Status"
FROM     hz_parties a,
         hz_party_sites b,
         hz_party_site_uses c
WHERE    (
                  Upper(a.party_name) LIKE Upper('Comp%')
         OR       a.party_id IN (300000005352190,
                                 300000001469001,
                                 300000005352164))
AND      a.party_id = b.party_id
AND      b.party_site_id = c. party_site_id
ORDER BY a.party_name

-------------------------------------------------------------------------------------

/* Customer Site Information – 3 */

SELECT   a.party_name ,
         a.party_id ,
         a.party_number ,
         a.party_type ,
         a.orig_system_reference ,
         a.country ,
         a.address1
                  ||','
                  || city
                  ||','
                  || postal_code
                  ||','
                  || state "ACCOUNT ADDRESS" ,
         a.status ,
         a.iden_addr_party_site_id ,
         a.iden_addr_location_id ,
         b.account_name ,
         c.cust_acct_site_id ,
         c.cust_account_id ,
         c.party_site_id ,
         c.orig_system_reference "Cust Acct Site Origin REF" ,
         c.status "Cust Acct Site Status" ,
         c.set_id "Cust Acct Site Set ID" ,
         c.bill_to_flag ,
         c.market_flag ,
         c.ship_to_flag ,
         d.orig_system_reference "Cust Acct Site Use Origin REF" ,
         d.status "Cust Acct Site Use Status" ,
         d.set_id ,
         d.site_use_id ,
         d.site_use_code ,
         d.primary_flag ,
         d.location ,
         d.bill_to_site_use_id
FROM     hz_parties a,
         hz_cust_accounts b,
         hz_cust_acct_sites_all c,
         hz_cust_site_uses_all d
WHERE    (
                  Upper(a.party_name) LIKE Upper('Comp%')
         OR       a.party_id IN (300000005352190,
                                 300000001469001,
                                 300000005352164))
AND      a.party_id = b.party_id (+)
AND      c.cust_account_id = b.cust_account_id
AND      c.cust_acct_site_id = d.cust_acct_site_id
ORDER BY a.party_name

-------------------------------------------------------------------------------------