pears.NetOwnerAccountEdit [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.NetOwnerAccountEdit ======
<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"."NetOwnerAccountEdit"( in "pWebUserID" char(20),in "pClient_Code" char(12),in "pObjID" char(20),in "pParentObject" char(20),in "pAccount_Name" char(60),in "pAddress_Line_1" char(40),in "pAddress_Line_2" char(40),in "pAddress_Line_3" char(40),in "pTown" char(30),in "pCounty" char(30),in "pCountry" char(30),in "pPostcode" char(20),in "pPhone" char(100),in "pFax" char(100),in "pWeb_Site" char(100),in "pOldClient_Code" char(12),in "pOldAccount_Name" char(60),in "pOldAddress_Line_1" char(40),in "pOldAddress_Line_2" char(40),in "pOldAddress_Line_3" char(40),in "pOldTown" char(30),in "pOldCounty" char(30),in "pOldCountry" char(30),in "pOldPostcode" char(20),in "pOldPhone" char(100),in "pOldFax" char(100),in "pOldWeb_Site" char(100) ) 
result( "pResult" char(250) ) 
begin
  declare @CompID char(20);
  declare "ssql" char(255);
  declare @AccountCode char(12);
  declare @DivisionID char(20);
  set "ssql" = '';
  set @DivisionID = (select "staff"."divisionid" from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
  if @DivisionID is null then
    select '99:~Permission denied';
    return
  end if;
  if "pAccount_Name" = '' then
    select '101:~Please ensure there is a Company Name';
    return
  end if;
  if "pObjID" = '' then
    set @CompID = "uniquekey"("pAccount_Name");
    if "pClient_Code" is null or "pClient_Code" = 'New' then
      select '102:~Please ensure there is a valid Account Code';
      return
    end if;
    if "pClient_Code" = any(select "clientcode" from "company") then
      select '103:~Please use a unique Account Code';
      return
    end if;
    set @AccountCode = "pClient_Code";
    insert into "company"( "companyid","name","keyname","clientcode","invoiceaddress","divisionid" ) values( @CompID,"pAccount_Name","ucase"("pAccount_Name"),@AccountCode,'1',@DivisionID ) 
  else
    set @CompID = "pObjID";
    if "pAccount_Name" <> "pOldAccount_Name" then
      set "ssql" = "ssql"+',name=pAccount_Name,keyname = ucase(pAccount_Name)'
    end if end if;
  if "pAddress_Line_1" <> "pOldAddress_Line_1" then
    set "ssql" = "ssql"+',addr1=pAddress_Line_1'
  end if;
  if "pAddress_Line_2" <> "pOldAddress_Line_2" then
    set "ssql" = "ssql"+',addr2=pAddress_Line_2'
  end if;
  if "pAddress_Line_3" <> "pOldAddress_Line_3" then
    set "ssql" = "ssql"+',addr3=pAddress_Line_3'
  end if;
  if "pTown" <> "pOldTown" then
    set "ssql" = "ssql"+',town=pTown'
  end if;
  if "pCounty" <> "pOldCounty" then
    set "ssql" = "ssql"+',county=pCounty'
  end if;
  if "pCountry" <> "pOldCountry" then
    set "ssql" = "ssql"+',country=pCountry'
  end if;
  if "pPostcode" <> "pOldPostcode" then
    set "ssql" = "ssql"+',Postcode=pPostcode'
  end if;
  if "ssql" > '' then
    execute immediate 'update company set '+"stuff"("ssql",1,1,'')+' where companyid = @CompID'
  end if;
  if "pPhone" <> "pOldPhone" then
    call "SetPhone"('C','Telephone',@CompID,"pPhone")
  end if;
  if "pFax" <> "pOldFax" then
    call "SetPhone"('C','Fax',@CompID,"pFax")
  end if;
  if "pWeb_Site" <> "pOldWeb_Site" then
    call "SetPhone"('C','Web Site',@CompID,"pWeb_Site")
  end if;
  select "string"('0:',@CompID,'#',@AccountCode,'~Success')
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAccountEdit" IS 
{create PROCEDURE pears."NetOwnerAccountEdit"( in "pWebUserID" char(20),in "pClient_Code" char(12),in "pObjID" char(20),in "pParentObject" char(20),in "pAccount_Name" char(60),in "pAddress_Line_1" char(40),in "pAddress_Line_2" char(40),in "pAddress_Line_3" char(40),in "pTown" char(30),in "pCounty" char(30),in "pCountry" char(30),in "pPostcode" char(20),in "pPhone" char(100),in "pFax" char(100),in "pWeb_Site" char(100),in "pOldClient_Code" char(12),in "pOldAccount_Name" char(60),in "pOldAddress_Line_1" char(40),in "pOldAddress_Line_2" char(40),in "pOldAddress_Line_3" char(40),in "pOldTown" char(30),in "pOldCounty" char(30),in "pOldCountry" char(30),in "pOldPostcode" char(20),in "pOldPhone" char(100),in "pOldFax" char(100),in "pOldWeb_Site" char(100) ) 
result( "pResult" char(250) ) 
begin
  declare @CompID char(20);
  declare "ssql" char(255);
  declare @AccountCode char(12);
  declare @DivisionID char(20);
  set "ssql" = '';
  set @DivisionID = (select "staff"."divisionid" from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
  if @DivisionID is null then
    select '99:~Permission denied';
    return
  end if;
  if "pAccount_Name" = '' then
    select '101:~Please ensure there is a Company Name';
    return
  end if;
  if "pObjID" = '' then
    set @CompID = "uniquekey"("pAccount_Name");
    if "pClient_Code" is null or "pClient_Code" = 'New' then
      select '102:~Please ensure there is a valid Account Code';
      return
    end if;
    if "pClient_Code" = any(select "clientcode" from "company") then
      select '103:~Please use a unique Account Code';
      return
    end if;
    set @AccountCode = "pClient_Code";
    insert into "company"( "companyid","name","keyname","clientcode","invoiceaddress","divisionid" ) values( @CompID,"pAccount_Name","ucase"("pAccount_Name"),@AccountCode,'1',@DivisionID ) 
  else
    set @CompID = "pObjID";
    if "pAccount_Name" <> "pOldAccount_Name" then
      set "ssql" = "ssql"+',name=pAccount_Name,keyname = ucase(pAccount_Name)'
    end if end if;
  if "pAddress_Line_1" <> "pOldAddress_Line_1" then
    set "ssql" = "ssql"+',addr1=pAddress_Line_1'
  end if;
  if "pAddress_Line_2" <> "pOldAddress_Line_2" then
    set "ssql" = "ssql"+',addr2=pAddress_Line_2'
  end if;
  if "pAddress_Line_3" <> "pOldAddress_Line_3" then
    set "ssql" = "ssql"+',addr3=pAddress_Line_3'
  end if;
  if "pTown" <> "pOldTown" then
    set "ssql" = "ssql"+',town=pTown'
  end if;
  if "pCounty" <> "pOldCounty" then
    set "ssql" = "ssql"+',county=pCounty'
  end if;
  if "pCountry" <> "pOldCountry" then
    set "ssql" = "ssql"+',country=pCountry'
  end if;
  if "pPostcode" <> "pOldPostcode" then
    set "ssql" = "ssql"+',Postcode=pPostcode'
  end if;
  if "ssql" > '' then
    execute immediate 'update company set '+"stuff"("ssql",1,1,'')+' where companyid = @CompID'
  end if;
  if "pPhone" <> "pOldPhone" then
    call "SetPhone"('C','Telephone',@CompID,"pPhone")
  end if;
  if "pFax" <> "pOldFax" then
    call "SetPhone"('C','Fax',@CompID,"pFax")
  end if;
  if "pWeb_Site" <> "pOldWeb_Site" then
    call "SetPhone"('C','Web Site',@CompID,"pWeb_Site")
  end if;
  select "string"('0:',@CompID,'#',@AccountCode,'~Success')
end
}
</code>