Modifications diverses

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

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

ALTER VIEW [dbo].[auf_v_controleTiersCDOS]
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 LIKE 0
    AND (
        code LIKE 'c%' OR
        code LIKE 'd%' OR
        code LIKE 'o%' OR
        code LIKE 'S_______'
    )
    AND subanal <> 46  -- 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 LIKE 0
    AND (
        code LIKE 'c%' OR
        code LIKE 'd%' OR
        code LIKE 'o%' OR
        code LIKE 'S_______'
    )
    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 LIKE 0
    AND (
        code LIKE 'c%' OR
        code LIKE 'd%' OR
        code LIKE 'o%' OR
        code LIKE 'S_______'
    )
    AND descr <> 76  -- 76 veut dire oui obligatoire

UNION

SELECT
    'DANS L''ONGLET IMPUTATION, L''OPTION ELEMENT ASSOCIE PAR DEFAULT N''A PAS ETE COCHE'
        AS 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 LIKE 0
    AND (
        code LIKE 'c%' OR
        code LIKE 'd%' OR
        code LIKE 'o%' OR
        code LIKE 'S_______'
    )
    AND subslevel <> 0  -- 0 = Fournisseur est associes a un autre element

UNION

SELECT
    'DANS L''ONGLET IMPUTATION, L''OPTION SYNTHESE DE COMPTE DOIT ETRE A ' +
        '"BAILLEUR" OU "MEMBRE"' AS categorie,
    code,
    [name],
    adddate,
    usrname,
    'accountsummary = ' + CONVERT(varchar(20),accountsummary)  anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND endyear LIKE 0
    AND code LIKE 'c%'
    AND (
        (code NOT LIKE 'cgrm%' AND accountsummary <> 'BAILLEUR')
        OR (code LIKE 'cgrm%' AND accountsummary <> 'MEMBRE')
    )

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 split <> 49 THEN 'niv. éclatement <> Néant'
        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 LIKE 0
    AND code NOT LIKE 'cgrm%'
    AND (
        code LIKE 'c%'
        OR code LIKE 'o%'
        OR (code LIKE 'S_______' AND len(code) > 5)
    )
    AND (
        matchable <> 76
        OR split <> 49
        OR statpay <> 84
    )
    AND code <> 'SGA12003'  -- Cette anomalie ne peut plus etre corrigee

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 summary <> 102 THEN 'niv. synthese <> compte'
        WHEN split <> 49 THEN 'niv. éclatement <> Néant'
        WHEN ltrim(rtrim(settle)) <> '' THEN 'Tiers payeur a été inscrit'
        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 LIKE 0
    AND (
        code LIKE 'd%'
        OR code LIKE 'cgrm%'
        OR (code LIKE 'S_______' AND len (code) < 5)
    )
    AND (
        matchable <> 76
        OR summary <> 102
        OR split <> 49
        OR NOT (ltrim(rtrim(settle)) = '' OR settle IS NULL)
        OR statpay <> 84
    )

UNION

SELECT
    'ONGLET CONDITIONS, LA CASE EST-CE UN ELEMENT CLIENT/FOURNISSEUR N''A PAS ETE COCHE'
        AS categorie,
    code,
    [name],
    adddate,
    usrname,
    '' anomalie
FROM oas_element
WHERE
    cmpcode = 'AUF'
    AND elmlevel = 2
    AND deldate IS NULL
    AND endyear LIKE 0
    AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
    AND code NOT IN (
        'SCA22143','SCA22151','SGA12003','CGRM451','CGRM462','CGRM448', 'SVU11623'
    )
    AND custsuppacc <> 76

UNION

SELECT
    'ONGLET CONDITIONS, ESCOMPTE AUTORISE, LA CASE "NON" 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 LIKE 0
    AND (code LIKE 'c%' OR code LIKE 'd%' OR code LIKE 'o%' OR code LIKE 'S_______')
    AND discenable <> 46
GO