Skip to content
logo
platformaerp.com

Sprawdzenie zgodności

0. Wstęp

Procedura backup tabeli

  • Procedura kontrolna [document].[test_balanceCheck]
  • Procedura naprawcza exec [wms].[stockBalanceFix] @itemid='70ed6492-1744-e611-80c5-9c8e994dc647'
  • Pomocniczy dddecode [dbo].[fn_decodeGuid]

Ważne pola

  • initialincomesourcelineid - id dostawy , jest na dokumentach wm, mm nawet przy zmianie magazynu

Zgłoszenie

Narzędzia

  • srs/740 Procedura kontrolna oparta o [document].[test_balanceCheck] - zwraca błedy w pozycji 005/007/009
  • srs/741 zestawienie ułatwiające analizę indeksu

Dodatkowe zapytania pomocnicze

1. Sprawdzenie wycen problemow Gdzie stany nie zgadzaja sie z ilościami pozostałymi na dostawach

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
/*Punkt 005. Sprawdzenie zgodności itemQuantityLeft z sumami na stanach magazynowych  [document].[test_balanceCheck]*/

        select ROW_NUMBER () OVER( ORDER BY src1.itemid,src1.itemSerialNumber) row,document_itemQuantityLeft,stock_quantity,
        src1.warehouseID,src1.itemid,src1.itemSerialNumber ,i.itemcode,i.itemname
    from (
        select sum(itemQuantityLeft) document_itemQuantityLeft, warehouseID, itemid,itemSerialNumber from document.documentLine dl
        inner join document.document d on d.documentId = dl.documentID
        where sign = 1 and dl.[status] = 1 and itemQuantity != 0 and itemQuantityLeft >= 0
        group by warehouseID, itemid, itemSerialNumber
    ) src1
    inner join (
        select sum(quantity) stock_quantity, warehouseID, itemid,itemSerialNumber from wms.stockBalance
        group by warehouseID, itemid,itemSerialNumber
    ) src2 on src1.itemid = src2.itemid and src1.warehouseID = src2.warehouseID
    and     isnull(src1.itemSerialNumber,'') = isnull(src2.itemSerialNumber,'')
            left join wms.item i on src2.itemid = i.itemid
    where document_itemQuantityLeft != stock_quantity
    order by itemcode
SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
/*Punkt 007. Rozminiencie sie wartosci pozostalych na dokumentach ze stanami  / procedura testowa  [document].[test_balanceCheck]*/

;with sb as
( select itemID, warehouseID, sum(quantity) stockQ, sum(value) stockV
    from wms.stockBalance group by itemID, warehouseID
), sivl as (select dl.itemID, d.warehouseID, sum(dl.itemQuantityLeft) docQL, sum(dl.sysItemValueLeft) docVL
    from document.documentLine dl inner join document.document d on d.documentID = dl.documentID
    where dl.[status] = 1 and dl.sign = 1 and dl.itemQuantityLeft > 0 group by itemID, warehouseID)
select
    sb.itemid,sb.warehouseid,stockQ,docQL,stockV,docVL,i.itemcode,i.itemname ,maxdates.maxdate
    from sb
    inner join sivl on sb.itemID = sivl.itemID and sb.warehouseID = sivl.warehouseID
    left join wms.item i on sb.itemid = i.itemid
    left join (select max(d.documentDate) maxdate ,dl.itemid from document.document d inner join  document.documentLine dl on d.documentid =dl.documentid
        where d.status=1 group by dl.itemid) maxdates on sb.itemid = maxdates.itemid
where sb.stockQ != sivl.docQL or sb.stockV != sivl.docVL
order by
itemcode

2. Zapytanie diagnozujące zgodność ilości i wartości dostawy

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
declare @itemID uniqueidentifier = '1751d958-9a72-e911-a984-060c7c3ef0b7'
declare @itemserialnumber varchar(50) = '020097918'
declare @initialIncomeSourceLineID uniqueidentifier ='F68D750E-4D90-167E-E252-48DAC880E851' --zrodlo dokumentu

