Query to get customer payment terms in oracle apps

Query to get customer payment terms in oracle apps

In this post , we will be discuss about SQL Query to get customer payment terms in oracle apps. Payment Terms is the agreement between two parties for example customer and supplier , about payment due. Based on the Payment Terms both parties do the payments to each other. This helps to decide when the payment will be due once we got the services or goods from other party. Here below I will share detail sql query which helps to get customer payment terms in oracle apps.

 

 

Important Table of customer payment terms SQL query.

 

1.hz_cust_site_uses_all.

2.hz_cust_acct_sites_all.

3.ar_customers.

4.ra_terms.

 

 

Complete SQL Query to get customer payment terms in oracle apps

 

select customer_name,SITE_USE_CODE,LOCATION,PAYMENT_TERM_ID,name
 from apps.hz_cust_site_uses_all a1,
  hz_cust_acct_sites_all a2,
  ar_customers a3,
  ra_terms a4
where a1.CUST_ACCT_SITE_ID=a2.CUST_ACCT_SITE_ID
and a2.CUST_ACCOUNT_ID=a3.CUSTOMER_ID
and a3.CUSTOMER_ID=:P_CUSTOMER_ID
and a1.PAYMENT_TERM_ID=a4.TERM_ID(+)

 

 

 

Query to get customer payment terms in oracle apps

Query to get customer payment terms in oracle apps

In this Post  you will get the sql query to get customer payment terms in oracle apps. Payment terms helps to fix the due dates of the customer payments so that we can calculate the debtor aging according to that. It also helps to calculate interest for the late payments. Here below is the complete sql query to get customer payment terms in oracle apps.

 

Example of Query to get customer payment terms in oracle apps

 

SELECT  

 PARTY_NAME,

 ART.NAME,

    hcsua.location SITE

    ,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,

    RA_TERMS ART

WHERE hp.party_id = hca.party_id

    AND hp.party_id = hps.party_id

    AND HCSUA.PAYMENT_TERM_ID=ART.TERM_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