Untitled

mail@pastecode.io avatar
unknown
plain_text
9 days ago
50 kB
3
Indexable
Never
ALTER PROCEDURE "MONODBC"."get_excess_obsol_stocked_plpbo6"(
    in @art_artnr nvarchar(16),
    in @invLogScope integer default 6,
    in @dbg integer default 0
)

result
(
    Order_date nvarchar(40),
    "180d_before" date,
    Part_No nvarchar(16),
    std_price numeric(16,2),
    Trans_Type nvarchar(21),
    OrdSum numeric(16,0),
    balance numeric(16,0),
    s_stock numeric(16,2),
    Ordered integer,
    Reserved integer,
    ReservedSum integer,
    invLogSum integer,
    invLogSumBefore integer,
    ordBalRest integer,
    rut_kort_logg nvarchar(255),
    balance_info nvarchar(128),
    first_row integer,
    last_row integer,
    excess integer,
    obsolete integer,
    uzasZap integer,
    noLastOp nvarchar(16) -- flaga braku ost operacji
)

begin

-- kursor pobierania kolejnych dat i liczenia salda do salda aktualnego
-- ma za zadanie wyszukac ostatnią datę gdzie saldo invoice log = saldo aktualne
DECLARE c_tt_art_order_window cursor for
	select 
		order_date,
        14d_before,
        part_no,
        std_price,
        Trans_type,
        balance,
        ordered,
        reserved
    from #tt_art_order_window
    where 
		trans_type = 'Sales' or 
        trans_type = 'Sales forecast' or 
        trans_type = 'Material requirement' or 
        trans_type = 'Material (Sug)';

-- kursor poszukiwania daty dla sumy z balance
-- pozniej kasowane sa wiersze ktore nie maja flagi
-- do okreslenia czy jest excess
DECLARE c_tt_inv_log_rows cursor for
    select 
        ll_datum,
        ll_ordernr,
        ll_saldo_delta,
        ll_saldo_akt,
        rut_kort_logg
    from
        #tt_inv_log_rows where ll_saldo_delta > 0
    order by ll_datum desc for read only;

-- ten sam kursor ale czyta wiersze od konca aby
-- z danego ll_saldo_delta wyszukac Excess z Reserved 
-- w order window
DECLARE c_tt_inv_log_rows_reverse cursor for
    select 
        ll_datum,
        ll_ordernr,
        ll_saldo_delta,
        ll_saldo_akt,
        rut_kort_logg
    from
        #tt_inv_log_rows
    order by ll_datum asc for read only;

declare @date180dAgo,@date180dBefore,@firstOrderDate datetime;
declare @safety_stock integer;
declare @reserveratadd,@reserverataddAll,@orderedAddAll integer;

declare @saldo, @excess, @obsolete, @balance integer;
declare @excess_value, @obsolete_value integer;
declare @kwnown_sch integer; 
declare @order_bal_rest numeric(16,0);
declare @bal_info_text nvarchar(1024);
declare @ll_saldo_delta_sum integer;
declare @rut_kort_logg,@rut_kort_logg_out nvarchar(128);
declare @art_last_row_cnt integer;
declare @art_last_row integer;
declare @art_order_lines_num integer;
declare @sumReserved integer;
declare @std_price numeric(16,2);
declare @known_sch integer;

-- zmienne dla kursora order window
declare @ttaow_order_date date;
declare @ttaow_14d_before date;
declare @ttaow_date180db date;
declare @ttaow_part_no nvarchar(16);
declare @ttaow_std_price numeric(16,2);
declare @ttaow_balance integer;
declare @ttaow_trans_type nvarchar(32);
declare @ttaow_ordered double;
declare @ttaow_reserved double;
declare @ttaow_sumReserved double;

-- zmienne dla kursora Inventory logu
declare @ttilr_art_last_row_cnt integer;
declare @ttilr_ll_saldo_delta_sum integer;
declare @ttilr_ll_datum datetime;
declare @ttilr_ll_ordernr nvarchar(16);
declare @ttilr_ll_saldo_delta integer;
declare @ttilr_ll_saldo_akt integer;
declare @ttilr_rut_kort_logg nvarchar(255);
declare @ttilr_date180db datetime;

-- do sprawdzania czy ktores ze zlecen ma niedokonczone operacje umieszczenia na magazynie
--declare @count_too_op integer;
--declare @too_sista_anstnr integer;
declare @too_op integer;
declare @too_ben nvarchar(64);
declare @too_sista_anstnr nvarchar(16);
declare @too_stv_id smallint;
declare @noLastOp nvarchar(16);

declare @uzasZap integer; -- licznik uzasadnionego zapasu dla danej wiazki, mowi ze prawdopodobnie ta ilosc zostanie sprzedana forecastem, ktory nie musi teraz istniec
declare @uzasZap_value integer; -- licznik uzasadnionego zapasu dla danej wiazki, mowi ze prawdopodobnie ta ilosc zostanie sprzedana forecastem, ktory nie musi teraz istniec

DECLARE err_notfound exception for sqlstate value '02000';

drop table if exists #get_article_part_info_anyuserhere;
create table #get_article_part_info_anyuserhere (
    period NVARCHAR(8) NULL,
    datum DATETIME NULL,
    datum0 DATETIME NULL,
    trans INTEGER NULL,
    ordernr NVARCHAR(20) NULL,
    lev_kund NVARCHAR(40),
    best DOUBLE NULL,
    reserverat DOUBLE NULL,
    saldo DOUBLE NULL,
    art_id INTEGER NULL,
    art_nr NVARCHAR(16),
    stalle INTEGER NULL,
	cc_row_id INTEGER NULL,
    cc_orsakande_typ INTEGER NULL,
    cc_orsakande_id INTEGER NULL, 
    cc_orsakande_ordernr NVARCHAR(20) NULL,
    ko_nr NVARCHAR(10) NULL, --customer code
    art_std_pris NUMERIC(15,5) NULL,
    art_eok NUMERIC(16,6) NULL,
    art_sak_lag NUMERIC(16,6) NULL DEFAULT 0,
    art_sak_tid SMALLINT NULL DEFAULT 0,
    art_ledtid NUMERIC(15,2) NULL DEFAULT 0,
);

--message @art_artnr type warning to client;
--begin try
-- pobierz dane order window z monitora dla określonego art.

if @dbg = 2 then
    message @art_artnr type warning to client;
