platformaerp.com

Weryfikacja interfejsu księgowego JBB

Interfejs składa się z

• tabeli custom.magazyn
• procedur
• systemu FK ENOVA

Procedura

Krok 1

Sprawdzić czy tabela custom.zakup / custom.magazyn pokrywa się z danymi w systemie. Może być np zmiana na dokumencie już wyeksportowanym.

declare @date date ='2024-01-01'

--porownanie z enova
select
    d.sumSysItemValue,x.value ,isnull(x.title,d.documentFullNR) document
from
    (select  sum(value) value,title from custom.magazyn   where  accountside=2
    and deleted=0 and documentDate>= @date  group by title) x
    left join
    (select documentFullNR,sumSysItemValue from document.document where  documentDate >=@date and status=1 ) d
    on x.title = d.documentFullNR
where
    abs(x.value) != abs(isnull(d.sumSysItemValue,0))

--porownanie z platforma
select
    d.sumSysItemValue,x.value ,isnull(x.title,d.documentFullNR) document
from
    (select documentFullNR,sumSysItemValue from document.document where  documentDate >=@date and status=1 ) d
    left join
    (select  sum(value) value,title from custom.magazyn   where  accountside=2
    and deleted=0 and documentDate>= @date  group by title) x
    on x.title = d.documentFullNR
where
    abs(x.value) != abs(isnull(d.sumSysItemValue,0))

Krok 2

a) Sprawdzic WN /MA custom zakup ponizej z wydrukiem ksiegowej b) Poprosic ksiegową o wydruk w formie excel

select
    sum(case when accountside =2 then currencyValue else 0 end) 'WN' ,
    sum(case when accountside =1 then currencyValue else 0 end) 'MA'
from
    [custom].[magazyn]  m
where
    m.documentdate >= '2018-05-01' and m.documentdate <= '2018-05-31'
    and account like '3114%'

Krok 3 sprawdzic dokumenty MM- MM+ powinny byc zgodne

Generalnie oni przesuwaja mm i zwracaja w tych samych cenach indeksy z regeneracja np literka r wiec mm- musza byc rowne mm+

    select d.documentType,sum(sysItemValue) from document.document d inner join document.documentline dl
    on d.documentid =dl.documentid  where   documentDate >= '2018-05-01'  and documentDate <= '2018-05-31'
    and warehouseID in ('0198FCF9-209B-4A67-A761-8059A9FA460F','935A1F64-28DF-40C2-9044-FBB43D456310' )
    group by d.documentType

Krok 4 Sprawdzic procedure testowa

 [document].[test_balanceCheck]

Krok 5 Przeliczyc stan jesli cos jest nei tak lub po zmianach na dokumentach

select distinct
    'exec [wms].[stockBalanceFix] @itemID = '''+ cast(dl.itemid  as varchar(36)) + ''' , @documentID= '''+  cast(d.documentid as varchar(36)) + ''', @username=''test'''
from
    document.document d inner join
    document.documentline dl on d.documentid = dl.documentid
where
    dl.status =1
    and  d.documentDate >= '2024-01-01'
    and d.documentDate <= '2024-03-31'