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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
| CREATE PROCEDURE lec_efacils1
//@dateinf datetime,
//@datesup datetime
AS
BEGIN
SELECT distinct
facture_cli.efac_codsit,
facture_cli.efac_numfaccli,
facture_cli.efac_typfaccli,
facture_cli.efac_indcdinot,
facture_cli.efac_codstafac,
facture_cli.efac_indimp,
facture_cli.efac_numdos,
facture_cli.efac_tgm,
facture_cli.efac_numvyg,
facture_cli.efac_codclifac,
facture_cli.efac_datfaccli,
facture_cli.efac_datcptcli,
facture_cli.efac_datpaicli,
facture_cli.efac_indenvcpt,
facture_cli.efac_mttdvi_ht,
facture_cli.efac_coddvi,
facture_cli.efac_tauchg,
facture_cli.efac_mttero_ht,
facture_cli.efac_codtva,
facture_cli.efac_tautva,
code_tva.ctva_statvails 'ctva_statvails',
facture_cli.efac_mttdvi_ttc,
facture_cli.efac_mttero_ttc,
facture_cli.efac_codaff,
facture_cli.efac_typdoc,
facture_cli.evol_idtcreenr,
lig_facture_cli.lfac_numligfaccli,
lig_facture_cli.lfac_codpre,
lig_facture_cli.lfac_typpre,
lig_facture_cli.lfac_prxpre,
lig_facture_cli.lfac_coduntfac,
lig_facture_cli.lfac_coddvi,
lig_facture_cli.lfac_tauchg,
lig_facture_cli.lfac_mttdvi_ht,
unt_dossier.udos_numintuntdos,
voyage.voya_indipoxpo,
voyage.voya_codporebq,
ISNULL(dossier.doss_codpordch,lig_facture_cli.lfac_codpordch),
ISNULL(dossier.doss_numvyg,facture_cli.efac_numvyg),
pfac_cli_ele.pfce_codoricde,
pfac_cli_ele.pfce_numcde,
commande.cmde_codict,
lig_facture_cli.lfac_tonfac,
pre_facturation_cli.evol_dhecreenr,
ISNULL(dossier.doss_numdos,lig_facture_cli.fac_numdos),
ISNULL
(
ISNULL
( (SELECT Max(tpsf_modpai)
FROM tiers_param_specifique
WHERE tiers_param_specifique.tpsf_codsit = facture_cli.efac_codsitref
AND tiers_param_specifique.tpsf_codtie = facture_cli.efac_codclifac
)
,
(SELECT Max(tpsf_modpai)
FROM tiers_param_specifique
WHERE tiers_param_specifique.tpsf_codsit = facture_cli.efac_codsitref
AND tiers_param_specifique.tpsf_codtie = NULL
)
)
,
tiers.tier_modpai
) 'modpai',
facture_cli.efac_codsitref
FROM facture_cli,lig_facture_cli, pre_facturation_cli, pfac_cli_ele, unt_dossier , dossier, commande, voyage, tiers, code_tva, paramsite_regle_action
// jointure table facture_cli <-> lig_facture_cli
WHERE facture_cli.efac_numfaccli = lig_facture_cli.lfac_numfaccli
AND facture_cli.efac_codsitref = lig_facture_cli.lfac_codsitref
AND facture_cli.efac_codsit = lig_facture_cli.lfac_codsit
// jointure table lig_facture_cli <-> pre_facturation_cli
AND lig_facture_cli.lfac_codsit *= pre_facturation_cli.pfac_codsit
AND lig_facture_cli.lfac_codsitref *= pre_facturation_cli.pfac_codsitref
AND lig_facture_cli.lfac_numfaccli *= pre_facturation_cli.pfac_numfaccli
AND lig_facture_cli.lfac_numligfaccli *= pre_facturation_cli.pfac_numligfaccli
//TD SOPRA 16/06/06 - Les 5 jointures suivantes font planter l'execution de la proc sous SQL SERVER
// jointure table pre_facturation_cli <-> pfac_cli_ele
AND pre_facturation_cli.pfac_numpeefacint *= pfac_cli_ele.pfce_numpeefacint
// jointure table pfac_cli_ele <-> unt_dossier
AND pfac_cli_ele.pfce_numintuntdos *= unt_dossier.udos_numintuntdos
// jointure table unt_dossier <-> dossier
AND unt_dossier.udos_codsit *= dossier.doss_codsit
AND unt_dossier.udos_numdos *= dossier.doss_numdos
AND unt_dossier.udos_tgm *= dossier.doss_tgm
//TD SOPRA 16/06/06
// jointure table dossier <-> voyage
AND dossier.doss_codsit *= voyage.voya_codsit
AND dossier.doss_numvyg *= voyage.voya_numvyg
// jointure table unt_dossier <-> commande
AND unt_dossier.udos_codoricde *= commande.cmde_codoricde
AND unt_dossier.udos_numcde *= commande.cmde_numcde
// jointure table facture_cli <-> tiers
AND facture_cli.efac_codclifac = tiers.tier_codtie
// jointure table facture_cli <-> code_tva
AND facture_cli.efac_codtva = code_tva.ctva_codtva
// critères de sélection
AND efac_codstafac = '1'
AND lig_facture_cli.lfac_codsit= paramsite_regle_action.psra_codsit
AND paramsite_regle_action.psra_codact = 'ils_pec_sit'
ORDER BY facture_cli.efac_codsit,
facture_cli.efac_codsitref,
facture_cli.efac_numfaccli,
lig_facture_cli.lfac_numligfaccli
END; |
