platformaerp.com

Przykład wystawienia korekty wartosci rozchodu recznie

20230914 KOREKTA WARTOSCIOWA DZIALAJACA wartosci pozostałych powyżej 0 i ilosci 0

Scenariusz na przychodzie pozostala wartosc dodatnia 0 ilosc

Warunek:

• documenttype=income
• sysitemvalueleft >0
• itemquantityleft = 0

Kroki:

• Dodajemy ps-
• Szukamy i pozycji dostawy na ktorej zostala wartosc , uruchamiamy procedure

Uwagi:

• Technicznie korekta powinna być powiazana z własciwym dokumentem wydania który zdjał nie ta ilosc ale nie ma to znaczenia, mozna sie zastanowić nad powiazaniem potem z wlasciwym pozycja wydania SOURCEDOCUMENTLINEID

BEGIN TRY
    BEGIN TRANSACTION

    declare @documentid  uniqueidentifier =  'b523a7cf-bea6-ee11-80f4-9c8e994dc646' --naglowek odkumentu ps- /ps+
    declare @incomedocumentlineid uniqueidentifier = '234c8f96-39c2-ec11-80ed-9c8e994dc647' --przychod pozycja
    declare @optionalSOURCEDOCUMENTLINEID uniqueidentifier = null -- '553df59a-b4ab-ec11-930b-002590d9752e' --null
    declare @itemid uniqueidentifier , @itemserialnumber varchar(50) , @value decimal (18,2)=0,
    @documentnr int, @documentfullnr varchar(50),@documentordinalnumber int,@sign int,@newsign int,@itemquantityleft decimal (18,2)=0

    update document.document set status=1 where documentid =@documentid
    --1.pobrnaie z income
    select @itemid = itemid,@value =sysItemValueLeft,@itemserialnumber =itemserialnumber ,@sign =sign ,@itemquantityleft =itemquantityleft
        from document.documentLine   where documentlineid =@incomedocumentlineid

    if ( @value = 0 or isnull(@itemquantityleft,0) <> 0 )
        raiserror('wartosc jest zerowa lub ilosc pozostala rozna od 0',16,1)
    if ( @value > 0  ) --trzeba zdjac pozostala ilosc
        set @newsign = -1
    if ( @value < 0  )
        set @newsign = 1

    --2.pobranie z naglowka dokumentu korekty
    select @documentnr = documentnr,@documentfullnr =documentfullnr
        from document.document   where documentid =@documentid
    set @documentordinalnumber = isnull((select count(*) from  document.documentline where documentid=@documentid)+1,1)

    if (@documentnr is null)
        raiserror('nie znaleziono dokumentu korekty',16,1)

    --3.Scenariusz na PRzychodzie pozostala wartosc dodatnia 0 ilosc
    insert into document.documentline
    (
        documentid,itemid ,itemserialnumber,iNITIALINCOMESOURCELINEID ,SOURCEDOCUMENTLINEID,signvalue,sign,status,
        sysitemvalue,sysItemValueLeft ,itemvalue,ItemValueLeft,itemQuantity ,itemQuantityLeft,
        itemprice,sysItemPrice,
        documentnr,documentfullnr
    )
    select @documentid,@itemid,@itemserialnumber,@incomedocumentlineid,isnull(@optionalSOURCEDOCUMENTLINEID,@incomedocumentlineid),@newsign,@newsign,1,
    abs(@value),abs(@value),abs(@value),abs(@value),0,0,
    abs(@value),abs(@value),
    @documentnr,@documentfullnr

    --4.oczyszczenie income
    update document.documentLine set sysItemValueLeft =0  where documentlineid=@incomedocumentlineid

    --5. Fix stanów
    exec wms.stockbalancefix @itemid =@itemid

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    --SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
    DECLARE @Message varchar(MAX) = ERROR_MESSAGE(), @Severity int = ERROR_SEVERITY(), @State smallint = ERROR_STATE();

    RAISERROR (@Message, @Severity, @State);
END CATCH

20230914 to nie działa poniżej

begin tran

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

select count(*) from document.document where status >= 1

exec document.documentline_outcomes_createCorrection @documentLineID = @guidEmpty, @username = 'm.kotynia', @newItemPrice = 62.5
, @manualOutcomeLineID = '974F52AA-1D88-E311-93F5-D89D6719E0CB', @manualValueChange = 0.37

exec document.documentline_outcomes_createCorrection @documentLineID = @guidEmpty, @username = 'm.kotynia', @newItemPrice = 34.49
, @manualOutcomeLineID = 'B1F1293C-E188-E311-93F5-D89D6719E0CB', @manualValueChange = 1.75

exec document.documentline_outcomes_createCorrection @documentLineID = @guidEmpty, @username = 'm.kotynia', @newItemPrice = 720.01
, @manualOutcomeLineID = 'FF714D14-777F-E311-93F5-D89D6719E0CB', @manualValueChange = -0.03

select count(*) from document.document where status >= 1

rollback tran

zbijanie korekt wz w jedna

begin tran

declare @initialCorrectedDocumentID uniqueidentifier = '45122970-6152-E611-944A-D89D6719E0CB'
declare @correctionID uniqueidentifier = 'CA8AFBAE-1D1E-4485-AE56-029951CE138C'
declare @correctionDocumentNR int
declare @correctionDocumentFullNR varchar(max)

select @correctionDocumentNR = documentNR, @correctionDocumentFullNR = documentFullNR
from document.document where documentID = @correctionID

update dl set documentID = @correctionID, documentNR = @correctionDocumentNR, documentFullNR = @correctionDocumentFullNR
from document.documentLine dl inner join document.document on dl.documentID = document.documentID and dl.[status] >= 0
where initialCorrectedDocumentID = @initialCorrectedDocumentID and documentDate >= '2016-08-03'

delete from document.document where initialCorrectedDocumentID = @initialCorrectedDocumentID and documentDate >= '2016-08-03'
and correctedDocumentID != initialCorrectedDocumentID

update document.document set lastCorrectionDocumentID = @correctionID where documentID = @initialCorrectedDocumentID

select count(*), dl.documentFullNR, document.documentFullNR, dl.documentID, document.documentNR, dl.documentNR, document.financialSystemExport
from document.document inner join document.documentLine dl on dl.documentID = document.documentID and dl.[status] >= 0
where initialCorrectedDocumentID = @initialCorrectedDocumentID
group by dl.documentFullNR, dl.documentID, document.documentNR, document.financialSystemExport
, document.documentFullNR, dl.documentNR
order by document.documentNR

select count(*), dl.documentFullNR, document.documentNR, document.documentDate, document.financialSystemExport
from document.document inner join document.documentLine dl on dl.documentID = document.documentID and dl.[status] >= 0
where initialCorrectedDocumentID = @initialCorrectedDocumentID
    and correctedDocumentID = @initialCorrectedDocumentID
group by dl.documentFullNR, document.documentNR, document.financialSystemExport, document.documentDate
order by document.documentNR

update d
set sumItemValue = src.IV, sumSysItemValue = src.SIV
from document.document d inner join (
select sum(itemValue * signValue) IV, sum(sysItemValue * signValue) SIV, documentID from document.documentLine
where documentID = @correctionID and [status] = 1 group by documentID) src
on src.documentID = d.documentID

select sumItemValue, sumSysItemValue from document.document where documentID = @correctionID

select sum(itemValue * signValue), sum(sysItemValue * signValue) from document.documentLine
where documentID = @correctionID and [status] = 1

rollback tran