01.
select
02.
adrrais,ADRRUE1, ADRRUE2, ADRVILL, adrcode,adriden, ADRNCLI,stmsite,STMVPA, STMVPR,STMUPR, STMCEXMVT,
03.
artcexr ARTCODE,
04.
sum
((
case
when
Smmttva = 0
then
nvl(((smmbiiu + smmpuiu + smmdtiu + smmuaiu + smmcriu + smmpriu + smmaiiu + smmexiu) - (smmsaiu + smmdtou + smmvriu + smmcoiu)),0)
05.
when
Smmttva = 7
then
nvl(((smmbiiu + smmpuiu + smmdtiu + smmuaiu + smmcriu + smmpriu + smmaiiu + smmexiu) - (smmsaiu + smmdtou + smmvriu + smmcoiu)),0)
06.
else
0
end
))
as
ending,
07.
sum
((
case
when
Smmttva = 0
then
nvl(((smmbiac + smmpuac + smmdtic + smmuaac + smmcrac + smmprac + smmaiac + smmexac) - (smmsaac + smmdtoc + smmvrac + smmcoac)),0)
08.
when
Smmttva = 7
then
nvl(((smmbiac + smmpuac + smmdtic + smmuaac + smmcrac + smmprac + smmaiac + smmexac) - (smmsaac + smmdtoc + smmvrac + smmcoac)),0)
09.
else
0
end
))
as
ending_cost ,
10.
11.
pkstrucobj.get_Desc(1,artcinr,:PLANG) ART_DESC,
12.
to_char(stmdmvt,
'dd/MON/yyyy'
) MVT_DATE,
13.
STMTMVT MVT_TYPE,
14.
Pkparpostes.Get_Postlibl(1,10,1068,STMTMVT,
'GB'
) MVT_DESC,
15.
STMMOTF MVT_REASON,
16.
Pkparpostes.Get_Postlibl(1,10,506,STMMOTF,
'GB'
) MVT_REASON_DESC,
17.
(
case
18.
when
stmval > 0
then
stmval
19.
else
0
20.
end
)
as
IN_QTY,
21.
(
case
22.
when
stmval < 0
then
stmval
23.
else
0
24.
end
) OUT_QTY,
25.
(
case
26.
when
STMVPR > 0
then
STMVPR
27.
else
0
28.
end
)
as
IN_Cost,
29.
(
case
30.
when
STMVPR < 0
then
STMVPR
31.
else
0
32.
end
) OUT_Cost,
33.
34.
STMVPR COST_VALUE,
35.
STMUPR UNIT_COST,
36.
initcap(to_Char(to_date(:PDATE||
'01'
,
'yyyymmdd'
),
'MONTH'
)) MONTH_LABEL,
37.
to_numbeR(substr(:PDATE,1,4)) YEAR_LABEL
38.
from
stomvt,artul,artrac,cliadres,stomvmois,
39.
(
40.
SELECT
OBJCINT p2
41.
FROM
STRUCREL
42.
CONNECT
BY
PRIOR
OBJCINT = OBJPERE
AND
TRUNC(
CURRENT_DATE
)
BETWEEN
OBJDDEB
AND
OBJDFIN
43.
START
WITH
OBJPERE = :PMER
AND
TRUNC(
CURRENT_DATE
)
BETWEEN
OBJDDEB
AND
OBJDFIN
44.
UNION
45.
SELECT
SOBCINT p2
46.
FROM
STRUCOBJ
47.
WHERE
SOBCINT = :PMER
48.
)
49.
where
arucinl = stmcinl
50.
and
artcinr = p2
51.
and
smmcinl = stmcinl
52.
and
smmmois = to_number(to_char(to_date(:PDATE||
'01'
,
'yyyymmdd'
),
'yyyymm'
))
53.
and
smmsite = stmsite
54.
and
arucinr = artcinr
55.
and
stmval <> 0
56.
and
to_numbeR(adrncli) = stmsite
57.
and
adradre = 1
58.
and
stmsite = to_numbeR(:PSITE)
59.
and
( artcexr = :PART
or
:PART =
'-1'
)
60.
and
to_char(stmdmvt,
'yyyymm'
) = :PDATE
61.
Group
by
adrrais,ADRRUE1, ADRRUE2, ADRVILL, adrcode,adriden, ADRNCLI,stmsite,STMVPA, STMVPR,STMUPR, STMCEXMVT,
62.
artcexr ARTCODE ,
63.
pkstrucobj.get_Desc(1,artcinr,:PLANG) ART_DESC,
64.
to_char(stmdmvt,
'dd/MON/yyyy'
) MVT_DATE,
65.
STMTMVT MVT_TYPE,
66.
Pkparpostes.Get_Postlibl(1,10,1068,STMTMVT,
'GB'
) MVT_DESC,
67.
STMMOTF MVT_REASON,
68.
Pkparpostes.Get_Postlibl(1,10,506,STMMOTF,
'GB'
) MVT_REASON_DESC,
69.
STMVPR COST_VALUE,
70.
STMUPR UNIT_COST,
71.
initcap(to_Char(to_date(:PDATE||
'01'
,
'yyyymmdd'
),
'MONTH'
)) MONTH_LABEL,
72.
to_numbeR(substr(:PDATE,1,4)) YEAR_LABEL
73.
order
by
stmdmvt