Monday 27 January 2020


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