work:logiciel_usineretraite:lc:creances_non_transmises_a_la_lc

[ Please fill or disable this placeholder (:wiki:site_notice) ]
You are here: start » work » logiciel_usineretraite » lc » creances_non_transmises_a_la_lc
This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== Principe de la requête GPS =====

<font 11.0pt/11;;inherit;;inherit>Certains GPS ont remontés des écarts non constants entre les Etats GRECCO et la Comptabilité Générale, apparus depuis la montée de la V231.</font>

<font 11.0pt/11;;inherit;;inherit>Suite à l’analyse de ces écarts, il a été constaté que certains montants d’un CRE Riche ne sont pas comptabilisés du fait de leur absence dans le FICCRE.</font>

<font 11.0pt/11;;inherit;;inherit>Dans l’attente du correctif, la requête suivante permet de lister les flux non reçus dans LC et de justifier les écarts ou une partie.</font>

<font 11.0pt/11;;inherit;;inherit>Amélioration de la requête : recherche les créances non envoyées à LC dans un CRE qui possède d'autres créances envo</font>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 =====

<code>
 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 ;
</code>

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

<file>

  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 ;
</file>
Trace: • requete_majlink • creances_non_transmises_a_la_lc
Search

Navigation

Print/export

Tools

QR Code
