View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0001428Database Comparer VCLGeneralpublic2018-06-04 11:392019-04-18 19:46
Reportershirokov Assigned Tobarry  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version7.0.0.1670 
Target Version7.0.0.1670 
Summary0001428: FB 2.5 CTE script in trigger
DescriptionThis valid FB 2.5 trigger code is marked as invalid (problem with CTE in EXISTS clause):
CREATE OR ALTER TRIGGER TR_Transports_SAP_A FOR TR_Transports AFTER INSERT OR UPDATE OR DELETE POSITION 32762
AS
DECLARE Tran_Status CHAR(1); -- Ei14 - transport.status
DECLARE idv TYPE OF COLUMN TR_Vehicles.id;
DECLARE idrq TYPE OF COLUMN TR_Requests.id;
DECLARE o_cmd TYPE OF COLUMN Orders.Command;
DECLARE o_list VARCHAR(1024);
BEGIN
 IF(Rdb$Get_Context('USER_SESSION','DatabaseReplicationFlag') IS NOT NULL) THEN EXIT;
 IF(UPDATING)THEN BEGIN
 IF(new.idTransportState IS DISTINCT FROM old.idTransportState)THEN BEGIN
 -- Calculate trasfer price
 IF(new.idTransportState = 2020)THEN BEGIN
 --EXECUTE PROCEDURE TR_CalculatePrice(new.id);
 END
 IF(old.idTransportState = 2010 AND new.idTransportState = 2020)THEN Tran_Status = 'N';
 IF(old.idTransportState = 2020 AND new.idTransportState = 2010)THEN Tran_Status = 'D';
 IF(Tran_Status IS NOT NULL)THEN BEGIN
 INSERT INTO TerminalEvents (idTerminal, idTerminalEventType, VersatileId, InputData)
 SELECT 0, 309, id, :Tran_Status FROM TR_Vehicles WHERE idTransport = new.id;
 END
 -- recalculate transport price for bundles
 IF(new.idTransportState = 2040)THEN BEGIN
 FOR SELECT id FROM TR_Vehicles WHERE idTransport = new.id
 INTO :idv DO
 EXECUTE PROCEDURE ks_jj_PriceRecalculation(:idv);
 END
 IF(new.idTransportState = 2050)THEN BEGIN
 o_list = '';
 FOR SELECT FIRST 15 DISTINCT O.Command
 FROM TR_LoadingPlan P, BHDs B, Orders O WHERE P.idTransport = new.id AND P.idBHD = B.id AND B.idOrder=O.id AND O.idOrderState<>4130
 INTO :o_cmd DO BEGIN
 IF(CHAR_LENGTH(o_list)>0) THEN o_list = o_list || ',';
 o_list = o_list || o_cmd;
 END
 IF(CHAR_LENGTH(o_list)>0) THEN
 EXCEPTION EIntegrityCheck 'Production Orders '||o_list||' are not closed, shipment cannot be created for transport '||new.TRNum;
 INSERT INTO TerminalEvents (idTerminal, idTerminalEventType, VersatileId, InputData)
 SELECT 0, 211, id, :Tran_Status FROM TR_Vehicles WHERE idTransport = new.id;
 END
 -- Close all fully delivered TR Request related to this transport
 IF(new.idTransportState IN (2110,2120)) THEN BEGIN
 FOR WITH RECURSIVE CTE (id, idTrRequest) AS (
 SELECT xB.id, xB.idTrRequest FROM Bhds xB
 JOIN TR_LoadingPlan xL ON xL.idBhd = xB.id
 WHERE xL.idTransport = new.id
 UNION ALL
 SELECT xB.id, xB.idTrRequest FROM Bhds xB
 JOIN CTE ON CTE.id = xB.idOwner
 )
 SELECT RQ.id FROM CTE
 JOIN TR_Requests RQ ON RQ.id = CTE.idTrRequest
 WHERE RQ.idState <> 5020
 INTO :idrq DO BEGIN
 -- Check that all other transports related to requests are delivered
 IF(NOT EXISTS(
 WITH RECURSIVE CTE (id, idOwner) AS (
 SELECT xB.id, xB.idOwner FROM Bhds xB WHERE xB.idTrRequest = :idRq
 UNION ALL
 SELECT xB.id, xB.idOwner FROM Bhds xB
 JOIN CTE ON CTE.id = xB.idOwner
 )
 -- TR might have bundles which are not part of any transport (https://ceeit.kingspan.info/helpdesk/Ticket/12342146)
 SELECT * FROM CTE
 LEFT JOIN TR_LoadingPlan LP ON LP.idBhd = CTE.id
 LEFT JOIN TR_Transports T ON T.id = LP.idTransport
 WHERE (T.idTransportState NOT IN (2110, 2120) OR T.idTransportState IS NULL)
 AND (T.id <> new.id OR T.id IS NULL)
 ))THEN BEGIN
 UPDATE TR_Requests SET idState = 5020 WHERE id = :idrq;
 END
 END
 END
 END
 END
END^
TagsNo tags attached.

Activities

barry

2018-06-04 14:13

administrator   ~0003777

v.7.0.0.1672

Issue History

Date Modified Username Field Change
2018-06-04 11:39 shirokov New Issue
2018-06-04 11:39 shirokov Status new => assigned
2018-06-04 11:39 shirokov Assigned To => barry
2018-06-04 14:13 barry Status assigned => resolved
2018-06-04 14:13 barry Resolution open => fixed
2018-06-04 14:13 barry Note Added: 0003777
2019-04-18 19:46 barry Status resolved => closed