Customer details table in oracle apps

Customer details table in oracle apps

In this post , We will discuss about Technical tables uses to store customer detail information's in oracle apps r12. customer details table in oracle apps are related to the TCA tables in oracle apps. Here below you will get all the complete information's of customer details table in oracle apps.

List of Tables in the customer details table in oracle apps

AR_CUSTOMERS :- THIS TABLE HELPS TO EXTRACT THE CUSTOMER NAME

HZ_CUST_ACCOUNTS_ALL :- THIS TABLE HELPS TO EXTRACT THE CUSTOMER ACCOUNT INFORMATION

HZ_CUST_ACCT_SITES_ALL :- THIS TABLE HELPS TO EXTRACT CUSTOMER SITES INFORMATIONS

HZ_CUST_SITE_USES_ALL :- THIS TABLES HELPS TO EXTRACT THE CUSTOMER SITES USES INFORMATIONS TABLES.

HZ_PARTY_SITES :- THIS TABLE HELPS TO STORE THE CUSTOMER PARTY SITES INFORMATIONS

HZ_LOCATIONS:- THIS TABLE HEPS TO STORE THE CUSTOMER LOCATION ADDRESS INFORMATIONS

Complete SQL query uses customer details table in oracle apps

SELECT   

 PARTY_NAME,

    'DNB' ACCT_SITE_SRC_SYS

    ,hcas.ORIG_SYSTEM_REFERENCE ACCT_SITE_SRC_SYS_REF

    ,'DNB' ACCT_SITE_PUR_SRC_SYS

    ,hcsua.ORIG_SYSTEM_REFERENCE ACCT_SITE_PUR_SRC_SYS_REF

    ,hcsua.SITE_USE_CODE PURPOSE        -- *

    ,hcsua.primary_flag PRIMARY_INDICATOR

    ,'I' INSERT_UPDATE_INDICATOR

    ,hcsua.location SITE

    ,NULL ACCT_ADDR_PURPOSE_SET        -- *

    ,NULL PURPOSE_FROM_DATE

    ,NULL PURPOSE_TO_DATE

    ,hl.COUNTRY Country

    ,hl.ADDRESS1 Address_Line_1

      ,hl.ADDRESS2 Address_Line_2

      ,hl.ADDRESS3 Address_Line_3

      ,hl.ADDRESS4 Address_Line_4

      ,hl.CITY City

      ,hl.STATE STATE

      ,hl.PROVINCE Province

      ,hl.COUNTY County

      ,hl.POSTAL_CODE Postal_Code

      ,hl.POSTAL_PLUS4_CODE Postal_Code_Extension

      ,hl.LANGUAGE Location_Language

      ,hl.DESCRIPTION Description

      ,hl.SHORT_DESCRIPTION Short_Description

      ,hl.SALES_TAX_GEOCODE Sales_Tax_Geocode

      ,hl.SALES_TAX_INSIDE_CITY_LIMITS Sales_Tax_Inside_City_Limits

FROM hz_parties hp

    ,hz_party_sites hps

    ,hz_cust_accounts hca

    ,hz_cust_acct_sites_all hcas

    ,hr_operating_units hou

    ,hz_party_usg_assignments hpua

    ,hz_cust_site_uses_all hcsua,

    HZ_LOCATIONS HL

WHERE hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND hp.STATUS = 'A'

    AND hca.STATUS = 'A'

    AND hcas.STATUS = 'A'

    AND hcsua.STATUS = 'A'

    AND hcsua.cust_acct_site_id = hcas.cust_acct_site_id

    AND hp.party_id = hpua.party_id

    AND hpua.PARTY_USAGE_CODE = 'CUSTOMER'

    AND hcas.org_id = hou.organization_id

     AND hcas.org_id=:P_ORG_ID

    AND hca.cust_account_id = hcas.cust_account_id

    AND HPS.LOCATION_ID=HL.LOCATION_ID;