Introduction
this query is used for AR Transactions Line with GST Query.
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
Summary
This Post described the AR Transactions Line with GST in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
