CREATE OR REPLACE PROCEDURE SP_JN_LN_CASE_SIGN_RPY_PLAN_H(PD_START_DATE IN DATE, PD_END_DATE IN DATE) IS
LV_PROGRAM_NAME VARCHAR2(30) := 'SP_JN_LN_CASE_SIGN_RPY_PLAN_H';
LV_TABLE_NAME VARCHAR2(32) := 'JN_LN_CASE_SIGN_RPY_PLAN_H';
LV_FILTER_NAME VARCHAR2(15) := 'FHBI';
LV_KEY_NAME1 VARCHAR2(32) := '';
LV_KEY_NAME2 VARCHAR2(32) := '';
LV_KEY_INFORMATION VARCHAR2(250) := NULL;
LV_KEY1 VARCHAR2(100) := '';
LV_KEY2 VARCHAR2(100) := '';
LN_INS_COUNTER NUMBER := 0;
LN_UPD_COUNTER NUMBER := 0;
LN_COMMIT_LABEL NUMBER := 0;
LN_PERIOD_ID NUMBER := 0;
LN_PERIOD_DAY NUMBER := 60;
C_COMMIT_REC NUMBER := 10000;
LD_START_DATE DATE;
LD_END_DATE DATE;
MAX_END_DATE DATE := DATE'2999-12-31';
MAX_DATA_DATE DATE;
/*--*******************************************************
-- PROCEDURE: SP_JN_LN_CASE_SIGN_RPY_PLAN_H
-- 案件原始还款计划历史拉链表
-- PERSON DATE COMMENTS
-- ------------- ----------- -------------------------
-- ZHUYUNFENG 2020-10-22 CREATED
--*******************************************************/
-- call fhbi.sp_jn_ln_case_sign_rpy_plan_h(null,null);
/*
-- Create table
-- drop table odsjoin.jn_ln_case_sign_rpy_plan_h purge;
create table odsjoin.jn_ln_case_sign_rpy_plan_h
(
risk_id varchar2(30)
,repay_detail_id varchar2(60)
,period_index decimal(10,0)
,accrued_repay_date date
,surplus_pr_amt decimal(18,2) -- 剩余本金
,plan_accrued_pr_amt decimal(18,2)
,plan_accrued_int_amt decimal(18,2)
,accrued_service1_amt decimal(18,2) -- 应收服务费1
,accrued_service2_amt decimal(18,2) -- 应收服务费2
,data_source varchar2(20)
,start_date date
,end_date date
,etl_date date default sysdate
)
;
comment on table odsjoin.jn_ln_case_sign_rpy_plan_h is '案件原始还款计划历史拉链表';
-- add comments to the columns
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.risk_id is '风控单编号';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.repay_detail_id is '还款明细id(主键:案件号+期次)';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.period_index is '期次';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.accrued_repay_date is '应还款日期';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.surplus_pr_amt is '剩余本金';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.plan_accrued_pr_amt is '计划应收本金';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.plan_accrued_int_amt is '计划应收利息';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.accrued_service1_amt is '应收服务费1';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.accrued_service2_amt is '应收服务费2';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.start_date is '开始日期';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.end_date is '结束日期';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.data_source is '数据来源系统';
comment on column odsjoin.jn_ln_case_sign_rpy_plan_h.etl_date is 'ETL日期';
create index odsjoin.jn_ln_case_sign_rpy_plan_h_u1 on odsjoin.jn_ln_case_sign_rpy_plan_h (repay_detail_id, risk_id,start_date);
drop table fhtmp.tmp_case_sign_rpy_plan_new purge;
create table fhtmp.tmp_case_sign_rpy_plan_new as select * from odsjoin.jn_ln_case_sign_rpy_plan_h where 1 <> 1;
*/
BEGIN
-- PROGRAM START
FHBI.PKG_GLOBAL.SP_GB_PROGRAM_START(LV_PROGRAM_NAME);
-- GET START DATE AND END DATE
FHBI.PKG_GLOBAL.SP_GB_PROGRAM_DATE(LV_FILTER_NAME,LD_START_DATE,LD_END_DATE);
IF PD_START_DATE IS NOT NULL THEN
LD_START_DATE := PD_START_DATE;
END IF;
IF PD_END_DATE IS NOT NULL THEN
LD_END_DATE := PD_END_DATE;
END IF;
LN_INS_COUNTER := 0;
if LD_START_DATE >= trunc(sysdate - 1,'dd') then -- 拉链表不跑历史数据
select max(start_date) into max_data_date from odsjoin.jn_ln_case_sign_rpy_plan_h; -- 将拉链表的最大 开始日期 赋值给 max_data_date
--
if max_data_date >= LD_START_DATE then -- 当天重跑起作用 -- 当 最大日期 大于或等于 输入的日期，则继续执行
-- setp 1 : 删除当批新增的数据 --
delete from odsjoin.jn_ln_case_sign_rpy_plan_h where start_date >= LD_START_DATE; -- 删除拉链表的 开始日期 大于 输入日期 的数据
commit; -- 提交
-- 回退拉链的结束时间 --
update odsjoin.jn_ln_case_sign_rpy_plan_h t1 -- 找出 拉链表中 结束日期 等于 输入日期 - 1 的数据，将结束日期 置为 DATE'2999-12-31'
set t1.end_date = max_end_date --参数 --
where 1 = 1 --
and t1.end_date = LD_START_DATE - 1 --
; --
commit; -- 提交
--
end if; --
--
EXECUTE IMMEDIATE 'truncate table fhtmp.tmp_case_sign_rpy_plan_new'; -- 清空临时表
--
-- setp 2 : 取当天新增或变化的数据插入到临时表new --
-- 这里不做 已删除的数据 的记录，因为要考虑到 如果抽到的源表，是空表，那么对于拉链表而言，就要对全量数据做闭链，
insert /*+Append*/ into fhtmp.tmp_case_sign_rpy_plan_new
(
risk_id -- 风控单编号
,repay_detail_id -- 还款明细id(主键:案件号+期次)
,period_index -- 期次
,accrued_repay_date -- 应还款日期
,surplus_pr_amt -- 剩余本金
,plan_accrued_pr_amt -- 计划应收本金
,plan_accrued_int_amt -- 计划应收利息
,accrued_service1_amt -- 应收服务费1
,accrued_service2_amt -- 应收服务费2
,data_source -- 数据来源系统
,start_date
,end_date
)
select /*+parallel(16)*/
risk_id -- 风控单编号
,repay_detail_id -- 还款明细id(主键:案件号+期次)
,period_index -- 期次
,accrued_repay_date -- 应还款日期
,surplus_pr_amt -- 剩余本金
,plan_accrued_pr_amt -- 计划应收本金
,plan_accrued_int_amt -- 计划应收利息
,accrued_service1_amt -- 应收服务费1
,accrued_service2_amt -- 应收服务费2
,data_source -- 数据来源系统
,LD_START_DATE as start_date
,MAX_END_DATE as end_date
from (
select
risk_id -- 风控单编号
,repay_detail_id -- 还款明细id(主键:案件号+期次)
,period_index -- 期次
,accrued_repay_date -- 应还款日期
,surplus_pr_amt -- 剩余本金
,plan_accrued_pr_amt -- 计划应收本金
,plan_accrued_int_amt -- 计划应收利息
,accrued_service1_amt -- 应收服务费1
,accrued_service2_amt -- 应收服务费2
,data_source -- 数据来源系统
from odsjoin.jn_ln_case_sign_repay_plan
minus
select
risk_id -- 风控单编号
,repay_detail_id -- 还款明细id(主键:案件号+期次)
,period_index -- 期次
,accrued_repay_date -- 应还款日期
,surplus_pr_amt -- 剩余本金
,plan_accrued_pr_amt -- 计划应收本金
,plan_accrued_int_amt -- 计划应收利息
,accrued_service1_amt -- 应收服务费1
,accrued_service2_amt -- 应收服务费2
,data_source -- 数据来源系统
from odsjoin.jn_ln_case_sign_rpy_plan_h
where LD_START_DATE - 1 between start_date and end_date
)
;
commit;
-- setp 3 : 对有变更的数据进行闭链
update odsjoin.jn_ln_case_sign_rpy_plan_h t1
set t1.end_date = LD_START_DATE - 1 --参数
where 1=1
and t1.end_date = MAX_END_DATE
and exists (select 1
from fhtmp.tmp_case_sign_rpy_plan_new t10
where t1.repay_detail_id = t10.repay_detail_id
)
;
COMMIT;
-- setp 4 : 新增数据插回目标表
insert /*+Append*/ into odsjoin.jn_ln_case_sign_rpy_plan_h
(
risk_id -- 风控单编号
,repay_detail_id -- 还款明细id(主键:案件号+期次)
,period_index -- 期次
,accrued_repay_date -- 应还款日期
,surplus_pr_amt -- 剩余本金
,plan_accrued_pr_amt -- 计划应收本金
,plan_accrued_int_amt -- 计划应收利息
,accrued_service1_amt -- 应收服务费1
,accrued_service2_amt -- 应收服务费2
,data_source -- 数据来源系统
,start_date -- 开始日期
,end_date -- 结束日期
,etl_date -- ETL日期
)
select
i.risk_id -- 风控单编号
,i.repay_detail_id -- 还款明细id(主键:案件号+期次)
,i.period_index -- 期次
,i.accrued_repay_date -- 应还款日期
,i.surplus_pr_amt -- 剩余本金
,i.plan_accrued_pr_amt -- 计划应收本金
,i.plan_accrued_int_amt -- 计划应收利息
,i.accrued_service1_amt -- 应收服务费1
,i.accrued_service2_amt -- 应收服务费2
,i.data_source -- 数据来源系统
,i.start_date -- 开始日期
,i.end_date -- 结束日期
,sysdate as etl_date -- ETL日期
from fhtmp.tmp_case_sign_rpy_plan_new i -- 新开链数据
where 1 = 1
;
commit;
EXECUTE IMMEDIATE 'truncate table fhtmp.tmp_case_sign_rpy_plan_new';
END IF;
-- PROGRAM COMPLETE
FHBI.PKG_GLOBAL.SP_GB_PROGRAM_COMPLETE(LV_PROGRAM_NAME,LN_UPD_COUNTER,LN_INS_COUNTER);
EXCEPTION
WHEN OTHERS THEN
-- PROGRAM ERROR RAISE
LV_KEY_INFORMATION := LV_TABLE_NAME || ':' ||
LV_KEY_NAME1 || '=' || TO_CHAR(LV_KEY1) || 'OR' ||
LV_KEY_NAME2 || '=' || TO_CHAR(LV_KEY2) ;
--FORM KEY INFORMATION
FHBI.PKG_GLOBAL.SP_GB_PROGRAM_ERROR_RAISE(LV_PROGRAM_NAME,LV_KEY_INFORMATION);
END SP_JN_LN_CASE_SIGN_RPY_PLAN_H;
投递网易等公司6个岗位 >