w_kmr01_11 ---- kmm13br copywrite by ld 2000/09/07 15:30
select distinct substring( kmm102.kmm102_bgt_type,1,4 ) as bgt_type,
( case substring( kmm102.kmm102_bgt_type,3,2 ) when "09" then "1" when "10" then "2"
when "11" then "3" else "9" end ) as bgt_class,
substring( kmm102.kmm102_bgt_type,1,2 ) as bgt_team,
( isnull( (select substring(kmc101.kmc101_name,charindex("-",kmc101.kmc101_name)+1,12) from kmc101
where ( kmc101.kmc101_type = "budgetcode" )
and ( kmc101.kmc101_code = substring( kmm102.kmm102_bgt_type,1,4 ) + "00" ) ),"" ) ) as bgt_name,
sum( isnull( kmm101.kmm101_bgt_jan,0 ) ) as bgt_amt01,
sum( isnull( kmm101.kmm101_bgt_feb,0 ) ) as bgt_amt02,
sum( isnull( kmm101.kmm101_bgt_mar,0 ) ) as bgt_amt03,
sum( isnull( kmm101.kmm101_bgt_apr,0 ) ) as bgt_amt04,
sum( isnull( kmm101.kmm101_bgt_may,0 ) ) as bgt_amt05,
sum( isnull( kmm101.kmm101_bgt_jun,0 ) ) as bgt_amt06,
sum( isnull( kmm101.kmm101_bgt_jul,0 ) ) as bgt_amt07,
sum( isnull( kmm101.kmm101_bgt_aug,0 ) ) as bgt_amt08,
sum( isnull( kmm101.kmm101_bgt_sep,0 ) ) as bgt_amt09,
sum( isnull( kmm101.kmm101_bgt_oct,0 ) ) as bgt_amt10,
sum( isnull( kmm101.kmm101_bgt_nov,0 ) ) as bgt_amt11,
sum( isnull( kmm101.kmm101_bgt_dec,0 ) ) as bgt_amt12,
( sum( isnull( kmm101.kmm101_bgt_jan,0 ) ) + sum( isnull( kmm101.kmm101_bgt_feb,0 ) ) +
sum( isnull( kmm101.kmm101_bgt_mar,0 ) ) + sum( isnull( kmm101.kmm101_bgt_apr,0 ) ) +
sum( isnull( kmm101.kmm101_bgt_may,0 ) ) + sum( isnull( kmm101.kmm101_bgt_jun,0 ) ) +
sum( isnull( kmm101.kmm101_bgt_jul,0 ) ) + sum( isnull( kmm101.kmm101_bgt_aug,0 ) ) +
sum( isnull( kmm101.kmm101_bgt_sep,0 ) ) + sum( isnull( kmm101.kmm101_bgt_oct,0 ) ) +
sum( isnull( kmm101.kmm101_bgt_nov,0 ) ) + sum( isnull( kmm101.kmm101_bgt_dec,0 ) ) ) as amt01,
( "xxxxxxxx" ) as amt02,
sum( isnull( ( case when ( convert(char(8),kmm102.kmm102_chk_date,112) < "yyyymmdd" )
then kmm102.kmm102_chk_amt else 0 end ),0 ) ) as amt09,
sum( isnull( ( case when ( ( convert(char(8),kmm102.kmm102_over_date,112) < "yyyymmdd" )
and kmm102.kmm102_over_status <> "00" )
then kmm102.kmm102_over_amt
when ( ( convert(char(8),kmm102.kmm102_over_date,112) < "yyyymmdd" )
and kmm102.kmm102_over_status = "00" )
then kmm102.kmm102_over_amt else 0 end ),0 ) ) as amt07,
sum( isnull( ( select case when ( ( convert(char(8),kmm103.kmm103_pay_date,112) < "yyyymmdd" )
and kmm103.kmm103_pay_status <> "2" )
then kmm103.kmm103_pay_amt else 0 end
from kmm103
where ( kmm103.kmm103_yy = kmm102.kmm102_yy )
and ( kmm103.kmm103_bgt_kind = kmm102.kmm102_bgt_kind )
and ( kmm103.kmm103_case_no = kmm102.kmm102_case_no ) ),0 ) ) as amt03,
sum( isnull( ( select case when ( ( convert(char(8),kmm103.kmm103_pay_date,112) < "yyyymmdd" )
and kmm103.kmm103_pay_status <> "1" )
then kmm103.kmm103_pay_amt else 0 end
from kmm103
where ( kmm103.kmm103_yy = kmm102.kmm102_yy )
and ( kmm103.kmm103_bgt_kind = kmm102.kmm102_bgt_kind )
and ( kmm103.kmm103_case_no = kmm102.kmm102_case_no ) ),0 ) ) as amt04
from kmm102,
kmm101
where ( kmm102.kmm102_yy = "yyy" ) and
( kmm102.kmm102_bgt_kind = "1" ) and
( kmm101.kmm101_yy =* kmm102.kmm102_yy ) and
( kmm101.kmm101_bgt_kind =* kmm102.kmm102_bgt_kind ) and
( kmm101.kmm101_bgt_type =* kmm102.kmm102_bgt_type )
group by substring( kmm102.kmm102_bgt_type,1,4 ),
substring( kmm102.kmm102_bgt_type,3,2 ),
substring( kmm102.kmm102_bgt_type,1,2 )
order by bgt_class asc,
bgt_type desc
;
