====== rm34449 Anwender vermisst die Einträge in der "Customer Group" in EDI Integration ======

[[https://cust-projects.seeburger.de/redmine/issues/34449|rm34449 Anwender vermisst die Einträge in der "Customer Group" in EDI Integration]]

===== Daten =====

TUSER_SCD.cDisplayname: salvid4\\ 
TUSER_SCD.cUniqueID: SMA0000N21IJ\\ 



===== Reproduzieren =====
Idee von Viktor:
  * in ''sas.messagescheduler.scheduler.onMetaChange'' den Customer Group Eintrag der aktuellen Zeile (der aktuell gewählte) aus der Liste der Customer Groups löschen. Damit wird der aktuelle Eintrag nicht gefunden und der Code fliegt wahrscheinlich auf die Schnauze.
    * das Problem ist wahrscheinlich dadurch verursacht, dass der aktuelle Eintrag in der Liste nicht auftaucht

Nützliche SQL Befehle (lokal!):
<code sql>
-- locally I've used the schedule with id=185
SELECT * FROM TAIREDISCHEDULER 
where CID=185
order by cID desc

-- originally CCUSTGROUPID was null
UPDATE TAIREDISCHEDULER set CCUSTGROUPID=2034967 where cID=185;

-- get the customer group with id 2034967
select * from SEESCMDB0.TORGANIZATION_SCD where cid=2034967;

</code>

Remote we have the problem for example with the schedule 1018:

<code sql>
select * from SEESCMDB0.TAIREDISCHEDULER where cID=1018;

-- 2034967 delivers "STELIA AEROSPACE"
select * from SEESCMDB0.TORGANIZATION_SCD where cid=2034967;

SELECT SEESCMDB0.TAIREDISCHEDULER.CID,CJOBIDREF,CACTIVE,CMESSAGETYPE,CCOMMENT, 
    TAIREDISCHEDULER.CCUSTGROUPID, TORGANIZATION_SCD.CNAME CCUSTGROUPNAME, 
    TAIREDISCHEDULER.CSUPPLIERCPID,TCONTROLPOINT_SCD.CNAME CSUPPLIERCPNAME,TCONTROLPOINT_SCD.CPSIPARTNER, 
  CSTARTDATE,CSTARTTIME,CFREQUENCY,CFREQUENCYUNIT,CMONDAY,CTUESDAY,CWEDNESDAY,CTHURSDAY,CFRIDAY,CSATURDAY,CSUNDAY 
FROM SEESCMDB0.TAIREDISCHEDULER 
JOIN SEESCMDB0.TCONTROLPOINT_SCD ON (TAIREDISCHEDULER.CSUPPLIERCPID = TCONTROLPOINT_SCD.CCPID) 
LEFT JOIN SEESCMDB0.TORGANIZATION_SCD ON (TORGANIZATION_SCD.CID = TAIREDISCHEDULER.CCUSTGROUPID) 
WHERE 1=1 
and SEESCMDB0.TAIREDISCHEDULER.CID=1018;
</code>

<code sql>

-- get CP ids for user 'salvid4' = SMA0000N21IJ
SELECT SEESCMDB0.tOrganization_SCD.cStatus AS cOrgStatus,
      SEESCMDB0.tOrganization_SCD.cOrgCode, 
      SEESCMDB0.tCPUser_SCD.cCPID, 
      SEESCMDB0.tUser_SCD.*,
      SEESCMDB0.tCPUser_SCD.cNotification 
      FROM SEESCMDB0.tUser_SCD 
      LEFT JOIN SEESCMDB0.tCPUser_SCD ON (SEESCMDB0.tUser_SCD.cUniqueID=SEESCMDB0.tCPUser_SCD.cUserID),SEESCMDB0.tOrganization_SCD
      WHERE SEESCMDB0.tUser_SCD.cOrganizationID=SEESCMDB0.tOrganization_SCD.cID and SEESCMDB0.tUser_SCD.cUniqueID='SMA0000N21IJ';
      
-- then we get CPs 10007544 and 10017660

-- now we fetch all routings for these two cps
SELECT SEESCMDB0.tNetworkLink_SCD_New.cId, SEESCMDB0.tNetworkLink_SCD_New.cCPBYUERID, SEESCMDB0.tNetworkLink_SCD_New.cCPSELLERID, 
SEESCMDB0.tNetworkLink_SCD_New.CISUNREGSUPPLIER,SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerNumber, 
SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerPlant, cpbuyer.cOrgCode, cpbuyer.cPlantCode, cpseller.cName cpsellername, cpbuyer.cName cpbuyername, vNetworkCommunication_SCD.cLogicalFilename, vNetworkCommunication_SCD.cCommunicationType,  
orgseller.cId orgsellerid, orgseller.cName orgsellername, orgseller.cParentId orgsellerparentid,  orgbuyer.cId orgbuyerid, orgbuyer.cName orgbuyername, orgbuyer.cName3 orgbuyernameshort, orgbuyer.cParentId orgbuyerparentid,  
addressBuyer.cCity buyerCity, addressSeller.cCity sellerCity, tNetworkSupplierInfo_SCD.CDELETIONTIMESTAMP, cpseller.cName || '/' || addressSeller.cCity as cSellerNameCity,  cpbuyer.cName || ' (' || cpbuyer.cPlantCode || ')' as cBuyerNamePlantCode  
FROM SEESCMDB0.tNetworkLink_SCD_New, SEESCMDB0.tControlPoint_SCD cpseller, SEESCMDB0.tControlPoint_SCD cpbuyer, 
SEESCMDB0.tOrganization_SCD orgseller, SEESCMDB0.tOrganization_SCD orgbuyer, SEESCMDB0.vNetworkCommunication_SCD, 
SEESCMDB0.tNetworkSupplierInfo_SCD, SEESCMDB0.TCPADDRESS_SCD addressBuyer, SEESCMDB0.TCPADDRESS_SCD addressSeller 
WHERE 
orgbuyer.cID = cpbuyer.cOrganizationID 
AND orgseller.cID = cpseller.cOrganizationID  
AND cpbuyer.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPByuerID 
AND cpseller.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPSellerID 
AND SEESCMDB0.tNetworkLink_SCD_New.cID=vNetworkCommunication_SCD.cIDNetLink 
AND SEESCMDB0.tNetworkLink_SCD_New.cId=SEESCMDB0.tNetworkSupplierInfo_SCD.cNetworkLinkId 
AND addressBuyer.cCPID = tNetworkLink_SCD_New.cCPByuerID 
AND addressSeller.cCPID = tNetworkLink_SCD_New.cCPSellerID 
AND ROWNUM <= 5000  
AND (tNetworkLink_SCD_New.cCPSELLERID IN (10007544, 10017660))  
ORDER BY tNetworkLink_SCD_New.cID, cSellerNumber, vNetworkCommunication_SCD.cID;
      

-- this query delivers the proper (maybe) results
-- at least it delivers 7 entries
select orgbuyer.* from SEESCMDB0.TORGANIZATION_SCD orgbuyer, SEESCMDB0.tControlPoint_SCD cpbuyer 
where 
orgbuyer.cID = cpbuyer.cOrganizationID 
and cpbuyer.ccpID in (select distinct CCPBYUERID from SEESCMDB0.tNetworkLink_SCD_New where CCPSELLERID IN (10007544, 10017660));

-- this query will be executed in reality (as SQL + Java) and delivers only 2 entries
select distinct cParentId from (
SELECT orgbuyer.* 
FROM SEESCMDB0.TORGANIZATION_SCD orgbuyer, SEESCMDB0.tControlPoint_SCD cpbuyer 
WHERE 
orgbuyer.cID = cpbuyer.cOrganizationID 
AND cpbuyer.ccpID IN (

select distinct CCPBYUERID from (

SELECT SEESCMDB0.tNetworkLink_SCD_New.cId, SEESCMDB0.tNetworkLink_SCD_New.cCPBYUERID, SEESCMDB0.tNetworkLink_SCD_New.cCPSELLERID, 
SEESCMDB0.tNetworkLink_SCD_New.CISUNREGSUPPLIER,SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerNumber, 
SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerPlant, cpbuyer.cOrgCode, cpbuyer.cPlantCode, cpseller.cName cpsellername, cpbuyer.cName cpbuyername, vNetworkCommunication_SCD.cLogicalFilename, vNetworkCommunication_SCD.cCommunicationType,  
orgseller.cId orgsellerid, orgseller.cName orgsellername, orgseller.cParentId orgsellerparentid,  orgbuyer.cId orgbuyerid, orgbuyer.cName orgbuyername, orgbuyer.cName3 orgbuyernameshort, orgbuyer.cParentId orgbuyerparentid,  
addressBuyer.cCity buyerCity, addressSeller.cCity sellerCity, tNetworkSupplierInfo_SCD.CDELETIONTIMESTAMP, cpseller.cName || '/' || addressSeller.cCity AS cSellerNameCity,  
cpbuyer.cName || ' (' || cpbuyer.cPlantCode || ')' AS cBuyerNamePlantCode  
FROM SEESCMDB0.tNetworkLink_SCD_New, SEESCMDB0.tControlPoint_SCD cpseller, SEESCMDB0.tControlPoint_SCD cpbuyer, 
SEESCMDB0.tOrganization_SCD orgseller, SEESCMDB0.tOrganization_SCD orgbuyer, SEESCMDB0.vNetworkCommunication_SCD, 
SEESCMDB0.tNetworkSupplierInfo_SCD, SEESCMDB0.TCPADDRESS_SCD addressBuyer, SEESCMDB0.TCPADDRESS_SCD addressSeller 
WHERE 
orgbuyer.cID = cpbuyer.cOrganizationID 
AND orgseller.cID = cpseller.cOrganizationID  
AND cpbuyer.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPByuerID 
AND cpseller.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPSellerID 
AND SEESCMDB0.tNetworkLink_SCD_New.cID=vNetworkCommunication_SCD.cIDNetLink 
AND SEESCMDB0.tNetworkLink_SCD_New.cId=SEESCMDB0.tNetworkSupplierInfo_SCD.cNetworkLinkId 
AND addressBuyer.cCPID = tNetworkLink_SCD_New.cCPByuerID 
AND addressSeller.cCPID = tNetworkLink_SCD_New.cCPSellerID 
AND ROWNUM <= 5000  
AND (tNetworkLink_SCD_New.cCPSELLERID IN (10007544, 10017660))  
ORDER BY tNetworkLink_SCD_New.cID, cSellerNumber, vNetworkCommunication_SCD.cID
)
)
);


</code>


Die Ursache für das Problem scheint die Anzahl der Einträge in der Tabelle ''tNetworkLink_SCD_New'' zu sein: <code sql>
-- this query delivers 1356705  => much more than 5000
SELECT COUNT(*) FROM SEESCMDB0.tNetworkLink_SCD_New;

-- the query which was used, delivers 8421 => unfortunately also more than 5000 
select count(*) from (
SELECT SEESCMDB0.tNetworkLink_SCD_New.cId, SEESCMDB0.tNetworkLink_SCD_New.cCPBYUERID, SEESCMDB0.tNetworkLink_SCD_New.cCPSELLERID, 
SEESCMDB0.tNetworkLink_SCD_New.CISUNREGSUPPLIER,SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerNumber, 
SEESCMDB0.tNetworkSupplierInfo_SCD.cSellerPlant, cpbuyer.cOrgCode, cpbuyer.cPlantCode, cpseller.cName cpsellername, cpbuyer.cName cpbuyername, vNetworkCommunication_SCD.cLogicalFilename, vNetworkCommunication_SCD.cCommunicationType,  
orgseller.cId orgsellerid, orgseller.cName orgsellername, orgseller.cParentId orgsellerparentid,  orgbuyer.cId orgbuyerid, orgbuyer.cName orgbuyername, orgbuyer.cName3 orgbuyernameshort, orgbuyer.cParentId orgbuyerparentid,  
addressBuyer.cCity buyerCity, addressSeller.cCity sellerCity, tNetworkSupplierInfo_SCD.CDELETIONTIMESTAMP, cpseller.cName || '/' || addressSeller.cCity AS cSellerNameCity,  
cpbuyer.cName || ' (' || cpbuyer.cPlantCode || ')' AS cBuyerNamePlantCode  
FROM SEESCMDB0.tNetworkLink_SCD_New, SEESCMDB0.tControlPoint_SCD cpseller, SEESCMDB0.tControlPoint_SCD cpbuyer, 
SEESCMDB0.tOrganization_SCD orgseller, SEESCMDB0.tOrganization_SCD orgbuyer, SEESCMDB0.vNetworkCommunication_SCD, 
SEESCMDB0.tNetworkSupplierInfo_SCD, SEESCMDB0.TCPADDRESS_SCD addressBuyer, SEESCMDB0.TCPADDRESS_SCD addressSeller 
WHERE 
orgbuyer.cID = cpbuyer.cOrganizationID 
AND orgseller.cID = cpseller.cOrganizationID  
AND cpbuyer.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPByuerID 
AND cpseller.cCPID = SEESCMDB0.tNetworkLink_SCD_New.cCPSellerID 
AND SEESCMDB0.tNetworkLink_SCD_New.cID=vNetworkCommunication_SCD.cIDNetLink 
AND SEESCMDB0.tNetworkLink_SCD_New.cId=SEESCMDB0.tNetworkSupplierInfo_SCD.cNetworkLinkId 
AND addressBuyer.cCPID = tNetworkLink_SCD_New.cCPByuerID 
AND addressSeller.cCPID = tNetworkLink_SCD_New.cCPSellerID 
--AND ROWNUM <= 5000  
AND (tNetworkLink_SCD_New.cCPSELLERID IN (10007544, 10017660))  
ORDER BY tNetworkLink_SCD_New.cID, cSellerNumber, vNetworkCommunication_SCD.cID
);
</code>

Der Filter, der die Begrenzung nach ROWNUM verursacht, wird in der Methode ''RoutingDAOImpl.getRoutingsByFilter()'' ausgewertet.