Query for AR Transactions Line with GST

SELECT h.trx_uom,

       h.trx_line_quantity,

       h.unit_price,

       h.line_amt,

       h.first_party_primary_reg_num gst_num,

       h.third_party_primary_reg_num cusomer_gst_num,

       b.line_number, b.description, b.uom_code,

       b.quantity_invoiced, b.unit_selling_price rate,

DECODE (b.quantity_invoiced * b.unit_selling_price,NULL, b.extended_amount,b.quantity_invoiced * b.unit_selling_price) totamt,

h.unround_taxable_amt_fun_curr "TAXBLE AMOUNT",

(SELECT jra.reporting_code

FROM jai_reporting_associations jra,

jai_item_templ_hdr jit

WHERE jra.entity_id = jit.template_hdr_id

AND jra.entity_code = 'ITEM'

AND jra.entity_source_table = 'JAI_ITEM_TEMPL_HDR'

AND ROWNUM = 1) "HSN/SAC Code",

h.trx_number,

h.trx_line_number,h.tax_invoice_num,

h.org_id

from

ra_customer_trx_all a ,

ra_customer_trx_lines_all b,

jai_tax_lines_all h,

hz_cust_accounts j

where a.customer_trx_id = h.trx_id

and a.customer_trx_id = b.customer_trx_id

and b.line_type <> 'TAX'

and H.TRX_LINE_ID = b.CUSTOMER_TRX_LINE_ID

and a.trx_number =nvl(:trx_number,a.TRX_NUMBER)

and j.cust_account_id = a.bill_to_customer_id

GROUP BY h.trx_uom,

h.trx_line_quantity,

h.unit_price,

h.line_amt,

h.first_party_primary_reg_num ,

h.third_party_primary_reg_num ,

b.line_number, b.description, b.uom_code,

b.quantity_invoiced, b.unit_selling_price ,

h.unround_taxable_amt_fun_curr,

h.trx_number,

h.trx_line_number,h.tax_invoice_num,

h.org_id,b.extended_amount

Order by trx_number,trx_line_number