end if;

--message @invLogScope type warning to client

--call get_article_part_info_anyuserhere(@art_artnr);
insert into #get_article_part_info_anyuserhere(
    period,
    datum,
    datum0,
    trans,
    ordernr,
    lev_kund,
    best,
    reserverat,
    saldo,
    art_id,
    art_nr,
    stalle,
	cc_row_id,
    cc_orsakande_typ,
    cc_orsakande_id, 
    cc_orsakande_ordernr,
--    ko_nr,
    art_std_pris,
    art_eok ,
    art_sak_lag ,
    art_sak_tid,
    art_ledtid
)
select 
   period,
    datum,
    datum0,
    trans,
    ordernr,
    lev_kund,
    best,
    reserverat,
    saldo,
    art_id,
    artnr,
    stalle,
	cc_row_id,
    cc_orsakande_typ,
    cc_orsakande_id, 
    cc_orsakande_ordernr,
--    ko_nr,
    art_std_pris,
    art_eok ,
    art_sak_lag ,
    art_sak_tid,
    art_ledtid
into #get_article_part_info_anyuserhere
from get_article_part_info_anyuserhere(@art_artnr);

set @reserveratadd = 0;

set @safety_stock = (select art_sak_lag from monitor.artikel_plan_stalle key join monitor.artikel where art_artnr = @art_artnr and ls_id = 1);
if @safety_stock is null then 
	set @safety_stock = 0;
end if;

set @balance = (select akt_saldo from get_article_saldo_plpbo(@art_artnr));
set @order_bal_rest = @balance;

if @dbg = 1 then 
    message 'sstock: ' + str(@safety_stock) + char(10) + ' order_bal_rest: ' + str(@order_bal_rest) type warning to client; 
end if;

set @known_sch = 0;

-- gdy sstock wiekszy od balance to pomin dalsze sprawdzanie
if @safety_stock > @order_bal_rest  then
    if @dbg = 1 then message 'safety_stock > balance (ok)' type warning to client; end if;
    set @bal_info_text = 'safety_stock > balance (ok)';
    set @excess_value = null;
    set @obsolete_value = null;
    set @known_sch = 1;
end if;

if @safety_stock = @order_bal_rest and @known_sch = 0 then
    if @dbg = 1 then message 'safety_stock = balance (ok)' type warning to client; end if;
    set @bal_info_text = 'safety_stock = balance (ok)';
    set @excess_value = null;
    set @obsolete_value = null;
    set @known_sch = 1;
end if;

if @order_bal_rest - @safety_stock <= 0 and @known_sch = 0 then
    if @dbg = 1 then message 'saldo - sstock <= 0' type warning to client; end if;
    set @bal_info_text = 'saldo - sstock <= 0';
    set @excess_value = null;
    set @obsolete_value = null;
    set @known_sch = 1;
end if;

--if not @kwnown_sch = 1 then
--    set @bal_info_text = '!!Other schema - not implemented!!';
--end if;

if @known_sch = 1 then
    select 
        '' as Order_Date,
        @date180dBefore as '180d_before',
        @art_artnr as 'Part_No',
        @std_price as std_price,
        '' as Trans_Type,
        null as OrdSum,
        @order_bal_rest as balance,
        @safety_stock as s_stock,
        null as 'Ordered',
        null as 'Reserved',
        null as ReservedSum,
        null as invLogSum,
        null as invLogSumBefore,
        null as ordBalRest,
        null as rut_kort_logg,
        @bal_info_text as balance_info,
        null as first_row,
        null as last_row,
        @excess_value as excess,
        @obsolete_value as obsolete,
        null as uzasZap,
        null as noLastOp;
    break;
end if;
-- jezeli sstock nie puste, > 0 i <= saldo to odejmuj od salda 
if @safety_stock is not null and @safety_stock > 0 and @safety_stock <= @order_bal_rest then 
    set @order_bal_rest = @order_bal_rest - @safety_stock;
end if;

if @dbg = 1 then message 'balance - sstock: ' + str(@order_bal_rest) type warning to client; end if;

set @saldo = @order_bal_rest;
set @obsolete = @order_bal_rest;
set @std_price = (select art_std_pris from monitor.artikel where art_artnr = @art_artnr);
--if @dbg = 1 then message 'balance: ' + str(@saldo) type warning to client; end if;

------------------------------------------ BRAK FORECASTOW W ORDER WINDOW ---------------------------------------------------------------
-- gdy brak forecastów 7,8,9,10,11 to wszystko jako obsolete 

