ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0DF3869E053B46B63588994 SELECT MONTH , YEAR , MM , SUM(TAX_AMOUNT) TAX_AMOUNT, SUM(CHARGED_BILLED) CHARGED_BILLED, SUM(service_cess_payable) SERVICE_CESS_PAYABLE , SUM(service_payable) SERVICE_PAYABLE , SUM(RECOVERED_AMOUNT) RECOVERED_AMOUNT, SERVICE_TYPE_CODE SERVICE_TYPE_CODE, JA_JAINST3_XMLP_PKG.cf_realised_amountformula(SUM ( TAX_AMOUNT ), SUM ( RECOVERED_AMOUNT ), SUM ( CHARGED_BILLED )) CF_realised_amount, JA_JAINST3_XMLP_PKG.cf_serviceformula(MONTH, YEAR) CF_service_amount, JA_JAINST3_XMLP_PKG.cf_invoice_numformula(MONTH, YEAR) CF_Invoice_num, JA_JAINST3_XMLP_PKG.cf_st_service_amountformula(MONTH, YEAR) CF_st_service_amount, JA_JAINST3_XMLP_PKG.cf_count_recordsformula(:CS_count_records) CF_count_records, JA_JAINST3_XMLP_PKG.CP_service_cess_amount_p CP_service_cess_amount, JA_JAINST3_XMLP_PKG.CP_Invoice_date_p CP_Invoice_date, JA_JAINST3_XMLP_PKG.CP_st_service_cess_amount_p CP_st_service_cess_amount FROM ( select month, year, MM, sum(tax_amount) tax_amount, sum(charged_billed) charged_billed, sum(decode(tax_type, 'SERVICE_EDUCATION_CESS', recovered_amount,0)) service_cess_payable , sum(decode(tax_type, 'Service', recovered_amount,0)) service_payable , sum(recovered_amount) recovered_amount, service_type_code from ( SELECT trxrecs.service_type_code service_type_code, TO_CHAR(trxrecs.transaction_date, 'MON') Month, TO_CHAR(trxrecs.transaction_date, 'MM') MM, TO_CHAR(trxrecs.transaction_date, 'YYYY') year, trxrecs.tax_type tax_type, trxrefs.item_id item_id, trxrefs.tax_amount tax_amount, trxrefs.taxable_basis charged_billed, trxrefs.recovered_amount recovered_amount FROM jai_rgm_trx_refs trxrefs, jai_rgm_trx_records trxrecs WHERE trxrefs.reference_id = trxrecs.reference_id AND trxrecs.source = 'AR' AND trxrecs.regime_code = 'SERVICE' AND trxrecs.regime_primary_regno = :prm_rgm_prim_regno AND trunc(trxrecs.transaction_date) BETWEEN trunc(:prm_fromdt ) AND trunc( :prm_todt ) AND trxrecs.organization_id = nvl(:prm_org_id, trxrecs.organization_id) AND trxrecs.service_type_code = :service_type_code1 UNION ALL SELECT trxrecs.service_type_code, TO_CHAR(trxrecs.transaction_date, 'MON') Month, TO_CHAR(trxrecs.transaction_date, 'MM') MM, TO_CHAR(trxrecs.transaction_date, 'YYYY') year, trxrecs.tax_type tax_type, TO_NUMBER(NULL) item_id, TO_NUMBER('0') tax_amount, TO_NUMBER('0') charged_billed, trxrecs.debit_amount recovered_amount FROM jai_rgm_trx_records trxrecs WHERE trxrecs.regime_code = 'SERVICE' AND trxrecs.regime_primary_regno = :prm_rgm_prim_regno AND trunc(trxrecs.transaction_date) BETWEEN trunc(:prm_fromdt ) AND trunc( :prm_todt ) AND trxrecs.organization_id = nvl(:prm_org_id, trxrecs.organization_id) AND ( trxrecs.source = 'SERVICE_DISTRIBUTE_OUT' OR ( trxrecs.source = 'MANUAL' AND trxrecs.source_trx_type IN ('LIABILITY','ADJUSTMENT-LIABILITY') ) ) AND trxrecs.service_type_code = :service_type_code1 ) group by month,MM,year, service_type_code UNION ALL SELECT TO_CHAR(DATE1, 'MON') MONTH , TO_CHAR(DATE1, 'RRRR') YEAR , TO_CHAR(DATE1, 'MM') MM , 0 TAX_AMOUNT, 0 CHARGED_BILLED, 0 SERVICE_CESS_PAYABLE , 0 SERVICE_PAYABLE , 0 RECOVERED_AMOUNT, :service_type_code1 SERVICE_TYPE_CODE FROM (SELECT ADD_MONTHS(:PRM_FROMDT1, ROWNUM -1) DATE1 FROM USER_OBJECTS WHERE ADD_MONTHS(:PRM_FROMDT1, ROWNUM -1) < :PRM_TODT1 ) ) where SERVICE_TYPE_CODE=:service_type_code1 GROUP BY month, year, MM,SERVICE_TYPE_CODE order by year,MM JA JAIST3_XML US JA India - Service Tax ST3 - draft Application: Asia/Pacific Localizations Source: India - Service Tax ST3 Report (XML) - Not Supported: Reserved For Future Use Short Name: JAIST3_XML DB package: JA_JAINST3_XMLP_PKG ZHS JA 印度 - 服务税 ST3 报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - 服务税 ST3 报表 (XML) - 不支持：已保留供将来使用 Short Name: JAIST3_XML DB package: JA_JAINST3_XMLP_PKG BI Publisher :cp_credit_availed :cp_educess_credit_availed :cp_invoice_date :cp_service_cess_amount :cp_st_service_cess_amount :cs_count_records :p_conc_request_id :p_regime :prm_fromdt :prm_fromdt1 :prm_org_id :prm_rgm_prim_regno :prm_service_type :prm_todt :prm_todt1 :service_type_code1 1 10 :p_regime LOV Oracle JAI_RGM_DEFINITIONS Y select regime_id id, regime_code value, null description from jai_rgm_definitions order by value,description 10001 Y US REGIME 2 20 :prm_rgm_prim_regno LOV Oracle JAI_RGM_REGISTRATION_NO Y select attribute_value id, attribute_value value, null description from jai_rgm_org_regns_v where regime_id=:$flex$.jai_rgm_definitions and 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 regime_id=:$flex$.jai_rgm_definitions and registration_type='OTHERS' and attribute_type_code='PRIMARY' group by bb.attribute_value ) order by value,description Y US Registration No ZHS 登记编号 3 30 :prm_fromdt Date Y US From Date ZHS 自日期 4 40 :prm_todt Date Y US To Date ZHS 至日期 5 50 :prm_org_id LOV Oracle JAI_SERVICE_ORGANIZATIONS Y select organization_id id, name value, null description from (select distinct name,organization_id,regime_code,attribute_value from jai_rgm_organizations_v) a where a.regime_code='SERVICE' and a.attribute_value=:$flex$.jai_rgm_registration_no order by value,description US Organization ZHS 组织 :$flex$.jai_rgm_definitions REGIME Registration No :$flex$.jai_rgm_registration_no Registration No Organization