Oracle apps technical tutorial

SQL Query to find RCM Invoices in Oracle Apps

SQL Query to find RCM Invoices in Oracle Apps


Below query will help you to find RCM invoices in Oracle Apps.

Reverse charge applies to the transactions, the system distributes the tax amounts that the goods or service receiver must pay to the supplier and to the tax authority based on the provider and receiver percentages: Oracle

This query has been developed for India Localization.


SELECT aia.INVOICE_id ,AIL.line_number, aia.invoice_type_lookup_code
     ,AIA.doc_sequence_value voucher_number 
    , AIA.invoice_num
    , aiA.INVOICE_DATE
    ,aiA.gl_date inv_header_gl_date
    ,TO_CHAR( AIDA.accounting_date,'MMYYYY') gl_period
    ,AIDA.accounting_date posted_date
    ,GCC.segment5  ACCT_CODE	
    ,GCC.concatenated_segments account_description
    ,(SELECT GCC.segment5
     FROM po_distributions_all PD
          , gl_code_combinations_kfv GCC
     WHERE PD.po_distribution_id = AIL.po_distribution_id
     AND GCC.code_combination_id = PD.code_combination_id
     AND ROWNUM = 1) PO_CHARGE_ACCT_CODE
          
    ,(SELECT JTL.TAX_REGIME_code 
        FROM JAI_TAX_LINES JTL
        WHERE 1 = 1
        and JTL.trx_id = AiA.invoice_id
        and JTL.trx_line_number = AIL.line_number
        AND JTL.ORG_ID = AIA.ORG_ID
        AND ROWNUM = 1) TAX_REGIME_code
    , APS.vendor_name

        
        ,(SELECT JTL.third_party_primary_reg_num 
        FROM JAI_TAX_LINES JTL
        WHERE 1 = 1
        and JTL.trx_id = AiA.invoice_id
        and JTL.trx_line_number = AIL.line_number
        AND JTL.ORG_ID = AIA.ORG_ID
        AND ROWNUM = 1) vendor_gstin
     
        , (SELECT HL.location_code
    FROM hr_locations_all HL
    , po_headers_all PH
    WHERE PH.po_header_id = NVL(AIA.quick_po_header_id , AIA.po_header_id)
    AND PH.bill_to_location_id = HL.location_id
    AND rownum = 1) bill_to_location

    ,(SELECT JTL.location_code 
        FROM JAI_TAX_LINES_V JTL
        WHERE 1 = 1
        and JTL.trx_id = AiA.invoice_id
        and JTL.trx_line_number = AIL.line_number
        AND JTL.ORG_ID = AIA.ORG_ID
        AND ROWNUM = 1)ship_to_location
     , (SELECT  hl.loc_information15
        FROM 
        hr_locations_all hl
       ,JAI_TAX_LINES JTL
    WHERE  JTL.tax_regime_code = 'IN-GST'
    and hl.location_id = JTL.location_id
    and JTL.trx_id = AiA.invoice_id
    and JTL.trx_line_number = AIL.line_number
    and ROWNUM = 1
     ) city

        , (SELECT  hl.loc_information16
    FROM  hr_locations_all hl ,JAI_TAX_LINES JTL
    WHERE  JTL.tax_regime_code = 'IN-GST'
    and hl.location_id = JTL.location_id
    and JTL.trx_id = AiA.invoice_id
    and JTL.trx_line_number = AIL.line_number
    and ROWNUM = 1
    ) state  
    ,(SELECT JTL.first_party_primary_reg_num 
        FROM JAI_TAX_LINES JTL
        WHERE 1 = 1
        and JTL.trx_id = AiA.invoice_id
        and JTL.trx_line_number = AIL.line_number
        AND JTL.ORG_ID = AIA.ORG_ID
        AND ROWNUM = 1)OLA_gstin

    ,(select JTC.tax_category_name
        from jai_tax_categories_v JTC
        , jai_tax_det_factors JTDF 
        ,JAI_TAX_LINES JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    and JTL.trx_line_number = AIL.line_number
    and JTDF.det_factor_id = JTL.det_factor_id
    AND NVL(JTDF.override_tax_category_id, JTDF.default_tax_category_id) = JTC.tax_category_id
    AND rownum = 1) tax_category        
    ,NVL(AIL.base_amount, AIL.amount) taxable_amount

    , (select NVL(JTLV.hsn_code_id,JTLV.sac_code_id)
    from jai_tax_det_fct_lines_v JTLV
    WHERE JTLV.entity_code               = 'AP_INVOICES'
    and JTLV.trx_id = AIA.invoice_id
    --and JTL.trx_line_number = AIL.line_number
    AND rownum = 1) hsn_sac_code
     
    ,AIDA.line_type_lookup_code
    ,AIDA.description
    ,AIA.org_id
    ,AIA.invoice_amount
    ,AIA.wfapproval_status
    ,AIA.invoice_type_lookup_code 
     
     
             , DECODE (
            AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS (
               AIA.INVOICE_ID,
               NVL(AIA.base_amount,AIA.INVOICE_AMOUNT),
               AIA.PAYMENT_STATUS_FLAG,
               AIA.INVOICE_TYPE_LOOKUP_CODE
            ),
            'FULL',
            'Fully Applied',
            'NEVER APPROVED',
            'Never Validated',
            'NEEDS REAPPROVAL',
            'Needs Revalidation',
            'CANCELLED',
            'Cancelled',
            'UNPAID',
            'Unpaid',
            'AVAILABLE',
            'Available',
            'UNAPPROVED',
            'Unvalidated',
            'APPROVED',
            'Validated',
            'PERMANENT',
            'Permanent Prepayment',
            NULL
         ) invoice_status
         , decode(ap_invoices_pkg.get_posting_status(AIA.invoice_id),'S','Selected', 'Y','Posted', 'N', 'UnPosted', 'P','Partial') posting_status

        ,AIL.description
        ,AIL.item_description
        ,APSSA.vendor_site_code
        
