CURSOR

/*SELECT BEFORE HOSEI*/
select tafd.*
  from TB_ADV_FEE_DETAIL tafd
 where tafd.Seikyu_Nengetsu = '201405'
   and not exists (select 1
          from tb_seikyu_meisai t
         where t.fee_mgmt_num = tafd.fee_mgmt_num
           and t.fee_mgmt_eda = tafd.fee_mgmt_eda)
   and tafd.seikyu_Shori_Flg = '2'
   and tafd.seikyu_Fuyo_Sign = '0'
   and tafd.gessho_kubun = '2'
   and tafd.moko_kind = '12'
   and tafd.Del_Flg = '0';

/*HOSEI SQL*/
CREATE OR REPLACE PROCEDURE SP_TB_SEIKYU_MEISAI IS

  CURSOR c_TB_SEIKYU_MEISAI IS
    select t.seikyu_mgmt_num,
           t.Shohi_Tax_Kubun_Code,
           t.Tax_Kubun,
           t.receipt_num,
           t.Seikyu_Nengetsu,
           t.ksai_sha_code
      from TB_SEIKYU_MEISAI t
     where (t.receipt_num, t.ksai_sha_code) in
           (SELECT tafd.receipt_num, tafd.ksai_sha_code
              FROM TB_ADV_FEE_DETAIL tafd
             WHERE tafd.Seikyu_Nengetsu = '201405'
               AND not exists
             (select 1
                      from tb_seikyu_meisai t
                     where t.fee_mgmt_num = tafd.fee_mgmt_num
                       and t.fee_mgmt_eda = tafd.fee_mgmt_eda)
               AND tafd.seikyu_Shori_Flg = '2'
               AND tafd.seikyu_Fuyo_Sign = '0'
               AND tafd.gessho_kubun = '2'
               AND tafd.moko_kind = '12'
               AND tafd.Del_Flg = '0'
             group by tafd.receipt_num, tafd.ksai_sha_code)
     group by t.seikyu_mgmt_num,
              t.Shohi_Tax_Kubun_Code,
              t.Tax_Kubun,
              t.receipt_num,
              t.Seikyu_Nengetsu,
              t.ksai_sha_code;
  c_row c_TB_SEIKYU_MEISAI%ROWTYPE;

