ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0DE3869E053B46B63588994 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 JA JAIRGMCR_XML US JA India - Service Tax Credit Register - draft 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 ZHS JA 印度 - 服务税信用登记表报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - 服务税信用登记表报表 (XML) - 不支持：已保留供将来使用 Short Name: JAIRGMCR_XML DB package: JA_JAINRGCR_XMLP_PKG BI Publisher :cp_from_date :cp_to_date :invoice_id1 :p_conc_request_id :p_from_date :p_org_where :p_regm_prmy_regn :p_reporting_entity_id :p_reporting_level :p_to_date :source_type1 1 10 :p_regm_prmy_regn LOV Oracle JAI_RGM_PRIM_REGNO Y select attribute_value id, attribute_value value, attribute_value description from jai_rgm_org_regns_v where registration_type='OTHERS' and attribute_type_code='PRIMARY' and (regime_org_id,attribute_value) in (select min(bb.regime_org_id),bb.attribute_value from jai_rgm_org_regns_v bb where registration_type='OTHERS' and attribute_type_code='PRIMARY' and regime_code='SERVICE' group by bb.attribute_value ) order by value,description Y US Primary registration ZHS 主要登记 2 20 :p_from_date Date Y US From Date ZHS 自日期 3 30 :p_to_date Date Y US To Date ZHS 至日期