platformaerp.com

MM, WM

Podsumowanie:

1. MM- jest powiązane z MM+ przez outcomeShiftLineID
2. MM- i MM+ powinny mieć initialIncomeSourceLineID to samo - to numer sluzacy do trackingu dostawy - mozna to naprawic => punkt MM02A
3. initialIncomeSourceLineID zawsze pochodzi z PZ, PW, PI+ inne dokumenty ktore pochodza z innych dokuemntow nie powinny miec tego

MM02A Brak powiazania MM- z MM+

Scenariusz zly gdzie mm+ generuje initialincomeSourceLineID. Nie powinno tak być, ale może sie zdarzyc w starych dokumentach. Mozna to naprawic jesli wartosc pozycji sie zgadza ustawiamy initialincomeSourceLineIDna mm+ i dokumentach powiazanych

  select mmplus.documentFullNR,
  mminus.initialIncomeSourceLineID , 
  mmplus.initialIncomeSourceLineID wrong,

  CONCAT( 'update  document.documentline  set initialincomesourcelineid=',
  QUOTENAME(mminus.initialIncomeSourceLineID,'''') , '  where initialincomesourcelineid =',QUOTENAME(mmplus.initialIncomeSourceLineID,'''')) FIX,

  CONCAT( 'select * from  document.documentline where initialincomesourcelineid =',QUOTENAME(mmplus.initialIncomeSourceLineID,'''')) TEXT
  from document.documentline mmplus
  inner join document.documentline mminus  on mmplus.Outcomeshiftlineid  = mminus.documentlineid
  and mmplus.initialIncomeSourceLineID  != mminus.initialIncomeSourceLineID
  and mmplus.sysItemValue = mminus.sysitemvalue

MM02B MM- MM+ sa powiazane przez outcomeShiftLineID

• do 2016-12 MM+ nie mialo powiązania ze wzgledu na to ze po jednej stronie moglo byc kilka partii a po drugiej byla usredniona ilosc.

SELECT top 1000  mm_minus.documentFullNR ,mm_plus.documentFullNR ,mm_minus.addDate,
   mm_minus.itemQuantity,mm_plus.itemQuantity,
   mm_minus.sysItemValue , mm_plus.sysItemValue,mm_plus.itemid
   FROM 
     document.documentLine mm_minus LEFT JOIN
     document.documentLine mm_plus 
      ON mm_minus.[documentLineID] = mm_plus.[outcomeShiftLineId] 
   where mm_minus.documentFullNR like 'mm-%'
   and mm_minus.status=1 and mm_plus.status=1
   and mm_minus.sysitemvalue != mm_plus.sysitemvalue
   --and mm_plus.itemQuantityLeft >0 ile z tych ma jeszcze partie
order by 3 desc;

MM02C WARNING Czy mozna naprawic outcomeShiftLineId

TAK

Sprawdzenie :

     with mm_minus AS (
    select d.documentfullnr,dl.itemid, dl.itemQuantity,dl.documentOrdinalNumber,d.documentDate,dl.outcomeShiftLineId,dl.sysitemvalue,d.warehouseID,d.warehouseIDB,dl.documentLineID from  document.document d inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documenttype dt on d.documenttype =dt.documenttype 
    where d.status=1 and dl.status =1 and d.documentType='mm-' ),
    mm_plus AS (
    select  d.documentfullnr, dl.itemid, dl.itemQuantity,dl.documentOrdinalNumber,d.documentDate,dl.outcomeShiftLineId,dl.sysitemvalue,d.warehouseID,dl.documentLineID from  document.document d inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documenttype dt on d.documenttype =dt.documenttype 
    where d.status=1 and dl.status =1 and d.documentType='mm+' )
    select 
      mm_minus.documentFullNR, mm_plus.documentFullNR ,mm_plus.[outcomeShiftLineId],mm_minus.[outcomeShiftLineId],mm_minus.documentlineid documentlineid_minus,mm_plus.documentlineid documentlineid_plus
    from mm_minus inner join mm_plus on 
      mm_minus.documentDate = mm_plus.documentDate and
      mm_minus.itemID = mm_plus.itemID and
      mm_minus.itemQuantity = mm_plus.itemQuantity  and
      mm_minus.sysitemvalue = mm_plus.sysitemvalue and
      mm_minus.warehouseIDB = mm_plus.warehouseID --and
      --mm_minus.documentOrdinalNumber = mm_plus.documentOrdinalNumber
    where 
      mm_plus.[outcomeShiftLineId] is null

Naprawienie:

    Begin transaction;

    with mm_minus AS (
    select d.documentfullnr,dl.itemid, dl.itemQuantity,dl.documentOrdinalNumber,d.documentDate,dl.outcomeShiftLineId,dl.sysitemvalue,d.warehouseID,d.warehouseIDB,dl.documentLineID from  document.document d inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documenttype dt on d.documenttype =dt.documenttype 
    where d.status=1 and dl.status =1 and d.documentType='mm-' ),
    mm_plus AS (
    select  d.documentfullnr, dl.itemid, dl.itemQuantity,dl.documentOrdinalNumber,d.documentDate,dl.outcomeShiftLineId,dl.sysitemvalue,d.warehouseID,dl.documentLineID from  document.document d inner join document.documentline dl on d.documentid = dl.documentID
    inner join document.documenttype dt on d.documenttype =dt.documenttype 
    where d.status=1 and dl.status =1 and d.documentType='mm+' )
    --select 
    --  mm_minus.documentFullNR, mm_plus.documentFullNR ,mm_plus.[outcomeShiftLineId],mm_minus.[outcomeShiftLineId],mm_minus.documentlineid documentlineid_minus,mm_plus.documentlineid documentlineid_plus
    --from mm_minus inner join mm_plus on 
    --  mm_minus.documentDate = mm_plus.documentDate and
    --  mm_minus.itemID = mm_plus.itemID and
    --  mm_minus.itemQuantity = mm_plus.itemQuantity  and
    --  mm_minus.sysitemvalue = mm_plus.sysitemvalue and
    --  mm_minus.warehouseIDB = mm_plus.warehouseID --and
    --  --mm_minus.documentOrdinalNumber = mm_plus.documentOrdinalNumber
    --where 
    --  mm_plus.[outcomeShiftLineId] is null

UPDATE dl
SET dl.outcomeShiftLineId = x.documentlineid_minus
FROM document.documentline dl
JOIN (
    SELECT 
        mm_plus.documentlineid AS documentlineid_plus,
        mm_minus.documentlineid AS documentlineid_minus
    FROM mm_minus
    INNER JOIN mm_plus ON 
        mm_minus.documentDate = mm_plus.documentDate AND
        mm_minus.itemID = mm_plus.itemID AND
        mm_minus.itemQuantity = mm_plus.itemQuantity AND
        mm_minus.sysitemvalue = mm_plus.sysitemvalue AND
        mm_minus.warehouseIDB = mm_plus.warehouseID
    WHERE mm_plus.outcomeShiftLineId IS NULL
) x ON dl.documentlineid = x.documentlineid_plus;


rollback transaction