create or replace type body datasourcetool as
  constructor function datasourcetool return self as result as
  begin
    in_row_count        := 0;
    in_del_count        := 0;
    in_column_count     := 0;
    in_auto_addcolumn   := 1;
    return;
  end;

  constructor function datasourcetool(aclob_xml clob) return self as result as
  begin
    declare
      lnv_parser          xmlparser.parser;
      lnv_doc             xmldom.domdocument;
      lnv_noderow         xmldom.domnodelist;
      lnv_nodecol         xmldom.domnodelist;
      lnv_n               xmldom.domnode;
      lnv_a               xmldom.domnamednodemap;
      lnv_an              xmldom.domnode;
      ln_attrcount        number;
      lvc_colname         varchar2(30);
      lvc_coltype         varchar2(30);
      lvc_attrname        varchar2(15);
      ln_pkindex          number;
      ln_external         number;
      ln_rowstatus_id     number;
      ln_xml_rowcount     number;
      lvc_default_value   varchar2(50);
    begin
      in_row_count        := 0;
      in_column_count     := 0;
      in_del_count        := 0;
      in_auto_addcolumn   := 0;
      
      --สร้างตัวแปร xmlparser
      lnv_parser            := xmlparser.newparser;
      xmlparser.setvalidationmode (lnv_parser, false);
      --รับ argument ไปใส่ในตัวแปร xmlparser
      xmlparser.parseclob(lnv_parser, aclob_xml);
      
      --สร้าง dom document จาก xmlparser
      lnv_doc               := xmlparser.getdocument(lnv_parser);
      
      -- หาชื่อ table และดัก exception ไม่ให้ error
      begin
        lnv_n                 := xmldom.item(xmldom.getelementsbytagname(lnv_doc, 'iscxml_data_table'), 0);
        lnv_a                 := xmldom.getattributes(lnv_n);
        lnv_an                := xmldom.item(lnv_a, 0);
        ln_attrcount          := xmldom.getlength(lnv_a);
        for j in 1 .. ln_attrcount loop
          lnv_an              := xmldom.item(lnv_a, j - 1);
          lvc_attrname        :=  xmldom.getnodename(lnv_an);
          if lvc_attrname = 'table_name' then
            self.ivc_tablename := xmldom.getnodevalue(lnv_an);
          end if;
        end loop;
      exception
        when others then
          null;
      end;
      
      --ดึง nodelist ใน tag datarow ใช้อ้างถึง row ต่างๆ
      lnv_noderow           := xmldom.getelementsbytagname(lnv_doc, 'iscxml_data_row');
      --เซ็ตค่าจำนวน row ให้ตัวแปร 
      ln_xml_rowcount       := xmldom.getlength(lnv_noderow);
      --ดึง node จาก row ที่ 0
      lnv_n                 := xmldom.item(lnv_noderow, 0);
      --ดึง node column มาจาก row แรก
      lnv_nodecol           := xmldom.getchildnodes(lnv_n);
      --เซ็ตค่าจำนวน column ให้ตัวแปร self
      in_column_count       := xmldom.getlength(lnv_nodecol);
      --ประกาศตัวแปร column
      intb_column           := ntb_datacolumn();
      --ประกาศตัวแปร row 
      intb_data             := ntb_diminsion2();
      --ประกาศตัวแปร del row
      intb_deleted          := ntb_diminsion2();
      
      --ลูปเพื่อเซ็ตข้อมูลของ datacolumn
      for i in 0..(self.in_column_count -1) loop
        --init ค่าเริ่มต้นให้ datacolumn
        ln_attrcount := 0;
        lvc_coltype := 'string';
        ln_pkindex := 0;
        ln_external := 0;
        --หา node column จาก xml เตรียมดึงข้อมูลจาก xml
        lnv_n := xmldom.item(lnv_nodecol, i);
        lnv_a := xmldom.getattributes(lnv_n);
        lvc_colname := xmldom.getnodename(lnv_n);
        lvc_default_value := null;
        
        -- ตรวจสอบ attribute พิเศษ
        ln_attrcount := xmldom.getlength(lnv_a);
        for j in 1 .. ln_attrcount loop
          lnv_an := xmldom.item(lnv_a, j - 1);
          lvc_attrname :=  xmldom.getnodename(lnv_an);
          if lvc_attrname = 'type' then
            lvc_coltype := lower(xmldom.getnodevalue(lnv_an));
          elsif lvc_attrname = 'pk_index' then
            ln_pkindex := to_number(xmldom.getnodevalue(lnv_an));
          elsif lvc_attrname = 'external' then
            ln_external := to_number(xmldom.getnodevalue(lnv_an));
          elsif lvc_attrname = 'defalult' then
            lvc_default_value := to_number(xmldom.getnodevalue(lnv_an));
          end if;
        end loop;
        --นำข้อมูล ที่ได้มาเซ็ตใน datacolumn
        declare
          lstr_col str_datacolumn;
        begin
          lvc_colname := lower(lvc_colname);
          lstr_col := str_datacolumn(lvc_colname, lvc_coltype, ln_pkindex, ln_external, lvc_default_value);
          self.intb_column.extend;
          self.intb_column(i+1) := lstr_col;
        end;
      end loop;
      
      --หาตำแหน่ง row_status
      index_of('row_status', ln_rowstatus_id);
      --ถ้าไม่มี column row_status ก็สร้างขึ้นมาเองเลย
      if ln_rowstatus_id < 0 then
        declare
          lstr_col str_datacolumn;
        begin
          self.in_column_count := self.in_column_count + 1;
          lstr_col := str_datacolumn('row_status', 'string', 0, 1, 'none');
          self.intb_column.extend;
          self.intb_column(self.in_column_count) := lstr_col;
        end;
      end if;
      
      --ลูปเพื่อเซ็ตข้อมูลใส่ใน row
      for i_row in 0 .. (ln_xml_rowcount -1) loop
        declare
          --LSTR_ROW STR_DATAROW;
          ls_value varchar(999);
          lntb_999 ntb_vc999;
        begin
          --ดู attr ของ row ว่า del หรือ data
          --LN_XML_ROWCOUNT
          
          --ดึง node จาก row ที่ i_row LNV_NODEROW
          lnv_n := xmldom.item(lnv_noderow, i_row);
          lnv_a := xmldom.getattributes(lnv_n);
          lvc_default_value := '';
          
          -- ตรวจสอบ attribute พิเศษ
          ln_attrcount := xmldom.getlength(lnv_a);
          for j in 1 .. ln_attrcount loop
            lnv_an := xmldom.item(lnv_a, j - 1);
            lvc_attrname :=  xmldom.getnodename(lnv_an);
            if lvc_attrname = 'deleted' then
              lvc_default_value := lower(xmldom.getnodevalue(lnv_an));
            end if;
          end loop;
          
          ------------------------------ ROW ที่ถูกลบ --------------------------------
          if lvc_default_value = 'true' then
            in_del_count := in_del_count + 1;
            lntb_999 := ntb_vc999();
            intb_deleted.extend;
            intb_deleted(intb_deleted.last) := lntb_999;
            --ลูปตามจำนวน column
            for i_col in 0 .. (self.in_column_count - 1) loop
              --ถ้าลูป I_CAL เจอ row_status แบบสร้างเอง ให้กำหนดค่าใส่เองเป็น insert
              if i_col = (self.in_column_count - 1) and ln_rowstatus_id < 0 then
                ls_value := 'none';
              else
                --ถ้าเป็น column ปกติ
                ls_value := '';
                --ดึง node จาก row ที่ i_row
                lnv_n := xmldom.item(lnv_noderow, i_row);
                --ดึง node list column มาจาก row ข้างบน
                lnv_nodecol := xmldom.getchildnodes(lnv_n);
                --ดึง node column มาจาก node ข้างบน
                lnv_n := xmldom.item(lnv_nodecol, i_col);
                --เซ็ตค่าใส่ตัวแปร
                ls_value := xmldom.getnodevalue(xmldom.getfirstchild(lnv_n));
              end if;
              intb_deleted(in_del_count).extend;
              intb_deleted(in_del_count)(i_col + 1) := ls_value;
            end loop;
            goto endloop_i_row;
          end if;          
          ------------------------------ ROW ปกติ --------------------------------
          lntb_999 := ntb_vc999();
          --เตรียมแถวใส่ข้อมูล
          in_row_count := in_row_count + 1;
          intb_data.extend;
          intb_data(in_row_count) := lntb_999;
          --ลูปตามจำนวน column
          for i_col in 0 .. (self.in_column_count - 1) loop
            --ถ้าลูป I_CAL เจอ row_status แบบสร้างเอง ให้กำหนดค่าใส่เองเป็น insert
            if i_col = (self.in_column_count - 1) and ln_rowstatus_id < 0 then
              ls_value := 'none';
            else
            --ถ้าเป็น column ปกติ
              ls_value := '';
              --ดึง node จาก row ที่ i_row
              lnv_n := xmldom.item(lnv_noderow, i_row);
              --ดึง node list column มาจาก row ข้างบน
              lnv_nodecol := xmldom.getchildnodes(lnv_n);
              --ดึง node column มาจาก node ข้างบน
              lnv_n := xmldom.item(lnv_nodecol, i_col);
              --เซ็ตค่าใส่ตัวแปร
              ls_value := xmldom.getnodevalue(xmldom.getfirstchild(lnv_n));
            end if;
            self.intb_data(in_row_count).extend;
            self.intb_data(in_row_count)(i_col + 1) := ls_value;
            <<endloop_i_col>>
            null;
          end loop;
        end;
        <<endloop_i_row>>
        null;
      end loop;
      return;
    end;
  end;
  
  member procedure itemstringdeleted(an_row number, an_col number, avc_return out varchar2) as
  begin
    declare
      lvc_value varchar2(999);
    begin
      if an_col <= 0 then
        avc_return := null;
        return;
      end if;
      lvc_value := intb_deleted(an_row)(an_col);
      avc_return := lvc_value;
    exception when others then
      avc_return := null;
    end;
  end;
  
  member procedure itemstringdeleted(an_row number, avc_col varchar2, avc_return out varchar2) as
  begin
    declare
      ln_index number;
    begin
      index_of(avc_col, ln_index);
      itemstringdeleted(an_row, ln_index, avc_return);
    end;
  end;
  
  member procedure itemnumberdeleted(an_row number, an_col number, an_return out number) as
  begin
    declare
      lvc_value varchar2(200);
    begin
      if an_col <= 0 then
        an_return := null;
        return;
      end if;
      itemstringdeleted(an_row, an_col, lvc_value);
      lvc_value := replace(lvc_value, ',', '');
      an_return := to_number(lvc_value);
    exception when others then
      an_return := null;
    end;
  end;
  
  member procedure itemnumberdeleted(an_row number, avc_col varchar2, an_return out number) as
  begin
    declare
      ln_index number := -1;
    begin
      index_of(avc_col, ln_index);
      itemnumberdeleted(an_row, ln_index, an_return);
    end;
  end;
  
  member procedure itemdatetimedeleted(an_row number, an_col number, adtm_return out date) as
  begin
    declare
      lvc_value   varchar2(50);
      lvc_format  varchar2(25);
      ln_strlen   number;
      ln_year     number;
    begin
      if an_col <= 0 then
        adtm_return := null;
        return;
      end if;
      itemstringdeleted(an_row, an_col, lvc_value);
      if lvc_value is null then 
        adtm_return := null;
        return;
      end if;
      ln_strlen := length(lvc_value);
      if ln_strlen > 12 then
        lvc_format := 'yyyy-mm-dd hh24:mi:ss';
      else
        lvc_format := 'yyyy-mm-dd';
      end if;
      adtm_return := to_date(lvc_value, lvc_format);
      ln_year := extract(year from adtm_return);
      if ln_year <= 1700 then
        adtm_return := null;
      end if;
    exception when others then
      adtm_return := null;
    end;
  end;
  
  member procedure itemdatetimedeleted(an_row number, avc_col varchar2, adtm_return out date) as
  begin
    declare
      ln_index number := -1;
    begin
      index_of(avc_col, ln_index);
      itemdatetimedeleted(an_row, ln_index, adtm_return);
    end;
  end;
  
  member function getitemstring(an_row number, avc_col varchar2) return varchar2 as
  begin
    declare
      ln_index  number;
      lvc_value varchar2(999);
      lvc_col   varchar2(30);
    begin
      ln_index := -1;
      lvc_value := '';
      lvc_col := lower(avc_col);
      for i_col in 1 .. self.in_column_count loop
        if(self.intb_column(i_col).column_name = lvc_col) then
          ln_index := i_col;
        end if;
        exit when ln_index > 0;
      end loop;
      if ln_index <= 0 then
        return null;
      end if;
      lvc_value := self.intb_data(an_row)(ln_index);
      return to_char(lvc_value);
    exception when others then
      return null;
    end;
  end;
  
  member function getitemstring(an_row number, an_col number) return varchar2 as
  begin
    declare
      ln_index  number;
      lvc_value varchar2(999);
    begin
      if an_col <= 0 then
        return null;
      end if;
      ln_index := an_col;
      lvc_value := '';
      lvc_value := intb_data(an_row)(ln_index);
      return to_char(lvc_value);
    exception when others then
      return null;
    end;
  end;

  member function getitemnumber(an_row number, avc_col varchar2) return number as
  begin
    declare
      ln_index    number;
      lvc_value   varchar2(300);
      lr_return   real;
      lvc_col     varchar2(30);
    begin
      lr_return := 0;
      ln_index := -1;
      lvc_value := '';
      lvc_col := lower(avc_col);
      for i_col in 1 .. self.in_column_count loop
        if(self.intb_column(i_col).column_name = lvc_col) then
          ln_index := i_col;
        end if;
        exit when ln_index > 0;
      end loop;
      if ln_index <= 0 then
        return null;
      end if;
      lvc_value := self.intb_data(an_row)(ln_index);
      lvc_value := replace(lvc_value, ',', '');
      lr_return := to_number(lvc_value);
      return lr_return;
    exception when others then
      return null;
    end;
  end;
  
  member function getitemnumber(an_row number, an_col number) return number as
  begin
    declare
      ln_index    number;
      lvc_value   varchar2(30);
      lr_return   real;
    begin
      lr_return := 0;
      ln_index := an_col;
      lvc_value := '';
      
      if ln_index <= 0 then
        return null;
      end if;
      
      lvc_value := intb_data(an_row)(ln_index);
      lvc_value := replace(lvc_value, ',', '');
      lr_return := to_number(lvc_value);
      return lr_return;
    exception when others then
      return null;
    end;
  end;

  member function getitemdatetime(an_row number, avc_col varchar2) return date as
  begin
    declare
      ln_index    number;
      lvc_value   varchar2(30);
      ldtm_return date;
      ln_strlen   number;
      ln_year     number := 0;
      lvc_col     varchar2(30);
    begin
      ldtm_return := null;
      ln_index    := -1;
      lvc_value   := '';
      lvc_col     := lower(avc_col);
      for i_col in 1 .. self.in_column_count loop
        if(self.intb_column(i_col).column_name = lvc_col) then
          ln_index := i_col;
        end if;
        exit when ln_index > 0;
      end loop;
      if ln_index <= 0 then
        return null;
      end if;
      lvc_value := self.intb_data(an_row)(ln_index);
      ln_strlen := length(lvc_value);
      if ln_strlen > 12 then
        ldtm_return := to_date(lvc_value, 'yyyy-mm-dd hh24:mi:ss');
      else
        ldtm_return := to_date(lvc_value, 'yyyy-mm-dd');
      end if;
      ln_year := extract(year from ldtm_return);
      if ln_year <= 1700 then
        return null;
      end if;
      return ldtm_return;
    exception when others then
      return null;
    end;
  end;
  
  member function getitemdatetime(an_row number, an_col number) return date as
  begin
    declare
      ln_index    number;
      lvc_value   varchar2(30);
      ldtm_return date;
      ln_strlen   number;
      ln_year     number := 0;
    begin
      ldtm_return := null;
      ln_index := an_col;
      lvc_value := '';
      
      if ln_index <= 0 then
        return null;
      end if;
      
      lvc_value := intb_data(an_row)(ln_index);
      ln_strlen := length(lvc_value);
      if ln_strlen > 12 then
        ldtm_return := to_date(lvc_value, 'yyyy-mm-dd hh24:mi:ss');
      else
        ldtm_return := to_date(lvc_value, 'yyyy-mm-dd');
      end if;
      ln_year := extract(year from ldtm_return);
      if ln_year <= 1700 then
        return null;
      end if;
      return ldtm_return;
    exception when others then
      return null;
    end;
  end;

  member function rowcount return number as
  begin
    return in_row_count;
  end;

  member function columncount return number as
  begin
    return in_column_count;
  end;
  
  member function deletecount return number as
  begin
     return in_del_count;
  end;
  
  member procedure index_of(avc_col in varchar2, an_index out number) as
    lvc_collower varchar2(30);
  begin
    an_index := -1;
    lvc_collower := lower(avc_col);
    for i_col in 1 .. self.in_column_count loop      
      if(self.intb_column(i_col).column_name = lower(avc_col)) then
        an_index := i_col;
      end if;
      exit when an_index > 0;
    end loop;
    null;
  end;

  member procedure setitem(an_row in number, avc_col in varchar2, avc_value in varchar2) as
  begin
    declare
      ln_index number;
      lvc_collower varchar2(30);
    begin
      lvc_collower := lower(avc_col);
      ln_index := -1;
      self.index_of(lvc_collower, ln_index);
      self.auto_addcolumn(ln_index, lvc_collower, 'string');
      if ln_index > 0 then
        self.intb_data(an_row)(ln_index) := avc_value;
      end if;
    end;
  end;

  member procedure setitem(an_row in number, avc_col in varchar2, an_value in number) as
  begin
    declare
      ln_index number;
      lvc_collower varchar2(30);
    begin
      lvc_collower := lower(avc_col);
      ln_index := -1;
      self.index_of(lvc_collower, ln_index);
      self.auto_addcolumn(ln_index, lvc_collower, 'number');
      if ln_index > 0 then
        self.intb_data(an_row)(ln_index) := to_char(an_value);
      end if;
    end;
  end;

  member procedure setitem(an_row in number, avc_col in varchar2, adtm_value in date) as
  begin
    declare
      ln_index number;
      lvc_collower varchar2(30);
    begin
      lvc_collower := lower(avc_col);
      ln_index := -1;
      self.index_of(lvc_collower, ln_index);
      self.auto_addcolumn(ln_index, lvc_collower, 'date');
      if ln_index > 0 then
        self.intb_data(an_row)(ln_index) := to_char(adtm_value , 'yyyy-mm-dd hh24:mi:ss');
      end if;
    end;
  end;
  
  member procedure auto_addcolumn(an_index in out number, avc_colname varchar2, avc_coltype varchar2) as
  begin
    if in_auto_addcolumn = 1 then
      if an_index <= 0 then
        add_column(avc_colname, avc_coltype, 0, 0, null);
        an_index := in_column_count;
      end if;
    end if;
  end;
  
  member procedure add_column(avc_colname in varchar2, avc_type in varchar2, an_pkindex number, an_external number, avc_default_value varchar2) is
    lstr_col str_datacolumn;
  begin
    if intb_column is null then
      intb_column := ntb_datacolumn();
    end if;
    in_column_count := in_column_count + 1;
    lstr_col := str_datacolumn(avc_colname, avc_type, an_pkindex, an_external, avc_default_value);
    intb_column.extend;
    intb_column(in_column_count) := lstr_col;
    for i in 1.. in_row_count loop
      if intb_data is null then
        intb_data := ntb_diminsion2();
      end if;
      if intb_data(i) is null then
        intb_data(i) := ntb_vc999();
      end if;
      intb_data(i).extend;
      intb_data(i)(in_column_count) := avc_default_value;
    end loop;
  end;
  
  member procedure add_row is
  begin
    in_row_count := in_row_count + 1;
    if intb_data is null then
      intb_data := ntb_diminsion2();
    end if;
    intb_data.extend;
    for i in 1 .. in_column_count loop
      if intb_data(in_row_count) is null then
        intb_data(in_row_count) := ntb_vc999();
      end if;
      intb_data(in_row_count).extend;
      intb_data(in_row_count)(i) := intb_column(i).default_value;
    end loop;
  end;
  
  member procedure export_xml(aclob_xml out clob) is
    lvc_colname varchar2(30);
    lvc_value varchar2(999);
    lvc_pkindex varchar2(20);
    lvc_external varchar(20);
    lvc_default varchar2(100);
    lvc_coltype varchar2(30);
  begin
    --LVC_TEXT := self.OP_GETITEMSTRING(1, '');
    aclob_xml := '<?xml version="1.0" encoding="UTF-8"?>
