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
| SELECT
w.client as "w_client",
w.IT as "code_IT",
w.engin as "w_engin",
itvm.ref as "ref_art",
itvm.it_desc as "desc_art",
sum(SM.MOVEMENTQUANTITY) as "Qte_sortie",
coalesce(vm.qte_vm,0) as "qte_vm",
sum(SM.MOVEMENTQUANTITY)+ coalesce(vm.qte_vm,0) as "qté a rendre"
FROM CSST_STOCKMOVEMENT SM
/*on fait la jointure avec la sous requete articLe*/
INNER join
(SELECT
it.id as "item_id",
e.code as "ref",
e.DESCRIPTION as"it_desc"
FROM CSST_ITEM it
/*fait une jointure pour avoir le code de l article*/
left outer join CSEQ_EQUIPMENT e
ON
it.id = e.id
/*que les vieilles matières*/
WHERE it.bsppisoldmatter = true) itvm
ON itvm.item_id = SM.ITEM_ID
/*fait une jointure avec les it*/
inner join
(select
wo.CODE as "IT",
wo.id as "id_wo",
sit.site_code as "client",
at.CODE as "type it",
mat.CODE as "engin"
FROM CSWO_WO wo
/* fait une jointure pour le type d it*/
inner join CSWO_ACTIONTYPE at
ON
at.ID =wo.ACTIONTYPE_ID
/*fait une jointure pour avoir l atelier de l it et que les ateliers du BMCO sauf PTV afin d'enlever les VM sortie du stock tri pour réparation*/
INNER join (SELECT
si.ID as "site_id",
si.CODE as "site_code"
FROM CSSY_SITE si
WHERE si.BSPPGROUPING like'ATELIER_BMCO' and si.CODE <>'BMCO_PTV')sit
ON sit.site_id = wo.BSPPATELIER_ID
/*fait une requete pour avoir l engin*/
left outer join (SELECT
woeq.WO_ID,
eq.CODE
from CSWO_WOEQPT woeq
left outer join CSEQ_EQUIPMENT eQ
ON woeq.EQPT_ID = eq.ID
where STRUCTURE_ID like 'MATERIAL') mat
ON wo.ID =mat.WO_ID) w
ON w.id_wo=SM.WO_ID
/*On fait la jointure avec le type mvt*/
inner join (select
ID as "typemvt_id",
CATEGORY as "type"
from CSST_MOVEMENTTYPE TYPEMVT
where (TYPEMVT.CATEGORY ='ISSUE'and TYPEMVT.Code <>'ELIMINATION_DESTRUC') or (TYPEMVT.CATEGORY ='ENTRY' and TYPEMVT.Code <>'ELIMINATION_DESTRUC') ) type_mvt
on SM.MOVEMENTTYPE_ID =type_mvt.typemvt_id
/*on fait la jointure avec IT VM*/
left outer join (SELECT
art_e.CODE as "art_code",
entrees.ITEM_ID as "id_item_vm",
entrees.BSPPOLDMATTERWO_ID as "id_wo",
SUM(entrees.MOVEMENTQUANTITY) as "qte_vm"
FROM CSST_STOCKMOVEMENT entrees
INNER JOIN CSST_ITEM art_i ON art_i.ID = entrees.ITEM_ID
INNER JOIN CSEQ_EQUIPMENT art_e ON art_e.ID = art_i.ID
WHERE entrees.BSPPOLDMATTERWO_ID is not null
AND art_i.BSPPISOLDMATTER = true
GROUP BY
art_e.CODE,
entrees.ITEM_ID,
entrees.BSPPOLDMATTERWO_ID) vm
on vm.id_wo = sm.WO_ID
and vm.id_item_vm = sm.ITEM_ID
GROUP BY
w.client,
w.IT,
w.engin,
itvm.ref,
itvm.it_desc,
vm.qte_vm
HAVING sum(SM.MOVEMENTQUANTITY) + coalesce(vm.qte_vm,0) <0 |
