GST Report with Sales details and Registration number of company.
This
Query is used for getting the AR GST report details in R12.
SELECT 'S'
,JTLA.THIRD_PARTY_PRIMARY_REG_NUM
,PARTY.PARTY_NAME
,RCTA.TRX_NUMBER
INVOICE_NUMBER
,RCTA.TRX_DATE
INVOICE_DATE
,RCTLA.LINE_NUMBER
,RCTLA.DESCRIPTION
,jtla.TRX_LINE_ID
,DECODE(JTLA.TRX_CURRENCY_CODE,'INR','
',JTLA.TRX_CURRENCY_CODE) FORIGN_CURRENCY
,SUM(RCTLA.QUANTITY_INVOICED*RCTLA.UNIT_SELLING_PRICE)
SALES_VALUE
, NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id =
jtt.tax_type_id(+)
and jtt.tax_type_code like '%CGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE='INR'),0) +
NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id =
jtt.tax_type_id(+)
and jtt.tax_type_code like '%SGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE='INR'),0)+
NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id = jtt.tax_type_id(+)
and jtt.tax_type_code like '%IGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE='INR'),0)
GST
,(SELECT
SUM(RCTLA.QUANTITY_INVOICED*RCTLA.UNIT_SELLING_PRICE)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTLA
WHERE JTLA.TRX_LINE_ID =
RCTLA.CUSTOMER_TRX_LINE_ID
AND JTLA.TRX_CURRENCY_CODE NOT IN 'INR'
AND RCTA.EXCHANGE_RATE IS NOT NULL)
FORIGN_VALUE
, NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id =
jtt.tax_type_id(+)
and jtt.tax_type_code like '%CGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE NOT IN
'INR'),0) +
NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id =
jtt.tax_type_id(+)
and jtt.tax_type_code like '%SGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE NOT IN
'INR'),0)+
NVL((SELECT jtla.unround_tax_amt_tax_curr
from jai_tax_types jtt
where jtla.tax_type_id =
jtt.tax_type_id(+)
and jtt.tax_type_code like '%IGST%'
AND jtla.TAX_EVENT_CLASS_CODE LIKE
'%SALES_TRANSACTION%'
and jtla.TRX_CURRENCY_CODE NOT IN
'INR'),0) FORIGN_CURRENCY_VALUE_GST
FROM
JAI_TAX_LINES_ALL JTLA
,RA_CUSTOMER_TRX_LINES_ALL
RCTLA
,RA_CUSTOMER_TRX_ALL
RCTA
,HZ_CUST_ACCOUNTS
CUST_ACCT
,HZ_PARTIES PARTY
,jai_tax_types jtt
WHERE
JTLA.TRX_ID=RCTA.CUSTOMER_TRX_ID
AND JTLA.TRX_LINE_ID =
RCTLA.CUSTOMER_TRX_LINE_ID
and
RCTLA.CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID
AND
RCTA.BILL_TO_CUSTOMER_ID=CUST_ACCT.CUST_ACCOUNT_ID
and CUST_ACCT.PARTY_ID
=PARTY.PARTY_ID
and jtla.tax_type_id =
jtt.tax_type_id(+)
and RCTA.TRX_NUMBER
=P_TRX_NUMBER
GROUP BY
JTLA.THIRD_PARTY_PRIMARY_REG_NUM
,PARTY.PARTY_NAME
,RCTA.TRX_NUMBER
,RCTA.TRX_DATE
,RCTLA.LINE_NUMBER
,RCTLA.DESCRIPTION
,jtla.unround_tax_amt_tax_curr
,jtla.tax_type_id
,jtla.TAX_EVENT_CLASS_CODE
,jtla.TRX_CURRENCY_CODE
,jtla.TRX_LINE_ID
,RCTA.EXCHANGE_RATE
,jtt.TAX_TYPE_CODE
