R12 Customer and Supp;ier Bank Accounts in oracle apps

 The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite.

Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

The Bank Accounts themselves are now stored in the new Oracle Payments Application. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

Now, linking the Bank Account to the relevant Customer is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Customer Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Customer Site that the Bank Account is actually attached to is a little bit trickier!

This is done through linking together the following tables IBY_EXTERNAL_PAYERS_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payer Record held in IBY_EXTERNAL_PAYERS_ALL using the ext_pmt_party_id. It is the External Payer Record that links us to a customer and Customer Site Use.

There is a record stored in the IBY_EXTERNAL_PAYERS_ALL table for every customer site use defined. The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site Use that the Bank Account has been assigned to.

The following query gives you the links required for matching a Bank Account to its Customer Site Record:

/* Formatted on 3/27/2017 4:38:20 PM (QP5 v5.114.809.3010) */
SELECT   cust.party_name customer_name,
         cust_acct.account_number,
         cust_uses.site_use_code,
         cust_loc.address1,
         cust_loc.address2,
         cust_loc.address3,
         cust_loc.address4,
         cust_loc.city,
         cust_loc.postal_code,
         bank.party_name bank_name,
         bank_prof.home_country,
         branch.party_name branch_name,
         branch_prof.bank_or_branch_number branch_number,
         account.bank_account_num,
         account.bank_account_name
  FROM   hz_parties bank,
         hz_relationships rel,
         hz_parties branch,
         hz_organization_profiles bank_prof,
         hz_organization_profiles branch_prof,
         iby_ext_bank_accounts account,
         iby_account_owners acc_owner,
         iby_external_payers_all ext_payer,
         iby_pmt_instr_uses_all acc_instr,
         hz_parties cust,
         hz_cust_accounts cust_acct,
         hz_cust_acct_sites_all cust_site,
         hz_cust_site_uses_all cust_uses,
         hz_locations cust_loc
 WHERE       1 = 1
         AND bank.party_id = rel.object_id
         AND bank.party_type = rel.object_type
         AND rel.object_table_name = 'HZ_PARTIES'
         AND rel.relationship_code = 'BRANCH_OF'
         AND rel.subject_id = branch.party_id
         AND rel.subject_type = branch.party_type
         AND rel.subject_table_name = 'HZ_PARTIES'
         AND bank.party_id = bank_prof.party_id
         AND branch.party_id = branch_prof.party_id
         AND bank.party_id = account.bank_id
         AND branch.party_id = account.branch_id
         AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
         AND acc_owner.account_owner_party_id = cust.party_id
         AND account.ext_bank_account_id = acc_instr.instrument_id
         AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
         AND ext_payer.cust_account_id = cust_acct.cust_account_id
         AND cust_acct.cust_account_id = cust_site.cust_account_id
         AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
         AND cust_uses.site_use_id = ext_payer.acct_site_use_id
         AND cust_uses.location = cust_loc.location_id
         AND cust.party_id = cust_acct.party_id; 

The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name

, aps.segment1 supplier_number

, ass.vendor_site_code supplier_site

, ieb.bank_account_num

, ieb.bank_account_name

, party_bank.party_name bank_name

, branch_prof.bank_or_branch_number bank_number

, party_branch.party_name branch_name

, branch_prof.bank_or_branch_number branch_number

FROM hz_parties party_supp

, ap_suppliers aps

, hz_party_sites site_supp

, ap_supplier_sites_all ass

, iby_external_payees_all iep

, iby_pmt_instr_uses_all ipi

, iby_ext_bank_accounts ieb

, hz_parties party_bank

, hz_parties party_branch

, hz_organization_profiles bank_prof

, hz_organization_profiles branch_prof

WHERE party_supp.party_id = aps.party_id

AND party_supp.party_id = site_supp.party_id

AND site_supp.party_site_id = ass.party_site_id

AND ass.vendor_id = aps.vendor_id

AND iep.payee_party_id = party_supp.party_id

