Oracle Application's Blog: Sql Query to Extract Customer Receivable & Revenue GL Accounts in Oracle Fusion

Sql Query to Extract Customer Receivable & Revenue GL Accounts in Oracle Fusion

Hi friends, we are going to discuss about the Sql Query to Extract Customer Receivable & Revenue GL Accounts in Oracle Fusion. We will share the detail sql query which helps to extract the customer details having Receivable & Revenue GL Accounts in Oracle Fusion. In Oracle fusion , Customer GL accounts details including Receivable & Revenue GL Accounts stores in the different tables as compared to oracle apps. In Oracle apps, Customer GL accounts details stores in the Customer Sites uses table but in oracle fusion the complete table layout got changed. In Oracle fusion , we have the completely different table for Customer Receivable & Revenue GL Accounts in Oracle Fusion. We will also try to share the detail sql query to extract the complete customer informations including the Receivable & Revenue GL Accounts in Oracle Fusion.

Sql Query to Extract Customer Receivable & Revenue GL Accounts in Oracle Fusion

Important Table to store the Customer Receivable & Revenue GL Accounts in Oracle Fusion

AR_REF_ACCOUNTS_ALL

HZ_PARTY_SITES

Detail SQL Query to Extract Customer Receivable & Revenue GL Accounts in Oracle Fusion

Here below is the detail sql query which helps to extract the complete customer details including the GL accounts Receivable & Revenue accounts in oracle fusion.

select DISTINCT hp.party_name customer_name,

a1.account_number customer_number,

A3.LOCATION,A3.PRIMARY_FLAG

,A4.PARTY_SITE_NUMBER

,A3.SITE_USE_CODE

,A5.ADDRESS1,A5.ADDRESS2,A5.ADDRESS3,A5.ADDRESS4,A5.CITY,A5.POSTAL_CODE,A5.STATE,A5.COUNTRY,hcp.EMAIL_ADDRESS

,hcp.CONTACT_POINT_TYPE

,hr.RELATIONSHIP_CODE

,a3.STATUS site_status

,hrr.RESPONSIBILITY_TYPE,

(select segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5||'-'||segment6 acct from gl_code_combinations

where code_combination_id=ref_acct.rev_ccid) rev_acct,

(select segment1||'-'||segment2||'-'||segment3||'-'||segment4||'-'||segment5||'-'||segment6 acct from gl_code_combinations

where code_combination_id=ref_acct.rec_ccid) rec_acct,

(SELECT A11.REGISTRATION_NUMBER FROM zx_registrations A11,ZX_PARTY_TAX_PROFILE A22

WHERE A11.TAX_REGIME_CODE=' GST'

AND A11.EFFECTIVE_TO IS NULL

AND ROWNUM=1

AND A11.PARTY_TAX_PROFILE_ID=A22.PARTY_TAX_PROFILE_ID

AND A22.party_id=a4.party_site_id) reg_no,

(SELECT TAX_CLASSIFICATION_CODE FROM ZX_PARTY_TAX_PROFILE A23

WHERE A23.party_id=a4.party_site_id) TAX_CLASSIFICATION_CODE

from hz_cust_accounts a1,hz_cust_acct_sites_all a2,

hz_cust_site_uses_all a3,HZ_PARTY_SITES A4,HZ_LOCATIONS A5,

hz_parties hp,hz_contact_points hcp, hz_relationships hr,hz_cust_account_roles hcar,

HZ_ROLE_RESPONSIBILITY hrr,AR_REF_ACCOUNTS_ALL ref_acct

where a1.CUST_ACCOUNT_ID=a2.CUST_ACCOUNT_ID

AND hcp.relationship_id = hr.relationship_id and hcp.CONTACT_POINT_TYPE = 'EMAIL' and hr.RELATIONSHIP_CODE = 'CONTACT_OF' and hr.object_id = hp.party_id

and nvl(hcp.end_date,sysdate+5) >sysdate

--and NVL(HCAR.STATUS,'A')='A'

and hcar.relationship_id = hcp.relationship_id

and hcar.CUST_ACCOUNT_ID=A1.CUST_ACCOUNT_ID

and hcar.CUST_ACCT_SITE_ID=A2.CUST_ACCT_SITE_ID

and hcar.CUST_ACCOUNT_ROLE_ID=hrr.CUST_ACCOUNT_ROLE_ID

and a1.party_id=hp.party_id

and a2.CUST_ACCT_SITE_ID=a3.CUST_ACCT_SITE_ID

and nvl(A1.STATUS,'A')='A'

and nvl(A3.STATUS,'A')='A'

AND A2.PARTY_SITE_ID=A4.PARTY_SITE_ID

AND A4.LOCATION_ID=A5.LOCATION_ID