Oracle Apps R12 TCA Customer Information Extract

SELECT hou.name source_ou, hp.party_type, hp.party_name customer_name, hp.known_as, --Account Information -- hca.account_number customer_number, hca.account_name, NULL orig_system_customer_ref, hca.customer_type, hcsu.location, hcsu.site_use_code, hcsub.location bill_to_loc_shipto, hcsu.primary_flag site_primary_flag, hcsu.status site_status, hps.party_site_name, hps.party_site_number, hcsu.primary_flag primary_site_use_flag, NULL orig_system_address_ref, --Site Address Information -- hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.state, hl.postal_code, --hl.country, ftv.territory_short_name, hcsu.gl_id_rec gl_id_rec_acnt_code, hcsu.gl_id_rev gl_id_rev_acnt_code, hcsu.tax_code, hcpc.name customer_profile_class_name, ac.name collector_name, rt.name payment_terms, hps.addressee, hcsu.ship_sets_include_lines_flag, hcsu.arrivalsets_include_lines_flag, hcsu.sched_date_push_flag, hcsu.freight_term, hcsu.fob_point, hcsu.ship_via, hcsu.ship_partial, hcsu.gsa_indicator, hcp.credit_checking, hcp.discount_terms, hcp.dunning_letters, hcp.interest_charges, hcp.send_statements, hcp.credit_balance_statements, hcp.credit_hold, hcp.standard_terms, hcp.interest_period_days, hcp.statement_cycle_id, hcp.tax_printing_option, hcp.auto_rec_incl_disputed_flag FROM hz_parties hp, hz_party_sites hps, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_cust_site_uses_all hcsub, hz_customer_profiles hcp, hz_cust_profile_classes hcpc, hz_locations hl, fnd_territories_vl ftv, ra_terms rt, ar_collectors ac, hr_operating_units hou WHERE hp.party_id = hca.party_id AND hca.cust_account_id = hcas.cust_account_id AND hps.party_site_id = hcas.party_site_id AND hcp.site_use_id (+) = hcsu.site_use_id AND hps.location_id = hl.location_id AND hcsub.site_use_id (+) = hcsu.bill_to_site_use_id AND hp.status = 'A' AND hcas.status = 'A' AND hca.status = 'A' AND hcsu.status = 'A' AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND rt.term_id (+) = hcsu.payment_term_id AND hcp.profile_class_id = hcpc.profile_class_id (+) AND hcsu.org_id = hou.organization_id AND ac.collector_id (+) = hcp.collector_id AND ftv.territory_code (+) = hl.country AND hou.name = :P_OU_NAME