JA India - Service Tax - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Service Tax Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINSTR_XML
DB package: JA_JAINSTR_XMLP_PKG
Select 
jrtr1.organization_id org_id1, 
jrtr1.invoice_id invoice_id,
jrtr1.reference_id service_ref_id,
jrtr1.taxable_basis service_basis,
jrtr1.trx_tax_amount service_trx_amount, 
jrtr1.tax_amount service_tax_amount,
jrtr1.recoverable_ptg service_rec_percent,
jrtr1.recoverable_amount service_rec_amount,
jrtr1.recovered_amount  service_payable_amount,
jrtr2.reference_id cess_ref_id,
jrtr2.taxable_basis cess_basis,
jrtr2.trx_tax_amount cess_trx_amount,
jrtr2.tax_amount cess_tax_amount,
jrtr2.recoverable_ptg cess_rec_percent,
jrtr2.recoverable_amount cess_rec_amount,
jrtr2.recovered_amount cess_payable_amount, 
	JA_JAINSTR_XMLP_PKG.cf_receipt_amountformula(jrtr1.invoice_id) CF_RECEIPT_AMOUNT, 
	JA_JAINSTR_XMLP_PKG.cf_1formula(jrtr1.invoice_id, jrtr1.organization_id, jrtr1.taxable_basis, jrtr1.recoverable_amount, jrtr2.recoverable_amount, jrtr1.recovered_amount, jrtr2.recovered_amount) CF_INVOICE_NUMBER, 
	JA_JAINSTR_XMLP_PKG.cf_service_typeformula(jrtr1.reference_id) CF_Service_Type,
	JA_JAINSTR_XMLP_PKG.CP_GROSS_INVOICE_AMOUNT_p CP_GROSS_INVOICE_AMOUNT,
	JA_JAINSTR_XMLP_PKG.CP_TAXABLE_BASIS_p CP_TAXABLE_BASIS,
	JA_JAINSTR_XMLP_PKG.CP_SERVICE_TAX_AMOUNT_p CP_SERVICE_TAX_AMOUNT,
	JA_JAINSTR_XMLP_PKG.CP_CESS_AMOUNT_p CP_CESS_AMOUNT,
	JA_JAINSTR_XMLP_PKG.CP_SERVICE_TAX_PAY_p CP_SERVICE_TAX_PAY,
	JA_JAINSTR_XMLP_PKG.CP_sh_cess_amount_p CP_sh_cess_amount,
	'X' dummy
from jai_rgm_trx_refs jrtr1, 
        jai_rgm_trx_refs jrtr2
where jrtr1.source='AR'
    and trunc(jrtr1.creation_date) between trunc(:p_from_date) and trunc(:p_to_date)
    and jrtr1.invoice_id = jrtr2.invoice_id(+)
    and jrtr1.tax_type ='Service'
    and jrtr2.tax_type = 'SERVICE_EDUCATION_CESS'
  and jrtr1.reference_id in (select reference_id from jai_rgm_Trx_records 
                                        where source ='AR'
                                          and source_table_name ='AR_RECEIVABLE_APPLICATIONS_ALL'
                                          and source_trx_type ='RECEIPT_APPLICATION'
                                          and regime_code ='SERVICE' 
                                          and tax_type='Service'
                                         ) 
 and jrtr1.organization_id=:Org_id
order by jrtr1.creation_Date
Parameter Name SQL text Validation
Location Code
 
LOV Oracle
Organization Name
 
LOV Oracle
Registration Number
 
LOV Oracle
End Date
 
Date
Start Date
 
Date
Ask a question