vwiki

User Tools

Site Tools

Trace:
rm24430

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
rm24430 [2018/02/08 11:18] – [Testdaten für SQL Abfragen] kalaschnikowrm24430 [2018/04/23 15:57] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== rm#24430 New Archiving rule - PO SL is set to archivd despite status not correct ======
  
 +[[https://cust-projects.seeburger.de/redmine/issues/24430|rm#24430]]
 +
 +Ursprüngliche Tickets :\\ 
 +[[https://cust-projects.seeburger.de/redmine/issues/23162|rm#23162 CR22285: Archiving: New rule for PO (considering invoice status)]]\\ 
 +[[https://cust-projects.seeburger.de/redmine/issues/22285|CR22285 Archiving: New rule for PO (considering invoice status)]]\\ 
 +
 +Ticket: 
 +
 +PO SL should not be marked as archivable if one or more invoices of the belonging PO item do NOT have the cinvoicecustomerstatus pay or rej.
 +
 +Fragen: 
 +  * Soll eine PO SL als "Archivable" markiert werden, wenn dafür noch keine Invoices existieren?
 +
 +
 +
 +Lösungsideen:\\ 
 +<code sql>
 +-- direkt: 2 x zählen und Anzahl der Sätze vergleichen
 +select invitem2.cLineItemId, countAll, countValid from ( (select count(*) countValid, cLineItemId from TAIRPOINVITEM where CINVOICECUSTOMERSTATUS IN ('pay', 'rej') group by cLineItemid) invitem2
 +INNER JOIN (select count(*) countAll, cLineItemid  from TAIRPOINVITEM group by cLineItemid order by CLINEITEMID desc) invitem3  ON invitem2.CLINEITEMID = invitem3.CLINEITEMID) where countAll=countValid
 +
 +-- angeblich einfachere Lösung, die aber bei <Null>-en nicht funktioniert
 +SELECT posched.CLINEID, posched.CREFHEADERID, invitem.CINVOICECUSTOMERSTATUS  FROM TAIRPOSCHEDULE posched 
 +  INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +  INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +  INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID AND (invitem.CINVOICECUSTOMERSTATUS IN ('pay', 'rej'))
 +  INNER JOIN TAIRPOINVITEM invitem2 ON invitem2.clineItemID             = lineitem.cLineItemID AND NOT (invitem2.CINVOICECUSTOMERSTATUS NOT IN ('pay', 'rej'))
 +</code>
 +
 +Was funktioniert:
 +
 +<code sql>
 +SELECT posched.CLINEID, posched.CREFHEADERID FROM TAIRPOSCHEDULE posched 
 +INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM 
 + AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID 
 +WHERE posched.CARCHIVABLE         IS NULL 
 + AND invitem.clineItemID  IN ( 
 + SELECT invitem2.cLineItemId FROM ( (
 + SELECT COUNT(*) countValid, cLineItemId FROM TAIRPOINVITEM WHERE CINVOICECUSTOMERSTATUS IN ('pay', 'rej') GROUP BY cLineItemid) invitem2  
 + INNER JOIN (SELECT COUNT(*) countAll, cLineItemid FROM TAIRPOINVITEM GROUP BY cLineItemid ORDER BY CLINEITEMID DESC) invitem3  ON invitem2.CLINEITEMID = invitem3.CLINEITEMID) 
 + WHERE countAll=countValid ) 
 + AND lineitem.CFULLYINVOICED        = 1 AND pr.CCUSTOMERCGID               = 100011
 +
 +-- als INNER JOIN:
 +
 +SELECT posched.CLINEID, posched.CREFHEADERID FROM TAIRPOSCHEDULE posched 
 +INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM 
 + AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID 
 +INNER JOIN (SELECT invitem2.cLineItemId FROM ( (
 + SELECT COUNT(*) countValid, cLineItemId FROM TAIRPOINVITEM WHERE CINVOICECUSTOMERSTATUS IN ('pay', 'rej') GROUP BY cLineItemid) invitem2  
 + INNER JOIN (SELECT COUNT(*) countAll, cLineItemid FROM TAIRPOINVITEM GROUP BY cLineItemid ORDER BY CLINEITEMID DESC) invitem3  ON invitem2.CLINEITEMID = invitem3.CLINEITEMID) 
 + WHERE countAll=countValid)  invitem4 on invitem.clineItemID = invitem4.clineItemID 
 +WHERE posched.CARCHIVABLE         IS NULL 
 + AND lineitem.CFULLYINVOICED        = 1 AND pr.CCUSTOMERCGID               = 100011
 +
 +-- Lösung(?)
 +
 +SELECT posched.CLINEID, posched.CREFHEADERID FROM TAIRPOSCHEDULE posched 
 +INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID 
 +INNER JOIN (SELECT COUNT(*) countValid, cLineItemId FROM TAIRPOINVITEM WHERE CINVOICECUSTOMERSTATUS IN ('pay', 'rej') GROUP BY cLineItemid) invitem2 on lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID AND invitem.cLineItemId = invitem2.cLineItemId 
 +INNER JOIN (SELECT COUNT(*) countAll, cLineItemId FROM TAIRPOINVITEM GROUP BY cLineItemid) invitem3 on lineitem.CPOSNUM = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID and invitem.cLineItemId = invitem3.cLineItemId 
 +WHERE posched.CARCHIVABLE         IS NULL 
 + AND lineitem.CFULLYINVOICED        = 1 
 + AND pr.CCUSTOMERCGID               = 100011
 + AND countValid = countAll
 +
 +</code>
 +
 +
 +Misc:\\ 
 +<code sql>
 +SELECT posched.CLINEID, posched.CREFHEADERID FROM TAIRPOSCHEDULE posched 
 +  INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +  INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +  INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID 
 +  
 +  WHERE 
 +    lineitem.CLINEITEMID = 58250
 +    -- and posched.CARCHIVABLE         IS NULL 
 +    AND 
 +    (invitem.CINVOICECUSTOMERSTATUS = 'pay' OR invitem.CINVOICECUSTOMERSTATUS = 'rej')
 +</code>
 +
 +Nützliche SQL-Befehle um Flags zurückzusetzen etc. :
 +
 +<code sql>
 +SELECT * FROM TAIRPOINVITEM where CLINEITEMID=5755079
 +
 +update TAIRPOINVITEM set CINVOICECUSTOMERSTATUS='pay' where cLineItemid=5755079;
 +
 +-- oder um nur einen Satz auf 'pvl' setzen:
 +update TAIRPOINVITEM set CINVOICECUSTOMERSTATUS='pvl' where cLineItemid=5755079 and CINVOICEITEMID=173;
 +
 +
 +SELECT * FROM TAIRPOLINEITEM where CLINEITEMID=5755079
 +
 +SELECT CARCHIVABLE FROM TAIRPOSCHEDULE where CREFHEADERID=545126
 +
 +update TAIRPOSCHEDULE set cArchivable= null where CREFHEADERID=545126
 +</code>
 +
 +===== Testszenario =====
 +Dateien sind im Verzeichnis ''D:\development\testdata\purchaseorder\rm#24430\''
 +
 +Bei der Order einfach die PONumber ändern und die Nachricht importieren.
 +
 +Dann, um eine Invoice zu erstellen, zuerst folgendes SQL ausführen:
 +<code sql>
 +SELECT posched.CREFHEADERID, CLINEITEMID, posched.cLinePONUM FROM TAIRPOSCHEDULE posched
 +INNER JOIN TAIRPOLINEITEM poline ON posched.CREFHEADERID = poline.CREFHEADERID
 +where posched.cLinePONUM like 'rm244%7'
 +
 +</code>
 +wo 'rm24430_004' die aktuelle PONumber ist.\\ 
 +Dann bei der BillableObject Nachricht folgende Felder anpassen:
 +  * ObjectId -> muss gleich TAIRPOSCHEDULE.cRefHeaderId sein
 +  * ObjectPositionId -> muss gleich TAIRPOLINEITEM.cLineItemId sein
 +  * MessageReference -> muss gleich der ClinePONUM sein (in unserem Fall ''rm24430_004''). Dies ist aber evtl. nicht notwendig.
 +
 +Dann die BillableObject Nachricht importieren.
 +
 +Der Job wird nur solche Nachrichten bearbeiten, deren Datum kleiner als ein bestimmter Wert ist. D.h. wir müssen noch das Datum in der Tabelle prärarieren:
 +<code sql>
 +update TAIRCHANGELOG set cActionTime=1217998391734 where CSUBOBJECTREFERENCE=(SELECT cLineId from TAIRPOSCHEDULE where cLinePONUM='rm24430_004') 
 +</code>
 +
 +
 +===== Testdaten für SQL Abfragen =====
 +
 +INSERT Invoice:
 +
 +<code sql>
 +INSERT INTO TAIRPOINVITEM 
 +(CINVOICEITEMID,CLINEITEMID,CINVOICEID,CINVOICENUMBER,CINVOICESENTDATE,CINVOICETYPE,CINVOICESTATUS,CINVOICECUSTOMERSTATUS,CINVOICEQTY,CLINEAMOUNT,CCURRENCY,CSUPPLIERCONTACTNAME,CINVOICEISSUER,CPAYEE,CREFERENCEINVOICENO,CCUSTOMER                 ,CINVOICEE,CSUPPLIER                             ,CSUPPLIERCONTACTEMAIL,CSUPPLIERCONTACTDEP,CSHIPFROM,CSHIPTO                  ,CTAXREP) VALUES
 +(141           ,2          ,4711      ,'INV-0815'    ,1487026800000   ,'380'       ,'Send'        ,'pay'                  ,1          ,11.95      ,'EUR'    ,null                ,null          ,null  ,'ROBOT-170127-1'   ,'PD_Airbus Operations SAS',null     ,'PD_RUAG Aerospace Structures GmbH GE','CON_mail@mail.net'  ,null               ,null     ,'AIRBUS Deutschland GmbH',null   )
 +;
 +</code>
 +
 +===== Lösung =====
 +
 +Die gewählte Lösung:
 +
 +<code sql>
 +SELECT posched.CLINEID, posched.CREFHEADERID FROM TAIRPOSCHEDULE posched 
 +INNER JOIN TAIRPARTNERRELATION pr ON pr.CRELATIONID = posched.CPRELATIONID 
 +INNER JOIN TAIRPOLINEITEM lineitem ON lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID 
 +INNER JOIN TAIRPOINVITEM invitem ON invitem.clineItemID             = lineitem.cLineItemID 
 +INNER JOIN (SELECT COUNT(*) countValid, cLineItemId FROM TAIRPOINVITEM WHERE CINVOICECUSTOMERSTATUS IN ('pay', 'rej') GROUP BY cLineItemid) invitem2 ON lineitem.CPOSNUM       = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID AND invitem.cLineItemId = invitem2.cLineItemId 
 +INNER JOIN (SELECT COUNT(*) countAll, cLineItemId FROM TAIRPOINVITEM GROUP BY cLineItemid) invitem3 ON lineitem.CPOSNUM = posched.CPOSNUM AND lineitem.CREFHEADERID = posched.CREFHEADERID AND invitem.cLineItemId = invitem3.cLineItemId 
 +WHERE posched.CARCHIVABLE         IS NULL 
 + AND lineitem.CFULLYINVOICED        = 1 
 + AND pr.CCUSTOMERCGID               = 100011
 + AND countValid = countAll
 +</code>