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"
"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 (CAS (CHAR (8),
KMM102.KMM102_CHK_DATE, 112) <"YYYYMMDD")
THEN KMM102.KMM102_CHK_AMT ELSE 0 End, 0)) AS AMT09,
Sum (isnull ((Case WHEN) (CAART (CHAR (8),
KMM102.KMM102_Over_Date, 112) <"yyyymmdd")
And KMM102.KMM102_OVER_STATUS <> "00")
THEN KMM102.KMM102_OVER_AMT
WHEN ((CONVERT (25), 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 (25, 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 (25, 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 bgg_class ASC,
BGT_TYPE DESC
;
