India AR GST tax report

  WITH Parameter AS
    (SELECT :Transaction_Start_Date AS BV_Transaction_Start_Date ,
        :Transaction_End_Date AS BV_Transaction_End_Date,
:GL_Start_Date AS BV_GL_Start_Date ,
        :GL_End_Date AS BV_GL_End_Date,
:Trx_Number_from AS BV_Trx_Number_From,
:Trx_Number_To AS BV_Trx_Number_To FROM DUAL)
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
    (select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id)  Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'CGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'SGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
'CGST' Tax_Type_1,
'SGCT' Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'CGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'SGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
    AND
        j.trx_id = trx.customer_trx_id
    AND
        j.trx_line_id = cl.customer_trx_line_id
    AND
        j.tax_category_name like 'Intrastate %'
    AND
        bill.org_id  = :org_id -- India
    AND
        trx.org_id  = :org_id-- India
    AND
    trx.complete_flag = 'Y'
AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where   ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id)  Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'IGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'XXXX',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
'IGST' Tax_Type_1,
'' Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'IGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'XXXX', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( cl.quantity_invoiced is not null or cl.quantity_credited is not null or cl.extended_amount is not null)
    AND
        j.trx_id = trx.customer_trx_id
    AND
        j.trx_line_id = cl.customer_trx_line_id
    AND
     j.tax_category_name like 'Interstate %'
    AND
        bill.org_id  = :org_id -- India
    AND
        trx.org_id  = :org_id -- India
AND
    trx.complete_flag = 'Y'
    AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)
UNION ALL
SELECT
    b.name Transaction_Source,
    rt.name Transaction_Type,
    trx.trx_date Transaction_Date,
    gd.gl_date GL_Date,
    j.ship_from_state IRM_State_Ship_from,
