Differences

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

--- rm34449 [2020/02/20 11:42] – [Reproduzieren] kalaschnikow
+++ rm34449 [2020/02/20 13:59] (current) – [Reproduzieren] kalaschnikow
@@ Line 1: / Line 1: @@
+====== 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.