BEGIN

  FOR c_row IN c_TB_SEIKYU_MEISAI LOOP
  
    insert into TB_SEIKYU_MEISAI
      (Seikyu_Mgmt_Num,
       Seikyu_Detail_Mgmt_Num,
       Seikyu_Nengetsu,
       Atsukai_Sha_Code,
       Ksai_Sha_Code,
       Adv_Sha_Code,
       Shukei_Adv_Baitai_Code,
       Shohi_Tax_Kubun_Code,
       Tax_Kubun,
       Adv_Kind_LCode,
       Adv_Kind_SCode,
       Adv_Kind_MCode,
       Adv_Kind_LName,
       Adv_Kind_MName,
       Adv_Kind_SName,
       Ksai_Hani_LCode,
       Ksai_Hani_MCode,
       Ksai_Hani_SCode,
       Ksai_Hani_LName,
       Ksai_Hani_MName,
       Ksai_Hani_SName,
       KanKind_Code,
       KanKind_Name,
       Tokubetsu_Margin_Kubun,
       Tokubetsu_Margin_Detail_Num,
       Tokubetsu_Margin_Num,
       Adv_Baitai_Bunrui_Code,
       Adv_Baitai_SName,
       Meisai_Sho_Subject,
       Kensu,
       Space_Kind_Code,
       Space_Sign,
       Uti_Space,
       Uti_Space_Sign,
       Kansan_Space,
       Adv_Nushi_Code,
       Adv_Nushi_Name_Ryaku1,
       Irozuri_Code,
       Irozuri_Name,
       Receipt_Num,
       Fee_Mgmt_Num,
       Fee_Mgmt_Eda,
       Seikyu_Fee,
       Ippan_Margin,
       Tokubetsu_Margin,
       Tokubetsu_Margin_Fee,
       Syoumi_Fee,
       Syoumi_Shohi_Tax,
       Ksai_Date,
       Tanto_Bu_Ka_Code,
       Mgmt_Num,
       Detail_Mgmt_Num,
       Input_Date_Time,
       Input_Per_Code,
       Update_Date_Time,
       Update_Per_Code,
       Update_Count,
       Del_Flg,
       Del_Date_Time,
       Del_Per_Code)
    
      select c_row.seikyu_mgmt_num,
             LPAD(to_char(to_number(seikyu_detail_mgmt_num) + ROWNUM),
                  5,
                  '0') as Shime_Check_Detail_Num,
             Seikyu_Nengetsu,
             Atsukai_Sha_Code,
             Ksai_Sha_Code,
             Adv_Sha_Code,
             Shukei_Adv_Baitai_Code,
             Shohi_Tax_Kubun_Code,
             Tax_Kubun,
             Adv_Kind_LCode,
             Adv_Kind_SCode,
             Adv_Kind_MCode,
             Adv_Kind_LName,
             Adv_Kind_MName,
             Adv_Kind_SName,
             Ksai_Hani_LCode,
             Ksai_Hani_MCode,
             Ksai_Hani_SCode,
             Ksai_Hani_LName,
             Ksai_Hani_MName,
             Ksai_Hani_SName,
             KanKind_Code,
             KanKind_Name,
             Tokubetsu_Margin_Kubun,
             Tokubetsu_Margin_Detail_Num,
             Tokubetsu_Margin_Num,
             Adv_Baitai_Bunrui_Code,
             Adv_Baitai_SName,
             Meisai_Sho_Subject,
             Kensu,
             Space_Kind_Code,
             Space_Sign,
             Uti_Space,
             Uti_Space_Sign,
             Kansan_Space,
             Adv_Nushi_Code,
             Adv_Nushi_Name_Ryaku1,
             Irozuri_Code,
             Irozuri_Name,
             Receipt_Num,
             Fee_Mgmt_Num,
             Fee_Mgmt_Eda,
             Seikyu_Fee,
             Ippan_Margin,
             Tokubetsu_Margin,
             Tokubetsu_Margin_Fee,
             Syoumi_Fee,
             Syoumi_Shohi_Tax,
             Ksai_Date,
             Tanto_Bu_Ka_Code,
             Mgmt_Num,
             Detail_Mgmt_Num,
             Input_Date_Time,
             Input_Per_Code,
             Update_Date_Time,
             Update_Per_Code,
             Update_Count,
             Del_Flg,
             Del_Date_Time,
             Del_Per_Code
        from (SELECT ROWNUM,
                     (select max(t1.Seikyu_Detail_Mgmt_Num)
                        from TB_SEIKYU_MEISAI t1
                       where t1.Seikyu_Mgmt_Num = c_row.Seikyu_Mgmt_Num) as seikyu_detail_mgmt_num,
                     tafd.Seikyu_Nengetsu,
                     tafd.Atsukai_Sha_Code,
                     tafd.Ksai_Sha_Code,
                     tafd.Adv_Sha_Code,
                     tafd.Shukei_Adv_Baitai_Code,
                     c_row.Shohi_Tax_Kubun_Code as Shohi_Tax_Kubun_Code,
                     c_row.Tax_Kubun as Tax_Kubun,
                     tafd.Adv_Kind_LCode,
                     tafd.Adv_Kind_SCode,
                     tafd.Adv_Kind_MCode,
                     tafd.Adv_Kind_SName,
                     tafd.Adv_Kind_MName,
                     tafd.Adv_Kind_LName,
                     tafd.Ksai_Hani_LCode,
                     tafd.Ksai_Hani_MCode,
                     tafd.Ksai_Hani_SCode,
                     tafd.Ksai_Hani_LName,
                     tafd.Ksai_Hani_MName,
                     tafd.Ksai_Hani_SName,
                     tafd.KanKind_Code,
                     tafd.KanKind_Name,
                     0 as Tokubetsu_Margin_Kubun,
                     null as Tokubetsu_Margin_Detail_Num,
                     null as Tokubetsu_Margin_Num,
                     tafd.Adv_Baitai_Bunrui_Code,
                     tafd.Adv_Baitai_SName,
                     tafd.Meisai_Sho_Subject,
                     1 as Kensu,
                     tafd.Space_Kind_Code,
                     ' ' as Space_Sign,
                     0 as Uti_Space,
                     ' ' as Uti_Space_Sign,
                     tafd.Dan_Kansan_Space as Kansan_Space,
                     tafd.Adv_Nushi_Code,
                     tafd.Adv_Nushi_Name_Ryaku1,
                     tafd.Irozuri_Code,
                     tafd.Irozuri_Name,
                     tafd.Receipt_Num,
                     tafd.Fee_Mgmt_Num,
                     tafd.Fee_Mgmt_Eda,
                     0 as Seikyu_Fee,
                     0 as Tokubetsu_Margin,
                     0 as Tokubetsu_Margin_Fee,
                     0 as Ippan_Margin,
                     0 as Syoumi_Fee,
                     nvl(tafd.Seikyu_Tax, 0) as Syoumi_Shohi_Tax,
                     tafd.Ksai_Date,
                     tafd.Tanto_Bu_Ka_Code,
                     null as Mgmt_Num,
                     null as Detail_Mgmt_Num,
                     sysdate as Input_Date_Time,
                     'M13905' as Input_Per_Code,
                     sysdate as Update_Date_Time,
                     'M13905' as Update_Per_Code,
                     0 as Update_Count,
                     '0' as Del_Flg,
                     null as Del_Date_Time,
                     'founder' as Del_Per_Code
              
                FROM TB_ADV_FEE_DETAIL tafd
              
               WHERE tafd.Seikyu_Nengetsu = c_row.seikyu_nengetsu
                 AND not exists
               (select 1
                        from tb_seikyu_meisai t
                       where t.fee_mgmt_num = tafd.fee_mgmt_num
                         and t.fee_mgmt_eda = tafd.fee_mgmt_eda)
                 AND tafd.seikyu_Shori_Flg = '2'
                 AND tafd.seikyu_Fuyo_Sign = '0'
                 AND tafd.gessho_kubun = '2'
                 AND tafd.moko_kind = '12'
                 AND tafd.Del_Flg = '0'
                 AND tafd.Receipt_Num = c_row.Receipt_Num
                 AND tafd.Ksai_Sha_Code = c_row.Ksai_Sha_Code);
  
  END LOOP;
  COMMIT;

END SP_TB_SEIKYU_MEISAI;

/*SELECT AFTER HOSEI*/
select t.*, tafd.*
  from TB_SEIKYU_MEISAI t, TB_ADV_FEE_DETAIL tafd
 where t.fee_mgmt_num = tafd.fee_mgmt_num
   and t.fee_mgmt_eda = tafd.fee_mgmt_eda
   and tafd.Seikyu_Nengetsu = '201405'
   and tafd.seikyu_Shori_Flg = '2'
   and tafd.seikyu_Fuyo_Sign = '0'
   and tafd.gessho_kubun = '2'
   and tafd.moko_kind = '12'
   and tafd.Del_Flg = '0'
   and t.Del_Per_Code = 'founder'
   and t.Del_Flg = '0';

/*CLEAR HOSEI FLG*/
 update TB_SEIKYU_MEISAI t set t.Del_Per_Code = null
 where t.Del_Per_Code = 'founder'
   and t.Del_Flg = '0';