********************************************************************************************************************
							-- ATTENTION

					-- ONLY TO BE EXECUTED IF GHA LICENSE is ON !!! 

					-- Procedure are referring to TT 24092 and TT 23711.

*******************************************************************************************************************


DROP PROCEDURE UpdateGHAAField;

CREATE OR REPLACE PROCEDURE UpdateGHAAField (ghaaid number, fields varchar2) IS
  dLastModified Date;
  key     ghaa.ghaa_id%type;
  street1 ghaa.ghaa_street1%type;
  street2 ghaa.ghaa_street2%type;
  street3 ghaa.ghaa_street3%type;
  zip     ghaa.ghaa_zip%type;
  city    ghaa.ghaa_city%type;
  dml_str varchar2(200);
  dml_updflds varchar2(200);
BEGIN
   select ghaa_street1, ghaa_street2, ghaa_street3, ghaa_zip, ghaa_city, nvl(LastModified, sysdate) into street1, street2, street3, zip, city, dLastModified from ghaa_xadr_alternate_view v where v.ghaa_id=ghaaid;
   
   -- check if ghaa_id exists. if exist >UPDATE. if not, the system returns: NO_DATA_FOUND > INSERT
   select ghaa_id into key from ghaa where ghaa_id=ghaaid;
   --update ghaa set ghaa_street1=street1, ghaa_street2=street2, ghaa_street3=street3, ghaa_zip=zip, ghaa_city=city, LastModified=dLastModified where ghaa_id=ghaaid;
   
   if fields is not null then 
     dml_updflds := null;
     if instr(fields, 'ghaa_street1',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghaa_street1 ='''||street1||'''';
     end if;
     if instr(fields, 'ghaa_street2',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghaa_street2 ='''||street2||'''';
     end if;
     if instr(fields, 'ghaa_street3',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghaa_street3 ='''||street3||'''';
     end if;
     if instr(fields, 'ghaa_zip',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghaa_zip ='''||zip||'''';
     end if;
     if instr(fields, 'ghaa_city',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghaa_city ='''||city||'''';
     end if;
  
     dml_str := 'UPDATE ghaa SET ' || dml_updflds || ' WHERE ghaa_id = :ghaa_id';
     execute immediate dml_str using ghaaid;
   end if;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       insert into ghaa(ghaa_id, ghaa_street1, ghaa_street2, ghaa_street3, ghaa_zip, ghaa_city, LastModified) VALUES (ghaaid , street1, street2, street3, zip, city, dLastModified);
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END UpdateGHAAField;
/


DROP PROCEDURE UpdateGHAGField;

CREATE OR REPLACE PROCEDURE UpdateGHAGField (ghagid number, fields varchar2) IS
  dlastmodified date;
  key    ghag.ghag_id%type;
  wlanid ghag.ghag_wlan_id%type;
  name1  ghag.ghag_name1%type;
  name2  ghag.ghag_name2%type;
  name3  ghag.ghag_name3%type;
  dml_str varchar2(200);
  dml_updflds varchar2(200);
begin
   select ghag_wlan_id, ghag_name1, ghag_name2, ghag_name3, nvl(lastmodified, sysdate) into wlanid, name1, name2, name3, dlastmodified from ghag_xcms_alternate_view v where v.ghag_id=ghagid;
   
   -- check if ghag_id exists. if exist >UPDATE. if not, the system returns: NO_DATA_FOUND > INSERT
   select ghag_id into key from ghag where ghag_id=ghagid;
   --update ghag set ghag_name1=name1, ghag_name2=name2, ghag_name3=name3, ghag_wlan_id=wlanid, LastModified=dLastModified where ghag_id=ghagid;
  
   if fields is not null then 
     dml_updflds := null;
     if instr(fields, 'ghag_name1',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghag_name1 ='''||name1||'''';
     end if;
     if instr(fields, 'ghag_name2',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghag_name2 ='''||name2||'''';
     end if;
     if instr(fields, 'ghag_name3',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghag_name3 ='''||name3||'''';
     end if;
     if instr(fields, 'ghag_wlan_id',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghag_wlan_id ='||to_char(wlanid);
     end if;
  
     dml_str := 'UPDATE ghag SET ' || dml_updflds || ' WHERE ghag_id = :ghag_id';
     execute immediate dml_str using ghagid;
   end if;
   
   exception
     when no_data_found then
       insert into ghag(ghag_id, ghag_wlan_id, ghag_name1, ghag_name2, ghag_name3, lastmodified) values (ghagid , wlanid, name1, name2, name3, dlastmodified);
     when others then
       -- Consider logging the error and then re-raise
       raise;
END UpdateGHAGField;
/
