pears.NetOwnerAccountDetails [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetOwnerAccountDetails ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>