<!-- HTML generated using hilite.me -->

select case when (t3.tgt_mth = '201903') then decode(t3.slip_yn, 'Y', '발행됨', '미발행') else '미발행' end as t_slip_yn , t2.asst_cd , t2.asst_seq , t2.asst_cd || '-' || t2.asst_seq AS asst_cd_seq -- , t1.lms_no -- , t1.lms_ro -- , t1.lms_no || '-' || t1.lms_ro AS lms_no_ro -- , t1.dep_cd -- , t1.dep_nm -- , t1.dep_nm || '(' || t1.dep_cd || ')' AS dep_cd_nm -- , t1.cost_type , t2.asst_ty_gb , t2.asst_ty_cd , ( SELECT dtl_nm FROM lms_c100 WHERE hcd_cd = '1015' AND dtl_cd = t2.asst_ty_gb ) || '_' || ( SELECT dtl_nm FROM lms_c100 WHERE hcd_cd = '1010' AND dtl_cd = t2.asst_ty_cd ) AS t_asst_ty_gb_cd , t2.asst_nm -- , t3.strt_dt -- , t3.end_dt -- , t3.slip_yn , case when t3.end_dt is null then TO_CHAR(to_date(t2.acq_dt, 'YYYYMMDD'), 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(last_day(TO_DATE('201903', 'YYYYMM')), 'YYYY-MM-DD') when (t3.tgt_mth is not null and t3.tgt_mth < '201903' ) then TO_CHAR(to_date(t3.end_dt, 'YYYYMMDD') + 1, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(last_day(TO_DATE('201903', 'YYYYMM')), 'YYYY-MM-DD') when (t3.tgt_mth = '201903' ) then TO_CHAR(to_date(t3.strt_dt, 'YYYYMMDD'), 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(TO_DATE(t3.end_dt, 'YYYYMMDD'), 'YYYY-MM-DD') end as dprs_strt_end , t2.acq_dt , TO_CHAR(t2.acq_ea, '99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."') || ' ' || t2.acq_unit AS acq_ea_unit , t2.acq_amt , t2.acc_dprs_amt as bf_acc_dprs_amt , t2.reval_amt , t2.resid_amt , ( t2.acq_amt - t2.acc_dprs_amt - t2.acc_imp_amt + t2.reval_amt ) as book_amt , ( t2.acq_amt - t2.acc_dprs_amt - t2.acc_imp_amt + t2.reval_amt - t2.resid_amt ) as tgt_dprs_amt , trunc((t2.acq_amt - t2.acc_dprs_amt - t2.acc_imp_amt + t2.reval_amt - t2.resid_amt) * case when t3.end_dt is null then ((TO_DATE(t2.acq_dt, 'YYYYMMDD') - TO_DATE(, 'YYYYMMDD') + 1) - (TO_DATE(t1.acq_dt, 'YYYYMMDD') - TO_DATE('20190331', 'YYYYMMDD') + 1)) / (TO_DATE(t2.acq_dt, 'YYYYMMDD') - TO_DATE('20190228', 'YYYYMMDD') + 1) when (t3.tgt_mth is not null and t3.tgt_mth < '201903' ) then TO_CHAR(to_date(t3.end_dt, 'YYYYMMDD') + 1, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(last_day(TO_DATE('201903', 'YYYYMM')), 'YYYY-MM-DD') when (t3.tgt_mth = '201903' ) then TO_CHAR(to_date(t3.strt_dt, 'YYYYMMDD'), 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(TO_DATE(t3.end_dt, 'YYYYMMDD'), 'YYYY-MM-DD') end , 0) as cur_dprs_amt from lms_l250 t2 RIGHT JOIN lms_l110 t1 ON t1.lms_no = t2.lms_no AND t1.lms_ro = t2.lms_ro_last LEFT JOIN ( SELECT l1.* FROM lms_l260 l1 INNER JOIN ( SELECT asst_cd , asst_seq , MAX(idx_no) idx_no FROM lms_l260 GROUP BY asst_cd , asst_seq ) l2 ON l1.asst_cd = l2.asst_cd AND l1.asst_seq = l2.asst_seq AND l1.idx_no = l2.idx_no where 1=1 AND ( l1.tgt_mth IS NULL OR l1.tgt_mth <= '201903' ) ) t3 ON t2.asst_cd = t3.asst_cd AND t2.asst_seq = t3.asst_seq and t2.lms_ro_last = t3.lms_ro WHERE t1.con_gb != 'C' AND t1.use_yn = 'Y' AND t1.del_yn = 'N' AND t2.use_yn = 'Y' AND t2.del_yn = 'N' ORDER BY t1.lms_no, t1.lms_ro, t2.asst_seq ;



아직 절반 밖에 안짠 쿼리인데.. 돌겠당.

ㅇㅇ