AND iep.party_site_id = site_supp.party_site_id

AND iep.supplier_site_id = ass.vendor_site_id

AND iep.ext_payee_id = ipi.ext_pmt_party_id

AND ipi.instrument_id = ieb.ext_bank_account_id

AND ieb.bank_id = party_bank.party_id

AND ieb.bank_id = party_branch.party_id

AND party_branch.party_id = branch_prof.party_id

AND party_bank.party_id = bank_prof.party_id

ORDER BY party_supp.party_name

, ass.vendor_site_code;

Customer Interface in AR and 11i R12 tables in oracle apps

"A customer is someone who makes use of or receives the products or services of an individual or organization." Its means it is one who become a entity in your business world, irrespective of your line of business. If you are manufacturer the customer is one to whom you provide the product and get the money or services for which your get paid.
Time to time the customer definition has been changed and now in today economy it can be redefined as:
A customer..may include users, consumers, demanders, commanders, and requestors. Any person or entity who interacts directly or indirectly with any business system, thus it can be a client within internal departments, a supplier from the procurement process, an employee, or someone who is ringing up the cash register.
What information is important to keep in Business?
Typical information required for any customer is address, contact, bank , profile,class. Oracle standard form does have more than 8 tabs which hold most of the information. A typical flow of customer setup in Oracle is as;

Fig: Standard Setup process for customer

Fig : Entity Model for Customer Setup
What is Customer Interface ?
Customer Interface is a oracle seeded tool that is used to import and validate current or historical customer information from other systems into Receivables. Once customer information is imported into the system, you can use Customer Interface to import additional data for that customer (such as additional contacts or addresses) and to update existing information. This is yet another options to enter Customer information other than manually update and enter new information using the Customer windows.
Customer Interface and Customer in pre 11i and 11i
If you are coming from some old version, if have been noticed few things has been changed:

• Customer tables have changed, to move customer in TCA model, it means
  • The HZ tables
  • The role of Parties
    • Note:Added in order to track prospective customers Due to CRM integration and adds “benefit” of having all customer “groups” stored in one location.

11i tables used by Customer Interface

• Pre 11i versions used only 12 tables
• 11i version uses 23+ tables
• Only 4 of those tables remain the same
• Main Customer tables have changed
• Revised look and feel to Customer screen, too

The Change
Here is significant changes has been noticed from pre 11i and r11i version.
FIND screen
in 11i Find window automatically appears while calling customer screen.


most important , the Match Results window now is included in 11i, and it represnt multiple lines due to Parties and Accounts:

Customer screen


Customer Tables in 11i

• Previous Tables that have changed
  • RA_CUSTOMERS
  • RA_ADDRESSES
  • RA_SITE_USES
  • RA_PHONES
  • RA_CONTACTS
  • AR_CUSTOMER_PROFILES
  • RA_CUSTOMER_RELATIONSHIPS
  • AR_CUSTOMER_PROFILE_AMOUNTS
• Tables that remain the same
  • RA_CUST_RECEIPT_METHODS
  • AP_BANK_BRANCHES
  • AP_BANK_ACCOUNTS
  • AP_BANK_ACCOUNT_USES

TCA model - how its drived

• RA_CUSTOMERS, previously the main customer table is now a view.This become view which consists of data in HZ_CUST_ACCOUNTS and HZ_PARTIES tables.
• New Customer Tables - also known as HZ Tables
• The new HZ Customer Tables have tables for Customer Accounts and Parties

Customer Table Vs HZ Tables in R12

Here is summarize information for both for them:



Considering Customer as Parties

• HZ_PARTIES stores information about organizations, groups, and people.
• If a party becomes a customer then the information for the customer is stored in the HZ_CUST_ACCOUNTS table.
• A Party record in the Parties table can have multiple customer account records in the Customer Accounts table.
• One row is created in HZ_PARTIES for every customer record that is imported through the Customer Interface.
• CRM uses the customer module making it a requirement for all customers to have a party id and customer id.