---------------------CGST---------------------------------------
           , (SELECT TAX_RATE_NAME 
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%CGST%'
    AND rownum = 1) cgst_rate 
    , (SELECT rounded_tax_amt_fun_curr
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%CGST%'
    ) cgst_amt   
    
       , (SELECT recoverable_flag
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AIA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%CGST%'
    AND rownum = 1) cgst_recoverable_flag
----------------------SGST------------------------------------
           , (SELECT TAX_RATE_NAME 
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%SGST%'
    AND rownum = 1) SGST_rate 
    , (SELECT rounded_tax_amt_fun_curr
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%SGST%'
    ) SGST_amt   
    
       , (SELECT recoverable_flag
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AIA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%SGST%'
    AND rownum = 1) SGST_recoverable_flag
    
----------------------------IGST--------------------------------    

           , (SELECT TAX_RATE_NAME 
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%IGST%'
    AND rownum = 1) IGST_rate 
    , (SELECT rounded_tax_amt_fun_curr
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%IGST%'
    ) IGST_amt   
    
       , (SELECT recoverable_flag
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AIA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%IGST%'
    AND rownum = 1) IGST_recoverable_flag
    
----------------------------CESS-------------------------------

           , (SELECT TAX_RATE_NAME 
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND upper(TAX_RATE_NAME) LIKE '%CESS%'
    AND rownum = 1) CESS_rate 
    , (SELECT rounded_tax_amt_fun_curr
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND upper(TAX_RATE_NAME) LIKE '%CESS%'
    ) CESS_amt   
    
       , (SELECT recoverable_flag
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AIA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND upper(TAX_RATE_NAME) LIKE '%CESS%'
    AND rownum = 1) CESS_recoverable_flag


----------------------------UTGST-------------------------------

           , (SELECT TAX_RATE_NAME 
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%UTGST%'
    AND rownum = 1) UTGST_rate 
    , (SELECT rounded_tax_amt_fun_curr
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AiA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%UTGST%'
    ) UTGST_amt   
    
       , (SELECT recoverable_flag
    FROM jai_tax_lines_v JTL
    WHERE JTL.entity_code               = 'AP_INVOICES'
    AND JTL.tax_regime_code = 'IN-GST'
    and JTL.trx_id = AIA.invoice_id
    AND JTL.trx_line_number = AIL.line_number
    AND TAX_RATE_NAME LIKE '%UTGST%'
    AND rownum = 1) UTGST_recoverable_flag

     
FROM            ap_invoices_all aia,
                ap_suppliers aps,
                ap_supplier_sites_all apssa,
                ap_lookup_codes apc,                
                ap_invoice_distributions_all aida,
                AP_INVOICE_LINES_ALL AIL,
                gl_code_combinations_kfv GCC
                
          WHERE aps.vendor_id = aia.vendor_id
            AND aia.vendor_site_id = apssa.vendor_site_id
            AND AIA.INVOICE_ID = AIL.INVOICE_ID
            AND AIDA.invoice_line_number = AIL.line_number
            AND apc.lookup_type = 'INVOICE TYPE'
            AND apc.lookup_code = aia.invoice_type_lookup_code
            AND aia.invoice_id = aida.invoice_id                     
            AND  GCC.code_combination_id = AIDA.dist_code_combination_id
            --and aiA.invoice_num = 'BAN/2017/0011'
            AND EXISTS (SELECT 1 FROM JAI_TAX_LINES JTL 
                WHERE 1=1 
                AND  jtl.self_assessed_flag = 'Y' 
                AND jtl.RECOVERABLE_FLAG = 'Y'
                AND aiA.invoice_id = jtl.trx_id
                AND aida.invoice_line_number = jtl.trx_line_id
                )
            and aida.accounting_date BETWEEN :1 AND :2;



Leave a Reply