if (select count(*) from #get_article_part_info_anyuserhere where trans in (7,8,9,10,11)) = 0 then

    if @dbg = 1 then message 'Brak forecastow' type warning to client; end if;

    --set @art_last_row_cntc = (select count(part_no) from  #tt_art_order_window where #tt_art_order_window.part_no = @art_artnr);
    -- message @art_last_row_cnt type warning to client;
    --set @art_order_lines_num = @art_last_row_cnt; -- ile jest wierszy
    --if @dbg = 1 then message 'Art ' + @art_artnr + ' ma wierszy: ' + str(@art_last_row_cnt) type warning to client; end if;
    --id @dbg=1 then select * from #tt_art_order_window ; end if;

    --set @ttilr_date180db = (select (DATEADD(DAY, -180,@order_date)));
    set @ttilr_date180db = (select (DATEADD(MONTH, -@invLogScope ,date(now())))); -- zakres szukania do roku od teraz

    drop table if exists #tt_inv_log_rows;

    -- pobierz do tabeli tymczasowej inventory log dla okreslonej wiazki
    if @dbg=1 then message 'Pobieram InvLog do #tt_inv_log_rows' type warning to client; end if;
    select 
        cast(ll_datum as date) as ll_datum,
        ll_ordernr,
        --cast(ll_saldo_delta as numeric(16,0)) ll_saldo_delta,
        cast(sum(ll_saldo_delta) as numeric(16,0)) ll_saldo_delta,
        cast(sum(ll_saldo_akt) as numeric(16,0)) ll_saldo_akt,
        list(mrb.rut_kort_logg) as rut_kort_logg,
        cast(null as nvarchar(64)) as balance_info,
        null as lessThanBalance, 
        null as uzasZap,
        cast(null as nvarchar(16)) as noLastOp
    into #tt_inv_log_rows
    from
        monitor.lagerlogg ml
        LEFT OUTER JOIN monitor.rutin_beskrivning mrb ON ml.rut_id = mrb.rut_id
    where
        ml.ll_artnr = @art_artnr and
        ll_datum >= @ttilr_date180db and -- do pół roku wstecz od dzisiaj (now)
        ml.ll_saldo_delta is not null AND
        ml.ll_saldo_delta > 0 and
        --mrb.rut_kort_logg IN ('Recording','Phys. Invt.','Del. PL Rep.') and
        --mrb.rut_kort_logg IN ('Del. PL Rep.','Recording','Undo Del. Re','Phys. Invt.','Op. Rep.') and
--        mrb.rut_kort_logg IN ('Recording','Phys. Invt.','Op. Rep.') and -- op rep dla wiazki 3222358279#001 doaje 2szt
        mrb.rut_kort_logg IN ('Recording','Op. Rep.','Arrival Rep.') and -- op rep dla wiazki 3222358279#001 doaje 2szt
        (ll_stalle = 1) AND 
        rut_sprak_id = 1 
    group by 
        ll_datum,
        ll_ordernr,
        lessThanBalance
    order by ll_datum desc; -- wg zapisu monitora data malejaco od teraz do histori

    --if @dbg = 1 then select * from #tt_inv_log_rows; end if;

    -- pobierz ilosc wierszy z inventory logu
    set @ttilr_art_last_row_cnt = (select count(*) from  #tt_inv_log_rows);
    if @dbg = 1 then message 'ilosc wierszy inv logu: ' +str(@ttilr_art_last_row_cnt) type warning to client; end if;

    set @known_sch = 0;

    -- gdy brak rekordów dla inv log to ustaw info o braku
    if @ttilr_art_last_row_cnt is null or @ttilr_art_last_row_cnt = 0 then 
        if @dbg = 1 then message 'Brak inv logu dla podanego art: ' type warning to client; end if;

        set @known_sch = 1; set @bal_info_text = 'Brak forecastów i inv logu dla podanego art';
        set @obsolete_value = @balance - @safety_stock;

    else
        -- gdy są jakiekolwiek rekordy w Inventory Logu
        if @ttilr_art_last_row_cnt = 1 then if @dbg=1 then message 'jeden wiersz inv log' type warning to client; end if;
        else if @dbg=1 then message 'wiele wierszy z inv log' type warning to client; end if;
        end if;

        if @dbg = 1 then message 'Są wiersze szukam ost wiersza z sumy inv log = balance' type warning to client; end if;

        set @ttilr_ll_saldo_delta_sum = 0;

        -- są rekordy w inv logu - kursor szuka ostatniego wiersza z sumy >= balance
        open c_tt_inv_log_rows;
        c_tt_inv_log_rows_nofcasts_loop:
        loop

            -- jezeli jest tylko jeden wiersz to pobierz
            if @ttilr_art_last_row_cnt = 1 then
                fetch c_tt_inv_log_rows into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            else
                -- pobierz wiersz z bazy
                fetch next c_tt_inv_log_rows into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            end if;

            -- zakocz petle gdy koniec 
            if sqlstate = err_notfound then
                if @dbg = 1 then message 'leave loop - c_tt_inv_log_rows_nofcasts_loop' type warning to client; end if;
                leave c_tt_inv_log_rows_nofcasts_loop;
            end if;

            -- sumuj saldo wiersza z inv log do poprzedniej wartosci
            if @ttilr_ll_saldo_delta > 0 then
            set @ttilr_ll_saldo_delta_sum = @ttilr_ll_saldo_delta_sum + @ttilr_ll_saldo_delta;
            end if;
            -- jezeli suma z delty inv log < (balance - safety stock) to dodawaj 
            if @ttilr_ll_saldo_delta_sum < ( select (@balance - @safety_stock)) then
                if @dbg = 1 then message 'inv log sum: ' + str(@ttilr_ll_saldo_delta_sum) + ' < balance: ' + str(@balance) type warning to client; end if;
            else
                if @dbg = 1 then 
                    message 'inv log sum >= balance: ' + str(@ttilr_ll_saldo_delta_sum)
                        + 'InvLog Data: ' +(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) type warning to client;
                end if;
                leave c_tt_inv_log_rows_nofcasts_loop;
            end if;

            if @dbg = 1 then 
                message 'data ost logu: '+(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) + char(10) 
                    + 'delta: ' + str(@ttilr_ll_saldo_delta) + char(10) 
                    + 'razem: ' + str(@ttilr_ll_saldo_delta_sum) + ' z ' + str(@balance) + char(10)
                    type warning to client; 
            end if;

        end loop;
        close c_tt_inv_log_rows;
        deallocate cursor c_tt_inv_log_rows;
        
        if @dbg = 1 then message 'Usuwam wiersze InvLog starsze niż ' + (CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) type warning to client; end if;

        delete from #tt_inv_log_rows where ll_datum < @ttilr_ll_datum;

       if (select sum(ll_saldo_delta) from #tt_inv_log_rows where ll_saldo_delta > 0) <= 0 or (select sum(ll_saldo_delta) from #tt_inv_log_rows) is null then
            set @bal_info_text = 'Suma invLog < 0 lub NULL (operacje zleceń?), pomijam dalszą analize';
            --message @bal_info_text type warning to client;

            -- przestarzały to reszta z balance - zapas bezp - suma reserved
            set @obsolete_value = (@balance - @safety_stock);
            -- gdyby suma reserved przekroczyła balance to koryguj wartosc ujemna na 0 bo cała suma przypada na excess
            if @obsolete_value <= 0 then
                set @obsolete_value = 0;
            end if;
            set @excess_value = (@balance - @obsolete_value);

            select 
            '' as Order_Date,
            @date180dBefore as '180d_before',
            @art_artnr as 'Part_No',
            @std_price as std_price,
            '' as Trans_Type,
            null as OrdSum,
            @order_bal_rest as balance,
            @safety_stock as s_stock,
            null as 'Ordered',
            null as 'Reserved',
            null as ReservedSum,
            null as invLogSum,
            null as invLogSumBefore,
            null as ordBalRest,
            null as rut_kort_logg,
            @bal_info_text as balance_info,
            null as first_row,
            null as last_row,
            @excess_value as excess,
            @obsolete_value as obsolete,
            null as uzasZap,
            null as noLastOp;
            break;
        end if; 

        if @dbg = 1 then message 'Poszukiwanie Excess/Obsolete dla Reserved w Order Windows wg Inv Log' type warning to client; end if;

        set @ttilr_art_last_row_cnt = (select count(*) from  #tt_inv_log_rows);
        
        -- ustaw zmienne excess i obsolete dla tej wiazki na 0
        set @excess =0;
        set @obsolete_value = 0;
        set @obsolete =0;
        set @uzasZap = @safety_stock; -- zeruj uzasadniony zapas co nowy artykul
        set @uzasZap_value = @safety_stock;

        -- kursor pobiera kolejno delty z wierszy i szuka exces i obsolete
        --begin try
        open c_tt_inv_log_rows_reverse;
        c_tt_inv_log_rows_reverse_nofcasts_loop:
        loop

            -- jezeli jest tylko jeden wiersz to pobierz
            if @ttilr_art_last_row_cnt = 1 then
                fetch c_tt_inv_log_rows_reverse into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            else
                -- pobierz wiersz z bazy
                fetch next c_tt_inv_log_rows_reverse into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            end if;

            -- zakocz petle gdy koniec 
            if sqlstate = err_notfound then
                if @dbg = 1 then message 'leave loop - c_tt_inv_log_rows_reverse_loop' type warning to client; end if;
                leave c_tt_inv_log_rows_reverse_nofcasts_loop;
            end if;

            -- jezeli jest zlecenie 
            select top 1 too_op,too_ben,too_stv_id
            into @too_op,@too_ben,@too_stv_id
            from monitor.artikel ma
            left outer join monitor.TORDER_HUVUD on torder_huvud.art_id = ma.art_id
            left outer join monitor.torder_operation on monitor.torder_operation.toh_id = monitor.torder_huvud.toh_id
            where toh_ordernr = @ttilr_ll_ordernr and art_artnr = @art_artnr --and too_ben like 'Pako%'
            order by too_op desc;

            if @dbg = 1 then
                message 'zlecenie:' + @ttilr_ll_ordernr + char(10) 
                + 'art:' + @art_artnr + char(10)
                + 'ost.operacja:'+str(@too_op) + char(10)
                + 'opis:'+@too_ben+char(10)+
                + 'finished:'+@too_sista_anstnr
                type warning to client;
            end if;

            -- jezeli jest jakas operacja ale nie finished
            -- jezeli jest jakas operacja ale nie finished
            if @too_op is not null and not @too_stv_id = 9 then
                set @nolastop = @ttilr_ll_ordernr;
            end if;

            -- ustal datę ll_datum o pół roku do przodu
            --set @date180dAgo = (select (DATEADD(MONTH, +6,@ttilr_ll_datum)));
            set @date180dAgo = (select (DATEADD(MONTH, +@invLogScope,@ttilr_ll_datum)));

            if @dbg = 1 then 
                message 'data logu: '+(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) + char(10) 
                + 'data logu +180d: '+(CONVERT( CHAR( 20 ), @date180dAgo, 104 )) + char(10)
                + 'delta: ' + str(@ttilr_ll_saldo_delta)
                type warning to client; 
            end if;

            -- jeżeli data dzisiaj < daty +6pół roku to uzasadniony zapas, pomin przetwarzanie, lub jesli delta = 0
            --if date(now()) < @date180dAgo or @ttilr_ll_saldo_delta = 0 then
            if date(now()) < @date180dAgo  then
                if @dbg = 1 then message 'Data +półR > od dzisiaj, uzasadniony zapas - pomijam' type warning to client; end if;
                -- nie wykonuje żadnego liczenia poza uzasadnonym zapasem
                if @ttilr_ll_saldo_delta > 0 then
                    set @uzasZap_value = @uzasZap_value + @ttilr_ll_saldo_delta;
                    -- gdy suma zapasu > balance to zrównaj aby wartosci nie byla > balance, w razie gdyby przeskoczyło ilość
                    if @uzasZap_value > @balance then set @uzasZap_value = @balance; end if;
                set @known_sch=1; set @bal_info_text = 'Data +półR > od dzisiaj, uzasadniony zapas - pomijam';
                end if;

            else
                -- data dzisiaj > daty z inv log +pół roku więc wartość inv log jest obsolete
                set @known_sch=1; set @bal_info_text = 'Data +półR < od dzisiaj, przetwarzam';
                if @dbg = 1 then message @bal_info_text + char(10) +
                        str(@ttilr_ll_saldo_delta) + ' + ' + str(@obsolete_value) + ' = ' 
                        + str(@obsolete_value + @ttilr_ll_saldo_delta) type warning to client; 
                end if;
                --if @ttilr_ll_saldo_delta > 0 then
                    set @obsolete_value = @obsolete_value + @ttilr_ll_saldo_delta;
                    if @obsolete_value > @balance then set @obsolete_value = @balance; end if;
                --end if;
                --update #tt_inv_log_rows set balance_info = 'Data +półR > od dzisiaj, uzasadniony zapas - pomijam' where current of c_tt_inv_log_rows_reverse;
            end if;

--            update #tt_inv_log_rows set uzasZap = @uzasZap

        end loop; -- end c_tt_inv_log_rows_reverse_nofcasts_loop

    end if; -- if @ttilr_art_last_row_cnt is null or @ttilr_art_last_row_cnt = 0 then 

    -- gdy inny nieznany schemat
    if @known_sch = 0 then set @bal_info_text = '!! no forecast in ord window - other schema !!'; end if;

    select 
        '' as Order_Date,
        @date180dAgo as '180d_before',
        @art_artnr as 'Part_No',
        @std_price as std_price,
        '' as Trans_Type,
        null as OrdSum,
        @order_bal_rest as balance,
        @safety_stock as s_stock,
        null as 'Ordered',
        null as 'Reserved',
        null as ReservedSum,
        null as invLogSum,
        null as invLogSumBefore,
        null as ordBalRest,
        null as rut_kort_logg,
        @bal_info_text as balance_info,
        null as first_row,
        null as last_row,
        @excess_value as excess,
        @obsolete_value as obsolete, -- brak obsolete bo istnieje inventory log i jest = balance, wiec calosc exces
        @uzasZap_value as uzasZap,
        @nolastOp as noLastOp;

    set  @known_sch = 0;

    break;

--end try 
--begin catch 
--message 'err - no transtyp proc: ' + @art_artnr type warning to client;
--end catch;

end if;  -- koniec warunku - gdy nie ma forecastów
------------------------------------------ KONIEC BRAK FORECASTOW W ORDER WINDOW ---------------------

------------------------------------------ SĄ FORECASTY W ORDER WINDOW ---------------------
if (select count(*) from #get_article_part_info_anyuserhere where trans in (7,8,9,10,11)) > 0 then
if @dbg = 1 then message 'sa forecasty' type warning to client; end if;
--begin try 

    -- pobierz sumę reserverat dla order window
    set @reserverataddAll = (
        select sum(reserverat) from #get_article_part_info_anyuserhere
    );

    set @orderedAddAll = (
        select sum(best) from #get_article_part_info_anyuserhere
    );

    if @dbg = 1 then message 'reserverataddAll: ' + CONVERT( CHAR( 20 ), @reserverataddAll, 104 ) + char(10) +
                    'orderedAddAll: ' + CONVERT( CHAR( 20 ), @reserverataddAll, 104 ) type warning to client; end if;
    -- 
    set @firstOrderDate = (select top 1 datum from #get_article_part_info_anyuserhere where trans in (7,8,9,10,11)order by datum asc);
    if @dbg = 1 then message 'First order date: ' + CONVERT( CHAR( 20 ), @firstOrderDate, 104 ) type warning to client; end if;

    -- pobierz do tabeli celem przetwarzania kursorem
    select 
        --(IF datum = datum0 then "monitor"."qf_format_date"(1, datum) end if) as Order_Date,
        cast(dateformat(datum,'yyyy-mm-dd') as date) as Order_Date,
        cast(DATEADD(MONTH, -@invLogScope,datum) as date)as '180d_before',
        art_nr as 'Part_No',
        cast(art_std_pris as numeric(16,2)) as std_price,
        (CASE trans
            when 2 then 'purchase'
            when 4 then 'Manufacturing'
            when 5 then 'Manufacturing (Sug)'
            when 7 then 'Sales'
            when 8 then 'Sales forecast'
            when 9 then 'Sales forecast cust.'
            when 10 then 'Material requirement'
            when 11 then 'Material (Sug)'
            when 17 then 'Stock order sug (In)'
            when 18 then 'Stock order sug (Out)'
            else 'Dzwon do informatyka'
        END) as 'Trans_Type',
        cast(null as integer) as OrdSum,
        cast((select akt_saldo from get_article_saldo_plpbo(@art_artnr)) as integer) as balance,
        @safety_stock as s_stock,
        sum(best) as 'Ordered',
        sum(reserverat) as 'Reserved',
        null as ReservedSum,
        null as invLogSum,
        null as invLogSumBefore,
        null as  ordBalRest,
        cast(NULL as nvarchar(255)) as rut_kort_logg,
        cast('NULL' as nvarchar(64)) as balance_info,
        null as first_row,
        null as last_row,
        cast(null as integer) as excess,
        cast(null as integer) as obsolete,
        null as uzasZap,
        cast(null as nvarchar(16)) as noLastOp
    INTO #tt_art_order_window 
    from 
        #get_article_part_info_anyuserhere 
    where Trans_Type in ('Sales forecast','Sales','Material requirement','Material (Sug)','Sales forecast cust.')
        group by 
        Order_Date,
        datum,
        '180d_before',
        Part_No,
        std_price,
        Trans_Type,
        balance,
        balance_info
    order by Order_Date asc;

    -- ilosc wierszy w order window dla danych typow zamowienia, wymagane do zliczenia ostatniego wiersza analizy kursora.
    -- zanim wypadnie z kursora musi podliczyc gdy osiagnie ostatni wiersz
    set @art_last_row_cnt = (select count(part_no) from  #tt_art_order_window where #tt_art_order_window.part_no = @art_artnr);
    -- message @art_last_row_cnt type warning to client;
    set @art_order_lines_num = @art_last_row_cnt; -- ile jest wierszy
    if @dbg = 1 then message 'Art ' + @art_artnr + ' ma wierszy: ' + str(@art_last_row_cnt) type warning to client; end if;

    --id @dbg=1 then select * from #tt_art_order_window ; end if;

    --set @ttilr_date180db = (select (DATEADD(DAY, -180,@order_date)));
    set @ttilr_date180db = (select (DATEADD(MONTH, -@invLogScope,date(now())))); -- zakres szukania do roku od teraz

    drop table if exists #tt_inv_log_rows;

    -- pobierz do tabeli tymczasowej inventory log dla okreslonej wiazki

    select 
        cast(ll_datum as date) as ll_datum,
        ll_ordernr,
        --cast(ll_saldo_delta as numeric(16,0)) ll_saldo_delta,
        cast(sum(ll_saldo_delta) as numeric(16,0)) ll_saldo_delta,
        cast(sum(ll_saldo_akt) as numeric(16,0)) ll_saldo_akt,
        list(mrb.rut_kort_logg) as rut_kort_logg,
        null as lessThanBalance, 
        null as uzasZap,
        null as noLastOp
    into #tt_inv_log_rows
    from
        monitor.lagerlogg ml
        LEFT OUTER JOIN monitor.rutin_beskrivning mrb ON ml.rut_id = mrb.rut_id
    where
        ml.ll_artnr = @art_artnr and
        ll_datum >= @ttilr_date180db and -- do pół roku wstecz od dzisiaj (now)
        ml.ll_saldo_delta is not null AND
        ml.ll_saldo_delta > 0 and
        --mrb.rut_kort_logg IN ('Recording','Phys. Invt.','Del. PL Rep.') and
        --mrb.rut_kort_logg IN ('Recording','Phys. Invt.','Del. PL Rep.') and
        --mrb.rut_kort_logg IN ('Del. PL Rep.','Recording','Undo Del. Re','Phys. Invt.','Op. Rep.') and
        mrb.rut_kort_logg IN ('Recording','Op. Rep.','Arrival Rep.') and
 --       mrb.rut_kort_logg IN ('Recording') and
        (ll_stalle = 1) AND 
        rut_sprak_id = 1 
--            order by ll_datum asc;
    group by 
        ll_datum,
        ll_ordernr,
        lessThanBalance
    order by ll_datum desc; -- wg zapisu monitora data malejaco od teraz do histori

    --if @dbg = 1 then 
    --select * from #tt_inv_log_rows;
    --end if;

    set @ttilr_art_last_row_cnt = (select count(*) from  #tt_inv_log_rows);
    set @ttilr_ll_saldo_delta_sum = 0;

    -- gdy brak rekordów dla inv log to ustaw
    if @ttilr_art_last_row_cnt is null or @ttilr_art_last_row_cnt = 0 then 
        if @dbg = 1 then message 'Brak inv log' type warning to client; end if;
        set @known_sch=0;
        -- gdy brak inv log i balance - safety stock > 0 to calosc jako excess
        if @balance - @safety_stock > 0 then
            set @known_sch=1;
            -- czasem do sumy reserved moze byc dodana forecastowa ordered.. i wtedy reserved jest zwiekszona
            -- w takiej sytuacji trzeba reserverataddall zmniejszyc do balance
            if @reserverataddAll > (select @balance - @safety_stock) then
                set @reserverataddAll = (select @balance - @safety_stock);    
            end if;
            --set @obsolete_value = ((select(@balance - @safety_stock)) - (select @reserverataddAll - @orderedAddAll));
            set @obsolete_value = ((select(@balance - @safety_stock)) - @reserverataddAll);

            set @excess_value = (@balance - @obsolete_value);
            if @obsolete_value <= 0 then
                set @obsolete_value = null;
            end if;
            set @bal_info_text = 'Brak Inventowry log';
        end if;

        
        if @known_sch = 1 then
        select 
        '' as Order_Date,
        @date180dBefore as '180d_before',
        @art_artnr as 'Part_No',
        @std_price as std_price,
        '' as Trans_Type,
        null as OrdSum,
        @order_bal_rest as balance,
        @safety_stock as s_stock,
        null as 'Ordered',
        null as 'Reserved',
        null as ReservedSum,
        null as invLogSum,
        null as invLogSumBefore,
        null as ordBalRest,
        null as rut_kort_logg,
        @bal_info_text as balance_info,
        null as first_row,
        null as last_row,
        @excess_value as excess,
        @obsolete_value as obsolete,
        null as uzasZap,
        null as noLastOp;
        break;
        end if;
        --update #tt_art_order_window 
        --set 
        --    #tt_art_order_window.excess = null,
        --    #tt_art_order_window.balance_info = 'Brak danych Recording w inv log (Excess?)'
        --where current of c_tt_art_order_window;            
    else

        set @ttilr_ll_saldo_delta_sum = 0;

        -- kursor szuka ostatniego wiersza z sumy >= balance

--    select * from #tt_inv_log_rows;
--    break;

        open c_tt_inv_log_rows;
        c_tt_inv_log_rows_loop:
        loop

            -- jezeli jest tylko jeden wiersz to pobierz
            if @ttilr_art_last_row_cnt = 1 then
                if @dbg=1 then message 'jeden (pierwszy) wiersz inv log' type warning to client; end if;
                fetch c_tt_inv_log_rows into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            else
                -- pobierz wiersz z bazy
                fetch next c_tt_inv_log_rows into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            end if;

            -- zakocz petle gdy koniec 
            if sqlstate = err_notfound then
                if @dbg = 1 then message 'leave loop' type warning to client; end if;
                leave c_tt_inv_log_rows_loop;
            end if;

            if @dbg = 1 then
                message 'pobrana data:' + (CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) type warning to client;
            end if;

            -- jezeli suma z delty inv log < balance to dodawaj pod warunkiem ze data z wiersza invlog > daty 180dbefore
            if @ttilr_ll_saldo_delta > 0 then
            set @ttilr_ll_saldo_delta_sum = @ttilr_ll_saldo_delta_sum + @ttilr_ll_saldo_delta;
            end if;
            if @ttilr_ll_saldo_delta_sum < ( select (@balance-@safety_stock)) then
                if @dbg = 1 then message 'inv log sum: ' + str(@ttilr_ll_saldo_delta_sum) + ' < balance: ' + str((select @balance - @safety_stock )) type warning to client; end if;
                --if @dbg = 1 then message 'inv log sum: ' + str(@ttilr_ll_saldo_delta_sum) + ' < balance: ' + str(@balance) + ' - ' + str(@safety_stock) + ' sstock' type warning to client; end if;
            else
                --if @dbg = 1 then message 'inv log sum >= balance: ' + str(@ttilr_ll_saldo_delta_sum) +  ' - ' + str(@safety_stock) + ' sstock, leave loop' type warning to client; end if;
                if @dbg = 1 then 
                    message 'inv log sum >= balance: ' + str(@ttilr_ll_saldo_delta_sum) +char(10) +
                     '@ttilr_ll_saldo_delta:' + str(@ttilr_ll_saldo_delta) + char(10) +
                     'InvLog Data: ' +(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) type warning to client;
                end if;
                leave c_tt_inv_log_rows_loop;
            end if;

            if @dbg = 1 then 
                message 'data logu: '+(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) + char(10) 
                + 'delta: ' + str(@ttilr_ll_saldo_delta) + char(10) 
                + 'razem: ' + str(@ttilr_ll_saldo_delta_sum) + ' z ' + str(@balance) + char(10)
                type warning to client; 
            end if;

        end loop;
        close c_tt_inv_log_rows;
        deallocate cursor c_tt_inv_log_rows;
        -- usun  ztabeli wszystkie wiersze wczesniejsze od ostatniego pretwarzanego
        if @dbg = 2 then message 'Usuwam wiersze starsze niz ' +(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) type warning to client; end if;
--select * from  #tt_inv_log_rows;
        delete from #tt_inv_log_rows where ll_datum < @ttilr_ll_datum;
--select * from  #tt_inv_log_rows;
--select sum(ll_saldo_delta) from #tt_inv_log_rows where ll_saldo_delta > 0;
--break ;
        -- sprawdz czy suma inv logu nie jest ujemna.. to znaczy ze w ciagu pol roku nie było recordingu na + lub są nietypowe wjscia na mag
        if (select sum(ll_saldo_delta) from #tt_inv_log_rows where ll_saldo_delta > 0) <= 0 then
            set @bal_info_text = 'Suma invLog < 0 (operacje zleceń?), pomijam analize fcastów';
            --message @bal_info_text type warning to client;

            -- przestarzały to reszta z balance - zapas bezp - suma reserved
            if @reserverataddAll > @balance then 
                set @reserverataddAll = (select @balance - @safety_stock);
            end if;

            set @obsolete_value = (@balance - @safety_stock - @reserverataddAll);
            -- gdyby suma reserved przekroczyła balance to koryguj wartosc ujemna na 0 bo cała suma przypada na excess
            if @obsolete_value <= 0 then
                set @obsolete_value = 0;
            end if;
            set @excess_value = (@balance - @obsolete_value);

            select 
            '' as Order_Date,
            @date180dBefore as '180d_before',
            @art_artnr as 'Part_No',
            @std_price as std_price,
            '' as Trans_Type,
            null as OrdSum,
            @order_bal_rest as balance,
            @safety_stock as s_stock,
            null as 'Ordered',
            null as 'Reserved',
            null as ReservedSum,
            null as invLogSum,
            null as invLogSumBefore,
            null as ordBalRest,
            null as rut_kort_logg,
            @bal_info_text as balance_info,
            null as first_row,
            null as last_row,
            @excess_value as excess,
            @obsolete_value as obsolete,
            null as uzasZap,
            null as noLastOp;
            break;
        end if; 

        --update #tt_art_order_window  set excess =  str(@ttilr_ll_saldo_delta_sum),
        --balance_info = @ttilr_ll_datum;

        if @dbg = 1 then message 'Ostatnia pozycja >= balance: ' + (CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) + 
        'wartosc changed: ' +str(@ttilr_ll_saldo_delta) +char(10) +
        'wartosc changed_sum: ' + str(@ttilr_ll_saldo_delta_sum) + char(10) + 
        'Poszukiwanie excess dla Reserved w order window' type warning to client; end if;

        -- ustaw zmienne excess i obsolete dla tej wiazki na 0
        set @excess =0;
        set @excess_value = 0;
        set @obsolete =0;
        set @uzasZap =@safety_stock; -- zeruj uzasadniony zapas co nowy artykul
        set @art_last_row = 0; -- ustaw znacznik czytania pierwszego wiersza z tabeli (inaczej jest fetch next)

        -- kursor pobiera kolejno delty z wierszy i szuka exces i obsolete
        open c_tt_inv_log_rows_reverse;
        c_tt_inv_log_rows_reverse_loop:
        loop

            -- jezeli jest tylko jeden wiersz lub licznik = 0 (pierwszy wiersz) to pobierz
            if @ttilr_art_last_row_cnt = 1 then
                if @dbg=1 then  message 'jeden wiersz inv log' type warning to client; end if;
                fetch c_tt_inv_log_rows_reverse into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            else
                -- pobierz wiersz z bazy
                fetch next c_tt_inv_log_rows_reverse into 
                    @ttilr_ll_datum,
                    @ttilr_ll_ordernr,
                    @ttilr_ll_saldo_delta,
                    @ttilr_ll_saldo_akt,
                    @ttilr_rut_kort_logg;
            end if;

            -- zakocz petle gdy koniec 
            if sqlstate = err_notfound then
                if @dbg = 1 then message 'leave loop - inv log reverse' type warning to client; end if;
                leave c_tt_inv_log_rows_reverse_loop;
            end if;

            -- sprawdz czy jest zlecenie dla tego inv log
            -- czy ostatnia operacja jest finished
            select top 1 too_op,too_ben,too_stv_id
            into @too_op,@too_ben,@too_stv_id
            from monitor.artikel ma
            left outer join monitor.TORDER_HUVUD on torder_huvud.art_id = ma.art_id
            left outer join monitor.torder_operation on monitor.torder_operation.toh_id = monitor.torder_huvud.toh_id
            where toh_ordernr = @ttilr_ll_ordernr and art_artnr = @art_artnr --and too_ben like 'Pako%'
            order by too_op desc;

            if @dbg = 1 then
                message 'zlecenie:' + @ttilr_ll_ordernr + char(10) + 
                'art:' + @art_artnr + char(10) +
                'ost.operacja:' + str(@too_op) + char(10)+
                'opis:' + @too_ben + char(10) +
                'finished:' + @too_sista_anstnr
                type warning to client;
            end if;

            -- jezeli jest jakas operacja ale nie finished
            if @too_op is not null and not @too_stv_id = 9 then
                set @nolastop = @ttilr_ll_ordernr;
            end if;

            -- ustal datę ll_datum o pół roku do przodu
            set @date180dAgo = (select (DATEADD(MONTH, +@invLogScope,@ttilr_ll_datum)));

            if @dbg = 1 then 
                message 'data logu:'+(CONVERT( CHAR( 20 ), @ttilr_ll_datum, 104 )) + char(10) 
                + 'data logu +180d:'+(CONVERT( CHAR( 20 ), @date180dAgo, 104 )) + char(10)
                + 'delta:' + str(@ttilr_ll_saldo_delta)
                type warning to client; 
            end if;

            -- jeżeli data dzisiaj < daty +6pół roku to uzasadniony zapas, pomin przetwarzanie, lub jesli delta = 0
            --if date(now()) < @date180dAgo or @ttilr_ll_saldo_delta = 0 then
            --set @uzasZap = @safety_stock; -- safety stock należy do zapasu uzasadnionego
            if date(now()) < @date180dAgo then
                if @dbg = 1 then message 'Data +półR > od dzisiaj, uzasadniony zapas - pomijam' type warning to client; end if;
                set @bal_info_text = 'Data +półR > od dzisiaj, uzasadniony zapas - pomijam';
                -- nie wykonuje żadnego liczenia poza uzasadnonym zapasem
                if @ttilr_ll_saldo_delta > 0 then
                    set @uzasZap = @uzasZap + @ttilr_ll_saldo_delta; -- 
                    if @dbg = 1 then message 'Uzas zap akt:' + str(@uzasZap) type warning to client; end if;
                end if;
                -- gdy wartosc przekroczy balance to = balance, nie powinien byc > od balance
                if @uzasZap > @balance then set @uzasZap = @balance; end if;
                if @uzasZap < 0 then set @uzasZap = 0; end if;

            else
                if @dbg = 1 then message 'Data +półR < od dzisiaj, przetwarzam' type warning to client; end if;

                set @sumReserved = 0; -- zeruj zliczanie sumy reserved
                set @art_last_row_cnt = (select count(part_no) from  #tt_art_order_window where #tt_art_order_window.part_no = @art_artnr);

                open c_tt_art_order_window;
                c_tt_art_order_window_loop:
                loop

                    -- jezeli jest tylko jeden wiersz to pobierz
                    if @art_last_row_cnt = 1 then
                        fetch c_tt_art_order_window into 
                        @ttaow_order_date,
                        @ttaow_14d_before,
                        @ttaow_part_no,
                        @ttaow_std_price,
                        @ttaow_trans_type,
                        @ttaow_balance,
                        @ttaow_ordered,
                        @ttaow_reserved;
                    else
                        -- pobierz wiersz z bazy
                        -- message 'wiele' type warning to client;
                        fetch next c_tt_art_order_window into 
                        @ttaow_order_date,
                        @ttaow_14d_before,
                        @ttaow_part_no,
                        @ttaow_std_price,
                        @ttaow_trans_type,
                        @ttaow_balance,
                        @ttaow_ordered,
                        @ttaow_reserved;
                    end if;

                    -- zakocz petle gdy koniec 
                    if sqlstate = err_notfound then
                        if @dbg = 1 then message 'leave loop' type warning to client; end if;
                        leave c_tt_art_order_window_loop;
                    end if;

                    --if @dbg = 1 then message 'art_last_row_cnt:' + str(@art_last_row_cnt) type warning to client; end if;

                        set @bal_info_text = '';     
                        if @ttaow_order_date = @date180dAgo then set @bal_info_text = ' order date = @date180dAgo: '; end if;
                        if @ttaow_order_date < @date180dAgo then set @bal_info_text = ' order date < @date180dAgo: '; end if;
                        if @ttaow_order_date > @date180dAgo then 
                            set @bal_info_text = ' order date > @date180dAgo: '; 
                            -- jezeli suma excess jest mniejsza od sumy reserved to dodaj
                            if @excess_value < @ttaow_reserved and @excess_value  < @balance then
                                if @ttilr_ll_saldo_delta > 0 then
                                    set @excess_value = @excess_value + @ttilr_ll_saldo_delta;
                                end if; 
                            end if;

                            if @excess_value > @balance then  
                                set @excess_value = @balance;
                            end if; 

                            if @dbg = 1 then message 'Excess:' + str(@excess_value) type warning to client; end if;
                        end if;

                    if @dbg = 1 then    
                        if not @bal_info_text = '' then
                            message '@ttaow_order_date: ' +(CONVERT( CHAR( 20 ), @ttaow_order_date, 104 ))+ @bal_info_text + (CONVERT( CHAR( 20 ), @date180dAgo, 104 )) type warning to client;
                        end if;
                    end if;
                    -- dodaj sume do reserved
                    --set @sumReserved = @sumReserved + @ttaow_reserved;

                    if @dbg = 1 then message 'Wart Reserved: ' + str(@ttaow_reserved)+char(10)+'Suma razem z okna order window: ' + str(@sumReserved) type warning to client; end if;
                    --update #tt_art_order_window
                    --set reservedSum = @sumReserved
                    --where current of c_tt_art_order_window; 

    		        set @art_last_row_cnt = @art_last_row_cnt - 1 ;
                    --message 'art_last_row_cnt:' + str(@art_last_row_cnt) type warning to client;
	    	        -- jezeli pierwszy wiersz kursora oznacz
		            if @art_last_row_cnt = @art_order_lines_num -1 then
				        update #tt_art_order_window set first_row = 1 where current of c_tt_art_order_window;
		            end if;
		            -- jezeli ostatni wiersz kursora oznacz
		            if @art_last_row_cnt = 0 then
				        update #tt_art_order_window set last_row = 1 where current of c_tt_art_order_window;
		            end if;

                end loop;
                close c_tt_art_order_window;
                deallocate cursor c_tt_art_order_window;
            end if;
            
            update #tt_art_order_window
            set reservedSum = @sumReserved,
                excess = @excess_value;

            --if @dbg = 1 then
            --    select * from #tt_art_order_window;
            --end if;

            if @dbg = 1 then message 'Suma koncowa: ' + str(@sumReserved) type warning to client; end if;
            --leave c_tt_inv_log_rows_reverse_loop;

            -- wyszukaj w tabeli orders sume reserved dla której data nie jest wieksza od daty z wiersza inv log +180 dni
        end loop;
		close c_tt_inv_log_rows_reverse;
		deallocate c_tt_inv_log_rows_reverse;

        -- zapisz w tabeli tt_inv_log_rows_reverse sumę zapasu uzasadnioneg
        update #tt_art_order_window set uzasZap = @uzasZap;
        update #tt_inv_log_rows set uzasZap = @uzasZap;
        if @bal_info_text is not null then 
        update #tt_art_order_window set balance_info = @bal_info_text;
        end if;
        --select * from #tt_inv_log_rows
        --end try 
        --begin catch
        --    message 'err c_tt_inv_log_rows_reverse' type warning to client;
        --end catch

    end if; --    if @ttilr_art_last_row_cnt is null or @ttilr_art_last_row_cnt = 0 then 

    -- oblicz excess i obsolete
    set @sumReserved = (select sum(reserved) from #tt_art_order_window);
    
    if not @sumReserved >= @balance then
        if @dbg=1 then    
            message 'licze obsolete' +char(10) +
            'balance: ' + str(@balance) + char(10) +
            'sumReserved: ' + str(@sumReserved) + char(10) +
        'uzasZap: ' + str(@uzasZap) type warning to client;
    end if;

--    if @uzasZap >= @sumReserved then
    update #tt_art_order_window 
    set
    --    excess = @sumReserved - (select distinct ReservedSum from #tt_art_order_window),
        nolastop = @nolastop,
        obsolete = (select distinct balance from #tt_art_order_window) - @uzasZap ;
    end if;
--    end if;

    update #tt_art_order_window set nolastop = @nolastop;

    -- jezel nie liczono pierwszego i ostatniego wiersza to pobierz tylko 1    
    if (select top 1 first_row from #tt_art_order_window) is null then
        select top 1 * from #tt_art_order_window order by balance_info desc;
    else
        -- w innym przypadku
        if not @dbg = 1 then
            delete from #tt_art_order_window where first_row is null;
        end if;
        select * from #tt_art_order_window order by balance_info desc;
    end if;
--end try 
--begin catch 
--message 'err - transtyp proc: ' + @art_artnr type warning to client;
--end catch;

end if; 
------------------------------------------ KONIEC SĄ FORECASTY W ORDER WINDOW ---------------------

end
Leave a Comment