/*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';