- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 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
- 95
- 96
- 97
- 98
- 99
return QUERY
with a as
(
with pr as
(
select p.ss, p.sb,
sum(sum_prih) as sum_prih
from material.prihod_by_all_sklads_by_doc_period(dt_ostatki) p
group by 1,2
),
r AS
(
select p.ss, p.sb,
sum(p.sum_by_cen) as sum_rash
from material.rashod_by_all_sklads_by_doc_period(dt_ostatki) p
group by 1,2
),
os AS
(
select o.ss, o.sb,
-- case when trim(o.sb)='1150' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1150'
-- when trim(o.sb)='1050' then 'Запчастини ВКМ,ВПК1 сб. 1050'
-- when trim(o.sb)='1105' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1105'
-- else o.name_sklad end as name_skl,
sum(o.sum_ost_doc) as sum_ost_doc--,
from material.ostatki_by_all_sklads(dt_ostatki) o
left join material.sklad s on (s.skladid = o.skladid)
group by 1,2
),
topl AS
(
select o.ss, o.sb,
sum(o.ost_sum) as sum_ost_doc,
sum(o.vos_sum) as sum_vos,
sum(o.prih_sum) as sum_prih,
sum(o.rash_sum) as sum_rash
from material.ostatki_by_all_toplot(dt_ostatki) o
group by 1,2
)
select os.ss, os.sb,
os.sum_ost_doc,
coalesce ( pr.sum_prih,0) as sum_prih,
coalesce (r.sum_rash,0) as sum_rash
from os
left join pr on (os.ss= pr.ss and os.sb= pr.sb )
left join r on (os.ss= r.ss and os.sb= r.sb )
UNION
select os.ss, os.sb,
os.sum_ost_doc,
-- os.sum_vos + os.sum_prih - os.sum_rash as sum_ost_doc ,
os.sum_prih,
-- os.sum_rash
-- case when period_dt$>='2018.08.01' then COALESCE( r.sum_rash,0)
-- else os.sum_vos-os.sum_ost_doc + os.sum_prih end as sum_rash --os.sum_rash END
-- r.sum_rash
os.sum_vos-os.sum_ost_doc + os.sum_prih
from topl os left join r on (os.ss= r.ss and os.sb= r.sb )
/*
select o.ss, o.sb,
sum(o.ost_sum) as sum_ost_doc,
sum(o.prih_sum) as sum_prih,
sum(o.rash_sum) as sum_rash
from material.ostatki_by_all_toplot(dt_ostatki) o left JOIN r on (r.ss =o.ss and r.sb=o.sb)
group by 1,2
*/
/* UNION
select ob.ss, ob.sus,
'112' as depart_name,
'112',
sum(ob.ost_sum),
sum(ob.pr_sum),
sum(ob.rm_sum)
from material.oborot_by_ss_112_by_sklads(period_dt, dt_ostatki) ob
group by 1,2,3,4 */
),
b as
(
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'112') bb where bb.sb = '0007'
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'153') bb
UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'152') bb
select bb.ss,bb.sb,bb.sisb_d ,bb.sisb_k, bb.sdob, 0 from cont.bbz_sald_by_sb(y,m,'152') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'201') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'207') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'209') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'203') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'221') bb
-- UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'231') bb
-- UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'232') bb
Follow us!