------------------------------------------------ -- M_STORITVE -------------------------------------------------------------------- SELECT ti.Record_Number AS ID_STORITVE, 'ST' AS VRSTA, CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END AS ID_LASTNIKA, b.BoatNo AS ID_PLOVILA, 'MIG' AS STORITEV, CASE WHEN ti.type='M' AND coalesce(ti.DefDateFrom,' ') <> ' ' and convert(varchar, DefDateFrom, 23) <> '1899-12-30' THEN convert(varchar, DefDateFrom, 23) ELSE convert(varchar, ti.InvDate, 23) END AS DATUM_OD, CASE WHEN ti.type='M' AND coalesce(ti.DefDateTo,' ') <> ' ' and convert(varchar, DefDateTo, 23) <> '1899-12-30' THEN convert(varchar, DefDateTo, 23) ELSE convert(varchar, ti.InvDate, 23) END AS DATUM_DO, ti.Units AS KOLICINA, 0 AS POPUST, ti.postValue AS ZNESEK, ti.vatrate AS DAVEK, ti.PostValue + ti.Vat AS FULL_AMOUNT, CASE WHEN ti.invoiced='Y' THEN ti.invoiceno END AS N_RACUNA, CASE WHEN ti.invoiced='Y' THEN convert(varchar, ti.InvDate, 23) END AS DATUM_RACUNA, CASE WHEN ti.invoiced='Y' THEN 1 ELSE 0 END AS ZAKLJUCEN, replace (ti.description, char(09), ' ') AS KOMENTAR, convert(varchar, getdate(), 23) AS DATUM_KREIRANJA, convert(varchar, getdate(), 23) AS DATUM_SPREMEMBE, 'MIGRATION' AS USER_KREIRANJA, 'MIGRATION' AS USER_SPREMEMBE, 'N' AS AUTO, 0 AS POPUST1, 0 AS POPUST2, 1 AS TECAJP, 'D' AS DOMACA_VALUTA, 0 AS PROVIZIJA, 100 AS COMPLETE, 1 AS KOLICINA_INSTR, b.PrimaryContactId AS ID_LASTNIKA_ORG, CASE WHEN ti.VatCode in (2, 3, 7) THEN 2 ELSE 1 END AS ID_DAVEK FROM TInvoice ti LEFT OUTER JOIN Boat b ON ti.BoatNo=b.BoatNo JOIN VW_CRM_Boat v ON b.boatno=v.boatNo WHERE ti.Type <> 'R' AND coalesce(ti.Type,' ') <> ' ' AND ti.servcode <> '0' --AND ti.Record_Number <= 60000 --AND ti.Record_Number > 60000 ; -------------------------------------------------------------------- -- M_STORITVE_DAVEK -------------------------------------------------------------------- SELECT ti.Record_Number AS ID_STORITVE_DAVEK, ti.Record_Number AS ID_STORITVE, 'DDV' AS VRSTA, ti.VatRate AS STOPNJA, ti.PostValue AS ZNESEK_OSNOVE, ti.vat AS ZNESEK_DAVKA, CASE WHEN ti.VatCode in (2, 3, 7) THEN 2 ELSE 1 END AS ID_DAVEK, convert(varchar, getdate(), 23) AS CREATE_DATE FROM TInvoice ti LEFT OUTER JOIN Boat b ON ti.BoatNo=b.BoatNo JOIN VW_CRM_Boat v ON b.boatno=v.boatNo WHERE ti.Type <> 'R' AND coalesce(ti.Type,' ') <> ' ' AND ti.servcode <> '0' --AND ti.Record_Number <= 60000 --AND ti.Record_Number > 60000 ; -------------------------------------------------------------------- -- RACUN_DATA -------------------------------------------------------------------- SELECT ti.Record_Number AS ID_RACUN_DATA, ti.invoiceno AS N_RACUNA, ti.Units AS KOLICINA, ti.vat AS ZN_DAVKA, ti.InValue AS CENABD, ti.invalue + ti.vat AS ZNESEK, ti.Record_Number AS ID_STORITVE, ti.TransNo AS ID_SALDKONT, IsNull(ti.[lineno],0) AS ZAP_ST, 0 AS POPUST, ti.VatRate AS DAV_STOPNJA, ti.invalue AS NETO, 'I' AS TIP, CASE WHEN ti.type='M' AND coalesce(ti.DefDateFrom,' ') <> ' ' and convert(varchar, DefDateFrom, 23) <> '1899-12-30' THEN convert(varchar, DefDateFrom, 23) ELSE convert(varchar, ti.InvDate, 23) END AS DATUM_OD, CASE WHEN ti.type='M' AND coalesce(ti.DefDateTo,' ') <> ' ' and convert(varchar, DefDateTo, 23) <> '1899-12-30' THEN convert(varchar, DefDateTo, 23) ELSE convert(varchar, ti.InvDate, 23) END AS DATUM_DO, replace (ti.description, char(09), ' ') AS KOMENTAR, CASE WHEN ti.VatCode in (2, 3, 7) THEN 2 ELSE 1 END AS ID_DAVEK, b.BoatNo AS ID_PLOVILA FROM TInvoice ti LEFT OUTER JOIN Boat b ON ti.BoatNo=b.BoatNo JOIN VW_CRM_Boat v ON b.boatno=v.boatNo WHERE ti.Type <> 'R' AND coalesce(ti.Type,' ') <> ' ' AND ti.servcode <> '0' AND ti.invoiced='Y' --AND ti.Record_Number <= 60000 --AND ti.Record_Number > 60000 ; -------------------------------------------------------------------- -- SALDKONT -------------------------------------------------------------------- SELECT CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END AS ID_KUPCA, convert(varchar, tr.transdate, 23) AS DATUM, invoreceiptno AS N_RACUNA, tr.transacno AS ID_SALDKONT, b.BoatNo AS ID_PLOVILA, CASE WHEN coalesce(tr.paymentdate,' ') = ' ' THEN convert(varchar, tr.transdate, 23) ELSE convert(varchar, tr.paymentdate, 23) END AS DATUM_VALUTACIJE, 'AED' AS VALUTA_RN, 1 AS TECAJ_VALUTE, 0 AS DAVEK_NA_MATERIAL, CASE WHEN tr.transtype in ('R', 'F') THEN 0 ELSE tr.Vat END AS DAVEK_NA_STORITVE, round(tr.Value,2) * CASE WHEN tr.transtype in ('R', 'F') THEN -1 ELSE 1 END AS ZNESEK, round(tr.Value,2) * CASE WHEN tr.transtype in ('R', 'F') THEN -1 ELSE 1 END AS ZA_PLACILO, convert(varchar, getdate(), 23) AS DATUM_KREIRANJA, convert(varchar, getdate(), 23) AS DATUM_SPREMEMBE, 'MIGRATION' AS USER_KREIRANJA, 'MIGRATION' AS USER_SPREMEMBE, CASE WHEN tr.transtype = 'I' THEN 'POS' WHEN tr.transtype = 'C' THEN 'DOB' WHEN tr.transtype IN ('R', 'F') THEN 'PLA' END AS VRSTA_RACUNA, CASE WHEN tr.transtype ='R' THEN (SELECT isnull(round(sum(Alloc_amount),2),0) FROM Allocation where credit_id=tr.transacno AND Cancelled=0) WHEN tr.transtype ='I' THEN (SELECT isnull(round(sum(Alloc_amount),2),0) FROM Allocation where debit_id=tr.transacno AND Cancelled=0) WHEN tr.transtype ='C' THEN (SELECT isnull(round(sum(Alloc_amount),2),0)*-1 FROM Allocation where credit_id=tr.transacno AND Cancelled=0) WHEN tr.transtype ='F' THEN (SELECT isnull(round(sum(Alloc_amount),2),0)*-1 FROM Allocation where debit_id=tr.transacno AND Cancelled=0) END AS PORABLJENO, CASE WHEN tr.transtype ='R' THEN CASE WHEN round((SELECT sum(Alloc_amount) FROM Allocation where credit_id=tr.transacno AND Cancelled=0),2) = round(tr.Value,2)*-1 THEN 1 ELSE 0 END WHEN tr.transtype ='I' THEN CASE WHEN round((SELECT sum(Alloc_amount) FROM Allocation where debit_id=tr.transacno AND Cancelled=0),2) = round(tr.Value,2) THEN 1 ELSE 0 END WHEN tr.transtype ='C' THEN CASE WHEN round((SELECT sum(Alloc_amount)*-1 FROM Allocation where credit_id=tr.transacno AND Cancelled=0),2) = round(tr.Value,2) THEN 1 ELSE 0 END WHEN tr.transtype ='F' THEN CASE WHEN round((SELECT sum(Alloc_amount)*-1 FROM Allocation where debit_id=tr.transacno AND Cancelled=0),2) = round(tr.Value,2)*-1 THEN 1 ELSE 0 END END AS ZAPRTO, 'I' AS SDK_RN_TIP, CASE WHEN substring(ltrim(tr.Reference),1,1)='-' THEN substring(ltrim(replace (tr.Reference, char(10), ' ')),2,14) ELSE replace (tr.Reference, char(10), ' ') END AS KOMENTAR, CASE WHEN tr.transtype IN ('R', 'F') THEN 'P' ELSE 'R' END AS SDK_RN_PL, round(tr.Value,2) * CASE WHEN tr.transtype in ('R', 'F') THEN -1 ELSE 1 END AS PROTIVREDNOST, 1 AS TECAJ_PRERACUN, 'REC' AS LOKAC, CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END AS ID_KUPCA_ORG, 0 AS KOPIJA, '1' AS BLAGAJNA, 0 AS ROUNDING, null AS N_KNJIZBE, null AS VALUTA_PL, null AS TECAJ_PL, 'N' AS DISABLE_REPORT FROM TSLTrans tr LEFT OUTER JOIN Boat b ON tr.BoatNo=b.BoatNo JOIN VW_CRM_Boat v ON b.boatno=v.boatNo LEFT OUTER JOIN Contact c ON b.PrimaryContactId=c.ContactId WHERE CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END IS NOT NULL ; -------------------------------------------------------------------- -- SALDKONT_KUPEC -------------------------------------------------------------------- SELECT CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END AS ID_KUPCA, tr.transacno AS ID_SALDKONT, CASE WHEN charindex('and/or', ti.CustName) > 0 THEN CASE WHEN coalesce(replace(replace(ti.House,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.Addline1,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.Addline1a,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.Addline2,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.Addline3,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.Addline4,'and/or',''),'.',''),' ')=' ' AND coalesce(replace(replace(ti.PostCode,'and/or',''),'.',''),' ')=' ' THEN ti.CustName END ELSE ti.CustName END AS PRIIMEK, CASE WHEN coalesce(replace(replace(ti.House,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.House,'and/or',''),'.','')) + CASE WHEN IsNumeric(substring(ti.House,1,1)) = 1 THEN ' ' ELSE '#' END ELSE '' END + CASE WHEN coalesce(replace(replace(ti.Addline1,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.Addline1,'and/or',''),'.','')) + '#' ELSE '' END + CASE WHEN coalesce(replace(replace(ti.Addline1a,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.Addline1a,'and/or',''),'.','')) + '#' ELSE '' END + CASE WHEN coalesce(replace(replace(ti.Addline2,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.Addline2,'and/or',''),'.','')) + '#' ELSE '' END + CASE WHEN coalesce(replace(replace(ti.PostCode,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.PostCode,'and/or',''),'.','')) + ' ' ELSE '' END + CASE WHEN coalesce(replace(replace(ti.Addline3,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.Addline3,'and/or',''),'.','')) + '#' ELSE '' END + CASE WHEN coalesce(replace(replace(ti.Addline4,'and/or',''),'.',''),' ')<>' ' THEN ltrim(replace(replace(ti.Addline4,'and/or',''),'.','')) ELSE '' END AS NASLOV FROM TSLTrans tr LEFT OUTER JOIN Boat b ON tr.BoatNo=b.BoatNo JOIN VW_CRM_Boat v ON b.boatno=v.boatNo LEFT OUTER JOIN (SELECT transno, max(CustName) AS CustName, max(House) AS House, max(Addline1) AS Addline1, max(Addline1a) As Addline1a, max(Addline2) AS Addline2, max(Addline3) AS Addline3, max(Addline4) AS Addline4, max(PostCode) AS PostCode FROM TInvoice GROUP BY TransNo) ti ON ti.TransNo=tr.transacno WHERE CASE WHEN coalesce(b.BoatNo,' ')=' ' THEN 1 ELSE b.PrimaryContactId END IS NOT NULL ; -------------------------------------------------------------------- -- SALDKONT_ZAP -------------------------------------------------------------------- SELECT a.alloc_id AS ID_SALDKONT_ZAP, a.debit_id AS ID_SALDKONT, a.credit_id AS ID_PL_SALDKONT, CASE WHEN (round(tr.Value,2) * CASE WHEN tr.transtype in ('R', 'F') THEN -1 ELSE 1 END) < 0 THEN abs(a.alloc_amount)*-1 ELSE a.Alloc_amount END AS ZNESEK, convert(varchar,a.alloc_date, 23) AS DATUM_SPREMEMBE, 'MIGRATION' AS USER_SPREMEMBE, 1 AS TECAJ, 0 AS ZNESEK_DAVKA FROM allocation a LEFT OUTER JOIN TSLTrans tr ON a.Debit_id=tr.transacno WHERE a.Cancelled=0;