Untitled
unknown
plain_text
a year ago
50 kB
9
Indexable
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 ---------------------
endEditor is loading...
Leave a Comment