<iscxml_data_table table_name="' || lower(ivc_tablename) || '">';
    for i in 1 .. in_row_count loop
      lvc_colname     := '';
      lvc_value       := '';
      aclob_xml := aclob_xml || '
  <iscxml_data_row>';
      for j in 1 .. in_column_count loop
        lvc_colname := intb_column(j).column_name;
        if intb_data(i)(j) is not null then
          lvc_value := intb_data(i)(j);
        else
          lvc_value := '';
        end if;
        aclob_xml := aclob_xml || '
    <' || lvc_colname || '>' || lvc_value || '</' || lvc_colname || '>';
      end loop;
      aclob_xml := aclob_xml || '
  </iscxml_data_row>';
    end loop;
    aclob_xml := aclob_xml || '
</iscxml_data_table>';
  end;
  
  member procedure export_xml_full(aclob_xml out clob) is
    lvc_colname varchar2(30);
    lvc_value varchar2(999);
    lvc_pkindex varchar2(20);
    lvc_external varchar(20);
    lvc_default varchar2(100);
    lvc_coltype varchar2(30);
  begin
    aclob_xml := '<?xml version="1.0" encoding="UTF-8"?>
<iscxml_data_table table_name="' || lower(ivc_tablename) || '">';
    for i in 1 .. in_row_count loop
      lvc_colname     := '';
      lvc_value       := '';
      lvc_pkindex     := '';
      lvc_external    := '';
      lvc_default     := '';
      lvc_coltype     := '';
      aclob_xml := aclob_xml || '
  <iscxml_data_row>';
      for j in 1 .. in_column_count loop
        if i = 1 then
          lvc_pkindex := ' pk_index="' || intb_column(j).primary_index || '"';
          lvc_external := ' external="' || intb_column(j).external_column || '"';
          lvc_coltype := ' type="' || intb_column(j).column_type || '"';
          if intb_column(j).default_value is not null then
            lvc_default := ' default="' || intb_column(j).default_value || '"';
          else
            lvc_default := '';
          end if;
        end if;
        lvc_colname := intb_column(j).column_name;
        if intb_data(i)(j) is not null then
          lvc_value := intb_data(i)(j);
        else
          lvc_value := '';
        end if;
        aclob_xml := aclob_xml || '
    <' || lvc_colname || lvc_coltype || lvc_pkindex || lvc_external || lvc_default || '>' || lvc_value || '</' || lvc_colname || '>'; 
      end loop;
      aclob_xml := aclob_xml || '
  </iscxml_data_row>';
    end loop;
    aclob_xml := aclob_xml || '
