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
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 |