drop procedure pmr_trn_invoicesummary;
 
DELIMITER $$ 
CREATE DEFINER=`root`@`localhost` PROCEDURE `pmr_trn_invoicesummary`() 
BEGIN 
   select distinct a.invoice_gid,a.agreement_gid, 
       CASE WHEN a.invoice_refno IS NULL OR a.invoice_refno = '' THEN a.invoice_gid ELSE a.invoice_refno END AS invoice_refno, CASE  
       WHEN a.invoice_status = 'IV Approved' THEN 'Invoice Completed'  ELSE REPLACE(a.invoice_status, 'IV', 'Invoice') END as invoice_status, a.invoice_flag,   
                  concat(c.contactperson_name,'/',c.email_id,'/',c.contact_telephonenumber) as contact,CASE when a.payment_flag <> 'PY Pending' then a.payment_flag   
                  else a.invoice_flag end as 'overall_status', format(a.initialinvoice_amount,2) as initialinvoice_amount,   
                  concat(c.vendor_code, ' / ',c.vendor_companyname) as Vendor,  
                  a.vendor_gid, format(a.invoice_amount,2) as invoice_amount,a.vendorinvoiceref_no, a.vendor_gid, i.costcenter_gid,   
                  CASE WHEN i.costcenter_gid is NOT NULL THEN   
                  (select costcenter_name from pmr_mst_tcostcenter x where h.costcenter_gid=x.costcenter_gid)   
                  ELSE (select costcenter_name from pmr_mst_tcostcenter y where j.costcenter_gid=y.costcenter_gid) END as costcenter_name,   
                   DATE_FORMAT(a.invoice_date, '%d-%m-%Y') AS invoice_date,  
                  DATE_FORMAT(a.payment_date, '%d-%m-%Y') AS payment_date,  
                  a.payment_flag,a.invoice_type,   
                  c.vendor_code, c.vendor_companyname,a.invoice_from,a.invoice_reference   
                  from acp_trn_tinvoice a   
                  left join acp_trn_tinvoicedtl b on a.invoice_gid = b.invoice_gid   
                  left join acp_mst_tvendor c on a.vendor_gid = c.vendor_gid   
                  left join acp_trn_tpo2invoice g on g.invoice_gid=a.invoice_gid   
                  left join pmr_trn_tpurchaseorder h on g.purchaseorder_gid=h.purchaseorder_gid   
                  left join pmr_mst_tcostcenter i on h.costcenter_gid=i.costcenter_gid   
                 left join pbl_trn_tserviceorder j on j.serviceorder_gid=a.invoice_reference  
                  where a.invoice_type<>'Opening Invoice'    
                  order by date(a.invoice_date) desc,a.invoice_date asc, a.invoice_gid desc ; 
END$$ 
DELIMITER ;