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
| select tmp.id_article, tmp.reference, tmp.cout_unit as cout_unit, tmp.num_achat
from
(select a.id_article, al.MNT_TOTAL_HT / (al.NB_LOT_CMDE * al.qte_cmde * al.lg_cmde as cout_unit, a.reference, b.num_achat
from t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
where a.id_groupe_article in (1,3)
and a.id_article = b.id_article
and al.id_achat_ligne = b.id_achat_ligne
and a.id_unite_stock = al.id_unite_cmde
and al.id_unite_cmde <> al.id_unite_tot_cmde
union
select a.id_article, al.MNT_TOTAL_HT / al.tot_cmde as cout_unit, a.reference, b.num_achat
from t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
where a.id_groupe_article in (1,3)
and a.id_article = b.id_article
and al.id_achat_ligne = b.id_achat_ligne
and a.id_unite_stock = al.id_unite_tot_cmde
and al.id_unite_tot_cmde <> al.id_unite_cmde
union
select a.id_article, al.MNT_TOTAL_HT / al.tot_cmde as cout_unit, a.reference, b.num_achat
from t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
where a.id_groupe_article in (1,3)
and a.id_article = b.id_article
and al.id_achat_ligne = b.id_achat_ligne
and a.id_unite_stock = al.id_unite_tot_cmde
and al.id_unite_tot_cmde = al.id_unite_cmde
union
SELECT art.id_article, a.MNT_TOTAL_HT / (a.NB_LOT_CMDE * a.qte_cmde * a.lg_cmde) as cout_unit, a.reference, b.num_achat
from t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a
where a.id_achat_ligne = b.id_achat_ligne
and b.id_article = art.id_article
and art.id_unite_stock = 1461
and a.id_unite_cmde = 1
and art.id_unite_stock <> a.id_unite_tot_cmde
union
SELECT art.id_article, a.MNT_TOTAL_HT * c.ratio_u1_u2/ (a.NB_LOT_CMDE * a.qte_cmde * lg_cmde) as cout_unit, a.reference, b.num_achat
from t_article art, unit_conv c,V_RAPPRO_BL_ACHAT b, t_achat_ligne a
where c.id_unit_abaque = art.id_unite_abaque
and a.id_achat_ligne = b.id_achat_ligne
and b.id_article = art.id_article
and c.id_unit2 = a.id_unite_cmde
and c.id_unit1 = case art.id_unite_stock
when 1461 then 1
else
art.id_unite_stock
end
and art.id_unite_stock <> a.id_unite_cmde
and art.id_unite_stock <> a.id_unite_tot_cmde
union
SELECT art.id_article, a.MNT_TOTAL_HT * 1000 / a.tot_cmde as cout_unit, a.reference, b.num_achat
from t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a
where a.id_achat_ligne = b.id_achat_ligne
and b.id_article = art.id_article
and art.id_unite_stock = 2
and a.id_unite_cmde = 4
and a.id_unite_cmde = a.id_unite_tot_cmde
union
SELECT art.id_article, a.MNT_TOTAL_HT / (1000 * a.tot_cmde) as cout_unit, a.reference, b.num_achat
from t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a
where a.id_achat_ligne = b.id_achat_ligne
and b.id_article = art.id_article
and art.id_unite_stock = 4
and a.id_unite_cmde = 2
and a.id_unite_cmde = a.id_unite_tot_cmde
union
select a.id_article,al.MNT_TOTAL_HT * ucv.ratio_u1_u2 / (al.tot_cmde * uc.ratio_u1_u2) as cout_unit, a.reference, ach.num_achat
from t_article a, unit_conv uc, V_RAPPRO_BL_ACHAT b, t_achat_ligne al, unit_conv ucv, t_achat ach
where a.id_article = b.id_article
and a.id_unite_stock = uc.id_unit2
and al.id_achat_ligne = b.id_achat_ligne
and al.id_unite_cmde = uc.id_unit1
and a.id_unite_abaque = ucv.id_unit_abaque
and al.id_achat = ach.id_achat) tmp |
Partager