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
Showing posts with label Customer Information Extract. Show all posts
Showing posts with label Customer Information Extract. Show all posts
Wednesday, February 12, 2020
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
Subscribe to:
Posts (Atom)