Introduction:
This SQL query is fetching the data of India customer’s transaction with the company and the customer GST details.
Cause of the issue:
To find out the customers GST details with the transaction and if any update is required this query can be used to fetch the details
How do we solve:
By providing this report users can able to find out what are all the updated GST details for the customer and the company.
SQL Query:
SELECT party_name,
account_number,
ps.party_site_number,
rct.trx_number,
DECODE (ps.status, ‘A’, ‘Active’, ‘I’, ‘Inactive’, ps.status)
AS site_status,
qrs.registration_number Party_GST_Number,
rct.FIRST_PTY_REG_ID,
(select zxr.registration_number from zx_registrations zxr
where zxr.REGISTRATION_ID =rct.FIRST_PTY_REG_ID
) Company_GST_Number,
rctl.DESCRIPTION,rctl.QUANTITY_INVOICED,rctl.UNIT_SELLING_PRICE,rctl.LINE_TYPE,rctl.EXTENDED_AMOUNT,rctl.UOM_CODE
FROM hz_party_sites ps,
hz_cust_accounts hca,
hz_parties hp,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
HZ_CUST_SITE_USES_ALL hcsua,
(SELECT zr.registration_number,
zr.tax_regime_code,
zr.tax,
zr.registration_status_code,
zr.effective_from,
zr.effective_to,
zptp.party_id,
zptp.party_tax_profile_id
FROM zx_registrations zr, zx_party_tax_profile zptp
WHERE zr.party_tax_profile_id = zptp.party_tax_profile_id) qrs
WHERE EXISTS (SELECT 1 FROM hz_cust_acct_sites_all hcas
WHERE hca.cust_account_id = hcas.cust_account_id
AND hcas.party_site_id = ps.party_site_id
AND hcas.party_site_id = ps.party_site_id
AND hcas.status = ‘A’
AND hcas.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcas.party_site_id = qrs.party_id)
AND hp.party_id = hca.party_id
AND hp.party_id = ps.party_id
AND hca.status = ‘A’
AND hca.cust_account_id = rct.bill_to_customer_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND hcsua.SITE_USE_ID = rct.BILL_TO_SITE_USE_ID
and rct.creation_date between TO_DATE(‘2023/07/01 00:00:01’, ‘yyyy/mm/dd hh24:mi:ss’) and TO_DATE(‘2024/03/31 23:59:44’, ‘yyyy/mm/dd hh24:mi:ss’)
AND rct.ORG_ID IN <India BU>
