Anonymous Blocks

do (  OUT EX_TOP_3_EMP_PO_COMBINED_CNT TABLE(
              FULLNAME nvarchar(256),
              CREATE_CNT INTEGER,
              CHANGE_CNT INTEGER,
              COMBINED_CNT INTEGER ) => ? )
 begin

po_create_cnt =  SELECT COUNT(*) AS CREATE_CNT, "CREATEDBY" as EID
     FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
            GROUP BY  "CREATEDBY";

po_change_cnt = SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY" as EID
     FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                     SELECT "POHEADER_ID"
                          FROM "OPENSAP_PURCHASEORDER_ITEMS"
          WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
             GROUP BY  "MODIFIEDBY";

EX_TOP_3_EMP_PO_COMBINED_CNT =
        SELECT "get_full_name"( "NAMEFIRST", "NAMEMIDDLE", "NAMELAST") as FULLNAME,
         crcnt.CREATE_CNT, chcnt.CHANGE_CNT,  crcnt.CREATE_CNT +
         chcnt.CHANGE_CNT AS COMBINED_CNT
            FROM "OPENSAP_MD_EMPLOYEES" as emp
            LEFT OUTER JOIN :PO_CREATE_CNT AS crcnt
             ON emp.email = crcnt.EID
           LEFT OUTER JOIN :PO_CHANGE_CNT AS chcnt
           ON emp.email = chcnt.EID
          ORDER BY COMBINED_CNT DESC LIMIT 3;

end;