select * from (
select dl.documentlineid,l.locationcode,
    d.documentDate,
    d.documentType,
    dl.sysitemPrice,
    format(dl.signvalue* dl.sysItemValue, 'g', 'pl-pl')  value,
    SUM(dl.signvalue* dl.sysItemValue) OVER(partition by l.locationid,initialIncomeSourceLineID ORDER BY documentDate) RunningTotal,
    SUM( dl.itemQuantity * dl.sign) OVER(partition by l.locationid,initialIncomeSourceLineID ORDER BY documentDate) RunningQuantity,
    dl.itemQuantity,
    case when dl.sign =-1 then 0 else dl.itemquantityleft end itemquantityleft,
    dl.itemValue,
    dl.itemValueleft,
    dl.initialIncomeSourceLineID,
    dl.itemSerialNumber,
    dl.documentfullnr,
    dl.archive,
    dl.sign,
    dl.signvalue
from
    document.documentLine dl
    inner join document.document d on d.documentID = dl.documentid
    left join wms.location l on d.warehouseid = l.locationID
    left join document.orderLine ol on ol.orderLineID =dl.orderlineid
    left join document.[order] o on ol.orderid=o.orderid
where
    ( @itemid is null or dl.itemID =@itemid)
    and dl.status = 1
    and d.status=1
    and (@itemserialnumber is null or dl.itemserialnumber =@itemserialnumber)
    and (@initialIncomeSourceLineID is null or dl.initialIncomeSourceLineID =@initialIncomeSourceLineID)

) x
--where runningquantity <0

order by
    documentDate,
    signvalue*-1  -- zeby ladnie sortowac przychody i rozchody

3. Analiza indeksu (srs/741)

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select
    dl.documentfullnr SYMBOL,
    d.documentDate DATE,
    dbo.fn_decodeguid(d.warehouseID,'warehouse') MAG,
    dbo.fn_decodeguid(initialIncomeSourceLineID,'documentfromdocumentline') SOURCE,
    dbo.fn_decodeguid(documentlineid,'itemfromdocumentline') ITEM,
    format(sysitemprice, 'g', 'pl-pl') SIP ,
    format(itemquantity, 'g', 'de-de') IQ,
    case when dl.sign =-1 then null else format(itemquantityleft, 'g', 'pl-pl')  end  IQL,
    --format(itemprice, 'g', 'pl-pl')  itemprice,
    --format(ItemValue, 'g', 'pl-pl') ItemValue,
    --format(ItemValueleft, 'g', 'pl-pl') IVL,
    format(dl.signvalue* dl.sysItemValue, 'g', 'pl-pl') SIV,
    case when dl.sign =-1 then null else format(dl.signvalue* dl.sysItemValue, 'g', 'pl-pl') end  SIVL,
    dl.itemSerialNumber SERIAL
from document.documentline dl
inner join document.document d on d.documentid =dl.documentid
where --dl.sourcedocumentlineid = @documentlineid or dl.documentlineid=@documentlineid
    dl.itemid= 'e3d47140-7246-ea11-a98a-060c7c3ef0b7'
    and dl.status = 1
    and d.status=1
    --and dbo.fn_decodeguid(d.warehouseID,'warehouse') = '006'
order by d.documentdate, dl.sign *-1

3.2 Pomoc do analizy

Procedura która liczy na piechotę wszystkie przychody i rozchody na danym indeksie i magazynie. Nie wuzględnia source docuemnt, ani partii (założenie, że naliczanie nie zadziałało prawidłowo w systemie)

SQL
1
exec tools.sprawdz_pozostala_ilosc_wartosc @itemCode = 'LAB000002', @warehouseCode = '300'

4.Porównanie stanów dziennych z dokumentami

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/*
Porownanie stanów dziennych z dokumentami,
raport pomocny w przyapdku wyjasniania wszelkich roznic

*/
declare @guidEmpty uniqueidentifier = newId()
declare @ges char(36) = cast(@guidEmpty as char(36))

select sb.stockBalanceDate ,stockDate,sb.valueIn,calcStB.itemvaluein,sb.valueOut, calcStB.itemvalueOut
from wms.stockBalanceDate sb
right join
(
    select
    sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity) itemQuantityIn
    , sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueIn
    , sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
    , sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueOut
    , dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID
    , cast(d.documentDate as date) stockDate -- uwaga w bardzo  starych rozliczeniach stan jest rozliczany po accdate
    from document.documentLine dl
    --inner join @items i on i.itemID = dl.itemID
    inner join document.document d on d.documentID = dl.documentID
    LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
    where dl.status = 1
    group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
    ,  cast(d.documentDate as date)
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
and sb.stockBalanceDate = calcStb.stockDate
where
    (isnull(sb.quantityIn, -1) != isnull(calcStB.itemQuantityIn, -1)
    or isnull(sb.quantityOut, -1) != isnull(calcStB.itemQuantityOut, -1)
    or isnull(sb.valueIn, -1) != isnull(calcStB.itemValueIn, -1)
    or isnull(sb.valueOut, -1) != isnull(calcStB.itemvalueOut, -1))

