pears.NetOwnerAccountDetails

CREATE PROCEDURE "pears"."NetOwnerAccountDetails"( IN "pWebUserID" CHAR(20),IN "pAccountID" CHAR(20) ) 
RESULT( "Client_Code" CHAR(12),"ObjID" CHAR(20),"Company_Name" CHAR(60),"Address_Line_1" CHAR(40),"Address_Line_2" CHAR(40),"Address_Line_3" CHAR(40),"Town" CHAR(30),"County" CHAR(30),"Country" CHAR(30),"Postcode" CHAR(20),"Phone" CHAR(100),"Fax" CHAR(100),"Web_Site" CHAR(100) ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pAccountID",'') = '' THEN -- return empty recordset for "new" record
    SELECT '','','','','','','','','','','',''
  ELSE -- if can't find pAccountID as clientcode then drop through and look for it as companyid
    IF EXISTS(SELECT * FROM "company" WHERE "clientcode" = "pAccountID" AND "InvoiceAddress" = 1 AND "divisionid" = "divid") THEN
      SELECT "clientcode" AS "Client_Code",
        "Company"."companyid" AS "ObjID",
        "Company"."Name" AS "Company_Name",
        "Company"."addr1" AS "Address_Line_1",
        "Company"."addr2" AS "Address_Line_2",
        "Company"."addr3" AS "Address_Line_3",
        "Company"."town" AS "Town",
        "Company"."county" AS "County",
        "Company"."country" AS "Country",
        "Company"."postcode" AS "Postcode",
        "getphone"('C','Telephone',"Company"."companyid") AS "Phone",
        "getphone"('C','Fax',"Company"."companyid") AS "Fax",
        "getphone"('C','Web Site',"Company"."companyid") AS "Web_Site"
        FROM "company"
        WHERE "clientcode" = "pAccountID" AND "InvoiceAddress" = 1 AND "divisionid" = "divid"
    ELSE -- needed because tree uses clientcode, but form uses companyid
      SELECT "clientcode" AS "Client_Code",
        "Company"."companyid" AS "ObjID",
        "Company"."Name" AS "Company_Name",
        "Company"."addr1" AS "Address_Line_1",
        "Company"."addr2" AS "Address_Line_2",
        "Company"."addr3" AS "Address_Line_3",
        "Company"."town" AS "Town",
        "Company"."county" AS "County",
        "Company"."country" AS "Country",
        "Company"."postcode" AS "Postcode",
        "getphone"('C','Telephone',"Company"."companyid") AS "Phone",
        "getphone"('C','Fax',"Company"."companyid") AS "Fax",
        "getphone"('C','Web Site',"Company"."companyid") AS "Web_Site"
        FROM "company"
        WHERE "companyid" = "pAccountID" AND "divisionid" = "divid"
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAccountDetails" IS 
{CREATE PROCEDURE pears."NetOwnerAccountDetails"( IN "pWebUserID" CHAR(20),IN "pAccountID" CHAR(20) ) 
RESULT( "Client_Code" CHAR(12),"ObjID" CHAR(20),"Company_Name" CHAR(60),"Address_Line_1" CHAR(40),"Address_Line_2" CHAR(40),"Address_Line_3" CHAR(40),"Town" CHAR(30),"County" CHAR(30),"Country" CHAR(30),"Postcode" CHAR(20),"Phone" CHAR(100),"Fax" CHAR(100),"Web_Site" CHAR(100) ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pAccountID",'') = '' THEN -- return empty recordset for "new" record
    SELECT '','','','','','','','','','','',''
  ELSE -- if can't find pAccountID as clientcode then drop through and look for it as companyid
    IF EXISTS(SELECT * FROM "company" WHERE "clientcode" = "pAccountID" AND "InvoiceAddress" = 1 AND "divisionid" = "divid") THEN
      SELECT "clientcode" AS "Client_Code",
        "Company"."companyid" AS "ObjID",
        "Company"."Name" AS "Company_Name",
        "Company"."addr1" AS "Address_Line_1",
        "Company"."addr2" AS "Address_Line_2",
        "Company"."addr3" AS "Address_Line_3",
        "Company"."town" AS "Town",
        "Company"."county" AS "County",
        "Company"."country" AS "Country",
        "Company"."postcode" AS "Postcode",
        "getphone"('C','Telephone',"Company"."companyid") AS "Phone",
        "getphone"('C','Fax',"Company"."companyid") AS "Fax",
        "getphone"('C','Web Site',"Company"."companyid") AS "Web_Site"
        FROM "company"
        WHERE "clientcode" = "pAccountID" AND "InvoiceAddress" = 1 AND "divisionid" = "divid"
    ELSE -- needed because tree uses clientcode, but form uses companyid
      SELECT "clientcode" AS "Client_Code",
        "Company"."companyid" AS "ObjID",
        "Company"."Name" AS "Company_Name",
        "Company"."addr1" AS "Address_Line_1",
        "Company"."addr2" AS "Address_Line_2",
        "Company"."addr3" AS "Address_Line_3",
        "Company"."town" AS "Town",
        "Company"."county" AS "County",
        "Company"."country" AS "Country",
        "Company"."postcode" AS "Postcode",
        "getphone"('C','Telephone',"Company"."companyid") AS "Phone",
        "getphone"('C','Fax',"Company"."companyid") AS "Fax",
        "getphone"('C','Web Site',"Company"."companyid") AS "Web_Site"
        FROM "company"
        WHERE "companyid" = "pAccountID" AND "divisionid" = "divid"
    END IF
  END IF
END
}