Customer Interface : The Flow:
The following diagram shows how customer information is imported into the customer tables.

11i Customer Interface Vs Oracle Base table
Here is summarize information for interface Vs base table. Once Customer Import get completed successfully , the data moved to these tables:

Please take a note, the bank model has been changed in r12, this will have till 11.5.10.2. If you are looking for R12 , refer to trm guide.
Where to start for Customer Interface
1.The first steps would be your is preparing Receivables setup activity

• Be sure to set up new data in Receivables that the Customer Interface should import. For example:
  • AutoCash Rule Sets
  • AutoInvoice Grouping Rules
  • Collectors
  • Customer Addresses
  • Customer Bank Information
  • Customer Exemptions
  • Customer Profile Classes
  • Demand Classes
  • Dunning Letter Sets
  • Freight Carriers
  • Payment Methods
  • Payment Terms
  • Statement Cycles
  • Tax Codes
• Be sure to also set up Lookups in Receivables that the Customer Interface should import. These are the lookups:
  • Countries
  • Site Use Codes
  • Credit Ratings
  • Risk Codes
  • Account Statuses
  • Communication Types
  • Customer Classes

2. Next is to map the Interface Tables

• RA_CUSTOMER_INTERFACE_ALL
  • ORIG_SYSTEM_CUSTOMER_REF
  • CUSTOMER_NAME
  • CUSTOMER_STATUS
  • INSERT_UPDATE_FLAG
  • CUSTOMER_NUMBER
  • ORIG_SYSTEM_ADDRESS_REF
  • PRIMARY_SITE_USE_FLAG
  • SITE_USE_CODE
  • ADDRESS1
  • COUNTRY
  • LOCATION
• RA_CUSTOMER_PROFILES_INT_ALL
  • CUSTOMER_PROFILE_CLASS_NAME
  • ORIG_SYSTEM_CUSTOMER_REF
  • INSERT_UPDATE_FLAG
  • CREDIT_HOLD
  • ORIG_SYSTEM_ADDRESS_REF
• RA_CONTACT_PHONES_INT_ALL
  • ORIG_SYSTEM_CUSTOMER_REF
  • ORIG_SYSTEM_TELEPHONE_REF
  • TELEPHONE
  • TELEPHONE_TYPE
  • INSERT_UPDATE_FLAG
  • ORIG_SYSTEM_ADDRESS_REF
  • ORIG_SYSTEM_CONTACT_REF
  • CONTACT_LAST_NAME
• RA_BANKS_INTERFACE
  • ORIG_SYSTEM_CUSTOMER_REF
  • PRIMARY_FLAG
  • START_DATE
  • BANK_ACCOUNT_NAME
  • BANK_ACCOUNT_CURRENCY_CODE
  • BANK_ACCOUNT_NUM
  • BANK_BRANCH_NAME
  • ORIG_SYSTEM_ADDRESS_REF
• RA_CUST_PAY_METHOD_INTERFACE
  • ORIG_SYSTEM_CUSTOMER_REF
  • START_DATE
  • PAYMENT_METHOD_NAME
  • PRIMARY_FLAG
  • ORIG_SYSTEM_ADDRESS_REF

Customer conversionInterface tables in R12
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

3. RUN the Import Program

• Run Import after AR Customer Interface tables have been populated
• Program will validate the data in the interface table before creating records in Receivables
• Run the Customer Interface process through the Submit Request window
• But, a separate navigational path is also provided
  Interfaces -> Customer
• Check output file for errors

• Make corrections and repeat import process

Not Surprise , if you get these....Common Errors..very common

• a3: Bill_To_Orig_Address_Ref is not a valid bill-to address
  • Verify the Bill-To address reference is valid. Keep in mind that when using the bill-to reference with a ship-to address record... the bill-to must already exist in Receivables.
  • Note: Ran into this issue. Try running bill-to records through the interface first and ship-to records as second batch - this will resolve the error. Do not Interface with both in the same batch.