5.Porównanie stanów dziennych z dokumentami i nagłówkami bardzo przydatne

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
/*1. Declare temp table*/
DECLARE @ListOfDates TABLE(d date)

DECLARE @StartDate DATE, @EndDate DATE , @warehouseid uniqueidentifier
SELECT @StartDate = '2018-01-01', @EndDate = '2023-07-31' ,@warehouseid='0198FCF9-209B-4A67-A761-8059A9FA460F' ;
WITH ListDates(AllDates) AS
(    SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates
    WHERE AllDates < @EndDate)
insert into @ListOfDates (d) SELECT AllDates  FROM ListDates
OPTION (MAXRECURSION 9999)

/*2. Declare temp table*/
select * from (
select
    dates.d,stockvalue,documentsvalue,headersvalue,stockvalue*2-documentsvalue-headersvalue testvalue,stockquantity,quantity, stockquantity-quantity teststock
    from @ListOfDates dates left join
    (
    select stockBalanceDate,sum(quantityIn) - sum(quantityout) stockquantity,sum(valueIn) - sum(valueout) stockValue from wms.stockBalanceDate
    where (@warehouseid is null or warehouseID=@warehouseid)
    group by stockBalanceDate ) stock on dates.d = stock.stockBalanceDate
    left join
    (select
        sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity)
        -sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) quantity,
         sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue)
        --, sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
        - sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) documentsvalue
        --, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID
        , cast(d.documentDate as date) stockDate -- uwaga w bardzo  starych rozliczeniach stan jest rozliczany po accdate
        from document.documentLine dl
        --inner join @items i on i.itemID = dl.itemID
        inner join document.document d on d.documentID = dl.documentID
        LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
        where dl.status = 1 and d.status=1 and (@warehouseid is null or d.warehouseID=@warehouseid)
    --  and dl.itemid =@itemid
        group by --dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
          cast(d.documentDate as date)
    ) documentlines
         on dates.d  =documentlines.stockDate
    left join
    (select
        sum(d.sumsysitemvalue) headersvalue
        , cast(d.documentDate as date) stockDate
        from  document.document d
        where d.status = 1  and  (@warehouseid is null or d.warehouseID=@warehouseid)
    --  and dl.itemid =@itemid
        group by --dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
          cast(d.documentDate as date)
    ) headers
         on  dates.d  =headers.stockDate
) totals
    --where testvalue !=0 or teststock!=0
order by 1

6.Porównanie stanów magazynowych na koniec miesięcy z obrotami w tym czasie

Zapytanie porównuje stan na koniec 2 różnych miesięcy i pokazuje ewentualną róznice między stanami, a dokumentami magazynowymi wystawionymi w tym okresie Przykładowe zgłoszenie: https://github.com/paanda-io/paanda.app.erp/issues/812

Zapytanie na podstawie raportów 403 i 351 Tabela t1 - stan magazynowy na dzień (data wczesniejsza do wpisania) Tabela t2 - stan magazynowy na dzień (data późniejsza) Tabela t3 - dokumenty za dany okres, pogrupowane według indeksu i numeru seryjnego W przykładzie: T1 - stan na koniec marca 2025 T2 - stan na koniec kwietnia 2025 T3 - podsumowanie dokumentów magazynowych wystawionych między 1 a 30 kwietnia 2025

SQL
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
DECLARE @t1 TABLE (row int, warehouseCode varchar(10), itemId uniqueidentifier, itemCode varchar(max),
itemName varchar(max), JM varchar(10), serialNumber varchar(100), quantity decimal (18,3), value decimal(18,2))

DECLARE @t2 TABLE (row int, warehouseCode varchar(10), itemId uniqueidentifier, itemCode varchar(max),
itemName varchar(max), JM varchar(10), serialNumber varchar(100), quantity decimal (18,3), value decimal(18,2))

