====== 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>