-- !!!PLEASE RUN ONLY ONCE IN CASE GHA LICENSE IS ENABLED!!!

 create or replace view ghaa_xadr_alternate_view
as
select
  xadr_id ghaa_id
  --,xadr_street1 ghaa_street1
 ,v8_GHA_translate(xadr_street1, 60) ghaa_street1
  --,xadr_street2 ghaa_street2
 ,v8_GHA_translate(xadr_street2, 60) ghaa_street2
  --,xadr_street3 ghaa_street3
 ,v8_GHA_translate(xadr_street3, 60) ghaa_street3
 ,v8_GHA_translate(xadr_zip, 15) ghaa_zip
  --,xadr_city ghaa_city
 ,v8_GHA_translate(xadr_city, 60) ghaa_city
 ,(select WMLG.WMLG_TIME_UPDATE from wmlg where WMLG.WMLG_TABLENAME ='xadr' and WMLG.WMLG_TABLE_ID = xadr_id AND ROWNUM = 1) LastModified
from xadr;

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 ='''||Replace(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 ='''|| Replace(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 ='''||Replace(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 ='''||Replace(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 ='''||Replace(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;
/
create or replace view ghad_xcid_alternate_view
as
select
  xcid_id ghad_id
 ,v8_GHA_translate(xcid_title, 30) ghad_title
 ,(select x2.xcag_longdesc from xcag x1, xcag x2 where x1.xcag_translation_id = x2.xcag_translation_id and x1.xcag_longdesc=xcid_addrgreet and x2.xcag_wlan_id=ghag_wlan_id and rownum=1) ghad_addrgreet
 ,(select x2.xcag_longdesc from xcag x1, xcag x2 where x1.xcag_translation_id = x2.xcag_translation_id and x1.xcag_longdesc=xcid_lettgreet   and x2.xcag_wlan_id=ghag_wlan_id and rownum=1) ghad_lettgreet
 ,(select WMLG.WMLG_TIME_UPDATE from wmlg where WMLG.WMLG_TABLENAME ='xcid' and WMLG.WMLG_TABLE_ID = xcid_id AND ROWNUM = 1) LastModified
from xcid, ghag
where xcid_id=ghag_id;

CREATE OR REPLACE PROCEDURE UpdateGHADField (ghadid number, fields varchar2) IS
  dLastModified Date;
  key        ghad.ghad_id%type;
  title      ghad.ghad_title%type;
  addrgreet  ghad.ghad_addrgreet%type;
  lettgreet  ghad.ghad_lettgreet%type;
  dml_str varchar2(200);
  dml_updflds varchar2(200);
BEGIN
   select ghad_title, ghad_addrgreet, ghad_lettgreet, nvl(LastModified, sysdate) into title, addrgreet, lettgreet, dLastModified from ghad_xcid_alternate_view v where v.ghad_id=ghadid;
   
   -- check if ghad_id exists. if exist >UPDATE. if not, the system returns: NO_DATA_FOUND > INSERT
   select ghad_id into key from ghad where ghad_id=ghadid;
   --update ghad set ghad_title=title, ghad_addrgreet=addrgreet, ghad_lettgreet=lettgreet, LastModified=dLastModified where ghad_id=ghadid;

   if fields is not null then 
     dml_updflds := null;
     if instr(fields, 'ghad_title',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghad_title ='''||Replace(title,'''','''''')||'''';
     end if;
     if instr(fields, 'ghad_addrgreet',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghad_addrgreet ='''||Replace(addrgreet,'''','''''')||'''';
     end if;
     if instr(fields, 'ghad_lettgreet',1,1) > 0 then 
       if dml_updflds is not null then dml_updflds := dml_updflds || ', '; end if;
       dml_updflds := dml_updflds  || 'ghad_lettgreet ='''||Replace(lettgreet,'''','''''')||'''';
     end if;
  
     dml_str := 'UPDATE ghad SET ' || dml_updflds || ' WHERE ghad_id = :ghad_id';
     execute immediate dml_str using ghadid;
   end if;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       insert into ghad(ghad_id, ghad_title, ghad_addrgreet, ghad_lettgreet, LastModified) VALUES (ghadid , title, addrgreet, lettgreet, dLastModified);
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END UpdateGHADField;
/