DECLARE @t3 TABLE (itemId uniqueidentifier, itemCode varchar(max), serialNumber varchar(100), 
quantity decimal (18,3), value decimal(18,2))

INSERT INTO @t1
select
    ROW_NUMBER () OVER(
                ORDER BY
                    i.itemName ASC
            ) row,
            warehouseCode,
            x.itemID,
            i.itemCode,
            i.itemName,
            cd.dictionaryvalue JM,
      x.serialNumber,
            x.quantity,
            x.value
            from (
        select
            l.locationCode warehouseCode,
            x.itemID,
      x.itemSerialNumber  serialNumber,
            sum(x.quantityIN - x.quantityOUT) quantity,
            sum(x.valueIN - x.valueOUT) value
        from
            wms.stockBalanceDate x
            inner join wms.location l on l.locationID = x.warehouseId
        where
            x.stockBalanceDate <= '20250331' AND x.warehouseID = '12143BB1-C41A-EC11-82E2-0690A1809DE2'
        group by
            x.warehouseID,
            l.locationCode,
            l.locationName,
            x.itemid,
      x.itemSerialNumber
        having
            (
                sum(x.quantityIN - x.quantityOUT) <> 0
                or sum(x.valueIN - x.valueOUT) <> 0
            )
) x 
    inner join wms.item i on x.itemID = i.itemid
    left join (select dictionaryvalue ,dictionarykeyguid from common.dictionary cd where  cd.dictionaryName='itemunit'  ) cd 
    on  cd.dictionarykeyguid = i.itemunitid 
order by itemname

INSERT INTO @t2
select 

    ROW_NUMBER () OVER(
                ORDER BY
                    i.itemName ASC
            ) row,
            warehouseCode,
            x.itemID,
            i.itemCode,
            i.itemName,
            cd.dictionaryvalue JM,
      x.serialNumber,
            x.quantity,
            x.value
            from (
        select
            l.locationCode warehouseCode,
            x.itemID,
      x.itemSerialNumber  serialNumber,
            sum(x.quantityIN - x.quantityOUT) quantity,
            sum(x.valueIN - x.valueOUT) value
        from
            wms.stockBalanceDate x
            inner join wms.location l on l.locationID = x.warehouseId
        where
            x.stockBalanceDate <= '20250430' AND x.warehouseID = '12143BB1-C41A-EC11-82E2-0690A1809DE2'
        group by
            x.warehouseID,
            l.locationCode,
            l.locationName,
            x.itemid,
      x.itemSerialNumber
        having
            (
                sum(x.quantityIN - x.quantityOUT) <> 0
                or sum(x.valueIN - x.valueOUT) <> 0
            )
) x 
    inner join wms.item i on x.itemID = i.itemid
    left join (select dictionaryvalue ,dictionarykeyguid from common.dictionary cd where  cd.dictionaryName='itemunit'  ) cd 
    on  cd.dictionarykeyguid = i.itemunitid 
order by itemname

