create or replace package body w_sheet_ln_collredeem as

  function of_init_reqcollmastredeem (avc_coopid varchar2,avc_mastno varchar2,adtm_deem date) return clob is
    ls_collcoopid     varchar2(50);
    ls_collmastno     varchar2(50);
    ldtm_reddate      date;
    ll_countcoll      number;
    ls_collrefno      varchar2(50);
    ls_colldesc       varchar2(500);
    ldc_collprice     number;
    ldtm_colldate     date;
    ln_redeemflag     number;
    ls_mcoopid        varchar2(50);
    ls_memno          varchar2(50);
    ls_prename        varchar2(300);
    ls_membname       varchar2(300);
    ls_membsurname    varchar2(300);
    lnv_data          datasourcetool;
    ll_row            number;
    lclob_xml         clob;
  begin
    ls_collcoopid		:= avc_coopid;
    ls_collmastno		:= avc_mastno;
    ldtm_reddate		:= adtm_deem;
    begin
      --ดึงข้อมูลหลักทรัพย์      
      select		lnc.collmast_refno, lnc.collmast_desc,  lnc.collmast_price, lnc.mortgage_date,  lnc.redeem_flag,  lnmc.memcoop_id,  lnmc.memco_no
        into		ls_collrefno,       ls_colldesc,        ldc_collprice,      ldtm_colldate,      ln_redeemflag,    ls_mcoopid,       ls_memno
      from		lncollmaster lnc, lncollmastmemco lnmc
      where	( lnc.coop_id	= ls_collcoopid )
      	and ( lnc.collmast_no	= ls_collmastno )
      	and ( lnc.coop_id = lnmc.coop_id)
      	and ( lnc.collmast_no = lnmc.collmast_no) 
      	and ( lnmc.collmastmain_flag = 1);
              
    exception when others then      
        fpb.throw('ไม่พบหลักทรัพย์ '||ls_collmastno );                        
    end;
    
    if ln_redeemflag = 1 then
        fpb.throw('ทะเบียนหลักทรัพย์นี้ได้ไถ่ถอนแล้ว '||ls_collmastno);
    end if;
    
    -- ตรวจสอบว่าติดค้ำประกันอยู่หรือเปล่า
    begin
      select		count( lncontmaster.loancontract_no )
      into		ll_countcoll
      from		lncontmaster, lncontcoll
      where	( lncontcoll.coop_id				= ls_collcoopid ) and
      ( lncontcoll.ref_collno 				    = ls_collmastno ) and
      ( lncontcoll.loancolltype_code	    = '04' ) and
      ( lncontcoll.coll_status 			      = 1 ) and
      ( lncontcoll.coop_id				      = lncontmaster.coop_id ) and
      ( lncontcoll.loancontract_no		    = lncontmaster.loancontract_no ) and
      ( lncontmaster.contract_status      > 0 );
      
      if ll_countcoll is null then ll_countcoll := 0; end if;
      
      if ll_countcoll > 0 then
        fpb.throw('ทะเบียนหลักทรัพย์นี้ไม่สามารถไถ่ถอนได้ เพราะยังมีสัญญาที่ใช้หลักทรัพย์นี้ค้ำประกันอยู่');
      end if;
    end;
    
    --หาชื่อสมาชิก
    begin
      select b.prename_desc, a.memb_name, a.memb_surname
      into ls_prename, ls_membname, ls_membsurname
      from mbmembmaster a, mbucfprename b
      where	
      ( a.coop_id	= ls_mcoopid ) and
      ( a.member_no	= ls_memno ) and
      ( a.prename_code = b.prename_code );
    end;
      
    lnv_data := datasourcetool();
    lnv_data.add_row();
    
    --LL_ROW		:= LNV_DATA.ROWCOUNT;
    lnv_data.setitem( 1, 'collmast_refno', ls_collrefno );
    lnv_data.setitem( 1, 'collmast_desc', ls_colldesc );
    lnv_data.setitem( 1, 'collmast_price', ldc_collprice );
    lnv_data.setitem( 1, 'collmast_date', ldtm_reddate );
    lnv_data.setitem( 1, 'prename_desc', ls_prename );
    lnv_data.setitem( 1, 'memb_name', ls_membname );
    lnv_data.setitem( 1, 'memb_surname', ls_membsurname );
    lnv_data.setitem( 1, 'memcoop_id', ls_mcoopid );
    lnv_data.setitem( 1, 'member_no', ls_memno );
    lnv_data.setitem( 1, 'collcoop_id', ls_collcoopid );
    lnv_data.setitem( 1, 'collmast_no', ls_collmastno );
    lnv_data.setitem( 1, 'redeem_date', ldtm_reddate );
    
    lnv_data.export_xml(lclob_xml);
    return lclob_xml;
  end of_init_reqcollmastredeem;

  procedure of_save_lccollmastredeem (aclob_xml clob, avc_entryid varchar2, avc_entrycoopid varchar2, avc_redeemno out varchar2, avc_coopcontrol varchar2) is
    lnv_data datasourcetool;
    ls_entrycoopid varchar(30);
    ls_entryid varchar(30);
    ldtm_entrydate date;
    ls_coopid varchar(30);
    ls_mastno varchar(30);
    ldtm_redeemdate date;
    lvc_docno varchar2(50);
    lvc_sql varchar2(4000);
  begin
    lnv_data := datasourcetool(aclob_xml);
    --สาขาที่จะใส่ใน PK ใช้สาขาคุม สาขาที่บันทึกใช้ที่ UI ส่งเข้ามา
    ls_entrycoopid	:= avc_entryid;
    ls_entryid		  := avc_entrycoopid;
    ldtm_entrydate	:= sysdate;
    
    ls_coopid		:= lnv_data.getitemstring( 1, 'collcoop_id');
    ls_mastno		:= lnv_data.getitemstring( 1, 'collmast_no');
    ldtm_redeemdate	:= lnv_data.getitemdatetime( 1, 'redeem_date');  
    
    --ขอเลขที่เอกสาร
    lvc_docno := n_pk_doccontrol.of_getnewdocno(avc_coopcontrol, 'LNCOLLMASTREDEEMNO');
    
    lnv_data.setitem( 1, 'coop_id', avc_coopcontrol );
    lnv_data.setitem( 1, 'redeem_docno', lvc_docno );
    lnv_data.setitem( 1, 'entry_id', ls_entryid );
    lnv_data.setitem( 1, 'entry_bycoopid', ls_entrycoopid );
    lnv_data.setitem( 1, 'entry_date', ldtm_entrydate );
    lnv_data.setitem( 1, 'redeem_status', 1 );
    begin
      lnv_data.create_sql_insert(1,lvc_sql);
      execute immediate lvc_sql;
    exception
      when others then
      rollback;
      fpb.throw('ไม่สามารถบันทึกข้อมูลคำร้องขอไถ่ถอนหลักทรัพย์ได้ (CollMast)' || sqlerrm);
    end;
    
    -- ปรับสถานะการไถ่ถอน
    begin
      update
        lncollmaster
      set 
        redeem_flag	= 1,
        redeem_date	= ldtm_redeemdate
      where
        ( coop_id		= ls_coopid ) and
        ( collmast_no	= ls_mastno )
      ;   
      --ส่งเลขที่ใบคำขอกลับ เผื่อนำไปทำรายการ
      avc_redeemno := lvc_docno;
    exception
      when others then
        rollback;
        fpb.throw('ไถ่ถอนหลักทรัพย์ ไม่สามารถปรับปรุงข้อมูลการไถ่ถอนหลักทรัพย์ในทะเบียนหลักทรัพย์ได้ #'||ls_mastno || ' ' || sqlerrm);
    end;
    
    commit;
  end of_save_lccollmastredeem;
  
end w_sheet_ln_collredeem;