Ta postopek pomaga odkriti in popraviti neskladne odprte količine WHTD_QUANTITY_AVAILABLE_FIFO, povezane nabavne cene ter FIFO-povezave za izbrani artikel in skladišče.
Kritično opozorilo: Procedura
PRODUCTION.RESET_FIFO_DATAbriše in ponovno ustvari FIFO-podatke ter med izvajanjem zaklene povezane zapise. Izvajajte jo samo pooblaščeno, izven delovnega časa, v pravilnem okolju in po preverjeni varnostni kopiji.
| Vrednost | Pomen |
|---|---|
:ITEM_ID |
ID artikla za diagnostične poizvedbe |
:WAHO_ID |
ID skladišča za diagnostične poizvedbe |
NSYS_ZONE |
Področje stranke |
NWAHO_CODE |
Šifra skladišča |
NITEM_CODE |
Šifra artikla |
Pred izvedbo nastavite :ITEM_ID in :WAHO_ID.
WITH PORABA AS (
SELECT
WHTF_ID_SOURCE,
SUM(CAST(LIWW_QUANTITY AS DECIMAL(31,7))) AS USED_QTY
FROM PRODUCTION.LINKWHTFWHTF
WHERE LIWW_TYPE = 'FIFO_2'
AND LIWW_QUANTITY IS NOT NULL
GROUP BY WHTF_ID_SOURCE
),
PRIHODI AS (
SELECT
R.WHTR_DATEDOCUMENT AS DOC_DATE,
F.WHTF_ID,
D.WHTD_QUANTITY AS ORIG_QTY,
D.WHTD_QUANTITY_AVAILABLE_FIFO AS OPEN_QTY,
COALESCE(P.USED_QTY, 0) AS USED_QTY,
D.WHTD_QUANTITY - COALESCE(P.USED_QTY, 0) AS SHOULD_BE_OPEN,
I.ITEM_CODE,
LI.ITEM_NAME,
W.WAHO_CODE,
I.ITEM_ID,
W.WAHO_ID
FROM PRODUCTION.WAREHOUSETRANSDETAIL D
JOIN PRODUCTION.WAREHOUSETRANS R
ON R.WHTR_ID = D.WHTR_ID
JOIN PRODUCTION.DOCUMENTTYPE DT
ON DT.DOTY_ID = R.DOTY_ID
AND DT.DOTY_FACTOR > 0
JOIN PRODUCTION.WAREHOUSETRANSFIFO F
ON F.WHTD_ID = D.WHTD_ID
LEFT JOIN PORABA P
ON P.WHTF_ID_SOURCE = F.WHTF_ID
JOIN PRODUCTION.ITEM I
ON I.ITEM_ID = D.ITEM_ID
LEFT JOIN PRODUCTION.L_ITEM LI
ON LI.ITEM_ID = I.ITEM_ID
AND LI.LANG_ID = 1
JOIN PRODUCTION.WAREHOUSE W
ON W.WAHO_ID = R.WAHO_ID
)
SELECT
DOC_DATE,
WAHO_CODE,
ITEM_CODE,
ITEM_NAME,
WHTF_ID,
ORIG_QTY,
OPEN_QTY,
SHOULD_BE_OPEN,
USED_QTY,
CASE
WHEN OPEN_QTY > ORIG_QTY THEN 'OPEN_GT_ORIG'
WHEN OPEN_QTY < 0 THEN 'OPEN_LT_ZERO'
END AS ERROR_FLAG
FROM PRIHODI P
WHERE (P.OPEN_QTY > P.ORIG_QTY OR P.OPEN_QTY < 0)
AND P.ITEM_ID = CAST(:ITEM_ID AS CHAR(26))
AND P.WAHO_ID = CAST(:WAHO_ID AS CHAR(26))
ORDER BY DOC_DATE
WITH UR;
| Oznaka | Pomen |
|---|---|
OPEN_GT_ORIG |
Odprta količina je večja od začetne |
OPEN_LT_ZERO |
Odprta količina je negativna |
SELECT
R.WHTR_DATEDOCUMENT,
F.WHTF_ID,
F.WHTF_TYPE,
D.WHTD_ID,
D.WHTD_QUANTITY AS ORIG_QTY,
D.WHTD_QUANTITY_AVAILABLE_FIFO AS OPEN_QTY,
C.WHTC_INPRICE,
C.WHTC_INVALUE
FROM PRODUCTION.WAREHOUSETRANSDETAIL D
JOIN PRODUCTION.WAREHOUSETRANS R
ON R.WHTR_ID = D.WHTR_ID
JOIN PRODUCTION.WAREHOUSETRANSFIFO F
ON F.WHTD_ID = D.WHTD_ID
JOIN PRODUCTION.WAREHOUSETRANSCALCULATION C
ON C.WHTF_ID = F.WHTF_ID
WHERE D.ITEM_ID = CAST(:ITEM_ID AS CHAR(26))
AND R.WAHO_ID = CAST(:WAHO_ID AS CHAR(26))
AND R.DOTY_ID IN (
SELECT DOTY_ID
FROM PRODUCTION.DOCUMENTTYPE
WHERE DOTY_FACTOR > 0
)
AND D.WHTD_QUANTITY_AVAILABLE_FIFO > 0
ORDER BY R.WHTR_DATEDOCUMENT
WITH UR;
WITH PORABA AS (
SELECT
F.WHTD_ID,
SUM(L.LIWW_QUANTITY) AS USED
FROM PRODUCTION.LINKWHTFWHTF L
JOIN PRODUCTION.WAREHOUSETRANSFIFO F
ON F.WHTF_ID = L.WHTF_ID_SOURCE
WHERE L.LIWW_TYPE = 'FIFO_2'
AND EXISTS (
SELECT 1
FROM PRODUCTION.WAREHOUSETRANS R
JOIN PRODUCTION.WAREHOUSETRANSDETAIL D2
ON D2.WHTR_ID = R.WHTR_ID
WHERE D2.WHTD_ID = F.WHTD_ID
AND D2.ITEM_ID = CAST(:ITEM_ID AS CHAR(26))
AND R.WAHO_ID = CAST(:WAHO_ID AS CHAR(26))
)
GROUP BY F.WHTD_ID
)
SELECT
D.WHTD_ID,
D.WHTD_QUANTITY,
COALESCE(P.USED, 0) AS USED,
D.WHTD_QUANTITY_AVAILABLE_FIFO AS AVAIL,
WHTR.WHTR_DATEDOCUMENT,
DOTY.DOTY_CODE,
LDOTY.DOTY_NAME
FROM PRODUCTION.WAREHOUSETRANSDETAIL D
LEFT JOIN PORABA P
ON P.WHTD_ID = D.WHTD_ID
LEFT JOIN PRODUCTION.WAREHOUSETRANS WHTR
ON WHTR.WHTR_ID = D.WHTR_ID
LEFT JOIN PRODUCTION.DOCUMENTTYPE DOTY
ON DOTY.DOTY_ID = WHTR.DOTY_ID
LEFT JOIN PRODUCTION.L_DOCUMENTTYPE LDOTY
ON LDOTY.DOTY_ID = DOTY.DOTY_ID
LEFT JOIN PRODUCTION.DOCUMENTSTATUS D3
ON D3.DOST_ID = WHTR.DOST_ID
WHERE D.ITEM_ID = CAST(:ITEM_ID AS CHAR(26))
AND EXISTS (
SELECT 1
FROM PRODUCTION.WAREHOUSETRANS R
WHERE R.WHTR_ID = D.WHTR_ID
AND R.WAHO_ID = :WAHO_ID
)
AND D3.DOST_CODE >= '05'
ORDER BY D.SYS_TSINSERT;
Procedura za izbrani artikel in skladišče:
LINKWHTFWHTF,WAREHOUSETRANSCALCULATION,WAREHOUSETRANSFIFO,WHTD_QUANTITY_AVAILABLE_FIFO v WAREHOUSETRANSDETAIL,CALL PRODUCTION.RESET_FIFO_DATA(
NSYS_ZONE => '<SYS_ZONE>',
NWAHO_CODE => '<SIFRA_SKLADISCA>',
NITEM_CODE => '<SIFRA_ARTIKLA>'
);
Pred zagonom še enkrat preverite vse tri vrednosti. Primerov iz dokumentacije ne izvajajte neposredno v produkciji.
Po izvedbi ponovno zaženite poizvedbe iz korakov 1–3 in preverite:
OPEN_GT_ORIG,OPEN_LT_ZERO,SYS_ZONE, skladišče in artikel,Ponovite vse kontrolne poizvedbe in primerjajte odprte količine, nabavne cene ter FIFO-povezave s stanjem pred posegom. Nato preverite zalogo in poslovni izpis za artikel.
Ne ponavljajte procedure brez analize dnevnika in potrditve skrbnika baze. Obvestite odgovorno osebo, shranite identifikator izvajanja in pripravite povrnitev iz varnostne kopije, če so podatki poškodovani.
Pomembno: V wiki ne shranjujte izpisov, ki vsebujejo podatke strank ali celotne produkcijske zapise.