oracle - Trigger is giving PL/SQL statement ignored error -

i have trigger , have modified using case statements it's giving errors:

create or replace trigger trg_stg_timeattendance   after insert or update on co_conf_em_tm_enr   referencing new new   each row declare   pragma autonomous_transaction;   --variable declaration stg_pos_ta - as400   l_induction_fl char(1) := 'n';   l_error_code   varchar2(10);   l_error_messg  varchar2(100);    --variable declaration stg_ta_payroll - lotus notes   v_ta_date         number(6) := null;   v_time_in         number(4) := null;   v_time_out        number(4) := null;   v_mgr_ovrd        char(1) := 'n';   v_area            varchar2(20) := null;   v_assc_name       varchar2(60) := null;   v_misc_wages_desc varchar2(30) := null;   v_shrt_str_name   varchar2(50) := null;   v_session_no      number(2) := 0;   v_oim_enabled     char(1);  begin   ----merging stg_pos_ta - as400 - start   begin     select decode(nvl(commission_percentage, 0), 0, 'y', 'n')       l_induction_fl       ebiz_user_cache      employee_id = :new.id_em;   exception     when no_data_found       l_induction_fl := 'n';   end;    if (:new.fl_conf = '1')     merge stg_pos_ta     using (select :new.id_tm_enr id_tm_enr,                   :new.id_em id_em,                   :new.id_mgr id_mgr,                   to_number(:new.id_str_rt) id_str_rt,                   :new.ts_em_tm_in ts_em_tm_in,                   :new.ts_em_tm_out ts_em_tm_out,                   :new.hrs_str hrs_str,                   :new.other_hrs_str other_hrs_str,                   :new.ty_tm ty_tm,                   :new.fl_conf fl_conf,                   :new.cd_mgr_rsn cd_mgr_rsn,                   :new.fl_mod fl_mod,                   :new.fl_del fl_del,                   l_induction_fl emp_induction_fl,                   :new.ts_crt_rcrd ts_crt_rcrd,                   :new.ts_mdf_rcrd ts_mdf_rcrd              dual) stg     on (time_ent_id = :new.id_tm_enr , to_number(str_id) = to_number(:new.id_str_rt))     when matched       update          set emp_id               = stg.id_em,              mgr_id               = stg.id_mgr,              emp_tm_in            = stg.ts_em_tm_in,              emp_tm_out           = stg.ts_em_tm_out,              tot_hrs              = stg.hrs_str,              tot_hrs_othrs        = stg.other_hrs_str,              time_typ             = stg.ty_tm,              confirm_mgr_fl       = stg.fl_conf,              mgr_rsn_cd           = stg.cd_mgr_rsn,              tm_modified_fl       = stg.fl_mod,              tm_deleted_fl        = stg.fl_del,              emp_induction_fl     = stg.emp_induction_fl,              ts_mdf_rcrd          = stg.ts_mdf_rcrd,              stg_osb_status       = 0,              stg_osb_process_date = null     when not matched       insert         (time_ent_id,          emp_id,          mgr_id,          str_id,          emp_tm_in,          emp_tm_out,          tot_hrs,          tot_hrs_othrs,          time_typ,          confirm_mgr_fl,          mgr_rsn_cd,          tm_modified_fl,          tm_deleted_fl,          emp_induction_fl,          ts_crt_rcrd,          ts_mdf_rcrd,          stg_event_id,          stg_osb_status,          stg_osb_error_message,          stg_load_date,          stg_osb_process_date)       values         (stg.id_tm_enr,          stg.id_em,          stg.id_mgr,          to_number(stg.id_str_rt),          stg.ts_em_tm_in,          stg.ts_em_tm_out,          stg.hrs_str,          stg.other_hrs_str,          stg.ty_tm,          stg.fl_conf,          stg.cd_mgr_rsn,          stg.fl_mod,          stg.fl_del,          l_induction_fl,          stg.ts_crt_rcrd,          stg.ts_mdf_rcrd,          seq_stg_event_id.nextval,          0,          null,          sysdate,          null);     commit;     ----merging stg_pos_ta - as400 - end      ----changes merging stg_ta_payroll - lotus notes - start     --manager override flag     if ((:new.ty_tm = '0' , :new.id_mgr not null) or        (:new.ty_tm != '0'))       v_mgr_ovrd := 'y';     end if;      --session number     if (:new.ty_tm = '0')       select count(*)         v_session_no         co_conf_em_tm_enr        id_em = :new.id_em          , trunc(ts_em_tm_in) = trunc(:new.ts_em_tm_in)          , ty_tm = '0'          , fl_conf = '1';        select v_session_no + 1 v_session_no dual;     else       select count(*)         v_session_no         co_conf_em_tm_enr        id_em = :new.id_em          , trunc(ts_em_tm_in) = trunc(:new.ts_em_tm_in)          , ty_tm != '0'          , fl_conf = '1';        select v_session_no + 50 v_session_no dual;     end if;      --area code store     begin       select nvl(id_node_prnt, 0)         v_area         ebiz_stg_store_hierarchy_cache        to_number(id_node) = to_number(:new.id_str_rt);     exception       when no_data_found         v_area := null;     end;      --associate name     begin       select lkup_val         v_oim_enabled         sh_ebiz_lookup        lkup_cd = 'oim_enabled'          , lkup_type = 'system_config';        if (v_oim_enabled = 'y')         select first_name || ' ' || last_name           v_assc_name           ebiz_user_cache          employee_id = :new.id_em;       else         select fn_em || ' ' || ln_em           v_assc_name           nbty_store_employee          id_em = :new.id_em;       end if;     exception       when no_data_found         v_assc_name := null;     end;      --misc wages description     begin       select lkup_val         v_misc_wages_desc         sh_ebiz_lookup        lkup_type = 'payroll_misc_wages'          , lkup_cd = :new.ty_tm;     exception       when no_data_found         v_misc_wages_desc := null;     end;      --short store name     begin       select trim(substr(nm_level, 1, instr(nm_level, '(') - 1))         v_shrt_str_name         ebiz_stg_store_hierarchy_cache        to_number(id_node) = to_number(:new.id_str_rt);     exception       when no_data_found         v_shrt_str_name := null;     end;      merge stg_ta_payroll     using (select :new.id_tm_enr id_tm_enr,                   to_number(:new.id_str_rt) id_str_rt,                   :new.id_em id_em,                   nvl(to_number(to_char(:new.ts_em_tm_in, 'yymmdd')), 0) v_ta_date,                   v_session_no v_session_no,                   :new.ts_em_tm_in ts_em_tm_in,                   :new.ts_em_tm_out ts_em_tm_out,                   (case                     when :new.ty_tm != '0'                      :new.ts_em_tm_in = ''                     else                      nvl(to_char(:new.ts_em_tm_in, 'hh24mi'), '0000')                   end) v_time_in,                   (case                     when :new.ty_tm != '0'                      :new.ts_em_tm_out = ''                     else                      nvl(to_char(:new.ts_em_tm_out, 'hh24mi'), '0000')                   end) v_time_out,                   v_mgr_ovrd v_mgr_ovrd,                   :new.id_mgr id_mgr,                   decode(:new.ty_tm, 'xx', '9', :new.ty_tm) ty_tm,                   :new.other_hrs_str other_hrs_str,                   v_area v_area,                   v_assc_name v_assc_name,                   v_misc_wages_desc v_misc_wages_desc,                   v_shrt_str_name v_shrt_str_name,                   (:new.hrs_str + :new.other_hrs_str) tot_hrs              dual) stg     on (time_ent_id = stg.id_tm_enr , to_number(str_id) = to_number(stg.id_str_rt))     when matched       update          set status  = null,              emp_id  = stg.id_em,              ta_date = stg.v_ta_date,              --session_no = stg.v_session_no,              time_in              = stg.v_time_in,              time_out             = stg.v_time_out,              mgr_override         = stg.v_mgr_ovrd,              mgr_emp_no           = stg.id_mgr,              misc_wages_code      = stg.ty_tm,              misc_wages_hrs       = stg.other_hrs_str,              area                 = stg.v_area,              associate_name       = stg.v_assc_name,              misc_wages_desc      = stg.v_misc_wages_desc,              shrt_str_name        = stg.v_shrt_str_name,              hrs                  = stg.tot_hrs,              stg_osb_process_date = null     when not matched       insert         (time_ent_id,          status,          str_id,          emp_id,          ta_date,          session_no,          time_in,          time_out,          mgr_override,          mgr_emp_no,          misc_wages_code,          misc_wages_hrs,          area,          associate_name,          misc_wages_desc,          shrt_str_name,          hrs,          stg_load_date,          stg_event_id,          stg_osb_status)       values         (stg.id_tm_enr,          null,          stg.id_str_rt,          stg.id_em,          stg.v_ta_date,          stg.v_session_no,          stg.v_time_in,          stg.v_time_out,          stg.v_mgr_ovrd,          stg.id_mgr,          stg.ty_tm,          stg.other_hrs_str,          stg.v_area,          stg.v_assc_name,          stg.v_misc_wages_desc,          stg.v_shrt_str_name,          stg.tot_hrs,          sysdate,          seq_stg_event_id.nextval,          0);     commit;   end if;   ----changes merging stg_ta_payroll - lotus notes - end exception   when others     update co_conf_em_tm_enr        set id_tlog_btch = 1,            btch_msg_dsc = 'error inserting stg_pos_commission - ' ||                           l_error_code || ' - ' || l_error_messg      id_em = :new.id_em        , to_char(ts_em_tm_in, 'dd-mm-yyyy') =            to_char(:new.ts_em_tm_in, 'dd-mm-yyyy');     commit; end trg_stg_timeattendance; 

i'm getting error on using keyword shows sql statement ignored , on case statement gives missing keyword error.

the case expressions malformed. let's consider following:

(case    when :new.ty_tm != '0'      :new.ts_em_tm_in = ''    else      nvl(to_char(:new.ts_em_tm_in, 'hh24mi'), '0000')    end) v_time_in 

the "when" branch attempts return boolean (which isn't legitimate data type in sql), "else" branch returns character string. that's not allowed - values returned branches in case expression must of same type, or null.

share , enjoy.

edit: , having seen comment replacing case decode - can same results case expression doing following:

case   when :new.ty_tm = '0'     nvl(to_char(:new.ts_em_tm_in,'hh24mi'),'0000')   else     null end v_time_in