• a1:Customer record for insert must have validated profile record defined
  • New customers and each Bill-To record must have a customer level profile in the RA_CUSTOMER_PROFILES_INT_ALL table.
• a8: Conflicting profile classes specified for this customer/site
  • Profile classes for customer and bill-to must be the same. Sites cannot have a profile class different from the customer.
• J1: Site_USE_CODE is not updateable.
• J3: LOCATION is not updateable.
• J2: PRIMARY_SITE_USE_FLAG is not updateable.
  • Keep in mind that site_use_code, primary_use_flag, and location may not be updateable through the Customer Interface
• A3: Customer reference for insert is already defined.
• A5: Customer Number already assigned to a different customer.
  • Customer reference and Customer number are values that must be unique. Verify the customer reference or customer number does not already exist for another customer.

Tips and Technique
1. Check out some of the Profile Options hitting Customer Import

• HZ: Generate Party Number
  • This the profile option can be updated at Site, Application, Responsibility and User levels.This profile option determines whether party number should be auto-generated. If value is 'No',means party number must be passed in by the user else if 'Yes' or if the value is not set, party number will be auto-generated.
• HZ: Generate Party Site Number
  • same as above for party site number set at all leval.
• HZ: Internal Party
  • This profile option is used as a part of CRM setup. This must be set if CRM is installed. It is used for data migration purpose.
• HZ: Generate Contact Number
  • This profile option determines whether contact number should be auto-generated.If the value is 'No', contact number must be passed in by the user. If the value is 'Yes' or if the value is not set, contact number will be auto-generated.

2. Automatic sequence number for customer number
Many times AR department is not like oracle seeded number which start by default 1000.Options are there:
From R11 and 11i, you cannot change the sequence via the forms and therefore any change that you make to the sequence would have to be
through SQLPlus and that would not be supported.

To set the sequence number

Step 1. In the Application Developer responsibility,
Menu: Application=>Database=>Sequence
Step 2. Query on sequence RA_CUSTOMERS_NUM_S This will bring up the sequence for the customer numbers and you can enter the number that you want it to start from.

To set automatic numbering for customer after setting the sequence:
Step 1. Menu:=>System=>System Options
Step 2. Region - Invoicing and Customers
Step 3. Check the box for Automatic Customer Numbering.

3. When doing Migration from other system, adviced to use TRIM Function

• When loading interface tables remove all trailing spaces from import data.
  Example: LTRIM(RTRIM(customer_name))

4.If importing large number of customers, run in smaller batches instead of all at once.
Oracle benchmark is about 10,000 records per batch is ideal, it is suggested to keep the batch size small.
5.When rolling out in Multi-Org , then you must populate the org_IDs in the interface tables and run the customer interface for each organization set-up responsiblity.

Query to find out the customer, line item, ordered qty and price info of the order :

SELECT   h.order_number,
           org.name customer_name,
           h.ordered_date order_date,
           ot.name order_type,
           s.name sales_rep,
           l.line_id,
           l.line_number,
           l.inventory_item_id,
           si.segment1,
           l.ordered_quantity,
           l.unit_selling_price,
           NVL (l.ordered_quantity, 0) * NVL (l.unit_selling_price, 0) amount,
           h.transactional_curr_code currency_code
    FROM   ra_salesreps s,
           oe_transaction_types_tl ot,
           oe_sold_to_orgs_v org,
           mtl_system_items_vl si,
           oe_order_lines_all l,
           oe_order_headers_all h
   WHERE       h.order_number = 14463
           AND h.org_id = 204
           AND l.header_id = h.header_id
           AND h.sold_to_org_id = org.organization_id
           AND (h.cancelled_flag IS NULL OR h.cancelled_flag = 'N')
           AND h.open_flag = 'Y'
           AND l.open_flag = 'Y'
           AND l.service_reference_line_id IS NULL
           AND l.inventory_item_id = si.inventory_item_id
           AND NVL (si.organization_id, 0) = 204            --Item master orgn
           AND h.order_type_id = ot.transaction_type_id
           AND h.salesrep_id = s.salesrep_id
           AND h.org_id = s.org_id
