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 |
