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 |