INSERT INTO @t3
SELECT
    wi.itemID,
        wi.itemCode,
        dl.itemSerialNumber,
        SUM(dl.itemQuantity),
        SUM(isnull(dl.sign *dl.sysitemValue,0)) sysitemValue
    FROM    /*Dane dokumentu*/
        document.document dd
        INNER JOIN document.documentType dt ON dt.documentType = dd.documentType AND dt.masterDocumentCategory = 'warehouse'
        inner JOIN [document].[documentLineList]() dl ON dl.documentID = dd.documentID
        LEFT JOIN wms.location wl ON wl.locationID = dd.warehouseID
        LEFT JOIN wms.location wl2 ON wl2.locationID = dd.warehouseIDB
        LEFT JOIN document.orderLine ol ON ol.orderLineID = dl.orderLineID
        LEFT JOIN document.[order] o ON o.orderID = ol.orderID
        LEFT JOIN wms.item wi ON wi.itemID = dl.itemID
        LEFT JOIN common.dictionary cd_itemunit ON cd_itemunit.dictionaryKeyGuid = wi.itemUnitID  AND cd_itemunit.dictionaryName='itemUnit'
        LEFT JOIN common.dictionary cd_itemgroup ON wi.itemGroup = cd_itemgroup.dictionaryKeyGuid AND cd_itemgroup.dictionaryName='itemgroup'
        LEFT JOIN common.dictionary cd_organisationUnit ON cd_organisationUnit.dictionaryKeyGuid=dd.organisationUnitID AND cd_organisationUnit.dictionaryName='organisationUnit'
        LEFT JOIN wms.location wll ON wll.locationID=dl.locationID
        LEFT JOIN firm.firm ff ON ff.firmID = dd.firmID
        LEFT JOIN wms.commisionLine cl ON cl.commisionLineID = dl.commisionLineID
        LEFT JOIN wms.commision clc ON clc.commisionID = cl.commisionID
        LEFT JOIN wms.item cli ON cl.itemID = cli.itemid  --nazwa na pozycji zlecenia 
        /*Filtr Typow dokumentow np RW-P,ZW */
        /*Dane korekty*/
        LEFT JOIN document.document dd_correction ON dd_correction.documentID=dd.initialCorrectedDocumentId
  WHERE 
        cast(dd.documentDate as date) >= '20250401'
        AND cast(dd.documentDate as date)  <= '20250430'
        AND dd.warehouseID = '12143BB1-C41A-EC11-82E2-0690A1809DE2'
  GROUP BY wi.itemID, wi.itemCode, dl.itemSerialNumber

SELECT t1.itemCode, t1.serialNumber, t2.itemCode, t2.serialNumber, t3.itemCode, t3.serialNumber, ISNULL(t1.quantity,0) t1q,
ISNULL(t1.value,0) t1v, ISNULL(t2.quantity,0) t2q, ISNULL(t2.value,0) t2v, ISNULL(t3.quantity,0) t3q, ISNULL(t3.value,0) t3v,
CASE 
WHEN (ISNULL(t2.value,0) - ISNULL(t1.value,0)) != ISNULL(t3.value,0) THEN 'ERROR' END [check]
FROM @t1 t1
FULL OUTER JOIN @t2 t2 ON t1.itemId = t2.itemId AND t1.serialNumber = t2.serialNumber
LEFT JOIN @t3 t3 ON (t1.itemId = t3.itemId OR t2.itemId = t3.itemId) AND (t1.serialNumber = t3.serialNumber OR t2.serialNumber = t3.serialNumber) 
--WHERE ISNULL(t1.value,0) != ISNULL(t2.value,0)
order by [check] desc, t1.itemCode, t2.itemCode, t3.itemCode

5.Naprawianie

