R12 CUSTOMER MASTER DETAILS QUERY

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 NOT LIKE '%Unapplied%'