select  p.patnum EXTACCOUNTREF ,pat.guarantor  ACCOUNT ,cast(p.codenum as char(20)) STOCK,concat(pat.LName,' ',pat.Fname , ' ' , pat.Birthdate, ' B=', pc.NoBillIns, ' K=',  IF((PERIOD_DIFF( EXTRACT( YEAR_MONTH FROM p.procdate ),
EXTRACT( YEAR_MONTH FROM   pat.birthdate) ) div 12) < 18,1,0) ) DOCREF, 
cast(p.procnum as char(20)) ID, cast(concat('@',p.toothnum)  as char(255)) Descr, 
p.procdate DELIVERDATE ,p.procfee PRICE, p.unitqty QTY,p.DATEEntryC DDATE from procedurelog p join patient pat on p.patnum = pat.patnum
join procedurecode pc on p.codenum= pc.codenum  
where  p.DATEEntryC >= <@OPENDENTAL_LASTD@>
 and procstatus = 2 and p.procfee <> 0
 
union
select  p.patnum EXTACCOUNTREF ,pat.guarantor  ACCOUNT ,'ADJUST' STOCK,concat(pat.LName,' ',pat.Fname , ' ' , pat.Birthdate, ' B=0 k=0') , Concat('AD', p.Adjnum) ID, cast(AdjNote as char(255)),   
p.AdjDate DELIVERDATE ,p.AdjAmt PRICE, 1 QTY,p.AdjDate DDATE from 
adjustment p join patient pat on p.patnum = pat.patnum 
where p.DateEntry >= <@OPENDENTAL_LASTD@>
 and p.AdjAmt <> 0
order by 7