z.B.
aus dem erfassten Bestand
- O-Sostr-Lager
- O-W13rp-Lager1
- O-W13rp-Lager2
- ...
Rechnungen kalkulieren
- Miete
- BK
- ...
--> /frepjs2000/Fr_Anf_Ang_Auftr_Pflege?submitaction=%2F%3D%2F&datatable_primarykey=25
Grundstücke, Lagerhallen und Co
kann man auch als Stücklisten betrachten.
- 1 - (TxtDoc-lager2026-07-08.html)
Einen Artikel kann man
- herstellen
- einkaufen
- verkaufen
- vermieten
- warten
- ...
( Was das ERP eben hergibt )
z.B.
--> /frepjs2000/Fr_Artikel_Pflege?submitaction=%2F%3D%2F&datatable_primarykey=129
Pro Artikelgruppe können individuelle Felder ohne Datenbank/Programm-Änderung definiert werden
--> s.a. https://jobst-software.net/ainfojs2000/InfoPrint_Default?only_REPORT_KNZ=TxtDoc&search_text=ADialog
- 2 - (TxtDoc-lager2026-07-08.html)
Zusammen mit den aktuellen Stammdaten,
ergibt sich der aktuelle Bedarf.
--> https://jobst-software.net/al_storage/?submitaction=%2F%3D%2F&datatable_primarykey=6
Auch hier, trägt die Last die SQL.
Webanwendung und/oder Office für Eingabe und Anzeige.
Die SQL kümmert sich um
- Tabellen -- Abfragen
- Benutzer -- Rollen -- Berechtigungen
- Integrität
- Grundlagen für Reports / PDFs
Sparsamer Umgang mit Scripts/Makros fördert
- Systemunabhängigkeit
und
- Sicherheit
- 3 - (TxtDoc-lager2026-07-08.html)
<dialog id="dialog_wart_details">
<form> <input name="json_input_ele_no" readonly type="hidden" />
<label>W_Art</label> <input name="W_Art" list="WART_TYP_LIST" />
<label>Intervall_Knz</label> <input name="Intervall_Knz" list="INTERVALL_KNZ_LIST" />
<label>Intervall_Cnt</label> <input name="Intervall_Cnt" type="number" min="0" max="3" step="1" />
<label>W_Text</label> <input name="W_Text" />
</form>
<button name="btnOK" >OK</button>
<button name="btnCancel" >abbrechen</button>
</dialog>
<datalist id="WART_TYP_LIST">
<option value='Wiedervorlage'>AN - Wiedervorlage</option>
<option value='Wartung'>LF - Service/Wartung</option>
<option value='Abo'>RE - Abo</option>
</datalist>
<datalist id="INTERVALL_KNZ_LIST">
<option value='taegl' >taegl</option>
<option value='woechentl' >woechentl</option>
<option value='monatl' >monatl</option>
<option value='jaehrl' >jaehrl</option>
</datalist>
<dialog id="dialog_apos_details">
<form> <input name="json_input_ele_no" readonly type="hidden" />
<label>Status</label> <input name="Status" list="APOS_STATUS_LIST" />
<label>W_Art</label> <input name="W_Art" list="APOS_WART_TYP_LIST" />
<label>Lager</label> <input name="Lager" list="APOS_STORAGELOCATION_LIST" />
<hr />
<label>EK_Ref</label> <input name="EK_Ref" />
</form>
<button name="btnOK" >OK</button>
<button name="btnCancel" >abbrechen</button>
</dialog>
<datalist id="APOS_STATUS_LIST">
<option value='begonnen' >begonnen</option>
<option value='beendet' >beendet</option>
<option value='abgenommen' >abgenommen</option>
</datalist>
<datalist id="APOS_WART_TYP_LIST">
<option value='Wartung'>LF - Service/Wartung</option>
</datalist>
<datalist id="APOS_STORAGELOCATION_LIST">
<option value='aLF'>aLF - Zug auf Lagerort Auftragslieferung</option>
</datalist>
- 4 - (TxtDoc-lager2026-07-08.html)
z.B. als Journal:
create or replace view al_storage.l_storage_lagerjournal_raw_raw_LF as
select all
5 as STORAGE_ID, Posid as STORAGE_POS_ID, Posid as BUCH_NR, 'LF' as INP_KNZ, 'Abg' AS ABG_ZUG_KNZ,
'central'::character varying(50) AS LAGERORT, apos.apos_details->>'Lager'::character varying(50) AS GEGENBUCH_LAGERORT,
a.LieferDatum as BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, (MENGE * -1) AS BEWEGUNGS_MENGE,
concat('ANr: ', a.ANr, ', ', 'Liefer-Adr: ', a.LieferAdressNr) as BUCHUNGSTEXT,
aPOS_details, erfasst_am::timestamp without time zone as created_at
FROM frepjs2000.AngebotePos apos join frepjs2000.Anfragen a on apos.ANr = a.ANr
where apos.apos_details->>'Lager' is not null
--
union all
--
select all
5 as STORAGE_ID, Posid as STORAGE_POS_ID, Posid as BUCH_NR, 'LF' as INP_KNZ, 'Zug' AS ABG_ZUG_KNZ,
apos.apos_details->>'Lager'::character varying(50) AS LAGERORT, 'central'::character varying(50) AS GEGENBUCH_LAGERORT,
a.LieferDatum as BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, MENGE AS BEWEGUNGS_MENGE,
concat('ANr: ', a.ANr, ', ', 'Liefer-Adr: ', a.LieferAdressNr) as BUCHUNGSTEXT,
aPOS_details, erfasst_am::timestamp without time zone as created_at
FROM frepjs2000.AngebotePos apos join frepjs2000.Anfragen a on apos.ANr = a.ANr
where apos.apos_details->>'Lager' is not null
--
order by 1, 2
;
- 5 - (TxtDoc-lager2026-07-08.html)
Das geht mit Indizes, löschen alter Daten, ...
oder Materialized Views.
z.B. in der Variante Materialized View - Table wird refreshed, wenn die Grunddaten sich ändern:
( Verwendung: db.refresh_materialized_viewsAsync(); dbTab = new SqlTab( db, ..., "al_storage.l_storage_lagerjournal_qry", ...) )
CREATE SEQUENCE al_storage.L_Storage_audit_dmlno_seq;
grant usage on al_storage.L_Storage_audit_dmlno_seq TO al_storageextuser_role, ahp_w3_extuser_role;
CREATE TABLE al_storage.L_Storage_audit
(
dml_id numeric( 14, 0) NOT NULL default 0,
--
dmlno numeric( 14, 0) NOT NULL,
dmloperation varchar(10) NOT NULL,
dmltime timestamp without time zone NOT NULL,
dmluser varchar(50) ,
dmlclient_addr varchar(50) ,
--
dml_details json,
--
CONSTRAINT L_Storage_audit_pk PRIMARY KEY ( dmlno ),
CONSTRAINT L_Storage_audit_chk_dml_id CHECK ( dml_id = 0 )
);
grant select, update on al_storage.L_Storage_audit to al_storageonlineuser_role, al_storagerole, al_storageextuser_role;
INSERT INTO al_storage.l_storage_audit( dmlno, dmloperation, dmltime ) VALUES ( 0, 'init', now() );
CREATE or replace FUNCTION al_storage.L_Storage_audit_tf() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
DECLARE
L_Storage_audit_dmlno int;
L_Storage_audit_dml_details json;
BEGIN
IF (TG_OP = 'DELETE') THEN
SELECT to_json( OLD.*) into L_Storage_audit_dml_details;
ELSE
SELECT to_json( NEW.*) into L_Storage_audit_dml_details;
END IF;
SELECT nextval('al_storage.L_Storage_audit_dmlno_seq') into L_Storage_audit_dmlno;
update al_storage.L_Storage_audit
SET DMLNO = L_Storage_audit_dmlno, dmloperation = TG_OP, dmltime = now(),
dmluser = user, dmlclient_addr = inet_client_addr()::varchar(50), dml_details = L_Storage_audit_dml_details;
IF (TG_OP = 'DELETE') THEN
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$BODY$;
CREATE TRIGGER L_Storage_audit_aiud AFTER INSERT OR DELETE OR UPDATE ON al_storage.L_Storage FOR EACH ROW EXECUTE FUNCTION al_storage.L_Storage_audit_tf();
CREATE TRIGGER L_StoragePos_audit_aiud AFTER INSERT OR DELETE OR UPDATE ON al_storage.L_StoragePos FOR EACH ROW EXECUTE FUNCTION al_storage.L_Storage_audit_tf();
-- --- --- ---
CREATE or replace FUNCTION al_storage.refresh_materialized_views() RETURNS integer SECURITY DEFINER AS $$
DECLARE
audit_tab_dmlno int;
lrr_mv_audit_dmlno int;
BEGIN
SELECT max(dmlno) from al_storage.L_Storage_audit into audit_tab_dmlno;
SELECT min(audit_dmlno) from al_storage.l_storage_lagerjournal_raw_raw into lrr_mv_audit_dmlno;
if audit_tab_dmlno is null or lrr_mv_audit_dmlno is null or audit_tab_dmlno <> lrr_mv_audit_dmlno then
refresh materialized view CONCURRENTLY al_storage.l_storage_lagerjournal_raw_raw;
return 1;
end if;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION al_storage.refresh_materialized_views() OWNER TO al_storagerole;
FUNCTION al_storage.l_storage_artikelkonto_qry_refresh() TO al_storagerole WITH GRANT OPTION;
-- --- --- ---
create materialized view al_storage.l_storage_lagerjournal_raw_raw as
select all
s_audit.dmlno as audit_dmlno,
STORAGE_ID, STORAGE_POS_ID, STORAGE_POS_ID as BUCH_NR, 'L' as INP_KNZ, 'Abg' AS ABG_ZUG_KNZ,
ABG_LAGERORT AS LAGERORT, ZUG_LAGERORT AS GEGENBUCH_LAGERORT, BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, (BEWEGUNGS_MENGE * -1) AS BEWEGUNGS_MENGE, BUCHUNGSTEXT,
STORAGE_POS_details, created_at
FROM
al_storage.L_Storage_audit s_audit,
al_storage.L_StoragePos stor_pos
--
union all
--
select all
s_audit.dmlno as audit_dmlno,
STORAGE_ID, STORAGE_POS_ID, STORAGE_POS_ID as BUCH_NR, 'L' as INP_KNZ, 'Zug' AS ABG_ZUG_KNZ,
ZUG_LAGERORT AS LAGERORT, ABG_LAGERORT AS GEGENBUCH_LAGERORT, BEWEGUNGS_DATUM, ARTIKELNR, ART_BEZEICHNUNG, BEWEGUNGS_MENGE, BUCHUNGSTEXT,
STORAGE_POS_details, created_at
FROM
al_storage.L_Storage_audit s_audit,
al_storage.L_StoragePos stor_pos
--
order by 1, 2
;
CREATE UNIQUE INDEX l_storage_lagerjournal_raw_raw_uidx on al_storage.l_storage_lagerjournal_raw_raw ( STORAGE_ID, STORAGE_POS_ID, ABG_ZUG_KNZ );
ALTER materialized view al_storage.l_storage_lagerjournal_raw_raw OWNER TO al_storagerole;
- 6 - (TxtDoc-lager2026-07-08.html)
{
"SELECT_FDNAMES": "vorgang_knz, adressnr, beldatum, artikelnr, lagerort, bewegungs_menge",
"FROM_SCHEMA": "al_storage",
"FROM_QRY": "l_storage_bestandsliste_qry"
}
--> https://jobst-software.net/frepjs2000/AReport?nur_AREPORT=Bestandsliste&report_search_text=%25&format=csv
- 7 - (TxtDoc-lager2026-07-08.html)