ORDER BY   l.line_id
/

a: Oe_sold_to_orgs_v is a view based on hz_parties and hz_cust_accounts.

b: Ra_salesreps is a view based on JTF_RS_SALESREPS and JTF_RS_RESOURCE_EXTNS_VL.

You must set the org context for the views to function properly as..

BEGIN
   fnd_client_info.set_org_context ('204');
END;

Query to find Customer items :

SELECT   hp.party_name customer,
           ci.customer_item_number,
           ci.customer_item_desc,
           msi.segment1 item,
           msi.description item_desc,
           ci.customer_category_code,
           ci.item_definition_level,
           ci.commodity_code_id,
           ci.address_id
    FROM   hz_parties hp,
           hz_cust_accounts hca,
           mtl_system_items msi,
           mtl_customer_items ci,
           mtl_customer_item_xrefs ix
   WHERE       ci.customer_item_id = ix.customer_item_id
           AND ix.inventory_item_id = msi.inventory_item_id
           AND ix.master_organization_id = msi.organization_id
           AND ci.customer_id = hca.cust_account_id
           AND hca.party_id = hp.party_id
ORDER BY   1, 2
/

Query to find Customer contacts and their roles :

SELECT   oc.JOB_TITLE,
           oc.PARTY_SITE_ID,
           RELATIONSHIP_ID,
           RELATIONSHIP_TYPE,
           hp.PARTY_NAME OBJECT_NAME,                               -- Contact
           OBJECT_ID,
           OBJECT_TYPE,
           OBJECT_TABLE_NAME,
           hr.PARTY_ID,
           RELATIONSHIP_CODE,
           SUBJECT_ID,
           SUBJECT_TYPE,
           SUBJECT_TABLE_NAME,
           oc.TITLE,
           oc.MAIL_STOP,
           oc.CONTACT_KEY,
           ocr.ROLE_TYPE,
           DIRECTIONAL_FLAG
    FROM   HZ_ORG_CONTACT_ROLES ocr,
           HZ_ORG_CONTACTS oc,
           HZ_PARTIES hp,
           HZ_RELATIONSHIPS hr
   WHERE       hr.subject_id = 1004                 --Party id of the customer
           AND hr.object_id = hp.party_id
           AND hr.RELATIONSHIP_ID = oc.PARTY_RELATIONSHIP_ID
           AND oc.ORG_CONTACT_ID = ocr.ORG_CONTACT_ID(+)
ORDER BY   OBJECT_NAME
/




Query to find communication channels for a customer :

SELECT   CONTACT_POINT_ID,
         CONTACT_POINT_TYPE,
         EMAIL_ADDRESS,
         PHONE_NUMBER,
         URL,
         CONTACTS,
         STATUS,
         OWNER_TABLE_NAME,
         OWNER_TABLE_ID,
         PRIMARY_FLAG,
         ORIG_SYSTEM_REFERENCE
  FROM   HZ_CONTACT_POINTS
 WHERE   OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = 1004


Query to find Organization type Party info :


SELECT   hp.PARTY_ID,
           hp.PARTY_NUMBER,
           hp.PARTY_NAME,
           hca.ACCOUNT_NUMBER,
           hca.CUST_ACCOUNT_ID,
           hop.ORGANIZATION_PROFILE_ID,
           hop.EFFECTIVE_START_DATE,
           hop.EFFECTIVE_END_DATE,
           hop.ORGANIZATION_NAME,
           hop.DUNS_NUMBER,
           hop.ENQUIRY_DUNS,
           hop.CEO_NAME,
           hop.CEO_TITLE,
           hop.PRINCIPAL_NAME,
           hop.PRINCIPAL_TITLE,
           hop.LEGAL_STATUS,
           hop.CONTROL_YR,
           hop.EMPLOYEES_TOTAL,
           hop.HQ_BRANCH_IND,
           hop.BRANCH_FLAG,
           hop.OOB_IND,
           hop.LINE_OF_BUSINESS
    FROM   HZ_ORGANIZATION_PROFILES hop, HZ_CUST_ACCOUNTS hca, HZ_PARTIES hp
   WHERE       hp.party_name = 'Hilman and Associates'
           AND hp.PARTY_ID = hca.PARTY_ID
           AND hp.PARTY_ID = hop.PARTY_ID
           AND SYSDATE BETWEEN hop.EFFECTIVE_START_DATE
                           AND  NVL (hop.EFFECTIVE_END_DATE, SYSDATE + 1)
