Thanks. Yes I did actually here is the full syntax, but I still get the error code:
declare @trans_num as varchar(10)
declare @student as varchar(10)
select @trans_num = 'E7281649' --Nomination E number
select @student = '45854' --Employee number
--First change the nominee
Update [nominees]
set
[nominee_no_emp]= @student
where id=@trans_num
select * from course_histories where no_sch=(select no_sch from nominations where id=@trans_num) and no_emp=(select nominee_no_emp from nominees where id=@trans_num)
select * from course_history_dtl where no_chd_po=@trans_num
--Second insert COURSE_HISTORIES entry
insert into course_histories (no_emp, cd_crs, no_ch_seq, no_sch, at_cd_fee, cd_ch_enrolby, dt_ch_compdt, qy_crs_ceu, cd_org, nm_lst_updt, dt_lst_updt, tx_ch_suser1, tx_ch_suser2, tx_ch_suser3, tx_ch_suser4, tx_ch_suser5, no_ch_nuser1, no_ch_nuser2, cd_seg)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.no_sch as 'no_sch',
(case
when nom.ob_tuition is null then '0.0'
else nom.ob_tuition
end) as 'at_cd_fee',
'SYSADM' as 'cd_ch_enrolby',
GETDATE() 'dt_ch_compdt',
nom.duty_hours as 'qy_crs_ceu',
e1.cd_org as 'cd_org',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
e2.tx_emp_suser1 as 'tx_ch_suser1',
e2.ad_emp_add1 + '/' + e2.ad_emp_add2 as 'tx_ch_suser2',
e2.ad_emp_email as 'tx_ch_suser3',
substring(e2.no_emp_phone1,1,3) + substring(e2.no_emp_phone1,5,3) + substring(e2.no_emp_phone1,9,4) as 'tx_ch_suser4',
a.contact_name + '/' + a.contact_phone as 'tx_ch_suser5',
null as 'no_ch_nuser1', --This field mirrors the class number and is vital for completion on training report
replicate('', 6-len(convert(varchar,e2.no_emp_nuser2))) + convert(varchar,e2.no_emp_nuser2) + '.' +
replicate('', 6-len(convert(varchar,e2.no_emp_nuser1))) + convert(varchar,e2.no_emp_nuser1) as 'no_ch_nuser2',
'DWD' as 'cd_seg'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join apprv_group a
on nom.apprv_group_id = a.apprv_group_id
where
nom.id=@trans_num
--Last insert COURSE_HISTORY_DTL entry
insert into course_history_dtl (no_emp, cd_crs, no_ch_seq, no_chd_po, cd_chd_bstat, cd_chd_btype, dt_chd_invoice, no_chd_regauth, cd_chd_pmeth, nm_lst_updt, dt_lst_updt, id_ch, dt_chd_edate)
IF EXISTS (select transaction_date from invoice_log where doc_no=@trans_num)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
i.transaction_date as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join invoice_log i
on nom.id = i.doc_no
where
nom.id=@trans_num
ELSE
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
null as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
where
nom.id=@trans_num