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
|
select cast(B.PRG_CODMRIN as INTEGER) PRG_CODMRIN,
MAX(B2.PRB_SEQUENCE) TRI_SEQUENCE,
MAX(G.GPB_CODPRINCIPAL) TRI_PRINC,
MAX(B2.GPB_COD) TRI_COD,
MAX(B2.PRB_PGI) TRI_PGI,
MAX(B2.PRB_CODWRIN) TRI_WRIN,
B.PRG_CODMRIN || ' ' COD,
min(' ' || G.PRG_LIB) LIB,
min(UNI_COD) UNI_COD,
sum(I.INV_QTETOTUNITE) STOCK_INIT,
sum(K.NST_STOCKOUV) NST_STOCKOUV,
sum(J.INV_QTETOTUNITE) STOCK_FINAL,
cast(null as NUMERIC (11, 3)) QTE_LIV,
cast(null as NUMERIC (11, 3)) QTE_TRAN,
cast(null as NUMERIC (11, 3)) QTE_PERTES,
cast(null as NUMERIC (11, 3)) STOCK_THEO,
cast(null as VARCHAR (20)) CALC_FINAL,
cast(null as NUMERIC (11, 3)) ECART_UU,
cast(null as NUMERIC (11, 3)) ECART_PRIX,
cast(null as NUMERIC (11, 3)) PCT_UU,
cast(null as NUMERIC (11, 3)) PCT_PRIX,
cast(sum(I.INV_QTETOTUNITE * J.INV_PRIXUNITE) as NUMERIC (11, 3))
PRIX_STOCK_INIT,
cast(sum(K.NST_STOCKOUV * J.INV_PRIXUNITE) as NUMERIC (11, 3))
PRIX_NST_STOCKOUV,
cast(sum(J.INV_QTETOTUNITE * J.INV_PRIXUNITE) as NUMERIC (11, 3))
PRIX_STOCK_FINAL,
cast(floatdiv(sum((select floatdiv(UTJ_QTETOTJOUR1, 1) + floatdiv(
UTJ_QTETOTJOUR2, 1) + floatdiv(UTJ_QTETOTJOUR3, 1) + floatdiv(
UTJ_QTETOTJOUR4, 1) + floatdiv(UTJ_QTETOTJOUR5, 1) + floatdiv(
UTJ_QTETOTJOUR6, 1) + floatdiv(UTJ_QTETOTJOUR7, 1) + floatdiv(
UTJ_QTETOTJOUR8, 1) + floatdiv(UTJ_QTETOTJOUR9, 1) + floatdiv(
UTJ_QTETOTJOUR10, 1) + floatdiv(UTJ_QTETOTJOUR11, 1) + floatdiv(
UTJ_QTETOTJOUR12, 1) + floatdiv(UTJ_QTETOTJOUR13, 1) + floatdiv(
UTJ_QTETOTJOUR14, 1) + floatdiv(UTJ_QTETOTJOUR15, 1) + floatdiv(
UTJ_QTETOTJOUR16, 1) + floatdiv(UTJ_QTETOTJOUR17, 1) + floatdiv(
UTJ_QTETOTJOUR18, 1) + floatdiv(UTJ_QTETOTJOUR19, 1) + floatdiv(
UTJ_QTETOTJOUR20, 1) + floatdiv(UTJ_QTETOTJOUR21, 1) + floatdiv(
UTJ_QTETOTJOUR22, 1) + floatdiv(UTJ_QTETOTJOUR23, 1) + floatdiv(
UTJ_QTETOTJOUR24, 1) + floatdiv(UTJ_QTETOTJOUR25, 1) + floatdiv(
UTJ_QTETOTJOUR26, 1) + floatdiv(UTJ_QTETOTJOUR27, 1) + floatdiv(
UTJ_QTETOTJOUR28, 1) + floatdiv(UTJ_QTETOTJOUR29, 1) + floatdiv(
UTJ_QTETOTJOUR30, 1) + floatdiv(UTJ_QTETOTJOUR31, 1) from
INVUTILISATIONJOUR U where UTJ_ANNEE = 2010 and UTJ_MOIS = 12 and
U.PRB_CODWRIN = B.PRB_CODWRIN)), 1) as NUMERIC (12, 3)) UTJ_QTETOT,
cast(floatdiv(sum((select J.INV_PRIXUNITE *(floatdiv(UTJ_QTETOTJOUR1, 1)
+ floatdiv(UTJ_QTETOTJOUR2, 1) + floatdiv(UTJ_QTETOTJOUR3, 1) + floatdiv
(UTJ_QTETOTJOUR4, 1) + floatdiv(UTJ_QTETOTJOUR5, 1) + floatdiv(
UTJ_QTETOTJOUR6, 1) + floatdiv(UTJ_QTETOTJOUR7, 1) + floatdiv(
UTJ_QTETOTJOUR8, 1) + floatdiv(UTJ_QTETOTJOUR9, 1) + floatdiv(
UTJ_QTETOTJOUR10, 1) + floatdiv(UTJ_QTETOTJOUR11, 1) + floatdiv(
UTJ_QTETOTJOUR12, 1) + floatdiv(UTJ_QTETOTJOUR13, 1) + floatdiv(
UTJ_QTETOTJOUR14, 1) + floatdiv(UTJ_QTETOTJOUR15, 1) + floatdiv(
UTJ_QTETOTJOUR16, 1) + floatdiv(UTJ_QTETOTJOUR17, 1) + floatdiv(
UTJ_QTETOTJOUR18, 1) + floatdiv(UTJ_QTETOTJOUR19, 1) + floatdiv(
UTJ_QTETOTJOUR20, 1) + floatdiv(UTJ_QTETOTJOUR21, 1) + floatdiv(
UTJ_QTETOTJOUR22, 1) + floatdiv(UTJ_QTETOTJOUR23, 1) + floatdiv(
UTJ_QTETOTJOUR24, 1) + floatdiv(UTJ_QTETOTJOUR25, 1) + floatdiv(
UTJ_QTETOTJOUR26, 1) + floatdiv(UTJ_QTETOTJOUR27, 1) + floatdiv(
UTJ_QTETOTJOUR28, 1) + floatdiv(UTJ_QTETOTJOUR29, 1) + floatdiv(
UTJ_QTETOTJOUR30, 1) + floatdiv(UTJ_QTETOTJOUR31, 1)) from
INVUTILISATIONJOUR U where UTJ_ANNEE = 2010 and UTJ_MOIS = 12 and
U.PRB_CODWRIN = B.PRB_CODWRIN)), 1) as NUMERIC (12, 3)) PRIX_QTETOT,
cast(null as NUMERIC (11, 3)) PRIX_LIV_TOT
from HISPRODBRUT B2,
INVPRODGENERIQUE G,
HISPRODBRUT B
left join INVINVENTAIRE I on (I.PRB_CODWRIN = B.PRB_CODWRIN and
I.CAL_DTACTIVITE = '11/30/2010')
left join INVINVENTAIRE J on (J.PRB_CODWRIN = B.PRB_CODWRIN and
J.CAL_DTACTIVITE = '4/14/2011')
left join INVNIVEAUSTOCK K on (K.PRB_CODWRIN = B.PRB_CODWRIN and
K.NST_ANNEE = 2010 and K.NST_MOIS = 12)
where B.PRB_DTEFFET <= '4/14/2011' and
(B.PRB_DTFINEFFET >= '4/14/2011' or
B.PRB_DTFINEFFET is null) and
B.PRB_ACTIF = 'T' and
B.PRB_PERIODEINV <= 0 and
B.PRG_CODMRIN = G.PRG_COD and
G.PRB_CODCALCULCOUT = B2.PRB_CODWRIN and
G.PRG_RENDSTAT = 'T' and
B2.PRB_DTEFFET <= '4/14/2011' and
(B2.PRB_DTFINEFFET >= '4/14/2011' or
B2.PRB_DTFINEFFET is null)
group by B.PRG_CODMRIN
order by 2,
3,
4,
5,
6 |
Partager