Principe de la requête GPS

yées à LC ainsi que d'une créance seule dans un CRE non envoyée à LC.

Paramètre Date à modifier avant exécution de la requête selon le besoin

Ci-dessous requête pour recherche en PCD/LCD

DECLARE GLOBAL TEMPORARY TABLE TXMRCRE_OK
(COBQQRDJ CHAR(3),
COCRERDJ CHAR(34) )
ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TXMRCRE_OK (COBQQRDJ,COCRERDJ)
SELECT COBQQRDJ
, COCRERDJ
FROM TXMRCRE
WHERE COBQQRDJ = 'CMR'
AND COSTACRE = 'OK' ;
CREATE INDEX IXTXMRCRE_OK
ON SESSION.TXMRCRE_OK
(COBQQRDJ ASC, COCRERDJ ASC) ;
DECLARE GLOBAL TEMPORARY TABLE TXMRCRE_REJET
(COBQQRDJ CHAR(3),
COCRERDJ CHAR(34) )
ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TXMRCRE_REJET (COBQQRDJ,COCRERDJ)
SELECT CRE.COBQQRDJ
, CRE.COCRERDJ
FROM TXMRCRE CRE
WHERE CRE.COBQQRDJ = 'CMR'
AND CRE.COSTACRE <> 'OK'
AND NOT EXISTS (SELECT 1
FROM SESSION.TXMRCRE_OK CREOK
WHERE CREOK.COBQQRDJ = CRE.COBQQRDJ
AND CREOK.COCRERDJ = CRE.COCRERDJ) ;
CREATE INDEX IXTXMRCRE_REJET
ON SESSION.TXMRCRE_REJET
(COBQQRDJ ASC, COCRERDJ ASC) ;
SELECT DISTINCT FRT.TYINS , CHAR(';')
,FRT.NOINS , CHAR(';')
,FRT.NOUEC , CHAR(';')
,FRT.IDSOUCPT , CHAR(';')
,FRT.IDENTGES , CHAR(';')
,FRT.IDCODINS , CHAR(';')
,FRT.COTRITAB , CHAR(';')
,FRT.COGAR , CHAR(';')
,FRT.COPARCRE , CHAR(';')
,FRT.COCPN , CHAR(';')
,FRT.ANPER , CHAR(';')
,FRT.COPERREF , CHAR(';')
,FRT.COEVTGES , CHAR(';')
,FRT.DTREFFLXITS , CHAR(';')
,FRT.COSIGFLXITS , CHAR(';')
,CHAR(FRT.MTFLXITS) , CHAR(';')
,EFT.IDEVTGESTEC , CHAR(';')
,EFT.DTENVFLX , CHAR(';')
,EFT.IDFLXITS
FROM TCMREFT EFT
INNER JOIN TCMRFRT FRT ON FRT.IDFLXITS = EFT.IDFLXITS
AND FRT.COPARCRE = EFT.COPARCRE
AND FRT.COBQQ = EFT.COBQQAVA
AND FRT.MTFLXITS <> 0
AND FRT.TYLIGCRE = 'C'
AND FRT.INTRTCRE = 'O'
WHERE EFT.DTENVFLX BETWEEN '01.12.2015' AND '31.12.2015'
AND EFT.IDUTIMAJ <> 'INITCAEUR'
AND EFT.IDPGMMAJ <> 'REQUETE'
AND EFT.IDPGMMAJ <> 'REEMICRE'
AND NOT EXISTS (SELECT 1
FROM TXMRPCD PCD
, TXMRLCD LCD
WHERE PCD.COBQQRDJ = EFT.COBQQAVA
AND PCD.COCRERDJ = EFT.IDEVTGESTEC
AND LCD.IDPAR = PCD.IDPAR
AND LCD.DTJOU = PCD.DTJOU
AND LCD.NOORDPIEECD = PCD.NOORDPIEECD
AND LCD.COBQQRDJ = PCD.COBQQRDJ
AND LCD.IDSOCCPT = PCD.IDSOCCPT
AND LCD.COEMECPT = PCD.COEMECPT
AND LCD.COLIVCPT = PCD.COLIVCPT
AND LCD.COFONIMP = PCD.COFONIMP
AND LCD.NOPIEECA = PCD.NOPIEECA
AND LCD.MTLIGECD = ABS(FRT.MTFLXITS))
AND NOT EXISTS (SELECT 1
FROM SESSION.TXMRCRE_REJET CRE
WHERE CRE.COBQQRDJ = EFT.COBQQAVA
AND CRE.COCRERDJ = EFT.IDEVTGESTEC)
WITH UR ;

Ci-dessous requête pour recherche en PC0/LC0

