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 |