alter table Invoices
MODIFY Bulstat varchar(15) NOT NULL

GO

alter table Proform
MODIFY Bulstat varchar(15) NOT NULL

GO

alter table VaucherInvoices
MODIFY Bulstat varchar(15) NOT NULL

GO

alter table SpaInvoices
MODIFY Bulstat varchar(15) NOT NULL

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_ExportKonto'))) THEN
Drop View v_ExportKonto
END IF


GO

create view 
  DBA.v_ExportKonto as
  select NN=I.InvoiceNo,
    VidClient=I.VidSmetka,
    PL_KOD=I.PayHow,
    NOMER_DO=1,
    VID_OSN=if I.VidSmetka = 2 then 901 else if I.VidSmetka = 7 then 926 else 0 endif endif,
    NOM_OSN=DBA.AlignRightStr(convert(integer,I.TrueInvoiceNo),10,'0'),
    KOD_OP=3,
    DATA_OSN=DATEFORMAT(I.TrueInvoiceDate,'DD.MM.YYYY'),
    SumaDDS=Round(I.VAT,2),
    SumaAll=Round(I.GrandTotal,2),
    SumaBezDDS=Round(I.TotalNoVAT,2),
    PARTIDA=if LENGTH(TRIM(I.Bulstat)) < 15 then DBA.AlignLeftStr(TRIM(I.Bulstat),15,'0') else TRIM(I.Bulstat) endif || NOM_OSN,
    KEY_L=LENGTH(PARTIDA),
    SKLAD=0 from
    DBA.Invoices as I where
    I.InvoiceType = 2 and
    (I.VidSmetka in( 2,7) ) and
    I.TrueInvoiceDate >= bvrData1 and I.TrueInvoiceDate <= bvrData2 union all
  select NN=I.InvoiceNo,
    VidClient=I.VidSmetka,
    PL_KOD=I.PayHow,
    NOMER_DO=1,
    VID_OSN=if I.VidSmetka = 2 then 901 else if I.VidSmetka = 7 then 926 else 0 endif endif,
    NOM_OSN=DBA.AlignRightStr(convert(integer,I.TrueInvoiceNo),10,'0'),
    KOD_OP=3,
    DATA_OSN=DATEFORMAT(I.TrueInvoiceDate,'DD.MM.YYYY'),
    SumaDDS=Round(I.VAT,2),
    SumaAll=Round(I.GrandTotal,2),
    SumaBezDDS=Round(I.TotalNoVAT,2),
    PARTIDA=if LENGTH(TRIM(I.Bulstat)) < 15 then DBA.AlignLeftStr(TRIM(I.Bulstat),15,'0') else TRIM(I.Bulstat) endif || NOM_OSN,
    KEY_L=LENGTH(PARTIDA),
    SKLAD=0 from
    DBA.SpaInvoices as I where
    I.InvoiceType = 2 and
    (I.VidSmetka in( 2,7) ) and
    I.TrueInvoiceDate >= bvrData1 and I.TrueInvoiceDate <= bvrData2 union all
  select NN=0,
    VidClient=0,
    PL_KOD=0,
    NOMER_DO=1,
    VID_OSN=928,NOM_OSN=
    DBA.AlignRightStr('0',10,'0'),
    KOD_OP=3,
    DATA_OSN=DATEFORMAT(bvrData2,'DD.MM.YYYY'),
    SumaDDS=(select Round(Sum(VAT),2) from DBA.Invoices where VidSmetka = 1 and InvoiceDateDt >= bvrData1 and InvoiceDateDt <= bvrData2)+
    (select Round(Sum(VAT),2) from DBA.SpaInvoices where VidSmetka = 1 and InvoiceDateDt >= bvrData1 and InvoiceDateDt <= bvrData2),
    SumaAll=(select Round(Sum(GrandTotal),2) from DBA.Invoices where VidSmetka = 1 and InvoiceDateDt >= bvrData1 and InvoiceDateDt <= bvrData2)+
    (select Round(Sum(GrandTotal),2) from DBA.SpaInvoices where VidSmetka = 1 and InvoiceDateDt >= bvrData1 and InvoiceDateDt <= bvrData2),
    SumaBezDDS=SumaAll-SumaDDS,PARTIDA='',
    KEY_L=0,
    SKLAD=0
// [v 04.Sep.2006] Yanko, ostavil sam niakoi izlishni poleta, samo za da sa ednakvi imenata na poletata s
//                        analogichnoto View v Riscont, i da ne se promeniat Fields na tablicite vav formite