(SELECT DISTINCT jtl.first_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') IRM_GST_Number,
    j.location_name Customer_State_Ship_to,
    party.party_name Party_Name_Customer,
    cust.account_number Customer_Account_Number,
    bill.location Party_Site_Name_Bill_to,
    (SELECT DISTINCT jtl.third_party_primary_reg_num
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Customer_GST_Number,
    trx.trx_number Invoice_Number,
    trx.invoice_currency_code Invoice_Currency,
(select max(app_trx2.trx_number)
from   
apps.ar_payment_schedules_all ps2,
apps.ar_receivable_applications_all app2,
apps.ra_customer_trx_all app_trx2
where   ps2.customer_trx_id = trx.customer_trx_id
and app2.customer_trx_id = trx.customer_trx_id
and app_trx2.customer_trx_id = app2.applied_customer_trx_id) Related_Invoice_Number,
    cl.line_number Invoice_Line_Number,
    cl.UOM_CODE UOM,
    nvl(cl.quantity_invoiced,cl.quantity_credited) Quantity,
    cl.unit_selling_price Unit_Price,
    cl.extended_amount Transaction_Line_Amount,
    cl.revenue_amount Taxable_Line_Amount,
(SELECT sum( decode(jrav.reporting_code,'CGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,
'IGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_1,
(SELECT sum( decode(jrav.reporting_code,'SGST',jtl.ROUNDED_TAX_AMT_FUN_CURR,0))
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Line_Amount_2,
    j.tax_category_name Tax_Category,
(SELECT MIN(jrav.reporting_code)
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Type_1,
(SELECT MAX(jrav.reporting_code)
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
AND jrav.reporting_code NOT IN ('CGST','IGST')
group by  jrav.reporting_code) Tax_Type_2,
'TBC' Line_Total_Gross,
    j.hsn_code HSN_Code,
    j.sac_code SAC_Code,
(SELECT max( decode(jrav.reporting_code,'CGST',jtl.tax_rate_percentage,
'IGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_1,
(SELECT max( decode(jrav.reporting_code,'SGST',jtl.tax_rate_percentage,null) )
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND trx.trx_date BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION') Tax_Rate_2,
(select    max( cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9  ) Revenue_Account
from     apps.gl_code_combinations cc,
apps.ra_cust_trx_line_gl_dist_all gld
where    gld.customer_trx_id =  trx.customer_trx_id
and      gld.customer_trx_line_id =  cl.customer_trx_line_id
and      cc.code_combination_id = Gld.Code_Combination_Id
and      gld.account_class = 'REV' ) Revenue_Account,
(SELECT   max( decode (jrav.reporting_code, 'CGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9,
 'IGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_1,
(SELECT   max( decode (jrav.reporting_code, 'SGST', cc.segment1||'.'||
         cc.segment2||'.'||
         cc.segment3||'.'||
         cc.segment4||'.'||
         cc.segment5||'.'||
         cc.segment6||'.'||
         cc.segment7||'.'||
         cc.segment8||'.'||
         cc.segment9, null) ) Expense_Account
    FROM apps.jai_tax_lines jtl,
    apps.jai_reporting_associations_v jrav,
    apps.jai_tax_accounts ta,
    apps.gl_code_combinations cc
    WHERE  jtl.trx_id = trx.customer_trx_id
      AND  jtl.trx_line_number = cl.line_number
     AND jtl.TAX_TYPE_ID       = jrav.ENTITY_ID
    AND SYSDATE BETWEEN NVL (jrav.EFFECTIVE_FROM, SYSDATE) AND NVL (jrav.EFFECTIVE_TO, SYSDATE + 1)
                AND jrav.ENTITY_CODE         = 'TAX_TYPE'
            AND jrav.REPORTING_TYPE_CODE = 'TAX_TYPES_CLASSIFICATION'
            AND ta.tax_account_entity_id = jtl.tax_type_id
          AND ta.organization_id = jtl.organization_id
          AND ta.location_id = Jtl.Location_Id
          AND cc.code_combination_id = ta.expense_ccid) Tax_Natural_Account_2
FROM
    apps.hz_cust_accounts cust,
    apps.hz_cust_acct_sites_all acct,
    apps.hz_cust_site_uses_all bill,
    apps.hz_party_sites party_site,
    apps.hz_locations loc,
    apps.hz_parties party,
    apps.ar_payment_schedules_all aps,
    apps.ra_customer_trx_all trx,
    apps.ra_cust_trx_types_all rt,
    apps.ra_batch_sources_all b,
    apps.ra_cust_trx_line_gl_dist_all gd,
    apps.ra_customer_trx_lines_all cl,
    apps.JAI_TRX_LINES_V J,
Parameter
WHERE
        cust.cust_account_id = acct.cust_account_id
    AND
        acct.cust_acct_site_id = bill.cust_acct_site_id
    AND
        acct.org_id = bill.org_id
    AND
        bill.site_use_code = 'BILL_TO'
    AND
        loc.location_id = party_site.location_id
    AND
        acct.party_site_id = party_site.party_site_id
    AND
        cust.party_id = party.party_id
    AND
        aps.customer_id (+) = cust.cust_account_id
    AND
        aps.customer_site_use_id (+) = bill.site_use_id
    AND
        trx.customer_trx_id = aps.customer_trx_id
    AND
        rt.cust_trx_type_id = trx.cust_trx_type_id
    AND
        b.batch_source_id = trx.batch_source_id
    AND
        trx.customer_trx_id = gd.customer_trx_id
    AND
        'REC' = gd.account_class
    AND
        'Y' = gd.latest_rec_flag
    AND
        cl.customer_trx_id = trx.customer_trx_id
    AND
         ( (cl.quantity_invoiced is not null or cl.quantity_credited is not null) or 
     ( cl.extended_amount is not null and rt.name = :Trx_source --'IN CM TDS')
)
    AND
        j.trx_id(+) = trx.customer_trx_id
    AND
        j.trx_line_id(+) = cl.customer_trx_line_id
    AND
        ( j.tax_category_name is null or (
      j.tax_category_name not like 'Interstate %'
  AND j.tax_category_name not like 'Intrastate %' )
)
    AND
        bill.org_id  = :org_id--379 -- India
    AND
        trx.org_id  = :org_id--379 -- India
AND
    trx.complete_flag = 'Y'
    AND
        trx.trx_number between nvl(Parameter.BV_Trx_Number_From, trx.trx_number) and  nvl(Parameter.BV_Trx_Number_To, trx.trx_number)
    AND
        trx.trx_date between nvl(Parameter.BV_Transaction_Start_Date,trx.trx_date) and nvl(Parameter.BV_Transaction_End_Date,trx.trx_date)
    AND
        gd.gl_date between nvl(Parameter.BV_GL_Start_Date,gd.gl_date) and nvl(Parameter.BV_GL_End_Date,gd.gl_date)