DECLARE GLOBAL TEMPORARY TABLE TXMRCRE_OK
(COBQQRDJ CHAR(3),
COCRERDJ CHAR(34) )
ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TXMRCRE_OK (COBQQRDJ,COCRERDJ)
SELECT COBQQRDJ
, COCRERDJ
FROM TXMRCRE
WHERE COBQQRDJ = 'CMR'
AND COSTACRE = 'OK' ;
CREATE INDEX IXTXMRCRE_OK
ON SESSION.TXMRCRE_OK
(COBQQRDJ ASC, COCRERDJ ASC) ;
DECLARE GLOBAL TEMPORARY TABLE TXMRCRE_REJET
(COBQQRDJ CHAR(3),
COCRERDJ CHAR(34) )
ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.TXMRCRE_REJET (COBQQRDJ,COCRERDJ)
SELECT CRE.COBQQRDJ
, CRE.COCRERDJ
FROM TXMRCRE CRE
WHERE CRE.COBQQRDJ = 'CMR'
AND CRE.COSTACRE <> 'OK'
AND NOT EXISTS (SELECT 1
FROM SESSION.TXMRCRE_OK CREOK
WHERE CREOK.COBQQRDJ = CRE.COBQQRDJ
AND CREOK.COCRERDJ = CRE.COCRERDJ) ;
CREATE INDEX IXTXMRCRE_REJET
ON SESSION.TXMRCRE_REJET
(COBQQRDJ ASC, COCRERDJ ASC) ;
DECLARE GLOBAL TEMPORARY TABLE TXMRPC0_TEMP
(COBQQRDJ CHAR(03)
, COCRERDJ CHAR(34)
, MTLIGECD DECIMAL(11, 0))
ON COMMIT PRESERVE ROWS ;
INSERT INTO SESSION.TXMRPC0_TEMP
(COBQQRDJ
, COCRERDJ
, MTLIGECD)
SELECT PC0.COBQQRDJ
, PC0.COCRERDJ
, LC0.MTLIGECD
FROM TXMRPC0 PC0
LEFT JOIN TXMRLC0 LC0 ON LC0.IDPAR = PC0.IDPAR
AND LC0.DTJOU = PC0.DTJOU
AND LC0.NOORDPIEECD = PC0.NOORDPIEECD
AND LC0.COBQQRDJ = PC0.COBQQRDJ
AND LC0.IDSOCCPT = PC0.IDSOCCPT
AND LC0.COEMECPT = PC0.COEMECPT
AND LC0.COLIVCPT = PC0.COLIVCPT
AND LC0.COFONIMP = PC0.COFONIMP
AND LC0.NOPIEECA = PC0.NOPIEECA
WHERE PC0.COBQQRDJ = 'CMR' ;
CREATE INDEX IXTXMRPC0_TEMP
ON SESSION.TXMRPC0_TEMP
(COBQQRDJ ASC
, COCRERDJ ASC
, MTLIGECD ASC) ;
SELECT DISTINCT FRT.TYINS , CHAR(';')
,FRT.NOINS , CHAR(';')
,FRT.NOUEC , CHAR(';')
,FRT.IDSOUCPT , CHAR(';')
,FRT.IDENTGES , CHAR(';')
,FRT.IDCODINS , CHAR(';')
,FRT.COTRITAB , CHAR(';')
,FRT.COGAR , CHAR(';')
,FRT.COPARCRE , CHAR(';')
,FRT.COCPN , CHAR(';')
,FRT.ANPER , CHAR(';')
,FRT.COPERREF , CHAR(';')
,FRT.COEVTGES , CHAR(';')
,FRT.DTREFFLXITS , CHAR(';')
,FRT.COSIGFLXITS , CHAR(';')
,CHAR(FRT.MTFLXITS) , CHAR(';')
,EFT.IDEVTGESTEC , CHAR(';')
,EFT.DTENVFLX , CHAR(';')
,EFT.IDFLXITS
FROM TCMREFT EFT
INNER JOIN TCMRFRT FRT ON FRT.IDFLXITS = EFT.IDFLXITS
AND FRT.COPARCRE = EFT.COPARCRE
AND FRT.COBQQ = EFT.COBQQAVA
AND FRT.MTFLXITS <> 0
AND FRT.TYLIGCRE = 'C'
AND FRT.INTRTCRE = 'O'
WHERE EFT.DTENVFLX BETWEEN '01.01.2015' AND '31.10.2015'
AND EFT.IDUTIMAJ <> 'INITCAEUR'
AND EFT.IDPGMMAJ <> 'REQUETE'
AND EFT.IDPGMMAJ <> 'REEMICRE'
AND NOT EXISTS (SELECT 1 FROM SESSION.TXMRPC0_TEMP PC0
WHERE PC0.COBQQRDJ = EFT.COBQQAVA
AND PC0.COCRERDJ = EFT.IDEVTGESTEC
AND PC0.MTLIGECD = ABS(FRT.MTFLXITS))
AND NOT EXISTS (SELECT 1
FROM SESSION.TXMRCRE_REJET CRE
WHERE CRE.COBQQRDJ = EFT.COBQQAVA
AND CRE.COCRERDJ = EFT.IDEVTGESTEC)
WITH UR ;