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) , IF(cp.procnum is not null,' V=0 ',' V=1') ) DOCREF, 
 
cast(p.procnum as char(20)) ID, cast(trim(concat('@',IF(cp.procnum is not null,'*#* ',''),
(CASE  
WHEN p.toothnum =1 THEN 'Element 18' 
WHEN p.toothnum =2 THEN 'Element 17' 
WHEN p.toothnum =3 THEN 'Element 16' 
WHEN p.toothnum =4 THEN 'Element 15' 
WHEN p.toothnum =5 THEN 'Element 14' 
WHEN p.toothnum =6 THEN 'Element 13' 
WHEN p.toothnum =7 THEN 'Element 12' 
WHEN p.toothnum =8 THEN 'Element 11' 
WHEN p.toothnum =9 THEN 'Element 21' 
WHEN p.toothnum =10 THEN 'Element 22' 
WHEN p.toothnum =11 THEN 'Element 23' 
WHEN p.toothnum =12 THEN 'Element 24' 
WHEN p.toothnum =13 THEN 'Element 25' 
WHEN p.toothnum =14 THEN 'Element 26' 
WHEN p.toothnum =15 THEN 'Element 27' 
WHEN p.toothnum =16 THEN 'Element 28' 
WHEN p.toothnum =17 THEN 'Element 38' 
WHEN p.toothnum =18 THEN 'Element 37' 
WHEN p.toothnum =19 THEN 'Element 36' 
WHEN p.toothnum =20 THEN 'Element 35' 
WHEN p.toothnum =21 THEN 'Element 34' 
WHEN p.toothnum =22 THEN 'Element 33' 
WHEN p.toothnum =23 THEN 'Element 32' 
WHEN p.toothnum =24 THEN 'Element 31' 
WHEN p.toothnum =25 THEN 'Element 41' 
WHEN p.toothnum =26 THEN 'Element 42' 
WHEN p.toothnum =27 THEN 'Element 43' 
WHEN p.toothnum =28 THEN 'Element 44' 
WHEN p.toothnum =29 THEN 'Element 45' 
WHEN p.toothnum =30 THEN 'Element 46' 
WHEN p.toothnum =31 THEN 'Element 47' 
WHEN p.toothnum =32 THEN 'Element 48' 

WHEN p.toothnum ='A' THEN 'Element 55' 
WHEN p.toothnum ='B' THEN 'Element 54' 
WHEN p.toothnum ='C' THEN 'Element 53' 
WHEN p.toothnum ='D' THEN 'Element 52' 
WHEN p.toothnum ='E' THEN 'Element 51' 
WHEN p.toothnum ='F' THEN 'Element 61' 
WHEN p.toothnum ='G' THEN 'Element 62' 
WHEN p.toothnum ='H' THEN 'Element 63' 
WHEN p.toothnum ='I' THEN 'Element 64' 
WHEN p.toothnum ='J' THEN 'Element 65' 
WHEN p.toothnum ='K' THEN 'Element 75'  
WHEN p.toothnum ='L' THEN 'Element 74' 
WHEN p.toothnum ='M' THEN 'Element 73' 
WHEN p.toothnum ='N' THEN 'Element 72' 
WHEN p.toothnum ='O' THEN 'Element 71' 
WHEN p.toothnum ='P' THEN 'Element 81' 
WHEN p.toothnum ='Q' THEN 'Element 82' 
WHEN p.toothnum ='R' THEN 'Element 83' 
WHEN p.toothnum ='S' THEN 'Element 84' 
WHEN p.toothnum ='T' THEN 'Element 85' 




ELSE '' END), 
(CASE  
WHEN p.Surf = '' THEN '' else Concat(' Vlak ',p.Surf) END)

))  as char(254)) 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  
left join claimproc cp on p.procnum = cp.procnum 
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

