JA India - Service Tax Credit Register - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Service Tax Credit Register Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIRGMCR_XML
DB package: JA_JAINRGCR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

SELECT  trxrefs.invoice_id             invoice_id2
                  ,trxrefs.item_id                item_id
                  ,trxrefs.organization_id        org_id
                 ,TO_NUMBER(NULL)                src_doc_id
                , sum(trxrecs.credit_amount)    tax_amount     
               ,trxrefs.tax_rate                st_rate
               ,sum(trxrefs.recoverable_amount) ST 
               ,'AP'                            source_type2
               ,trxrefs.tax_type                tax_type
             , apa.invoice_amount             value
             , trxrecs.service_type_code	service_type_code, 
	JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(trxrecs.service_type_code) CF_Service_type, 
	JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, trxrefs.tax_rate, sum ( trxrefs.recoverable_amount ), apa.invoice_amount) CF_VALUE, 
	JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), trxrefs.item_id, :SOURCE_TYPE1) CF_DESCRIPTION
FROM    
        jai_rgm_trx_records          trxrecs,
        jai_rgm_trx_refs             trxrefs,
        ap_invoices_all              apa  
WHERE   trxrefs.reference_id  = trxrecs.reference_id
AND     trxrecs.source        in ( 'AP')
AND     trxrecs.regime_primary_regno = :p_regm_prmy_regn
AND     trxrecs.regime_code          = 'SERVICE'
AND     trunc(trxrecs.transaction_date) between
                                     trunc(:p_from_date) AND trunc (:p_to_date)
AND      trxrefs.invoice_id   = :invoice_id1 
AND      :source_type1 = 'AP'
AND     apa.invoice_id        =  trxrefs.invoice_id
Group By
        trxrefs.invoice_id
       ,trxrefs.item_id
       ,trxrefs.organization_id
       ,trunc(trxrefs.creation_date)
       ,trxrefs.tax_rate
       ,trxrefs.tax_type
       ,'AP'
       ,apa.invoice_amount
       ,trxrecs.service_type_code  
UNION
SELECT
        TO_NUMBER(NULL)           invoice_id2      ,
        TO_NUMBER(NULL)           item_id         ,
        TO_NUMBER(NULL)           org_id          ,
        jrtr.source_document_id   src_doc_id      ,
        sum(jrtr.credit_amount)   tax_amount      ,
            TO_NUMBER(NULL)       st_rate         ,
            TO_NUMBER(NULL)        ST             ,
            'DISTRIBUTION'         source_type2    ,
            jrtr.tax_type                      tax_type       ,
            TO_NUMBER(NULL)           value 	  ,
            jrtr.service_type_code service_type_code, 
	JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(jrtr.service_type_code) CF_Service_type, 
	JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL)) CF_VALUE, 
	JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), TO_NUMBER(NULL), :SOURCE_TYPE1) CF_DESCRIPTION
FROM
      jai_rgm_trx_records jrtr
WHERE
      jrtr.source               = 'SERVICE_DISTRIBUTE_IN'
AND   jrtr.regime_code          = 'SERVICE'
AND   jrtr.regime_primary_regno = :p_regm_prmy_regn
AND   trunc(jrtr.transaction_date) BETWEEN
                   trunc(:p_from_date) AND trunc (:p_to_date)
and   :source_type1 = 'DISTRIBUTION'      
and   jrtr.source_document_id  = :invoice_id1        
GROUP BY  to_number(null)
        , jrtr.transaction_date
                ,jrtr.source_document_id
                , jrtr.tax_type
                , 'DISTRIBUTION'
                ,jrtr.service_type_code  
UNION
SELECT
        TO_NUMBER(NULL)           invoice_id2    ,
        TO_NUMBER(NULL)           item_id       ,
        TO_NUMBER(NULL)           org_id        ,
        jrtr.source_document_id  src_doc_id     ,
        sum(jrtr.credit_amount)  tax_amount     ,  
        jrtr.tax_rate              st_rate        ,
    TO_NUMBER(NULL)              ST             ,
    'MANUAL'                    source_type2     ,
    jrtr.tax_type               tax_type        ,
    jrtr.assessable_value          value        ,
    jrtr.service_type_code	service_type_code, 
	JA_JAINRGCR_XMLP_PKG.cf_service_typeformula(jrtr.service_type_code) CF_Service_type, 
	JA_JAINRGCR_XMLP_PKG.cf_valueformula(:INVOICE_ID1, :SOURCE_TYPE1, jrtr.tax_rate, TO_NUMBER(NULL), jrtr.assessable_value) CF_VALUE, 
	JA_JAINRGCR_XMLP_PKG.cf_descriptionformula(TO_NUMBER ( NULL ), TO_NUMBER(NULL), :SOURCE_TYPE1) CF_DESCRIPTION
FROM
      jai_rgm_trx_records jrtr ,
     JAI_RGM_MANUAL_TRXS        jmt  ,
      po_vendor_sites_all pvsa
WHERE
      jrtr.source               = 'MANUAL'
AND   jrtr.regime_code          = 'SERVICE'
AND   jrtr.source_document_id = jmt.transaction_number
AND   pvsa.vendor_id        = jmt.party_id
AND   pvsa.vendor_site_id   = jmt.party_site_id
AND   jrtr.source_trx_type      IN ('ADJUSTMENT-RECOVERY','RECOVERY')
AND   jrtr.regime_primary_regno = :p_regm_prmy_regn
AND   trunc(jrtr.transaction_date) BETWEEN
                   trunc(:p_from_date) AND trunc(:p_to_date)
AND :source_type1 = 'MANUAL'   
AND jrtr.source_document_id = :invoice_id1 
GROUP BY
        TO_NUMBER(NULL)
       ,jrtr.transaction_date
       ,jrtr.tax_type
       ,jrtr.assessable_value
       ,jrtr.source_document_id 
       ,jrtr.tax_rate
       ,'MANUAL'
       ,jrtr.service_type_code  
ORDER BY 9 desc
Parameter Name SQL text Validation
Primary registration
 
LOV Oracle
From Date
 
Date
To Date
 
Date