question

Bugmesh avatar image
Bugmesh asked

Export Wizard error - Invalid object name

I am working on Exporting this result set to an Excel spreadsheet using this code: SELECT acctno,premium,convert(varchar(10),effdate,101) FROM SPECIAL_DB.DBO.AR_13_WPWithEffDate WHERE armonth in (select armonth from #ddate) and mformanual='mf' SELECT acctno,premium,convert(varchar(10),effdate,101) FROM SPECIAL_DB.DBO.AR_13_WPWithEffDate WHERE armonth in (select armonth from #ddate) and mformanual'mf' You would think this would be pretty straight forward but the following error is generated: TITLE: SQL Server Import and Export Wizard ------------------------------ The statement could not be parsed. ------------------------------ ADDITIONAL INFORMATION: Deferred prepare could not be completed. Statement(s) could not be prepared. **Invalid object name '#ddate'.** (Microsoft SQL Server Native Client 10.0) I would appreciate any assistance
export-dataparsing
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
'#ddate' is referring to a local temporary table. If that table doesn't exist before the execution of those particular statements, the query can't find the table to use in the subquery. Do you have the code that defines that temporary table? You might need to use a global temporary table if it's defined outside the context of this particular query. Hope that helps!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

daya avatar image
daya answered
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')
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A couple of comments... 1. Do you have a question? If so, try asking it as a new question, rather than as what appears to be just a slab of code 2. Please format your code so it's easily readable. This will help you understand it, and us with your question. If you have one. See (1), above.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.