ORDER BY   hca.ACCOUNT_NUMBER

List of Duplicate Addresses in Customers

/* Formatted on 7/17/2014 12:14:11 PM (QP5 v5.115.810.9015) */
SELECT acct.account_number,
       party.party_name,
       locations.address1,
       locations.address2,
       locations.city,
       locations.state,
       locations.postal_code
FROM hz_party_sites psiteo,
     hz_cust_accounts acct,
     hz_parties party,
     (SELECT /*+ index ( loc HZ_LOCATIONS_N1 ) */
            location_id,
             loc.address1,
             loc.address2,
             loc.city,
             loc.state,
             loc.postal_code,
             parties
      FROM hz_locations loc,
           (SELECT loc.address1,
                   loc.address2,
                   loc.city,
                   loc.state,
                   COUNT (DISTINCT psite.party_id) parties
            FROM hz_locations loc, hz_party_sites psite
            WHERE psite.location_id = loc.location_id
            GROUP BY loc.address1, loc.address2, loc.city, loc.state
            HAVING COUNT ( * ) > 10) addr
      WHERE     loc.address1 = addr.address1
            AND loc.address2 = addr.address2
            AND loc.city = addr.city
            AND loc.state = addr.state) locations
WHERE     psiteo.location_id = locations.location_id
      AND psiteo.party_id = party.party_id
      AND acct.party_id = party.party_id
ORDER BY locations.postal_code,
         locations.state,
         locations.city,
         locations.address1,
         party.party_name,
         acct.account_number

Available phones/emails/web for an account Detail Query

/* Formatted on 7/17/2014 12:11:32 PM (QP5 v5.115.810.9015) */
SELECT account_number,
       'Party LeveL Communication' communication_level,
       phne.contact_point_purpose,
       phne.contact_point_type,
       DECODE (phne.contact_point_type,
               'PHONE',
               '(' || phne.phone_area_code || ') ' || phne.phone_number,
               'EMAIL',
               phne.email_address,
               'WEB',
               phne.url
       )
          comm_detail,
       phne.primary_flag,
       NULL address
FROM hz_contact_points phne, hz_cust_accounts acct
WHERE     acct.party_id = phne.owner_table_id
      AND phne.owner_table_name = 'HZ_PARTIES'
      AND acct.account_number = '1000'
UNION
SELECT account_number,
       'Party Site LeveL Communication',
       phne.contact_point_purpose,
       phne.contact_point_type,
       DECODE (phne.contact_point_type,
               'PHONE',
               '(' || phne.phone_area_code || ') ' || phne.phone_number,
               'EMAIL',
               phne.email_address,
               'WEB',
               phne.url
       )
          comm_detail,
       phne.primary_flag,
          loc.address1
       || ','
       || loc.address2
       || ','
       || loc.city
       || ','
       || loc.state
       || ','
       || loc.postal_code
          address
FROM hz_contact_points phne,
     hz_cust_acct_sites_all site,
     hz_cust_accounts acct,
     hz_party_sites psite,
     hz_locations loc
WHERE     site.cust_acct_site_id = phne.owner_table_id
      AND phne.owner_table_name = 'HZ_PARTY_SITES'
      AND site.cust_account_id = acct.cust_account_id
      AND site.party_site_id = psite.party_site_id
      AND psite.location_id = loc.location_id
      AND acct.account_number = '1000'

