Skip to content
logo
platformaerp.com

Numeracja

Sprawdzenie zgoności pozycji numerów

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
select  distinct
    d.documentFullNR,
    dl.documentFullNR,
    d.documentNR,
    w.warehouseID
from document.document d
    inner join document.documentline dl on d.documentid=dl.documentid
    inner join document.documenttype dt on dt.documentType = d.documentType
    inner join wms.location w on w.locationID  = d.warehouseID
where
    dl.status =1 and d.status=1
    and w.locationCode  ='1300'
    and dt.baseDocumentType = 'PZ'--  pz, pzi
    and cast(d.documentDate as date) >= '2023-11-01'
    and cast(d.documentDate as date) <= '2023-11-30'
    and d.documentFullNR  != dl.documentFullNR
order by 1,2

Procedura

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
/*
PRocedura zmiany numeru dokuemnty

*/

begin tran

    /*KROK 1 Sprawdzenie
    Skrypt umożliwiający renumeracje dokumentow
    oraz sprawdzenie ciaglosci
    checkOnly = 0 dokona zmian
    checkOnly = 1 tylko wyświetli co się pozmienia
    */
    begin tran
    exec [document].[document_manualyRenumber] @checkOnly = 1, @documentType = 'RW-P', @warehouseCode = '300', @documentMonth = 202202
    rollback tran

    /*Krok 2 Update numeru*/
    declare @documentFullNR varchar(20) =  'RW-P/647/300/0222'
    declare @newdocumentFullNR varchar(20) = 'RW-P/44/300/0222'
    declare @newdocumentnr int = 44
    declare @documentid uniqueidentifier

    select @documentid = documentid from document.document where documentFullNR =  @documentFullNR
    update document.document set  documentFullNR =   @newdocumentFullNR , documentnr  = @newdocumentnr
    where documentid =  @documentid

    update document.documentline set   documentFullNR =   @newdocumentFullNR
    where documentid =  @documentid

    /*KROK 3 aktualizacja serii numeratora  tabela [common].[Numerator]  */
    -- cofniecie numeracji
    --update [common].[Numerator] set numerator = @newdocumentnr
    --where remotesource='document' and series like 'RW-P3000222%'
    -- Typ dokumentu/magazyn/miesiac/rok w zaleznosci od instancji jest roznic

--commit tran
rollback tran

/* Sprawdzenie ciaglosci numeracji recznie*/
select
    documentid,documentmonth,documentdate,documentnr , documentfullnr
from
    document.document
where
    documenttype='RW-P'
    and warehouseid = (select warehouseid from wms.location where locationcode= '800')
    and  cast(year( documentdate) as varchar(4))  + right('0'+cast(month (documentdate) as varchar(2)),2)  = '201908'
order by 3

/*
Wyrownanie numeracji
na pozycji document.documentline tez jest documentfullnr
*/
update document.documentline set documentfullnr = d.documentfullnr
from document.documentline dl  inner join document.document d on d.documentid =dl.documentid
where d.documentfullnr != dl.documentfullnr

/*
Trzeba zmienic tabelke numerator
*/

select *  from common.numerator where remotesource='document' and series like '%0919'