rm#24430 New Archiving rule - PO SL is set to archivd despite status not correct

rm#24430 New Archiving rule - PO SL is set to archivd despite status not correct

Ursprüngliche Tickets :
rm#23162 CR22285: Archiving: New rule for PO (considering invoice status)
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:

-- 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'))

Was funktioniert:

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

Misc:

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')

Nützliche SQL-Befehle um Flags zurückzusetzen etc. :

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

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:

SELECT posched.CREFHEADERID, CLINEITEMID, posched.cLinePONUM FROM TAIRPOSCHEDULE posched
INNER JOIN TAIRPOLINEITEM poline ON posched.CREFHEADERID = poline.CREFHEADERID
WHERE posched.cLinePONUM LIKE 'rm244%7'

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:

UPDATE TAIRCHANGELOG SET cActionTime=1217998391734 WHERE CSUBOBJECTREFERENCE=(SELECT cLineId FROM TAIRPOSCHEDULE WHERE cLinePONUM='rm24430_004')

Testdaten für SQL Abfragen

INSERT Invoice:

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   )
;

Lösung

Die gewählte 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

Table of Contents

rm#24430 New Archiving rule - PO SL is set to archivd despite status not correct

Testszenario

Testdaten für SQL Abfragen

Lösung