Fix drill-down de l'interro budgétaire pour la nouvelle codification

[auf_coda_additions.git] / db / procedures / auf_p_interro_detail.sql

USE [coda-prod]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[auf_p_interro_detail]
    @projetposte varchar(8),
    @annee int,
    @type varchar(3)
AS
SET NOCOUNT ON

CREATE TABLE  #depense_recette (
    el1 varchar(20)
)

IF @type = 'DEP'
    INSERT INTO #depense_recette
    SELECT code
    FROM oas_element
    WHERE
        cmpcode = 'AUF'
        AND elmlevel = 1
        AND (code LIKE '2%' OR code LIKE '6%' OR code LIKE '9%')
ELSE
    INSERT INTO #depense_recette
    SELECT code
    FROM oas_element
    WHERE
        cmpcode = 'AUF'
        AND elmlevel = 1
        AND (code LIKE '1%' OR code LIKE '7%')

SELECT
    h.doccode AS code_doc,
    h.docnum AS num_doc,
    h.yr AS exercice,
    h.period AS periode,
    CONVERT(varchar, h.docdate, 102) AS date_doc,
    l.el1 AS pcg,
    l.el2 AS tiers_operation,
    l.el3 AS projet_poste,
    l.descr AS description_ligne,
    h.curdoc AS dev_doc,
    l.valuedoc AS montant_doc,
    l.valuehome AS montant_eur,
    CASE l.deb_cred_ind
        WHEN 160 THEN 'Crédit'
        ELSE 'Débit'
    END AS debit_credit,
    l.ref1 AS numero_facture,
    '' AS numero_avoir,
    l.ref3 AS payeur,
    l.ref4 AS salarie,
    l.ref5 AS numero_cheque,
    CASE l.statpay
        WHEN 84 THEN 'D - Disponible'
        WHEN 87 THEN 'A - Attente'
        WHEN 171 THEN 'X - Fermé (non lettrable)'
        WHEN 89 THEN 'P - Payé'
        WHEN 93 THEN 'O - Proposé'
        WHEN 51 THEN 'C_NULL'
        WHEN 369 THEN 'C_DRAFT_AVAILABLE'
        WHEN 420 THEN 'C_DRAFT_PAID'
        WHEN 506 THEN 'C_DRAFT_HELD'
        ELSE '-'
    END AS statut_paiement,
    CONVERT(varchar, l.duedate , 102) AS date_echeance,
    l.usrname AS utilisateur,
    CASE h.status
        WHEN 77 THEN 'T - Actif'
        WHEN 78 THEN 'M - Imputé'
        WHEN 79 THEN 'C_DOCSTAT_CANCEL'
        WHEN 116 THEN 'C - Corbeille'
        WHEN 235 THEN 'V - Réservé'
        ELSE '-'
    END AS statut_doc,
    CONVERT(varchar, h.inpdate , 102) AS date_saisie
FROM oas_dochead h, oas_docline l
WHERE
    h.cmpcode = 'AUF'
    AND l.cmpcode = 'AUF'
    AND h.yr = @annee
    AND h.period BETWEEN 1 AND 12
    AND h.doccode = l.doccode
    AND h.docnum = l.docnum
    AND l.el1 NOT LIKE '96%'
    AND l.el1 NOT LIKE '97%'
    AND l.el1 NOT LIKE '98%'
    AND LEFT(l.el3,2) <> '95'
    AND l.el3 LIKE @projetposte+'%'
    AND l.el1 IN (SELECT el1 FROM #depense_recette)
ORDER BY  l.el2, l.el1, h.yr, h.period

DROP TABLE #depense_recette
GO