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