</iscxml_data_table>';
  end;
  
  member procedure create_sql_insert(an_row number, avc_return out varchar2) as
    ln_cc             number := 0;
    lvc_values        varchar2(4000);
    lvc_value         varchar2(999);
    lvc_coltype       varchar2(30);
    lvc_dateformat    varchar2(25);
    ln_strlen         number;
  begin
    avc_return := 'insert into ' || ivc_tablename || '(';
    lvc_values := ' values (';
    
    for i in 1 .. in_column_count loop
      if intb_column(i).external_column = 0 and ((intb_column(i).column_name = 'row_status' or intb_column(i).column_name = 'running_number') = false) then
        -- ท่อนเพิ่ม column
        if ln_cc = 0 then
          avc_return := avc_return || intb_column(i).column_name;
        else
          avc_return := avc_return || ', ' || intb_column(i).column_name;
        end if;
        
        -- ท่อนเพิ่ม data
        lvc_value       := '';
        lvc_coltype     := '';
        ln_strlen       := 0;
        lvc_dateformat  := '';
          
        lvc_value       := 'null';
        lvc_coltype     := intb_column(i).column_type;
        
        if lvc_coltype = 'date' then
          declare
            ln_year number := 0;
          begin
            lvc_value         := intb_data(an_row)(i);
            ln_strlen         := length(lvc_value);
            if ln_strlen > 12 then
              lvc_dateformat  := 'yyyy-mm-dd hh24:mi:ss';
            else
              lvc_dateformat  := 'yyyy-mm-dd';
            end if;
            ln_year           := extract(year from to_date(lvc_value, lvc_dateformat));
            lvc_value         := 'to_date(''' || lvc_value || ''', ''' || lvc_dateformat || ''')';
            if ln_year <= 1700 then
              lvc_value       := 'NULL';
            end if;
            if lvc_value is null then
              lvc_value       := 'NULL';
            end if;
          exception
            when others then
              lvc_value       := 'NULL';
          end;
        elsif lvc_coltype = 'number' then
          begin
            --REPLACE(LVC_VALUE, ',', '');
            lvc_value         := replace(intb_data(an_row)(i), ',', '');
            if lvc_value is null then
              lvc_value         := 'NULL';
            end if;
          exception
            when others then
              lvc_value         := 'NULL';
          end;
        else
          begin
            lvc_value   := intb_data(an_row)(i);
            lvc_value   := '''' || lvc_value || '''';
            if lvc_value is null then
              lvc_value   := 'NULL';
            end if;
          exception
            when others then
              lvc_value   := 'NULL';
          end;
        end if;
        
        if ln_cc = 0 then
          lvc_values := lvc_values || lvc_value;
        else
          lvc_values := lvc_values || ', ' || lvc_value;
        end if;
        --ก่อนลูปต่อไป เซ็ตค่า + 1
        ln_cc := ln_cc + 1;
      end if;
    end loop;
    if(ln_cc > 0) then
      avc_return := avc_return || ')' || lvc_values || ')';
    else
      avc_return := '';
    end if;
  end;
  
  member procedure create_sql_update(an_row number, avc_return out varchar2) as
    ln_cc             number    := 0;
    lvc_values        varchar2(999);
    lvc_value         varchar2(999);
    lvc_colname       varchar2(30);
    lvc_coltype       varchar2(30);
    lvc_where         varchar2(2000);
    ln_pk_count       number    := 0;
  begin
    avc_return    := 'update ' || ivc_tablename || ' set ';
    for i in 1 .. in_column_count loop
      lvc_colname := lower(intb_column(i).column_name);
      if lvc_colname = 'row_status' or lvc_colname = 'running_number' then
        goto endloop_i;
      end if;
      
      lvc_coltype := intb_column(i).column_type;
      lvc_value   := intb_data(an_row)(i);
      lvc_values  := '';
      
      if lvc_coltype = 'date' then
        declare
          ldtm_value date;
        begin
          ldtm_value := getitemdatetime(an_row, i);
          if ldtm_value is not null then
            lvc_values := 'to_date('''|| to_char(ldtm_value, 'yyyy-mm-dd hh24:mi:ss') || ''', ''yyyy-mm-dd hh24:mi:ss'')';
          else
            lvc_values := 'NULL';
          end if;
        exception
          when others then
            lvc_values := 'NULL';
        end;
      elsif lvc_coltype = 'number' then
        declare
          ln_value number;
        begin
          ln_value      := self.getitemnumber(an_row, i);
          lvc_values    := to_char(ln_value);
        exception
          when others then
            lvc_values  := 'NULL';
        end;
      else
        begin
          lvc_values    := '''' || lvc_value || '''';
        exception
          when others then
            lvc_values  := 'NULL';
        end;
      end if;
      
      -- เดี๋ยวกลับมาเขียน where ที่นี่ได้เลย
      if intb_column(i).primary_index > 0 then
        ln_pk_count := ln_pk_count + 1;
        
        if ln_pk_count = 1 then
          lvc_where := lvc_where || lvc_colname || '=' ;
        else
          lvc_where := lvc_where || ' and ' || lvc_colname || '=' ;
        end if;
        
        if lvc_coltype = 'date' then
          declare
            ldtm_date_value date;
          begin
            ldtm_date_value := self.getitemdatetime(an_row, lvc_colname);
            if ldtm_date_value is null then
              fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=UPDATE)');
            else
              lvc_where := lvc_where || lvc_values;
            end if;
          end;
        elsif lvc_coltype = 'number' then
          declare
            ln_num_value number;
          begin
            ln_num_value := self.getitemnumber(an_row, lvc_colname);
            if ln_num_value is null then
              fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=UPDATE)');
            else
              lvc_where := lvc_where || lvc_values;
            end if;
          end;
        else
          declare
            lvc_vc_value varchar2(999);
          begin
            lvc_vc_value := self.getitemstring(an_row, lvc_colname);
            if lvc_vc_value is null then
              fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=UPDATE)');
            else
              lvc_where := lvc_where || lvc_values;
            end if;
          end;
        end if;
        goto endloop_i;
      end if;
      
      if intb_column(i).external_column = 0 then
        if ln_cc = 0 then
          avc_return := avc_return || lvc_colname || '=' || lvc_values;
        else
          avc_return := avc_return || ', ' || lvc_colname || '=' || lvc_values;
        end if;
        ln_cc := ln_cc + 1;
      end if;
      <<endloop_i>>
      null;
    end loop;
    if ln_cc > 0 and ln_pk_count > 0 then
      avc_return := avc_return || ' where ' || lvc_where;
    else
      avc_return := '';
    end if;
  end;
  
  member procedure create_sql_delete(an_row number, avc_return out varchar2) as
    ln_cc             number    := 0;
    lvc_values        varchar2(999);
    lvc_colname       varchar2(30);
    lvc_coltype       varchar2(30);
    ln_pk_count       number    := 0;
    ln_pk_index       number    := 0;
  begin
    avc_return    := 'delete from ' || ivc_tablename || ' where ';
    for i in 1 .. in_column_count loop
      ln_pk_index := intb_column(i).primary_index;
      if ln_pk_index <= 0 then
        goto endloop_i;
      end if;
      
      ln_pk_count := ln_pk_count + 1;
      lvc_colname := intb_column(i).column_name;
      lvc_coltype := intb_column(i).column_type;
      lvc_values  := '';
      
      if lvc_coltype = 'date' then
        declare
          ldtm_value date;
        begin
          ldtm_value := getitemdatetime(an_row, i);
          if ldtm_value is not null then
            lvc_values := 'to_date('''|| to_char(ldtm_value, 'yyyy-mm-dd hh24:mi:ss') || ''', ''yyyy-mm-dd hh24:mi:ss'')';
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETE)');
          end if;
        end;
      elsif lvc_coltype = 'number' then
        declare
          ln_value number;
        begin
          ln_value      := self.getitemnumber(an_row, i);
          if ln_value is not null then
            lvc_values    := to_char(ln_value);
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETE)');
          end if;
        end;
      else
        declare
          lvc_value varchar2(999);
        begin
          lvc_value     := self.getitemstring(an_row, lvc_colname);
          if lvc_value is not null then
            lvc_values    := '''' || lvc_value || '''';
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETE)');
          end if;
        end;
      end if;
      if ln_pk_count = 1 then
        avc_return := avc_return || lvc_colname || '=' || lvc_values;
      else
        avc_return := avc_return || ' and ' || lvc_colname || '=' || lvc_values;
      end if;
      <<endloop_i>>
      null;
    end loop;
    if ln_pk_count <= 0 then
      avc_return := '';
    end if;
  end;
  
  member procedure create_sql_deleted(an_row number, avc_return out varchar2) as
    ln_cc             number    := 0;
    lvc_values        varchar2(999);
    lvc_colname       varchar2(30);
    lvc_coltype       varchar2(30);
    ln_pk_count       number    := 0;
    ln_pk_index       number    := 0;
  begin
    avc_return    := 'delete from ' || ivc_tablename || ' where ';
    for i in 1 .. in_column_count loop
      ln_pk_index := intb_column(i).primary_index;
      if ln_pk_index <= 0 then
        goto endloop_i;
      end if;
      
      ln_pk_count := ln_pk_count + 1;
      lvc_colname := intb_column(i).column_name;
      lvc_coltype := intb_column(i).column_type;
      lvc_values  := '';
      
      if lvc_coltype = 'date' then
        declare
          ldtm_value date;
        begin
          --LDTM_VALUE := GETITEMDATETIME(AN_ROW, I);
          itemdatetimedeleted(an_row, i, ldtm_value);
          if ldtm_value is not null then
            lvc_values := 'to_date('''|| to_char(ldtm_value, 'yyyy-mm-dd hh24:mi:ss') || ''', ''yyyy-mm-dd hh24:mi:ss'')';
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETE)');
          end if;
        end;
      elsif lvc_coltype = 'number' then
        declare
          ln_value number;
        begin
          --LN_VALUE      := SELF.GETITEMNUMBER(AN_ROW, I);
          itemnumberdeleted(an_row, i, ln_value);
          if ln_value is not null then
            lvc_values    := to_char(ln_value);
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETE)');
          end if;
        end;
      else
        declare
          lvc_value varchar2(999);
        begin
          --LVC_VALUE     := SELF.GETITEMSTRING(AN_ROW, LVC_COLNAME);
          itemstringdeleted(an_row, i, lvc_value);
          if lvc_value is not null then
            lvc_values    := '''' || lvc_value || '''';
          else
            fpb.throw('ไม่พบค่าในคอลัมน์คีย์ ' || lvc_colname || ' (#SP=DELETED)');
          end if;
        end;
      end if;
      if ln_pk_count = 1 then
        avc_return := avc_return || lvc_colname || '=' || lvc_values;
      else
        avc_return := avc_return || ' and ' || lvc_colname || '=' || lvc_values;
      end if;
      <<endloop_i>>
      null;
    end loop;
    if ln_pk_count <= 0 then
      avc_return := '';
    end if;
  end;
  
  member procedure execute_deleted(an_row_affect out number) as
    lvc_sql varchar2(5000);
    lvc_colname varchar2(30);
    lvc_rowstat varchar2(30);
  begin
    an_row_affect := 0;
    for i in 1 .. in_del_count loop
      create_sql_deleted(i, lvc_sql);
      if lvc_sql is not null then
        execute immediate lvc_sql;
        an_row_affect := an_row_affect + 1;
      end if;
    end loop;
    for i in 1 .. in_row_count loop
      lvc_rowstat := getitemstring(i, 'row_status');
      if lvc_rowstat = 'insert' then
        create_sql_insert(i, lvc_sql);
        if lvc_sql is not null then
          execute immediate lvc_sql;
          an_row_affect := an_row_affect + 1;
        end if;
      elsif lvc_rowstat = 'update' then
        create_sql_update(i, lvc_sql);
        if lvc_sql is not null then
          execute immediate lvc_sql;
          an_row_affect := an_row_affect + 1;
        end if;
      end if;
    end loop;
  end;
end;