ORACLE MASTERMINDS

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