Query to Find Customer Query in Oracle Apps R12

SELECT hca.account_number customer_number, hp.party_name customer_name,
       (SELECT hcpa.currency_code
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id = hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " CURRENCY",
       hp.attribute1 " Customer Credit Limit",
       hp.attribute1 " Customer Order Limit",
       (SELECT meaning
          FROM ar_lookups
         WHERE lookup_type = 'CUSTOMER CLASS'
           AND lookup_code = hca.customer_class_code)
                                                    "Customer Classification",
       (SELECT hcpc.NAME
          FROM hz_cust_profile_classes hcpc,
               hz_customer_profiles hcp
         WHERE hcpc.profile_class_id = hcp.profile_class_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Customer Profile ",
       (SELECT NAME
          FROM ra_terms
         WHERE term_id = hca.payment_term_id) "Customer Payment Terms",
       (SELECT meaning
          FROM ar_lookups
         WHERE lookup_type = 'CUSTOMER_CATEGORY'
           AND lookup_code = hcasa.customer_category_code)
                                                          "Customer Category",
       hps.party_site_number site_number,
       (SELECT hcpa.trx_credit_limit
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id =
                                     hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " site Credit Limit",
       (SELECT hcpa.overall_credit_limit
          FROM hz_cust_profile_amts hcpa
         WHERE hcpa.cust_account_id = hca.cust_account_id
           AND hcpa.site_use_id IS NULL) " Site Order Limit",
       (SELECT hcpc.NAME
          FROM hz_cust_profile_classes hcpc,
               hz_customer_profiles hcp,
               hz_cust_site_uses_all hcsua
         WHERE hcpc.profile_class_id = hcp.profile_class_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Customer Site Profile ",
       (SELECT rt.NAME
          FROM hz_customer_profiles hcp,
               ra_terms rt,
               hz_cust_site_uses_all hcsua
         WHERE hcp.standard_terms = rt.term_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Site Payemnt Terms",
       (SELECT DISTINCT hcasa.attribute1
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                               hcasa.cust_acct_site_id)
                                                       " Customer Site Class",
       (SELECT address1
          FROM hz_locations
         WHERE location_id = hps.location_id) address1,
       (SELECT address2
          FROM hz_locations
         WHERE location_id = hps.location_id) address2,
       (SELECT address3
          FROM hz_locations
         WHERE location_id = hps.location_id) address3,
       (SELECT address4
          FROM hz_locations
         WHERE location_id = hps.location_id) address4,
       (SELECT city
          FROM hz_locations
         WHERE location_id = hps.location_id) city,
       (SELECT postal_code
          FROM hz_locations
         WHERE location_id = hps.location_id) postal_code,
       (SELECT state
          FROM hz_locations
         WHERE location_id = hps.location_id) state,
       (SELECT ftt.territory_short_name
          FROM fnd_territories_tl ftt, hz_locations hl
         WHERE hl.country = ftt.territory_code
           AND hl.location_id = hps.location_id) country,
       (SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
                    AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
       (SELECT DISTINCT hcsua.LOCATION
                   FROM hz_cust_site_uses_all hcsua
                  WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
                    AND hcsua.site_use_code = 'SHIP_TO'
  and hcsua.status='A') ship_to_location,
       (SELECT resource_name
          FROM jtf_rs_defresources_v jrd,
               hz_cust_site_uses_all hcsua
         WHERE jrd.resource_id = hcsua.primary_salesrep_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Sales Person",
       (SELECT NAME
          FROM oe_transaction_types_v ott,
               hz_cust_site_uses_all hcsua
         WHERE ott.transaction_type_id = hcsua.order_type_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Order type",
       (SELECT NAME
          FROM qp_price_lists_v qpl,
               hz_cust_site_uses_all hcsua
         WHERE qpl.price_list_id = hcsua.price_list_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') " Price List Name ",
       (SELECT organization_name
          FROM org_organization_definitions ood,
               hz_cust_site_uses_all hcsua
         WHERE ood.organization_id = hcsua.warehouse_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') "Ware House ",
       (SELECT segment1 || '.' || segment2 || '.' || segment3
          FROM ra_territories rt, hz_cust_site_uses_all hcsua
         WHERE rt.territory_id = hcsua.territory_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'SHIP_TO') territory,
       (SELECT    segment1
               || '.'
               || segment2
               || '.'
               || segment3
               || '.'
               || segment4
               || '.'
               || segment5
               || '.'
               || segment6
               || '.'
               || segment7
               || '.'
               || segment8
          FROM gl_code_combinations gcc, hz_cust_site_uses_all hcsua
         WHERE code_combination_id = hcsua.gl_id_rec
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') " Receivable Account ",
       (SELECT    segment1
               || '.'
               || segment2
               || '.'
               || segment3
               || '.'
               || segment4
               || '.'
               || segment5
               || '.'
               || segment6
               || '.'
               || segment7
               || '.'
               || segment8
          FROM gl_code_combinations gcc, hz_cust_site_uses_all hcsua
         WHERE gcc.code_combination_id = hcsua.gl_id_rev
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') " Revenue Account ",
       (SELECT ac.NAME
          FROM hz_customer_profiles hcp,
               ar_collectors ac
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id IS NULL
           AND hcp.cust_account_id = hca.cust_account_id)
                                                   "Customer Collector Code ",
       (SELECT ac.description
          FROM hz_customer_profiles hcp,
               ar_collectors ac
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id IS NULL
           AND hcp.cust_account_id = hca.cust_account_id)
                                                   "Customer Collector Name ",
       (SELECT ac.NAME
          FROM hz_customer_profiles hcp,
               ar_collectors ac,
               hz_cust_site_uses_all hcsua
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO')
                                              "Customer Site Collector Code ",
       (SELECT ac.description
          FROM hz_customer_profiles hcp,
               ar_collectors ac,
               hz_cust_site_uses_all hcsua
         WHERE hcp.collector_id = ac.collector_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO')
                                              "Customer Site Collector Name ",
       (SELECT ar.meaning
          FROM hz_customer_profiles hcp,
               ar_lookups ar
         WHERE ar.lookup_type = 'CREDIT_CLASSIFICATION'
           AND hcp.credit_classification = ar.lookup_code
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Customer Credit Class",
       (SELECT ar.meaning
          FROM hz_customer_profiles hcp,
               ar_lookups ar,
               hz_cust_site_uses_all hcsua
         WHERE ar.lookup_type = 'CREDIT_CLASSIFICATION'
           AND hcp.credit_classification = ar.lookup_code
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id = hcsua.site_use_id
           AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Customer Site Credit Class",
       (SELECT hcsua.primary_flag
          FROM hz_cust_site_uses_all hcsua
         WHERE hcsua.cust_acct_site_id =
                                    hcasa.cust_acct_site_id
           AND hcsua.site_use_code = 'BILL_TO') "Primary Bill To",
       (SELECT NAME
          FROM ar_statement_cycles astc,
               hz_customer_profiles hcp
         WHERE astc.statement_cycle_id = hcp.statement_cycle_id
           AND hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Statement Cycle",
       (SELECT hcp.cons_bill_level
          FROM hz_customer_profiles hcp
         WHERE hcp.cust_account_id = hca.cust_account_id
           AND hcp.site_use_id IS NULL) " Bill Level",
       (SELECT hcp.cons_inv_type
          FROM hz_customer_profiles hcp
         WHERE hcp.cust_account_id =
                               hca.cust_account_id
           AND hcp.site_use_id IS NULL) "Type ( Detail / Summary)",
       hca.attribute1 sec_type, hca.attribute2 bank, hca.attribute3 branch,
       hca.attribute4 doc_no, hca.attribute5 amount,
       hca.attribute6 issue_date, hca.attribute7 exp_date,
       hca.attribute8 " Customer Information", hca.attribute9 company_type,
       hca.attribute10 "CREDIT LMT CHECK", hca.attribute11 years,
       hca.attribute12 "FROM DATE", hca.attribute13 "TO DATE",
       hca.attribute14 " CREDIT_AUTOHOLD(Y/N)",
       hca.attribute15 "CEILING_LIMIT(OMR)", hca.attribute16 cash_cust,
       hca.attribute17 comm_regn_no_date, hca.attribute18 id_card_no,
       hca.attribute19 grace_period, hca.attribute20 dormant_rationalisation
  FROM hz_parties hp,
       hz_party_sites hps,
       hz_cust_accounts_all hca,
       hz_cust_acct_sites_all hcasa
 WHERE hp.party_id = hps.party_id
   AND hp.party_id = hca.party_id
   AND hcasa.party_site_id = hps.party_site_id
   AND hca.cust_account_id = hcasa.cust_account_id
   AND hca.account_number = '415091';

How to differentiate customer and suppliers data in hz_parties table?

SELECT   *
    FROM   (SELECT   hp.party_number,
                     hp.party_name,
                     hp.status,
                     DECODE (NVL (hpu.party_usage_code, hp.party_type),
                             'ORGANIZATION', 'CUSTOMER',
                             NVL (hpu.party_usage_code, hp.party_type))
                        party_type
              FROM   hz_party_usg_assignments hpu, hz_parties hp
             WHERE   hp.party_id = hpu.party_id(+))
ORDER BY   party_type;

Query to Find Customer Contact in Oracle Apps R12

SELECT hcasa.org_id,
  role_acct.account_number,
  hcasa.orig_system_reference,
  rel.subject_id,
  rel.object_id,
  party.party_id party_id,
  rel_party.party_id rel_party_id,
  acct_role.cust_account_id ,
  acct_role.cust_acct_site_id ,
  party.person_pre_name_adjunct contact_prefix,
  SUBSTR(party.person_first_name, 1, 40) contact_first_name,
  SUBSTR(party.person_middle_name, 1, 40) contact_middle_name,
  SUBSTR(party.person_last_name, 1, 50) contact_last_name,
  party.person_name_suffix contact_suffix,
  acct_role.status,
  org_cont.job_title contact_job_title,
  org_cont.job_title_code contact_job_title_code,
  rel_party.address1 contact_address1,
  rel_party.address2 contact_address2,
  rel_party.address3 contact_address3,
  rel_party.address4 contact_address4,
  rel_party.country contact_country,
  rel_party.state contact_state,
  rel_party.city contact_city,
  rel_party.county contact_county,
  rel_party.postal_code contact_postal_code
FROM hz_contact_points cont_point,
  hz_cust_account_roles acct_role,
  hz_parties party,
  hz_parties rel_party,
  hz_relationships rel,
  hz_org_contacts org_cont,
  hz_cust_accounts role_acct,
  hz_contact_restrictions cont_res,
  hz_person_language per_lang,
  hz_cust_acct_sites_all hcasa
WHERE acct_role.party_id             = rel.party_id
AND acct_role.role_type              = 'CONTACT'
AND org_cont.party_relationship_id   = rel.relationship_id
AND rel.subject_id                   = party.party_id
AND rel_party.party_id               = rel.party_id
AND cont_point.owner_table_id(+)     = rel_party.party_id
AND cont_point.contact_point_type(+) = 'EMAIL'
AND cont_point.primary_flag(+)       = 'Y'
AND acct_role.cust_account_id        = role_acct.cust_account_id
AND role_acct.party_id               = rel.object_id
AND party.party_id                   = per_lang.party_id(+)
AND per_lang.native_language(+)      = 'Y'
AND party.party_id                   = cont_res.subject_id(+)
AND cont_res.subject_table(+)        = 'HZ_PARTIES'
AND role_acct.cust_account_id        = hcasa.cust_account_id
AND hcasa.cust_acct_site_id          = acct_role.cust_acct_site_id
AND party.party_id                   ='525338';