Re-usable SQL Script for Customer Data Model in Oracle Fusion

 

SQL Script:

/* Retrieve recently created customers with full hierarchy in Fusion TCA */

                                                                                        by Venkat

SELECT

    p.party_id,

    p.party_number,

    p.party_name,

    p.party_type,

    ca.cust_account_id,

    ca.account_number,

    ca.account_name,

    ps.party_site_id,

    l.location_id,

    l.address1,

    l.city,

    l.state,

    l.postal_code,

    l.country,

    cas.cust_acct_site_id,

    su.site_use_id,

    su.site_use_code,

   cp.contact_point_id,

    cp.contact_point_type,

    cp.email_address,

    cp.phone_number,

    cp.status

FROM 

    hz_parties p,

     hz_cust_accounts ca,

     hz_party_sites ps,

     hz_locations l,

     hz_cust_acct_sites cas,

     hz_cust_site_uses_all su,

     hz_contact_points cp

WHERE 1=1

AND p.party_id = ca.party_id

  AND p.party_id = ps.party_id(+)

  AND ps.location_id = l.location_id(+)

  AND ca.cust_account_id = cas.cust_account_id(+)

  AND cas.cust_acct_site_id = su.cust_acct_site_id(+)

  AND ps.party_site_id = cp.owner_table_id(+)

  AND cp.owner_table_name(+) = 'HZ_PARTY_SITES'

  AND p.creation_date >= SYSDATE - 1

ORDER BY p.creation_date DESC