JA India - Cenvat Monthly Return Capital Goods-Textile - draft

select 
	rg23.organization_id,
	rg23.location_id,
	rg23.slno,
          vsite.document_type,
           rg23.excise_invoice_no,
           rg23.excise_invoice_date,
           pov.vendor_name,
           vsite.vendor_type,
           vsite.ec_code,
           rg23.transaction_date,
           rg23.cr_basic_ed,
           rg23.cr_other_ed,
            'AED(TTA)',
            'AED(GSI)',
           rg23.cr_additional_ed,
           'OTHER',msi.description,
            ja_msi.item_tariff,
            rg23.receipt_ref receipt_id,
            rg23.register_id,
            hrl.description DESCRIPTION1,
            hrl.address_line_1,
            hrl.address_line_2,
            hrl.address_line_3, 
	JA_JAINMCENCG_EXC_XMLP_PKG.cf_assesablevalueformula(rg23.receipt_ref) CF_Assesablevalue, 
	JA_JAINMCENCG_EXC_XMLP_PKG.cf_qtyformulaformula(rg23.receipt_ref, rg23.register_id) CF_QTYFORMULA
from JAI_CMN_RG_23AC_II_TRXS rg23,
            po_vendors pov,
            JAI_CMN_VENDOR_SITES vsite, 
            mtl_system_items msi,
            JAI_INV_ITM_SETUPS ja_msi,
            hr_locations hrl 
 where rg23.register_type='C'
     and rg23.opening_balance < rg23.closing_balance
     and rg23.organization_id=:P_ORGANIZATION_ID
     and rg23.location_id=:P_LOCATION_ID
     and rg23.vendor_id=pov.vendor_id
     and rg23.vendor_id=vsite.vendor_id
     and rg23.vendor_site_id = vsite.vendor_site_id
     and rg23.organization_id=msi.organization_id
     and rg23.inventory_item_id=msi.inventory_item_id
     and rg23.organization_id=ja_msi.organization_id
     and rg23.inventory_item_id=ja_msi.inventory_item_id
    and  hrl.location_id=:P_LOCATION_ID
    and trunc(rg23.transaction_date) >= 
            nvl((trunc(:P_TRN_FROM_DATE)),  
            trunc(rg23.transaction_date))
     and trunc(rg23.transaction_date) <=                   
 nvl((trunc(:P_TRN_TO_DATE)),trunc(sysdate))  
   order by rg23.slno   asc