5.1 Niezgodne stany gorace

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/*
exec wms.stockbalancefix @username='sys'
Wygenerowanie fixow dla calego miesiaca
*/
select
'exec   [wms].[stockBalanceFix] @itemid =''' + cast(dl.itemID as varchar(36)) + ''', @documentid =''' + cast(d.documentid as varchar(36)) + ''',@username=''m.kotynia'' '
from document.documentLine dl
inner join document.document d on dl.documentID = d.documentID
where   d.documentdate >= '2018-05-01' and d.documentdate <= '2018-05-31'
and d.status =1

5.2 FIX roznicy miedzy nagłówkiem pozycji a pozycjami

Uwaga może być niebezpieczne

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Begin transaction

    update document.document
    set
    sumitemvalue = total.iv,
    sumsysitemvalue= total.siv
    from  document.document dd inner join (
        select sum(itemQuantity * sign) IQ, sum(itemValue * signValue) IV, sum(sysItemValue * signValue) SIV
        , d.documentID
        from document.document d
        inner join document.documentLine dl on dl.documentID = d.documentID and dl.[status] = 1
        --where d.documentMonth >= 201512-- @currentDocumentMonth - 2
        group by d.documentID) total on dd.documentid = total.documentid
    where dd.documentid in

    (
        -- punkt 11 z punktem kontrolnym
        select d.documentID --, d.documentdate
        from document.document d
        inner join
        (   select sum(itemQuantity * sign) IQ, sum(itemValue * signValue) IV, sum(sysItemValue * signValue) SIV
            , d.documentID
            from document.document d
            inner join document.documentLine dl on dl.documentID = d.documentID and dl.[status] = 1
            --where d.documentMonth >= 2020-- @currentDocumentMonth - 2
            group by d.documentID
            ) dl on dl.documentID = d.documentID
        --where d.documentMonth >= 2020-- >= @currentDocumentMonth - 2
        and (d.sumItemQuantity != dl.IQ or d.sumItemValue != dl.IV or d.sumSysItemValue != dl.SIV)
    )

commit transaction

5.2 (inprogress) Próba ustawienia ilości pozostałej na prawidłową

  • nie dizała jeśli są korekty to zapytanie dziala dobrze tylko tam gzie nie ma korekt bo korekta ma initialincomesourcelineid na pz

Uwaga może być niebezpieczne

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
    --026448E4-05D6-E740-124D-48DB794F9E94 -nie zgadza sie
/*
1. to zapytanie dziala dobrze tylko tam gzie nie ma korekt bo korekta ma initialincomesourcelineid na pz
*/
select
        income.documentdate,income.documentfullnr,income.documentLineID,income.itemid,i.itemcode, income.itemQuantityLeft ,outcome.docQ,
        'update document.documentline set itemquantityleft =' + cast(docq as varchar(10))+ ' where documentlineid ='''+ cast(income.documentLineID as varchar(36))+''''
    from
    --przychody
    (select d.documentdate, d.documentfullnr, dl.documentLineID,dl.itemid,dl.itemQuantityLeft  from document.documentLine dl inner join document.document d on d.documentID =dl.documentID
    where dl.documentLineID = dl.initialIncomeSourceLineID  and dl.status = 1 and d.status=1
    ) income left join
    --wydania
    (
    select
    dl.initialIncomeSourceLineID,
    sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity)-sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) docq
    ---     sum(case when cordl.sign = 1 then 1 else 0 end * cordl.itemQuantity)-sum(case when cordl.sign = -1 then 1 else 0 end * cordl.itemQuantity) docq
    from document.documentLine dl
    inner join document.document d on d.documentID = dl.documentID
    LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
    where dl.status = 1 and d.status=1 --and (@warehouseid is null or d.warehouseID=@warehouseid)
--  and dl.initialSourceDocumentLineId  is null --korekty maja tu wypelnione
    group by --dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
    dl.initialIncomeSourceLineID
    )outcome
    on income.documentLineID = outcome.initialIncomeSourceLineID
    --itemy
    left join wms.item i on i.itemid = income.itemid
    where
        income.itemQuantityLeft !=outcome.docQ
        -- income.documentlineid='93831B89-62DE-742C-1F04-48DA33F34846'
        --and outcome.docQ >=0 --uwaga na minus tez sa trzeba juz recznei to zalatwic
            --and income.documentlineid not in ( select initialIncomeSourceLineID from document.documentline where iscorrectiveline=1)
    order by 1 desc

        -- select * from document.documentline where initialIncomeSourceLineID ='5FEBC90F-4A65-7403-39BC-48DA8F2C2CD0'

5.3 ustaw archive dla oczywistych srs/741

Uwaga może być niebezpieczne

SQL
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
-- jesli  pz=rw

update document.documentline  set archive =1
where  initialIncomeSourceLineID in
(
select  rozchody.initialIncomeSourceLineID

from (
select
    dl.locationID,
    dl.documentlineid,
    dl.itemprice,
    dl.sysitemPrice,
    dl.itemQuantity,
    dl.itemquantityleft,
    dl.itemValue,
    dl.itemValueleft,
    dl.sysitemValue,
    dl.sysitemValueleft,
    dl.itemSerialNumber,

    dl.initialIncomeSourceLineID
from
    document.documentLine dl
    inner join document.document d on d.documentID = dl.documentid
    inner join  document.documenttype dt on dt.documentType = d.documentType
    left join wms.location l on d.warehouseid = l.locationID
where
    dl.status = 1
    and d.status=1
    and dt.baseDocumentType = 'PZ'
) przychody
inner join
(
select
    --dl.documentlineid,
    --dl.itemprice,
    --dl.sysitemPrice,
    sum(dl.itemQuantity) itemQuantity,
    --dl.itemquantityleft,
    sum(dl.itemValue) itemValue,
    --dl.itemValueleft,
    sum(dl.sysitemValue) sysitemValue,
    --dl.sysitemValueleft,
    --dl.itemSerialNumber,
    dl.initialIncomeSourceLineID
from
    document.documentLine dl
    inner join document.document d on d.documentID = dl.documentid
    inner join  document.documenttype dt on dt.documentType = d.documentType
    left join wms.location l on d.warehouseid = l.locationID
where
    dl.status = 1
    and d.status=1
    and dt.baseDocumentType in ('RW','WZ')
group by  dl.initialIncomeSourceLineID

) rozchody on
    przychody.itemQuantity =rozchody.itemQuantity
    and przychody.documentlineid =rozchody.initialIncomeSourceLineID
    and przychody.sysitemValue =rozchody.sysitemValue
    and przychody.itemValue =rozchody.itemValue
    and przychody.itemValueLeft =0
    and przychody.sysitemValueLeft=0
    and przychody.itemQuantityLeft=0
)

update document.documentLine
set archive=1 where documentlineid in (
select
rozchody.documentlineid
--rozchody.initialIncomeSourceLineID  -- potem tutaj

from (
select
    dl.locationID,
    dl.documentlineid,
    dl.itemprice,
    dl.sysitemPrice,
    dl.itemQuantity,
    dl.itemquantityleft,
    dl.itemValue,
    dl.itemValueleft,
    dl.sysitemValue,
    dl.sysitemValueleft,
    dl.itemSerialNumber,

    dl.initialIncomeSourceLineID
from
    document.documentLine dl
    inner join document.document d on d.documentID = dl.documentid
    inner join  document.documenttype dt on dt.documentType = d.documentType
    left join wms.location l on d.warehouseid = l.locationID
where
    dl.status = 1
    and d.status=1
    and dt.baseDocumentType = 'PZ'
) przychody
inner join
(
select
    dl.locationID,
    dl.documentlineid,
    dl.itemprice,
    dl.sysitemPrice,
    dl.itemQuantity,
    dl.itemquantityleft,
    dl.itemValue,
    dl.itemValueleft,
    dl.sysitemValue,
    dl.sysitemValueleft,
    dl.itemSerialNumber,

    dl.initialIncomeSourceLineID
from
    document.documentLine dl
    inner join document.document d on d.documentID = dl.documentid
    inner join  document.documenttype dt on dt.documentType = d.documentType
    left join wms.location l on d.warehouseid = l.locationID
where
    dl.status = 1
    and d.status=1
    and dt.baseDocumentType in ('RW','WZ')
) rozchody on
    przychody.itemQuantity =rozchody.itemQuantity
    and przychody.documentlineid =rozchody.initialIncomeSourceLineID
    and przychody.sysitemValue =rozchody.sysitemValue
    and przychody.itemValue =rozchody.itemValue
    and przychody.sysItemPrice = rozchody.sysItemPrice
    and przychody.itemValueLeft =0
    and przychody.sysitemValueLeft=0
    and przychody.itemQuantityLeft=0
    and przychody.locationid = rozchody.locationid
)

5.4 Korekta wartości na linijkach dokumentu

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
select * from document.document where documentFullNr = 'WZ/182/006/0225'

select * from document.documentLine where documentID = 'EBD45FD0-BCA4-8B9D-86C0-01951E3BD141'
and status > -1

begin tran
update document.documentLine set itemValue = '2847.37', itemValueLeft = '2847.37',
sysItemValue = '2847.37', sysItemValueLeft = '2847.37', correctedItemValue = '2847.37',
sysCorrectedItemValueQcorrExcluded = '2847.37'
WHERE documentLineID = 'CE4D3092-C5EE-EF11-9065-9C6B00264B23'
commit

exec wms.stockBalanceFix @itemID = '7C382071-6272-E911-A984-060C7C3EF0B7'

Przykładowe zgłoszenie

Opis: Rozbieżności między wartosciami wynikajcymi z sumy dokumentów a raportami systemu na danym magazynie

  • 11.01 Stany magazynowe - podsumowanie - wartość dla magazynu 604 na 30.04 wynosi 478 546,24
  • 10.01 Rejestr obrotów - podsumowanie - suma dla 01.05-31.05.2020 wynosi -111 877,26
SQL
1
exec[report].[warehouseDocumentTOTAL] @datestart='2020-05-01', @DateEnd='2020-05-31', @warehouse='604', @username='sys'
  • Suma powyższych liczb to 366 668,98 i powinna być równa stanowi magazynu na 31.05 (raport 11.01).