select

substr(a.djxh,6) as id,

       (a.nd || a.yf) DECL_YM,

       a.zspm_dm as LEVY_ITEM_ID,

       b.zsxmmc  as LEVY_ITEM_NAME, --||'--'||d.zspmmc

       100 as DECL_STATUS,

       a.nsqx_dm as TAX_TERM_CODE,

       c.nsqxmc  as TAX_TERM_NAME,

       to_char(a.sbqx, 'yyyyMMdd') as DECL_TREM,

       to_char(a.sjtb_sj, 'yyyyMM') as DATA_CREATE_YM,

       a.sjgsdq as area_code

       from

(

select djxh, nd, yf, zspm_dm, nsqx_dm, sbqx, sjtb_sj, sjgsdq,zsxm_dm

  from tci.sb_ysbtj

 where zfbz_1 is null

   or zfbz_1 = 'N'

   and exists (select *

          from (select to_number('' || i.id) as iid

                  from i_reg_account i

                 where mgr_dept in

                       (select t.org_id

                          from i_organization t

                         where t.isdirectguanhu = 1

                         start with (t.org_id = 24300900000)

                        connect by prior t.org_id = t.parent_id)) d

         where djxh = iid)

   and trim(yf) = to_char(sysdate, 'MM')

   and nd = to_char(SysDate, 'YYYY')

) a left join dm_gy_zsxm b

on a.zsxm_dm = b.zsxm_dm

left join dm_gy_nsqx  c

on a.nsqx_dm = c.nsqx_dm