JA India - Service Tax ST3 - draft

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