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; |
Partager