List of Phones used by more than one accounts

/* Formatted on 7/17/2014 12:09:28 PM (QP5 v5.115.810.9015) */
SELECT hcp_out.phone_area_code,
       hcp_out.phone_number,
       hca_out.account_number,
       party_name
FROM hz_contact_points hcp_out,
     hz_cust_accounts hca_out,
     hz_parties party,
     (SELECT hcp.phone_area_code, hcp.phone_number
      FROM hz_contact_points hcp,
           hz_cust_accounts hca,
           (SELECT hcp_in.phone_area_code, hcp_in.phone_number
            FROM hz_contact_points hcp_in
            WHERE hcp_in.creation_date >= TO_DATE ('1-JAN-2008', 'DD-MON-YYYY')
                  AND hcp_in.creation_date <
                        TO_DATE ('1-JAN-2012', 'DD-MON-YYYY')
                  AND hcp_in.owner_table_name = 'HZ_PARTIES'
                  AND hcp_in.contact_point_type = 'PHONE') new_phone
      WHERE     hcp.phone_area_code = new_phone.phone_area_code
            AND hcp.phone_number = new_phone.phone_number
            AND hca.party_id = hcp.owner_table_id
      GROUP BY hcp.phone_area_code, hcp.phone_number
      HAVING COUNT (DISTINCT hca.account_number) > 1) dup_phone
WHERE     hcp_out.phone_area_code = dup_phone.phone_area_code
      AND hcp_out.phone_number = dup_phone.phone_number
      AND hca_out.party_id = hcp_out.owner_table_id
      AND party.party_id = hca_out.party_id
ORDER BY hcp_out.phone_area_code,
         hcp_out.phone_number,
         hca_out.account_number

List of Customer Accounts that have 2 or more Identifying Addresses

/* Formatted on 7/17/2014 12:07:32 PM (QP5 v5.115.810.9015) */
SELECT     /*+ use_nl(ps ca) */
      ca.account_number, cnt, ca.creation_date, ca.last_update_date
FROM (SELECT ps.party_id, COUNT (ps.party_id) cnt
      FROM hz_party_sites ps
      WHERE identifying_address_flag = 'Y'
      GROUP BY ps.party_id
      HAVING COUNT (ps.party_id) > 1) ps, hz_cust_accounts ca
WHERE ps.party_id = ca.party_id


/* Formatted on 7/17/2014 12:07:51 PM (QP5 v5.115.810.9015) */
SELECT ca.account_number, cnt, ca.creation_date, ca.last_update_date
FROM (SELECT ps.party_id, COUNT (ps.party_id) cnt
      FROM hz_party_sites ps
      WHERE identifying_address_flag = 'Y'
      GROUP BY ps.party_id
      HAVING COUNT (ps.party_id) > 1) ps, hz_cust_accounts ca
WHERE ps.party_id = ca.party_id

Person Credit Card Accounts Detail Query

/* Formatted on 7/17/2014 12:05:24 PM (QP5 v5.115.810.9015) */
SELECT party_name,
       party_number,
       ccnumber,
       instrument_type,
       chname "Name on the Card",
       expirydate,
       finame,
       card_issuer_code,
       invalidation_reason,
       loc.address1,
       loc.address2,
       loc.city,
       loc.state,
       loc.postal_code
FROM ar.hz_parties pty,
     iby.iby_creditcard cc,
     ar.hz_party_site_uses psu,
     ar.hz_party_sites ps,
     ar.hz_locations loc
WHERE     pty.party_id = :party_id
      AND pty.party_id = cc.card_owner_id
      AND cc.addressid = psu.party_site_use_id
      AND psu.party_site_id = ps.party_site_id
      AND ps.location_id = loc.location_id

Person Profile Overlapping Start and End Dates

