Show quotes or estimates that HAVE been converted to a bill.
Conversions are only recorded from v7.1.1
Create as export spreadsheet so the can export to csv
Conversions are only recorded from v7.1.1
Create as export spreadsheet so the can export to csv
select BM."Ref_Number" "Transaction Num",
case when BM."Type" = 7 then 'Quote'
else 'Estimate' end "Bill Type",
CM."First_Name" "First Name",
CM."Last_Name" "Last Name",
(select LIST(DISTINCT AM."Name")
from "Bill_Items" BI
join "Animal_Main" AM on AM."Ref_Number" = BI."Itm_Sub"
where BI."Bil_Number" = BM."Ref_Number") "Patients",
BM."Total" "Total",
BM."Dis_Date" "Creation Date",
SM."Id" "Staff"
from "Bill_Main" BM
join "Client_Main" CM on CM."Ref_Number" = BM."Cln_Number"
join "Staff_Main" SM on SM."Ref_Number" = BM."Cur_Staff"
where BM."Dis_Date" between :Datelo/*Created From*/ and :Datehi/*To*/
and BM."Type" in (7,8)
and "InBitValue" (512, "Bill_Prop") = 'Y'
and BM."Acc_State" <> 59
case when BM."Type" = 7 then 'Quote'
else 'Estimate' end "Bill Type",
CM."First_Name" "First Name",
CM."Last_Name" "Last Name",
(select LIST(DISTINCT AM."Name")
from "Bill_Items" BI
join "Animal_Main" AM on AM."Ref_Number" = BI."Itm_Sub"
where BI."Bil_Number" = BM."Ref_Number") "Patients",
BM."Total" "Total",
BM."Dis_Date" "Creation Date",
SM."Id" "Staff"
from "Bill_Main" BM
join "Client_Main" CM on CM."Ref_Number" = BM."Cln_Number"
join "Staff_Main" SM on SM."Ref_Number" = BM."Cur_Staff"
where BM."Dis_Date" between :Datelo/*Created From*/ and :Datehi/*To*/
and BM."Type" in (7,8)
and "InBitValue" (512, "Bill_Prop") = 'Y'
and BM."Acc_State" <> 59