1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
| ALTER PROCEDURE dbo.EXPL_LectureReceptions
(
@NombreMois int
)
AS
declare @DateComparaison datetime
set @DateComparaison=dbo.DebutMois(getdate())
set @DateComparaison=DateAdd(month,-@NombreMois,@DateComparaison)
print @DateComparaison
SELECT TB_JOURNAL.jou_nummvt_n AS NumID, TB_JOURNAL.jou_typmvt_a AS ExplouNeg, TB_JOURNAL.jou_typlig_a AS AchouVte,
TB_JOURNAL.jou_fourni_a AS FrnChantier, TB_JOURNAL.jou_etabfo_a AS CodeChantier, TB_JOURNAL.jou_numcha_n AS NumChantier,
TB_JOURNAL.jou_soccha_n AS Societe, RTRIM(TB_JOURNAL.jou_regcha_a) AS Region, TB_JOURNAL.jou_codcse_n AS Secteur,
TB_CSE.cse_libelle_a AS LibelleSecteur, TB_JOURNAL.jou_codnv1_n AS NV1, TB_JOURNAL.jou_codnv2_n AS NV2,
TB_JOURNAL.jou_codnv3_n AS TypeOperation, EXPL_ListeTypesOperations.LibelleTypeOperation, TB_JOURNAL.jou_codnv4_n AS NV4,
TB_JOURNAL.jou_codnv5_n AS NV5, TB_JOURNAL.jou_codope_n AS Operation, TB_JOURNAL.jou_libope_a AS LibelleOperation,
TB_JOURNAL.jou_datmvt_d AS DateMvt, TB_JOURNAL.jou_numstat_a AS Statut1, TB_JOURNAL.jou_codeta_a AS Etab1,
EXPL_ListeStatuts1.NomPrenom AS NomPrenom1, TB_JOURNAL.jou_codres_n AS Ressource1, TB_RESSOU_1.res_libelle_a AS LibelleRessource1,
TB_JOURNAL.jou_typfac_a AS TypeFacture, TB_JOURNAL.jou_codpro1_a AS P1, TB_JOURNAL.jou_codpro2_a AS P2,
TB_JOURNAL.jou_codpro3_a AS P3, TB_JOURNAL.jou_codpro4_a AS P4, TB_JOURNAL.jou_codpro5_a AS P5, TB_JOURNAL.jou_codpro6_a AS P6,
TB_JOURNAL.jou_codpro7_a AS P7, TB_JOURNAL.jou_procli_n AS ProduitClient, TB_JOURNAL.jou_numctr_n AS NumContrat,
TB_JOURNAL.jou_numfam_n AS NumFamille, TB_JOURNAL.jou_numlig_n AS NumLigne, TB_JOURNAL.jou_libpro_a AS LibelleProduit,
TB_JOURNAL.jou_numstat1_a AS Statut2, TB_JOURNAL.jou_codeta1_a AS Etab2, EXPL_ListeStatuts2.NomPrenom AS NomPrenom2,
TB_JOURNAL.jou_codres1_n AS Ressource2, TB_RESSOU_2.res_libelle_a AS LibelleRessource2, CAST(TB_JOURNAL.jou_qtefac_n AS decimal(18, 3))
AS QteFac, TB_JOURNAL.jou_unfac_n AS UniteFac, CAST(TB_JOURNAL.jou_pumc_n AS decimal(18, 8)) AS PU,
CAST(TB_JOURNAL.jou_mthtmc_n AS decimal(18, 2)) AS MTHT, TB_JOURNAL.jou_codtva_n AS TVA, TB_JOURNAL.jou_txtva_n AS TauxTVA,
TB_JOURNAL.jou_codfor_n AS CVO, TB_JOURNAL.jou_txfor_n AS TauxCVO, TB_JOURNAL.jou_statut_a AS Stade,
TB_JOURNAL.jou_numebl_a AS NumBL, TB_JOURNAL.jou_datliv_d AS DateLivraison, TB_JOURNAL.jou_reffac_a AS RefFacture,
TB_JOURNAL.jou_confor_n AS Conformite, TB_JOURNAL.jou_observ_a AS Observations, TB_JOURNAL.jou_stock_a AS Stock,
TB_JOURNAL.jou_stkmaj_a AS StockMaJ, TB_JOURNAL.jou_mvtsto_n AS NumMvtStock, TB_JOURNAL.jou_faccom_a AS FactureComplementaire,
TB_JOURNAL.jou_typtpt_a AS TypeTransport, TB_JOURNAL.jou_immatr_a AS Immatriculation, TB_JOURNAL.jou_nbrkms_n AS Kms,
TB_JOURNAL.jou_pourab_n AS PourcentageRabais, TB_JOURNAL.jou_monrab_n AS MontantRabais, TB_JOURNAL.jou_motrab_n AS Rabais,
TB_JOURNAL.jou_actif_a AS Actif, TB_JOURNAL.jou_pefc_a AS PEFC, TB_JOURNAL.jou_codtra_n AS Tranche,
TB_JOURNAL.jou_typges_n AS CodePEFC, CAST(CASE WHEN TB_JOURNAL.jou_mthtes_n IS NULL OR
TB_JOURNAL.jou_mthtes_n = 0 THEN TB_JOURNAL.jou_mthtmc_n ELSE TB_JOURNAL.jou_mthtes_n END AS decimal(18, 4)) AS MontantHTEscompte,
TB_JOURNAL.jou_codesc_n AS Escompte, TB_JOURNAL.jou_tauesc_n AS TauxEscompte, TB_JOURNAL.jou_numstat2_a AS Statut3,
TB_JOURNAL.jou_codeta2_a AS Etab3, EXPL_ListeStatuts3.NomPrenom AS NomPrenom3, TB_JOURNAL.jou_codres2_n AS Ressource3,
TB_RESSOU_3.res_libelle_a AS LibelleRessource3, TB_JOURNAL.jou_numstat3_a AS Statut4, TB_JOURNAL.jou_codeta3_a AS Etab4,
TB_JOURNAL.jou_profil_a AS Profil, EXPL_ListeStatuts4.NomPrenom AS NomPrenom4, TB_JOURNAL.jou_codres3_n AS Ressource4,
TB_RESSOU_4.res_libelle_a AS LibelleRessource4, TB_JOURNAL.jou_qterab_n AS QteRabais, TB_JOURNAL.jou_bloc_a AS Bloc,
TB_CHANTIER.cha_nomcha_a AS LibelleChantier, EXPL_ListeUnites.LibelleUnite AS LibelleUniteFac,
EXPL_ListeUnites.AbregeUnite AS AbregeUniteFac, '' AS StatutEdition, TB_ECHCON.ech_modrgt_n AS ModeReglt,
DIC_NIV3.nv3_lib_a AS LibelleModeReglt, CG_PRODUITS.pro_conv_stere_n AS ConvSteres, CG_PRODUITS.pro_conv_tonne_n AS ConvTonnes,
CG_PRODUITS.pro_conv_M3_n AS ConvM3, CAST(TB_JOURNAL.jou_qtefac_n * CG_PRODUITS.pro_conv_stere_n AS decimal(18, 3)) AS QteSteres,
CAST(TB_JOURNAL.jou_qtefac_n * CG_PRODUITS.pro_conv_tonne_n AS decimal(18, 3)) AS QteTonnes,
CAST(TB_JOURNAL.jou_qtefac_n * CG_PRODUITS.pro_conv_M3_n AS decimal(18, 3)) AS QteM3, TB_JOURNAL.jou_numfac_n AS NumeroFacture,
TB_JOURNAL.jou_datfac_d AS DateFacture, TB_JOURNAL.jou_datdecfac_d AS DateDeclenchementFacture, EXPL_ListeOperations.QteouVal
FROM TB_JOURNAL INNER JOIN
TB_CHANTIER ON TB_JOURNAL.jou_fourni_a = TB_CHANTIER.cha_fourni_a AND TB_JOURNAL.jou_etabfo_a = TB_CHANTIER.cha_etabfo_a AND
TB_JOURNAL.jou_numcha_n = TB_CHANTIER.cha_numcha_n INNER JOIN
TB_CSE ON TB_JOURNAL.jou_codcse_n = TB_CSE.cse_codcse_n LEFT OUTER JOIN
EXPL_ListeStatuts AS EXPL_ListeStatuts1 ON TB_JOURNAL.jou_numstat_a = EXPL_ListeStatuts1.Statut AND
TB_JOURNAL.jou_codeta_a = EXPL_ListeStatuts1.Etab LEFT OUTER JOIN
EXPL_ListeOperations ON TB_JOURNAL.jou_codope_n = EXPL_ListeOperations.Operation LEFT OUTER JOIN
CG_PRODUITS ON TB_JOURNAL.jou_codpro1_a = CG_PRODUITS.pro_codpro1_a AND
TB_JOURNAL.jou_codpro2_a = CG_PRODUITS.pro_codpro2_a AND TB_JOURNAL.jou_codpro3_a = CG_PRODUITS.pro_codpro3_a AND
TB_JOURNAL.jou_unfac_n = CG_PRODUITS.pro_uios_n AND CG_PRODUITS.pro_codpro4_a = '0' AND CG_PRODUITS.pro_codpro5_a = '0' AND
CG_PRODUITS.pro_codpro6_a = '0' AND CG_PRODUITS.pro_codpro7_a = '0' LEFT OUTER JOIN
TB_RESSOU AS TB_RESSOU_4 ON TB_JOURNAL.jou_codres3_n = TB_RESSOU_4.res_numero_n LEFT OUTER JOIN
TB_RESSOU AS TB_RESSOU_3 ON TB_JOURNAL.jou_codres2_n = TB_RESSOU_3.res_numero_n LEFT OUTER JOIN
TB_RESSOU AS TB_RESSOU_2 ON TB_JOURNAL.jou_codres1_n = TB_RESSOU_2.res_numero_n LEFT OUTER JOIN
TB_RESSOU AS TB_RESSOU_1 ON TB_JOURNAL.jou_codres_n = TB_RESSOU_1.res_numero_n LEFT OUTER JOIN
EXPL_ListeTypesOperations ON TB_JOURNAL.jou_codnv3_n = EXPL_ListeTypesOperations.TypeOperation LEFT OUTER JOIN
EXPL_ListeStatuts AS EXPL_ListeStatuts2 ON TB_JOURNAL.jou_numstat1_a = EXPL_ListeStatuts2.Statut AND
TB_JOURNAL.jou_codeta1_a = EXPL_ListeStatuts2.Etab LEFT OUTER JOIN
EXPL_ListeStatuts AS EXPL_ListeStatuts3 ON TB_JOURNAL.jou_numstat2_a = EXPL_ListeStatuts3.Statut AND
TB_JOURNAL.jou_codeta2_a = EXPL_ListeStatuts3.Etab LEFT OUTER JOIN
EXPL_ListeStatuts AS EXPL_ListeStatuts4 ON TB_JOURNAL.jou_numstat3_a = EXPL_ListeStatuts4.Statut AND
TB_JOURNAL.jou_codeta3_a = EXPL_ListeStatuts4.Etab LEFT OUTER JOIN
EXPL_ListeUnites ON TB_JOURNAL.jou_unfac_n = EXPL_ListeUnites.Unite LEFT OUTER JOIN
TB_ECHCON ON TB_JOURNAL.jou_numctr_n = TB_ECHCON.ech_numero_n AND TB_ECHCON.ech_actif_a = 'O' LEFT OUTER JOIN
DIC_NIV3 ON DIC_NIV3.nv3_codnv3_n = TB_ECHCON.ech_modrgt_n
WHERE (TB_JOURNAL.jou_datmvt_d >= @DateComparaison) AND (TB_JOURNAL.jou_actif_a = 'O') |
