question

select distinct e.ecf_no 'ECF No',convert(varchar,di.docinward_date,106) 'ECF Receipt Date' ,e.ecf_amount 'ECF Amount',e.ecf_description 'ECF Description',i.invoice_type ,em.employee_code ,em.employee_name,case i.invoice_type when 'S' then s.supplierheader_suppliercode else case when i.invoice_supplier_gid IS NULL or i.invoice_supplier_gid=0 or i.invoice_supplier_gid='' then 'NULL' else s.supplierheader_suppliercode end end 'Vendor Code' ,case i.invoice_type when 'S' then s.supplierheader_name else case when i.invoice_supplier_gid IS NULL or i.invoice_supplier_gid=0 or i.invoice_supplier_gid=''then 'NULL' else s.supplierheader_name end end'Vendor Name',sc.suppliercontact_address1 'Vendor Address' ,scty.city_name 'City',sstat.state_name 'State Name' ,sc.suppliercontact_pincode 'Pincode',s.supplierheader_panno 'Vendor PAN' ,o.organizationtype_name 'Vendor Type' ,isnull((select top 1 td.taxdetails_regno from asms_trn_ttaxdetails td where td.taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'STAX' and tax_isremoved = 'N') and td.taxdetails_supplierheader_gid =s.supplierheader_gid and td.taxdetails_isremoved = 'N' ),'-') 'Vendor Service Tax Registration No' ,isnull((select top 1 td.taxdetails_regno from asms_trn_ttaxdetails td where td.taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'VAT' and tax_isremoved = 'N') and td.taxdetails_supplierheader_gid =s.supplierheader_gid and td.taxdetails_isremoved = 'N' ),'-') 'Vendor VAT Registration No',(select top 1 tdsdetails_certificateno + '~' + cast(tdsdetails_exemption_thresholdvalue as varchar) +'~'+ convert(varchar,tdsdetails_exemption_periodfrom,106) + '~' + convert(varchar,tdsdetails_exemption_periodto,106)from asms_trn_ttdsdetails inner join asms_trn_ttaxdetails on taxdetails_gid=tdsdetails_taxdetails_gid and taxdetails_isremoved='N' where taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'TDS' and tax_isremoved = 'N') and s.supplierheader_gid=taxdetails_supplierheader_gid and tdsdetails_isremoved='N' group by tdsdetails_certificateno + '~' + cast(tdsdetails_exemption_thresholdvalue as varchar) +'~'+ convert(varchar,tdsdetails_exemption_periodfrom,106) + '~' + convert(varchar,tdsdetails_exemption_periodto,106)) as 'TDSCERTNO~THRESOLDVAL~EXCEMPERIODFROM~EXCEMPERIODTO' ,(select sum(invoice_amount ) from iem_trn_tinvoice sinv where sinv.invoice_supplier_gid= s.supplierheader_gid and sinv.invoice_isremoved='N' and sinv.invoice_date >='2015-04-01' and sinv.invoice_date <='2015-03-31') as 'Consumed Value' ,convert(varchar,e.ecf_claim_month,106) 'Expense Month/Advance Month',ecs.ecfstatus_name 'ECF Status' ,convert(varchar,p.paymenttrans_insert_date ,106)'Payment Date',convert(varchar,p.paymenttrans_insert_date ,106)'OGL Value Date' ,convert(varchar,p.paymenttrans_insert_date ,106)'OGL Tran Date',convert(varchar,i.invoice_date,106) 'Invoice Date' ,i.invoice_no 'Invoice no',i.invoice_amount 'Invoice Amount',i.invoice_remark 'Invoice Remarks' ,d.debitline_gl_no 'Expense GL code',(select gl_name from iem_mst_tgl where gl_no=d.debitline_gl_no ) as 'GL Desc' ,ec.expcat_name+ '~'+ec.expcat_code 'Expense Category',es.expsubcat_name+'~'+es.expsubcat_code 'Expense Sub-category' ,isnull(d.debitline_desc,'-') 'Description/Narration',r.employee_code 'Raiser ID' ,r.employee_name 'Raiser Name',b.branch_code 'Raiser Branch Code' ,b.branch_name 'Raiser Branch Name',ci.city_name 'Supplier Branch City' ,st.state_name 'Supplier Branch State',d.debitline_ou_code 'OU Code', (select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='STAX' and invoicetax_isremoved ='N'and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no ) as 'STTAXCode~STTaxname~STRate~STTAXABLEAMT~STAmt~STGL',(select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='SBC' and invoicetax_isremoved ='N'and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no ) as 'SBCode~Swatch Bharat Name~SBRate~SBTAXABLEAMT~SBAmt~SBGL', (select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='KKC' and invoicetax_isremoved ='N' and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+ taxsubtype_name + '~' +cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no ) as 'KKCCode~KKC~KKCRate~KKCTAXABLEAMT~KKCAmt~KKCGL', (select top 1 taxsubtype_code+'~'+taxsubtype_name + '~' +cast(withholdtax_rate as NCHAR) + '~' + cast(withholdtax_taxable_amount as NCHAR ) + '~' +cast (withholdtax_amount as NCHAR ) + '~' +withholdtax_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_twithholdtax on tax_gid =withholdtax_tax_gid and taxsubtype_gid =withholdtax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='TDS'and withholdtax_isremoved ='N'and withholdtax_invoice_gid =i.invoice_gid group by taxsubtype_code+'~'+taxsubtype_name + '~' +cast(withholdtax_rate as NCHAR) + '~' + cast(withholdtax_taxable_amount as NCHAR ) + '~' +cast (withholdtax_amount as NCHAR ) + '~' + withholdtax_gl_no ) as 'TDScode~TDSTaxname~TDSRate~TDSTAXABLEAMT~TDSAmt~TDSGL', (select top 1 taxsubtype_code+'~'+taxsubtype_name + '~' + cast(withholdtax_rate as NCHAR) + '~' +cast(withholdtax_taxable_amount as NCHAR ) + '~' + cast (withholdtax_amount as NCHAR ) + '~' +withholdtax_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_twithholdtax on tax_gid =withholdtax_tax_gid and taxsubtype_gid =withholdtax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='WCT' and withholdtax_isremoved ='N'and withholdtax_invoice_gid =i.invoice_gid group by taxsubtype_code+'~'+taxsubtype_name + '~' +cast(withholdtax_rate as NCHAR) + '~' + cast(withholdtax_taxable_amount as NCHAR ) + '~' +cast (withholdtax_amount as NCHAR ) + '~' + withholdtax_gl_no ) as 'WCTCode~WCTTaxname~WCTRate~WCTTAXABLEAMT~WCTAmt~WCTGL' ,d.debitline_cc_code as 'CC Code',d.debitline_product_code as 'Product COde' ,i.invoice_gid as 'Invoice Gid',d.debitline_amount as 'Debit Amount' ,d.debitline_gid as 'DebitGid',(select top 1 cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020008' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N' group by cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' + gl_name + '~' + hsn_code ) as 'CGSTRate~CGSTTAXABLEAMT~CGSTAmt~CGSTGL~GLNAME~HSNCODE',(select top 1 cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020007' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N'group by cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code) as 'SGSTRate~SGSTTAXABLEAMT~SGSTAmt~SGSTGL~GLNAME~HSNCODE', (select top 1 cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020009' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N' group by cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code ) as 'IGSTRate~IGSTTAXABLEAMT~IGSTAmt~IGSTGL~GLNAME~HSNCODE', sl.state_name as providerstate ,i.invoice_gstin_vendor, pl.state_name Receiverstate , invoice_gstin_ficcl from iem_trn_tecf e inner join iem_trn_tinvoice as i on i.invoice_ecf_gid = e.ecf_gid and i.invoice_isremoved = 'N' Inner join iem_trn_tdebitline as d on d.debitline_invoice_gid = i.invoice_gid and d.debitline_isremoved = 'N' inner join iem_trn_tcreditline as c on c.creditline_invoice_gid = i.invoice_gid and c.creditline_isremoved = 'N' left join iem_trn_tpaymenttrans as p on p.paymenttrans_ecf_gid = e.ecf_gid and p.paymenttrans_inv_gid = i.invoice_gid and p.paymenttrans_isremoved = 'N' left join iem_trn_tdocinward as di on di.docinward_doc_no=e.ecf_no and di.docinward_isremoved = 'N' left join asms_trn_tsupplierheader as s on s.supplierheader_gid = i.invoice_supplier_gid and s.supplierheader_isremoved = 'N' left join (select suppliercontact_supplierheader_gid,suppliercontact_isremoved,min(suppliercontact_city_gid) suppliercontact_city_gid ,min(suppliercontact_state_gid) suppliercontact_state_gid,max(suppliercontact_address1) suppliercontact_address1 ,min(suppliercontact_pincode ) suppliercontact_pincode from asms_trn_tsuppliercontact group by suppliercontact_supplierheader_gid,suppliercontact_isremoved) as sc on sc.suppliercontact_supplierheader_gid=s.supplierheader_gid and suppliercontact_isremoved = 'N' left join asms_mst_torganizationtype as o on o.organizationtype_gid=s.supplierheader_organizationtype_gid and o.organizationtype_isremoved = 'N' left join iem_mst_texpcat as ec on ec.expcat_gid=d.debitline_expcat_gid and ec.expcat_isremoved = 'N' left join iem_mst_texpsubcat as es on es.expsubcat_gid=d.debitline_expsubcat_gid and es.expsubcat_isremoved = 'N' left join iem_mst_temployee as em on em.employee_gid = i.invoice_employee_gid and em.employee_isremoved = 'N' left join iem_mst_temployee as r on r.employee_gid = e.ecf_raiser and r.employee_isremoved = 'N' left join iem_mst_tbranch as b on b.branch_code = d.debitline_ou_code and b.branch_isremoved = 'N' LEFT join iem_mst_tcity as ci on ci.city_gid = r.employee_city_gid and ci.city_isremoved = 'N' LEFT join iem_mst_tstate st on st.state_gid = ci.city_state_gid and st.state_isremoved = 'N' left join iem_mst_tcity as scty on scty.city_gid = sc.suppliercontact_city_gid and scty.city_isremoved = 'N' left join iem_mst_tstate sstat on sstat.state_gid = sc.suppliercontact_state_gid and sstat.state_isremoved = 'N' left join iem_mst_tstate sl on i.invoice_provider_location =sl.state_gid and sl.state_isremoved='N' left join iem_mst_tstate pl on i.invoice_receiver_location =pl.state_gid and pl.state_isremoved='N' inner join iem_mst_tecfstatus ecs on ecs.ecfstatus_status = e.ecf_status and ecs.ecfstatus_isremoved = 'N' where e.ecf_isremoved = 'N' and convert(date,p.paymenttrans_insert_date,106) >= '2017-08-01' and convert(date,p.paymenttrans_insert_date,106) <='2017-08-31' union all select distinct e.ecf_no 'ECF No',convert(varchar,di.docinward_date,106) 'ECF Receipt Date' ,e.ecf_amount 'ECF Amount',e.ecf_description 'ECF Description',i.invoice_type,em.employee_code ,em.employee_name, case i.invoice_type when 'S' then s.supplierheader_suppliercode else case when i.invoice_supplier_gid IS NULL or i.invoice_supplier_gid=0 or i.invoice_supplier_gid=''then 'NULL' else s.supplierheader_suppliercode end end 'Vendor Code' ,case i.invoice_type when 'S' then s.supplierheader_name else case when i.invoice_supplier_gid IS NULL or i.invoice_supplier_gid=0 or i.invoice_supplier_gid=''then 'NULL' else s.supplierheader_name end end'Vendor Name',sc.suppliercontact_address1 'Vendor Address' ,scty.city_name 'City',sstat.state_name 'State Name' ,sc.suppliercontact_pincode 'Pincode',s.supplierheader_panno 'Vendor PAN' ,o.organizationtype_name 'Vendor Type' ,isnull((select top 1 td.taxdetails_regno from asms_trn_ttaxdetails td where td.taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'STAX' and tax_isremoved = 'N') and td.taxdetails_supplierheader_gid =s.supplierheader_gid and td.taxdetails_isremoved = 'N' ),'-') 'Vendor Service Tax Registration No',isnull((select top 1 td.taxdetails_regno from asms_trn_ttaxdetails td where td.taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'VAT' and tax_isremoved = 'N') and td.taxdetails_supplierheader_gid =s.supplierheader_gid and td.taxdetails_isremoved = 'N'),'-') 'Vendor VAT Registration No', (select top 1 tdsdetails_certificateno + '~' + cast(tdsdetails_exemption_thresholdvalue as varchar) +'~'+ convert(varchar,tdsdetails_exemption_periodfrom,106) + '~' +convert(varchar,tdsdetails_exemption_periodto,106) from asms_trn_ttdsdetails inner join asms_trn_ttaxdetails on taxdetails_gid=tdsdetails_taxdetails_gid and taxdetails_isremoved='N' where taxdetails_tax_gid=(select tax_gid from iem_mst_ttax where tax_code = 'TDS' and tax_isremoved = 'N') and s.supplierheader_gid=taxdetails_supplierheader_gid and tdsdetails_isremoved='N'group by tdsdetails_certificateno + '~' + cast(tdsdetails_exemption_thresholdvalue as varchar) +'~'+ convert(varchar,tdsdetails_exemption_periodfrom,106) + '~' +convert(varchar,tdsdetails_exemption_periodto,106) ) as 'TDSCERTNO~THRESOLDVAL~EXCEMPERIODFROM~EXCEMPERIODTO',(select sum(invoice_amount ) from iem_trn_tinvoice sinv where sinv.invoice_supplier_gid= s.supplierheader_gid and sinv.invoice_isremoved='N'and sinv.invoice_date >='2015-04-01' and sinv.invoice_date <='2015-03-31' ) as 'Consumed Value',convert(varchar,e.ecf_claim_month,106) 'Expense Month/Advance Month' ,ecs.ecfstatus_name 'ECF Status',convert(varchar,p.paymenttrans_insert_date ,106)'Payment Date' ,convert(varchar,p.paymenttrans_insert_date ,106)'OGL Value Date',convert(varchar,p.paymenttrans_insert_date ,106)'OGL Tran Date' ,convert(varchar,i.invoice_date,106) 'Invoice Date',i.invoice_no 'Invoice no',i.invoice_amount 'Invoice Amount' ,i.invoice_remark 'Invoice Remarks',d.debitline_gl_no 'Expense GL code',(select gl_name from iem_mst_tgl where gl_no=d.debitline_gl_no ) as 'GL Desc' ,ec.expcat_name+ '~'+ec.expcat_code 'Expense Category',es.expsubcat_name+'~'+es.expsubcat_code 'Expense Sub-category' ,isnull(d.debitline_desc,'-') 'Description/Narration',r.employee_code 'Raiser ID' ,r.employee_name 'Raiser Name',b.branch_code 'Raiser Branch Code',b.branch_name 'Raiser Branch Name' ,ci.city_name 'Supplier Branch City',st.state_name 'Supplier Branch State',d.debitline_ou_code 'OU Code', (select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='STAX' and invoicetax_isremoved ='N'and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no ) as 'STTAXCode~STTaxname~STRate~STTAXABLEAMT~STAmt~STGL', (select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' +cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='SBC' and invoicetax_isremoved ='N' and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no ) as 'SBCode~Swatch Bharat Name~SBRate~SBTAXABLEAMT~SBAmt~SBGL', (select top 1 taxsubtype_code + '~' +taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='KKC' and invoicetax_isremoved ='N' and invoicetax_invoice_gid =i.invoice_gid group by taxsubtype_code +'~'+taxsubtype_name + '~' + cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no ) as 'KKCCode~KKC~KKCRate~KKCTAXABLEAMT~KKCAmt~KKCGL', (select top 1 taxsubtype_code+'~'+taxsubtype_name + '~' + cast(withholdtax_rate as NCHAR) + '~' +cast(withholdtax_taxable_amount as NCHAR ) + '~' + cast (withholdtax_amount as NCHAR ) + '~' +withholdtax_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_twithholdtax on tax_gid =withholdtax_tax_gid and taxsubtype_gid =withholdtax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='TDS' and withholdtax_isremoved ='N'and withholdtax_invoice_gid =i.invoice_gid group by taxsubtype_code+'~'+ taxsubtype_name + '~' +cast(withholdtax_rate as NCHAR) + '~' + cast(withholdtax_taxable_amount as NCHAR ) + '~' +cast (withholdtax_amount as NCHAR ) + '~' + withholdtax_gl_no ) as 'TDScode~TDSTaxname~TDSRate~TDSTAXABLEAMT~TDSAmt~TDSGL',(select top 1 taxsubtype_code+'~'+taxsubtype_name + '~' +cast(withholdtax_rate as NCHAR) + '~' +cast(withholdtax_taxable_amount as NCHAR ) + '~' + cast (withholdtax_amount as NCHAR ) + '~' +withholdtax_gl_no from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_twithholdtax on tax_gid =withholdtax_tax_gid and taxsubtype_gid =withholdtax_taxsubtype_gid and tax_isremoved ='N' where tax_code ='WCT' and withholdtax_isremoved ='N'and withholdtax_invoice_gid =i.invoice_gid group by taxsubtype_code+'~'+taxsubtype_name + '~' + cast(withholdtax_rate as NCHAR) + '~' +cast(withholdtax_taxable_amount as NCHAR ) + '~' + cast (withholdtax_amount as NCHAR ) + '~' +withholdtax_gl_no ) as 'WCTCode~WCTTaxname~WCTRate~WCTTAXABLEAMT~WCTAmt~WCTGL',d.debitline_cc_code as 'CC Code' ,d.debitline_product_code as 'Product COde',i.invoice_gid as 'Invoice Gid' ,d.debitline_amount as 'Debit Amount',d.debitline_gid as 'DebitGid', (select top 1 cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' + gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020008' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N'group by cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' + taxsubtype_gl_no + '~' + gl_name + '~' + hsn_code) as 'CGSTRate~CGSTTAXABLEAMT~CGSTAmt~CGSTGL~GLNAME~HSNCODE', (select top 1 cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020007' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N' group by cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code) as 'SGSTRate~SGSTTAXABLEAMT~SGSTAmt~SGSTGL~GLNAME~HSNCODE', (select top 1 cast(invoicetax_rate as NCHAR) + '~' +cast(invoicetax_taxable_amount as NCHAR ) + '~' + cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code from iem_mst_ttax inner join iem_mst_ttaxsubtype on taxsubtype_tax_gid =tax_gid and taxsubtype_isremoved ='N' left join iem_trn_tinvoicetax on tax_gid =invoicetax_tax_gid and taxsubtype_gid =invoicetax_taxsubtype_gid and tax_isremoved ='N' left join iem_mst_tgl g on g.gl_no=invoicetax_gl_no and gl_isremoved='N' left join iem_mst_thsn on hsn_gid=invoicetax_hsn_gid and hsn_isremoved='N'where taxsubtype_gl_no ='122020009' and invoicetax_invoice_gid =i.invoice_gid and invoicetax_isremoved='N'group by cast(invoicetax_rate as NCHAR) + '~' + cast(invoicetax_taxable_amount as NCHAR ) + '~' +cast (invoicetax_amount as NCHAR ) + '~' +taxsubtype_gl_no + '~' +gl_name + '~' + hsn_code ) as 'IGSTRate~IGSTTAXABLEAMT~IGSTAmt~IGSTGL~GLNAME~HSNCODE',sl.state_name as providerstate ,i.invoice_gstin_vendor, pl.state_name Receiverstate , invoice_gstin_ficcl from iem_trn_tecf e inner join iem_trn_tinvoice as i on i.invoice_ecf_gid = e.ecf_gid and i.invoice_isremoved = 'N' left join iem_trn_tdebitline as d on d.debitline_invoice_gid = i.invoice_gid and d.debitline_isremoved = 'N' inner join iem_trn_tcreditline as c on c.creditline_invoice_gid = i.invoice_gid and c.creditline_isremoved = 'N' left join iem_trn_tpaymenttrans as p on p.paymenttrans_ecf_gid = e.ecf_gid and p.paymenttrans_inv_gid = i.invoice_gid and p.paymenttrans_isremoved = 'N' left join iem_trn_tdocinward as di on di.docinward_doc_no=e.ecf_no and di.docinward_isremoved = 'N' left join asms_trn_tsupplierheader as s on s.supplierheader_gid = i.invoice_supplier_gid and s.supplierheader_isremoved = 'N' left join (select suppliercontact_supplierheader_gid,suppliercontact_isremoved,min(suppliercontact_city_gid) suppliercontact_city_gid ,min(suppliercontact_state_gid) suppliercontact_state_gid,max(suppliercontact_address1) suppliercontact_address1 ,min(suppliercontact_pincode ) suppliercontact_pincode from asms_trn_tsuppliercontact group by suppliercontact_supplierheader_gid,suppliercontact_isremoved ) as sc on sc.suppliercontact_supplierheader_gid=s.supplierheader_gid and suppliercontact_isremoved = 'N' left join asms_mst_torganizationtype as o on o.organizationtype_gid=s.supplierheader_organizationtype_gid and o.organizationtype_isremoved = 'N' left join iem_mst_texpcat as ec on ec.expcat_gid=d.debitline_expcat_gid and ec.expcat_isremoved = 'N' left join iem_mst_texpsubcat as es on es.expsubcat_gid=d.debitline_expsubcat_gid and es.expsubcat_isremoved = 'N' left join iem_mst_temployee as em on em.employee_gid = i.invoice_employee_gid and em.employee_isremoved = 'N' left join iem_mst_temployee as r on r.employee_gid = e.ecf_raiser and r.employee_isremoved = 'N' left join iem_mst_tbranch as b on b.branch_code = d.debitline_ou_code and b.branch_isremoved = 'N' LEFT join iem_mst_tcity as ci on ci.city_gid = r.employee_city_gid and ci.city_isremoved = 'N' LEFT join iem_mst_tstate st on st.state_gid = ci.city_state_gid and st.state_isremoved = 'N' left join iem_mst_tcity as scty on scty.city_gid = sc.suppliercontact_city_gid and scty.city_isremoved = 'N' left join iem_mst_tstate sstat on sstat.state_gid = sc.suppliercontact_state_gid and sstat.state_isremoved = 'N' left join iem_mst_tstate sl on i.invoice_provider_location =sl.state_gid and sl.state_isremoved='N' left join iem_mst_tstate pl on i.invoice_receiver_location =pl.state_gid and pl.state_isremoved='N' inner join iem_mst_tecfstatus ecs on ecs.ecfstatus_status = e.ecf_status and ecs.ecfstatus_isremoved = 'N'where e.ecf_isremoved = 'N' and convert(date,p.paymenttrans_insert_date,106) >= '2017-08-01' and convert(date,p.paymenttrans_insert_date,106) <='2017-08-31'and e.ecf_no not in(select e.ecf_no from iem_trn_tecf e inner join iem_trn_tinvoice as i on i.invoice_ecf_gid = e.ecf_gid and i.invoice_isremoved = 'N' Inner join iem_trn_tdebitline as d on d.debitline_invoice_gid = i.invoice_gid and d.debitline_isremoved = 'N' inner join iem_trn_tcreditline as c on c.creditline_invoice_gid = i.invoice_gid and c.creditline_isremoved = 'N' left join iem_trn_tpaymenttrans as p on p.paymenttrans_ecf_gid = e.ecf_gid and p.paymenttrans_inv_gid = i.invoice_gid and p.paymenttrans_isremoved = 'N' left join iem_trn_tdocinward as di on di.docinward_doc_no=e.ecf_no and di.docinward_isremoved = 'N' left join asms_trn_tsupplierheader as s on s.supplierheader_gid = i.invoice_supplier_gid and s.supplierheader_isremoved = 'N' left join (select suppliercontact_supplierheader_gid,suppliercontact_isremoved,min(suppliercontact_city_gid) suppliercontact_city_gid ,min(suppliercontact_state_gid) suppliercontact_state_gid,max(suppliercontact_address1) suppliercontact_address1 ,min(suppliercontact_pincode ) suppliercontact_pincode from asms_trn_tsuppliercontact group by suppliercontact_supplierheader_gid,suppliercontact_isremoved ) as sc on sc.suppliercontact_supplierheader_gid=s.supplierheader_gid and suppliercontact_isremoved = 'N' left join asms_mst_torganizationtype as o on o.organizationtype_gid=s.supplierheader_organizationtype_gid and o.organizationtype_isremoved = 'N' left join iem_mst_texpcat as ec on ec.expcat_gid=d.debitline_expcat_gid and ec.expcat_isremoved = 'N' left join iem_mst_texpsubcat as es on es.expsubcat_gid=d.debitline_expsubcat_gid and es.expsubcat_isremoved = 'N' left join iem_mst_temployee as em on em.employee_gid = i.invoice_employee_gid and em.employee_isremoved = 'N' left join iem_mst_temployee as r on r.employee_gid = e.ecf_raiser and r.employee_isremoved = 'N' left join iem_mst_tbranch as b on b.branch_code = d.debitline_ou_code and b.branch_isremoved = 'N' LEFT join iem_mst_tcity as ci on ci.city_gid = r.employee_city_gid and ci.city_isremoved = 'N' LEFT join iem_mst_tstate st on st.state_gid = ci.city_state_gid and st.state_isremoved = 'N' left join iem_mst_tcity as scty on scty.city_gid = sc.suppliercontact_city_gid and scty.city_isremoved = 'N' left join iem_mst_tstate sstat on sstat.state_gid = sc.suppliercontact_state_gid and sstat.state_isremoved = 'N' left join iem_mst_tstate sl on i.invoice_provider_location =sl.state_gid and sl.state_isremoved='N' left join iem_mst_tstate pl on i.invoice_receiver_location =pl.state_gid and pl.state_isremoved='N' inner join iem_mst_tecfstatus ecs on ecs.ecfstatus_status = e.ecf_status and ecs.ecfstatus_isremoved = 'N' where e.ecf_isremoved = 'N' and convert(date,p.paymenttrans_insert_date,106) >= '2017-08-01' and convert(date,p.paymenttrans_insert_date,106) <='2017-08-31')