-- Function: pharm_billing_remove(p_id_invoice integer, p_id_billing integer)

-- DROP FUNCTION pharm_billing_remove(p_id_invoice integer, p_id_billing integer);

CREATE OR REPLACE FUNCTION pharm_billing_removeAll(p_id_invoice integer, p_id_billing integer)
 RETURNS integer AS

$BODY$
Declare
l_billing_invoice int;
l_id_patient   int;
l_id_invoice   int;
l_unitNumber   int;
BEGIN

Select  id_patient
 Into l_id_patient
From  pharm_invoice
Where id_invoice=p_id_invoice;
--- not found ---
If Not FOUND
Then
RAISE EXCEPTION 'Invoice [%] not found!', p_id_invoice;
End If;
--- check billing remainder ---
Select   id_invoice,
  unitNumber
   Into l_billing_invoice,
    l_unitNumber
From   pharm_Billing
Where   id_billing=p_id_billing;



If Not FOUND
Then
RAISE EXCEPTION 'Billing [%] not found!', p_id_billing;
End If;


If l_billing_invoice<>p_id_invoice
Then
RAISE EXCEPTION 'Billing [%] not applied to invoice [%]!', p_id_billing,p_id_invoice;
End If;


If l_unitNumber<= 0 
Then
RAISE EXCEPTION 'Billing [%] dont have units!', p_id_billing;
ELSE(

 Delete
From  pharm_InvoiceItems
Where  id_billing=p_id_billing;

Delete
From  pharm_billing
Where  id_billing=p_id_billing;
)

End If;





If Not Exists(
 Select   1 
 From pharm_Billing
 Where id_invoice=p_id_invoice
 ) And
Not Exists(
 Select   1 
 From pharm_InvoiceOperations
 Where id_invoice=p_id_invoice
 )
Then

Update  pharm_Invoice
Set  Status ='D'
Where id_invoice=p_id_invoice;
End If;  

Return l_id_invoice;
END;
$BODY$
 LANGUAGE 'plpgsql';
ALTER FUNCTION pharm_billing_removeAll(p_id_invoice integer, p_id_billing integer) OWNER TO root;