JA India - Cenvat Monthly Returns - draft

SELECT
        rg23.organization_id                                            ,
        rg23.location_id                                                ,
        rg23.excise_invoice_no                                          ,
        trunc(rg23.excise_invoice_date) excise_invoice_date           ,
        sum( nvl(rg23.cr_basic_ed,0) -
             nvl(rg23.dr_basic_ed,0)
           )                                  cenvat                    ,
      sum( nvl(rg23.cr_other_ed,0)  -
             nvl(rg23.dr_other_ed,0)
           )                                  sed                       ,
        sum( nvl(rg23.cr_additional_ed,0) -
             nvl(rg23.dr_additional_ed,0)
           )                                  additional_duty           ,
        sum( nvl(rg23.cr_additional_cvd,0) -
             nvl(rg23.dr_additional_cvd,0)
           )                                  additional_cvd           , 
        max( nvl(rg23.cr_basic_ed,0)       + 
             nvl(rg23.cr_other_ed,0)       +
             nvl(rg23.cr_additional_ed,0)  + 
             nvl(rg23.cr_additional_cvd,0)                        
           )                                  max_transaction_amount    , 
        'AED(TTA)'                                                      ,
        'AED(GSI)'                                                      ,
        'OTHER',
	max(transaction_source_num) transaction_id, 
	JA_JAINMCEN_XMLP_PKG.cf_rounded_cenvat_amtformula(rg23.excise_invoice_no, rg23.excise_invoice_date, sum ( nvl ( rg23.cr_basic_ed , 0 ) - nvl ( rg23.dr_basic_ed , 0 ) )) CF_rounded_cenvat_amt, 
	JA_JAINMCEN_XMLP_PKG.cf_sedformula(sum ( nvl ( rg23.cr_other_ed , 0 ) - nvl ( rg23.dr_other_ed , 0 ) )) CF_sed, 
	JA_JAINMCEN_XMLP_PKG.cf_addlcvdformula(sum ( nvl ( rg23.cr_additional_cvd , 0 ) - nvl ( rg23.dr_additional_cvd , 0 ) )) CF_addlcvd, 
	JA_JAINMCEN_XMLP_PKG.cf_addlformula(sum ( nvl ( rg23.cr_additional_ed , 0 ) - nvl ( rg23.dr_additional_ed , 0 ) )) CF_addl, 
	JA_JAINMCEN_XMLP_PKG.CF_POP_COUNTFORMULA CF_pop_count,
	JA_JAINMCEN_XMLP_PKG.CP_sh_edu_p CP_sh_edu,
	JA_JAINMCEN_XMLP_PKG.CP_edu_p CP_edu,
	JA_JAINMCEN_XMLP_PKG.CP_addlcvd_p CP_addlcvd,
	JA_JAINMCEN_XMLP_PKG.CP_addl_p CP_addl,
	JA_JAINMCEN_XMLP_PKG.CP_sed_p CP_sed
FROM
        JAI_CMN_RG_23AC_II_TRXS                    rg23
WHERE
        rg23.register_type          =   :p_register_type                                     AND
        (rg23.opening_balance        <=   rg23.closing_balance  
          OR  exists (select 1 from rcv_transactions aa
                           where (aa.transaction_id = rg23.receipt_ref OR to_char(aa.transaction_id) = rg23.reference_num)
                           and ( aa.transaction_type IN ('RECEIVE' , 'DELIVER', 'RETURN TO RECEIVING')
                                    OR (aa.transaction_type ='CORRECT' and exists (select 1 from rcv_transactions where transaction_id = aa.parent_transaction_id 
                                                                                                                   and transaction_type IN ('RECEIVE' , 'DELIVER', 'RETURN TO RECEIVING'))
                                           )
                                  )
                           )
        ) AND
        rg23.organization_id        =   :p_organization_id                                   AND
        rg23.location_id            =   :p_location_id                                       AND
        to_date(trunc(rg23.creation_date),'DD-MON-YY')   BETWEEN nvl(:p_trn_from_date,to_date(trunc(rg23.creation_date),'DD-MON-YY'))  AND
                                            nvl(:p_trn_to_date , to_date(trunc(sysdate),'DD-MON-YY'))            AND
       inventory_item_id           <> 0                                                          AND
         nvl(rounding_id,9999)      <> -1
GROUP BY
        rg23.organization_id                    ,
        rg23.location_id                        ,
        rg23.excise_invoice_no                  ,
        rg23.excise_invoice_date                ,
        'AED(TTA)'                              ,
        'AED(GSI)'                              ,
        'OTHER'
ORDER BY
        rg23.excise_invoice_no                  ,
        rg23.excise_invoice_date