Fix drill-down de l'interro budgétaire pour la nouvelle codification
[auf_coda_additions.git] / db / procedures / auf_p_interro_detail.sql
1 USE [coda-prod]
2 GO
3
4 SET ANSI_NULLS OFF
5 GO
6 SET QUOTED_IDENTIFIER OFF
7 GO
8
9 ALTER PROCEDURE [dbo].[auf_p_interro_detail]
10 @projetposte varchar(8),
11 @annee int,
12 @type varchar(3)
13 AS
14 SET NOCOUNT ON
15
16 CREATE TABLE #depense_recette (
17 el1 varchar(20)
18 )
19
20 IF @type = 'DEP'
21 INSERT INTO #depense_recette
22 SELECT code
23 FROM oas_element
24 WHERE
25 cmpcode = 'AUF'
26 AND elmlevel = 1
27 AND (code LIKE '2%' OR code LIKE '6%' OR code LIKE '9%')
28 ELSE
29 INSERT INTO #depense_recette
30 SELECT code
31 FROM oas_element
32 WHERE
33 cmpcode = 'AUF'
34 AND elmlevel = 1
35 AND (code LIKE '1%' OR code LIKE '7%')
36
37 SELECT
38 h.doccode AS code_doc,
39 h.docnum AS num_doc,
40 h.yr AS exercice,
41 h.period AS periode,
42 CONVERT(varchar, h.docdate, 102) AS date_doc,
43 l.el1 AS pcg,
44 l.el2 AS tiers_operation,
45 l.el3 AS projet_poste,
46 l.descr AS description_ligne,
47 h.curdoc AS dev_doc,
48 l.valuedoc AS montant_doc,
49 l.valuehome AS montant_eur,
50 CASE l.deb_cred_ind
51 WHEN 160 THEN 'Crédit'
52 ELSE 'Débit'
53 END AS debit_credit,
54 l.ref1 AS numero_facture,
55 '' AS numero_avoir,
56 l.ref3 AS payeur,
57 l.ref4 AS salarie,
58 l.ref5 AS numero_cheque,
59 CASE l.statpay
60 WHEN 84 THEN 'D - Disponible'
61 WHEN 87 THEN 'A - Attente'
62 WHEN 171 THEN 'X - Fermé (non lettrable)'
63 WHEN 89 THEN 'P - Payé'
64 WHEN 93 THEN 'O - Proposé'
65 WHEN 51 THEN 'C_NULL'
66 WHEN 369 THEN 'C_DRAFT_AVAILABLE'
67 WHEN 420 THEN 'C_DRAFT_PAID'
68 WHEN 506 THEN 'C_DRAFT_HELD'
69 ELSE '-'
70 END AS statut_paiement,
71 CONVERT(varchar, l.duedate , 102) AS date_echeance,
72 l.usrname AS utilisateur,
73 CASE h.status
74 WHEN 77 THEN 'T - Actif'
75 WHEN 78 THEN 'M - Imputé'
76 WHEN 79 THEN 'C_DOCSTAT_CANCEL'
77 WHEN 116 THEN 'C - Corbeille'
78 WHEN 235 THEN 'V - Réservé'
79 ELSE '-'
80 END AS statut_doc,
81 CONVERT(varchar, h.inpdate , 102) AS date_saisie
82 FROM oas_dochead h, oas_docline l
83 WHERE
84 h.cmpcode = 'AUF'
85 AND l.cmpcode = 'AUF'
86 AND h.yr = @annee
87 AND h.period BETWEEN 1 AND 12
88 AND h.doccode = l.doccode
89 AND h.docnum = l.docnum
90 AND l.el1 NOT LIKE '96%'
91 AND l.el1 NOT LIKE '97%'
92 AND l.el1 NOT LIKE '98%'
93 AND LEFT(l.el3,2) <> '95'
94 AND l.el3 LIKE @projetposte+'%'
95 AND l.el1 IN (SELECT el1 FROM #depense_recette)
96 ORDER BY l.el2, l.el1, h.yr, h.period
97
98 DROP TABLE #depense_recette
99 GO