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
| SELECT pos.num_pos AS NUMERO_DE_DOSSIER
, pos.cod_trs_cli AS code_client
, pos.nom_com AS nom_exploitant
, pos.nbr_tc AS nombre_de_conteneur
, pos.nbr_col AS nombre_de_colis
, TO_CHAR(dos.dat_etd, 'DD/MM/YYYY') AS date_ETD
, TO_CHAR(dos.dat_eta, 'DD/MM/YYYY') AS date_ETA
, SUM(fcl.mnt_prs / fct.tau_dev) AS montant
, pos.mrg_prv AS MARGE
, pos.cod_vil_pol AS POL
, pos.cod_vil_pod AS POD
FROM sat_facl fcl
INNER JOIN
sat_fact fct
ON fcl.cod_soc = fct.cod_soc
AND fcl.cod_eta = fct.cod_eta
AND fcl.typ_uat = fct.typ_uat
AND fcl.num_uat = fct.num_uat
AND fcl.cod_etp = fct.cod_etp
AND fcl.num_lif = fct.num_lig
INNER JOIN
tra_posi pos
ON pos.num_pos = fct.num_uat
INNER JOIN
tra_doss dos
ON dos.num_int_dos = pos.num_int_dos
WHERE pos.cod_trs_cli IS NOT NULL
AND dos.dat_etd > SYSDATE -1
AND dos.dat_etd < SYSDATE + 15
OR dos.dat_etd = SYSDATE
OR dos.dat_etd = SYSDATE + 15
AND fcl.typ_uat = 'P'
AND Sat_facl.num_uat LIKE 'F0%'
GROUP BY pos.num_pos
, pos.cod_trs_cli
, pos.nom_com
, pos.nbr_tc
, pos.nbr_col
, TO_CHAR(dos.dat_etd, 'DD/MM/YYYY')
, TO_CHAR(dos.dat_eta, 'DD/MM/YYYY')
, pos.mrg_prv
, pos.cod_vil_pol
, pos.cod_vil_pod |
Partager