/* Formatted on 7/17/2014 12:03:09 PM (QP5 v5.115.810.9015) */
SELECT pp_out.party_id,
       (SELECT account_number
        FROM ar.hz_cust_accounts ca
        WHERE ca.party_id = pp_out.party_id)
          acct_num,
       (SELECT pty.party_type || ',' || pty.orig_system_reference
        FROM ar.hz_parties pty
        WHERE pty.party_id = pp_out.party_id)
          party_type,
       pp_out.person_profile_id,
       pp_out.creation_date,
       pp_out.effective_start_date,
       pp_out.effective_end_date,
       pp_out.*
FROM ar.hz_person_profiles pp_out
WHERE pp_out.party_id IN
            (SELECT party_id
             FROM ar.hz_person_profiles pp
             WHERE EXISTS
                      (SELECT 1
                       FROM ar.hz_person_profiles pp_in
                       WHERE pp_in.party_id = pp.party_id
                             AND pp_in.person_profile_id <> pp.person_profile_id
                             AND (pp.effective_start_date BETWEEN pp_in.effective_start_date
                                                              AND  NVL (pp_in.effective_end_date,
                                                                        SYSDATE
                                                                        + 100
                                                                   )
                                  OR NVL (pp.effective_end_date, SYSDATE) BETWEEN pp_in.effective_start_date
                                                                              AND  NVL (pp_in.effective_end_date,
                                                                                        SYSDATE
                                                                                        + 100
                                                                                   ))))
ORDER BY pp_out.party_id DESC

Customer contacts and their phone, email, URL

/* Formatted on 7/17/2014 12:01:39 PM (QP5 v5.115.810.9015) */
SELECT account_number "Account Number",
       obj.party_name "Customer Name",
       sub.party_name "Contact Name",
       hcp.contact_point_type || ': '
       || DECODE (hcp.contact_point_type,
             'EMAIL', hcp.email_address,
             'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number,
             'WEB', hcp.url,
             'Unknow contact Point Type ' || hcp.contact_point_type)
          "How to Contact"
FROM apps.hz_cust_accounts hca,
     apps.hz_parties obj,
     apps.hz_relationships rel,
     apps.hz_contact_points hcp,
     apps.hz_parties sub
WHERE     hca.party_id = rel.object_id
      AND hca.party_id = obj.party_id
      AND rel.subject_id = sub.party_id
      AND rel.relationship_type = 'CONTACT'
      AND rel.directional_flag = 'F'
      AND rel.party_id = hcp.owner_table_id
      AND hcp.owner_table_name = 'HZ_PARTIES';

Query to get Customer Information in R12

select  distinct

        hp.party_name "Customer Name",

        hca.account_number,

        hca.status,

        hcsu.location,

        hcsu.site_use_code,

        hcsu.status loc_stat,

        ps.class,

        hcsu.site_use_id,

        hcpc.name profile_name,

        hl.address1,

        hl.address2,

        hl.address3,

        hl.city,

        hl.state,

        hl.postal_code,

        ps.customer_id,

        ps.customer_site_use_id,

        hps.identifying_address_flag,

        ps.trx_date,

        HOU.NAME "Operating Unit"

from    apps.hz_parties hp,

        apps.hz_party_sites hps,

        apps.hz_locations hl,

        apps.hz_cust_accounts hca,

        apps.hz_cust_acct_sites hcas,

        apps.hz_cust_site_uses hcsu,

        apps.hz_customer_profiles hcp,

        apps.hz_cust_profile_classes hcpc,

        apps.ar_payment_schedules_all ps,

        apps.hr_operating_units hou

where   hp.party_id = hca.party_id(+)

        and hp.party_id = hcp.party_id

        and hp.party_id = hps.party_id

        and hps.party_site_id = hcas.party_site_id

        and hps.location_id = hl.location_id

        and hca.cust_account_id = hcas.cust_account_id

        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id

        and hca.cust_account_id = hcp.cust_account_id

        and hca.cust_account_id = ps.customer_id

        and hcp.profile_class_id = hcpc.profile_class_id

        and ps.customer_site_use_id = hcsu.site_use_id

        and hcsu.org_id = hou.organization_id;