database:procedures:pears_netagencycontactset



pears.NetAgencyContactSet

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE PROCEDURE "pears"."NetAgencyContactSet"( IN "pWebUserID" CHAR(20),IN "pemploymentid" CHAR(20),IN "pdirectphone" CHAR(250) DEFAULT 'NOT_SET',IN "pmobile" CHAR(250) DEFAULT 'NOT_SET',IN "pemail" CHAR(250) DEFAULT 'NOT_SET',IN "pdirectfax" CHAR(250) DEFAULT 'NOT_SET',IN "pcascademethod" CHAR(1) DEFAULT '$',IN "pcascadeaddress" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDdirectphone" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDmobile" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDemail" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDdirectfax" CHAR(250) DEFAULT 'NOT_SET',IN "pOLDcascademethod" CHAR(1) DEFAULT '$',IN "pOLDcascadeaddress" CHAR(250) DEFAULT 'NOT_SET' ) 
RESULT( "pResult" CHAR(250) ) 
//IQXWeb
BEGIN
  IF NOT "pemploymentid" = any(SELECT "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF "isnull"("pcascademethod",'') <> "isnull"("pOLDcascademethod",'') OR "isnull"("pcascadeaddress",'') <> "isnull"("pOLDcascadeaddress",'') THEN
    SET "pcascademethod" = "nullif"("pcascademethod",'$');
    SET "pcascadeaddress" = "nullif"("pcascadeaddress",'NOT_SET');
    INSERT INTO "cascadedeliveryaddress"( "companyid","deliverymethod" ) SELECT DISTINCT "employment"."companyid",'E' FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"
        AND NOT "employment"."companyid" = any(SELECT "companyid" FROM "cascadedeliveryaddress");
    UPDATE "cascadedeliveryaddress" SET "deliverymethod" = "isnull"("ucase"("pcascademethod"),"deliverymethod"),"address" = "isnull"("pcascadeaddress","address") WHERE "companyid"
       = any(SELECT "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
  END IF;
  IF "isnull"("pdirectphone",'') <> "isnull"("pOLDdirectphone",'') THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Direct Telephone',"pdirectphone")
  END IF;
  IF "isnull"("pmobile",'') <> "isnull"("pOLDmobile",'') THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Mobile',"pmobile")
  END IF;
  IF "isnull"("pemail",'') <> "isnull"("pOLDemail",'') THEN
    UPDATE "iqxnetuserlink" KEY JOIN "iqxnetuser" SET "iqxnetuser"."emailaddress" = "pemail" WHERE "iqxnetuserlink"."employmentid" = "pemploymentid";
    CALL "IQXNetSetPhones"("pWebUserID",'CP','E-mail',"pemail")
  END IF;
  IF "isnull"("pdirectfax",'') <> "isnull"("pOLDdirectfax",'') THEN
    CALL "IQXNetSetPhones"("pWebUserID",'CP','Direct Fax',"pdirectfax")
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-10-10 PC doc and test
2018-04-23 PC revise tests
 
Tests:
Direct Phone
Mobile
Email
Fax
CascadeMethod,CascadeAddress
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from phone where phoneid like 'test%';
update iqxnetuser set emailaddress=null where iqxnetuserid='TEST.AGENCY';
delete from CascadeDeliveryAddress where companyid='TEST';
select * from NetAgencyContactSet('test.agency','TEST','0999 999 990',null,null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,'0999 999 991',null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,'[email protected]',null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected];
expect EOF;
select emailaddress from iqxnetuser where iqxnetuserid='TEST.AGENCY';
expect [email protected];
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,'0999 999 992',null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected],df=0999!999!992;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,null,'E','[email protected]',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected],df=0999!999!992;
expect EOF;
select DeliveryMethod,Address from cascadedeliveryaddress where CompanyID='TEST';
expect DeliveryMethod=E,[email protected];
expect EOF;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyContactSet" IS 
{CREATE PROCEDURE pears."NetAgencyContactSet"(IN pWebUserID CHAR(20),IN pemploymentid CHAR(20),IN pdirectphone CHAR(250) DEFAULT 'NOT_SET',IN pmobile CHAR(250) DEFAULT 'NOT_SET',IN pemail CHAR(250) DEFAULT 'NOT_SET',IN pdirectfax CHAR(250) DEFAULT 'NOT_SET',IN pcascademethod CHAR(1) DEFAULT '$',IN pcascadeaddress CHAR(250) DEFAULT 'NOT_SET',IN pOLDdirectphone CHAR(250) DEFAULT 'NOT_SET',IN pOLDmobile CHAR(250) DEFAULT 'NOT_SET',IN pOLDemail CHAR(250) DEFAULT 'NOT_SET',IN pOLDdirectfax CHAR(250) DEFAULT 'NOT_SET',IN pOLDcascademethod CHAR(1) DEFAULT '$',IN pOLDcascadeaddress CHAR(250) DEFAULT 'NOT_SET')
RESULT(pResult CHAR(250))
//IQXWeb
BEGIN
  IF NOT pemploymentid = any(SELECT employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid) THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  IF isnull(pcascademethod,'') <> isnull(pOLDcascademethod,'') OR isnull(pcascadeaddress,'') <> isnull(pOLDcascadeaddress,'') THEN
    SET pcascademethod=NULLIF(pcascademethod,'$');
    SET pcascadeaddress=NULLIF(pcascadeaddress,'NOT_SET');
    INSERT INTO cascadedeliveryaddress( companyid,deliverymethod) SELECT DISTINCT employment.companyid,'E' FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID
        AND NOT employment.companyid = any(SELECT companyid FROM cascadedeliveryaddress);
    UPDATE cascadedeliveryaddress SET deliverymethod = isnull(ucase(pcascademethod),deliverymethod),address = isnull(pcascadeaddress,address) WHERE companyid
       = any(SELECT employment.companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID)
  END IF;
  IF isnull(pdirectphone,'') <> isnull(pOLDdirectphone,'') THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Direct Telephone',pdirectphone)
  END IF;
  IF isnull(pmobile,'') <> isnull(pOLDmobile,'') THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Mobile',pmobile)
  END IF;
  IF isnull(pemail,'') <> isnull(pOLDemail,'') THEN
    UPDATE iqxnetuserlink KEY JOIN iqxnetuser SET iqxnetuser.emailaddress = pemail WHERE iqxnetuserlink.employmentid = pemploymentid;
    CALL IQXNetSetPhones(pWebUserID,'CP','E-mail',pemail)
 
  END IF;
  IF isnull(pdirectfax,'') <> isnull(pOLDdirectfax,'') THEN
    CALL IQXNetSetPhones(pWebUserID,'CP','Direct Fax',pdirectfax)
  END IF;
  SELECT '0:~Success'
END
 
/* DOC
2016-10-10 PC doc and test
2018-04-23 PC revise tests
 
Tests:
    Direct Phone
    Mobile
    Email
    Fax
    CascadeMethod,CascadeAddress
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from phone where phoneid like 'test%';
update iqxnetuser set emailaddress=null where iqxnetuserid='TEST.AGENCY';
delete from CascadeDeliveryAddress where companyid='TEST';
select * from NetAgencyContactSet('test.agency','TEST','0999 999 990',null,null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,'0999 999 991',null,null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,'[email protected]',null,null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected];
expect EOF;
select emailaddress from iqxnetuser where iqxnetuserid='TEST.AGENCY';
expect [email protected];
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,'0999 999 992',null,null,null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected],df=0999!999!992;
expect EOF;
select * from NetAgencyContactSet('test.agency','TEST',null,null,null,null,'E','[email protected]',null,null,null,null,null,null);
expect 0:~Success;
select replace(directphone,' ','!') as DP,replace(mobile,' ','!') as mob,email,replace(directfax,' ','!') as df from netAgencyContact('test.agency');
expect DP=0999!999!990,mob=0999!999!991,[email protected],df=0999!999!992;
expect EOF;
select DeliveryMethod,Address from cascadedeliveryaddress where CompanyID='TEST';
expect DeliveryMethod=E,[email protected];
expect EOF;
*/
}
  • database/procedures/pears_netagencycontactset.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1