Modifications diverses

[auf_coda_additions.git] / db / vues / auf_v_controleBoursiers.sql

USE [coda-prod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[auf_v_controleBoursiers]
AS

SELECT
    'DANS L''ONGLET OPTION, LE SOUS-ANALYSE A ETE COCHE ALORS QU''IL NE DEVRAIT PAS'
        AS categorie,
    code,
    [name],
    adddate,
    usrname,
    '' anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND subanal <> 76  -- 76 = oui et 46 = non

UNION

SELECT
    'DANS L''ONGLET IMPUTATION, N''A PAS ETE CREE COMME ELEMENT DE GAUCHE' categorie,
    code,
    [name],
    adddate,
    usrname,
    CASE
        WHEN accounttype = 9 THEN 'compte de bilan'
        WHEN accounttype = 56 THEN 'compte de resultat'
    END anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND accounttype <> 40  -- 40 veut dire element de gauche

UNION

SELECT
    'DANS L''ONGLET IMPUTATION,LA DESCRIPTION OBLIGATOIRE N''A PAS ETE COCHE' categorie,
    code,
    [name],
    adddate,
    usrname,
    ''  anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND descr <> 76  -- 76 veut dire oui obligatoire

UNION

SELECT
    'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT A ETE COCHE' categorie,
    code,
    [name],
    adddate,
    usrname,
    'Niv. associes = ' + CONVERT(varchar(2),subslevel)  anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        ) OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND subslevel <> 3  -- 3 = Fournisseur est associes a un autre element 3

UNION

SELECT
    'DANS L''ONGLET LETTRAGE ET STATUTS ( PAIEMENTS AUTOMATIQUES,STATUTS PAR DEFAUT)'
        AS categorie,
     code,
     [name],
     adddate,
     usrname,
     CASE
        WHEN matchable <> 76 THEN 'élém lettrage non coché'
        WHEN statpay <> 84 THEN 'statut paiement non disponible'
     END anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND (matchable <> 76 OR statpay <> 84)

UNION

SELECT
    'ONGLET CONTROLE DES PAIEMENTS' categorie,
    code,
    [name],
    adddate,
    usrname,
    CASE
        WHEN paper <> 76  AND elec <> 76  THEN 'cheque et virement n''a pas été selectionné'
    END anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND code LIKE '%L'
    AND len(rtrim(ltrim(code))) IN (11, 14)
    AND paper <> 76
    AND elec <> 76

UNION

SELECT
    'TAILLE DE L''ELEMENT' categorie,
    code,
    [name],
    adddate,
    usrname,
    '' anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND code LIKE '%L'
    AND (
        endyear = 0
        OR (
            endyear = YEAR(GETDATE())
            AND endperiod >= MONTH(GETDATE())
        )
        OR endyear > YEAR(GETDATE())
    )
    AND LEN(RTRIM(LTRIM(code))) NOT IN (11, 14)

GO