---------------------贷款明细表(TRS部LN)
create table tmp_fsd_GTRF_LOAN nologging compress
as select acct_no,
lending_ref,
branch_code,
data_date,
loan_type from tmp_fsd_loandetailtab where 1=2;
insert into tmp_fsd_GTRF_LOAN
(acct_no,
lending_ref,
branch_code,
data_date,
loan_type
)
select t. ilctcd || t.ilgmab || lpad(t. ilacb, 3, '0') || lpad(t. ilacs, 6, '0') || lpad(t. ilacx, 3, '0') acct_no, --贷款账号
trim(t.ilapty) || trim(t.illnnm) || /*t.ildtav ||*/
t. ilctcd || t.ilgmab || lpad(t. ilacb, 3, '0') || lpad(t. ilacs, 6, '0') || lpad(t. ilacx, 3, '0') lending_ref, --借据号
lpad(t. ilacb, 3, '0') branch_code, --分行号
t.data_date,
'' load_type,
case
when e.ivincp = 1 then
'F'
when e.ivincp = 2 then
'S'
end rate_type
from ods.iplnmsp t
left join (select ivctcd, ivgmab, ivacb, ivacs, ivacx, ivapty, ivlnnm, ivincp
from (select ivctcd,
ivgmab,
ivacb,
ivacs,
ivacx,
ivapty,
ivlnnm,
ivincp,
row_number() over(partition by ivctcd, ivgmab, ivacb, ivacs, ivacx, ivapty, ivlnnm order by ivavdt || ivavtm desc) max_rno
from ods.iplndhp_all
where data_date<='20141128' f)
where max_rno = 1a
) e on t.ilctcd = e.ivctcd
and t.ilgmab = e.ivgmab
and t.ilacb = e.ivacb
and t.ilacs = e.ivacs
and t.ilacx = e.ivacx
and t.ilapty = e.ivapty
and t.illnnm = e.ivlnnm
where t.data_date = vdate -- vdate
and exists (select 1
from v_trs_prd c
where c.ctcd = t.ilctcd
and c.gmab = t.ilgmab
and c.apty = t.ilapty
and c.db = 'L')
and b.data_date = '20141128';
commit;
----------------------贷款明细表(TRS部BL)
insert into tmp_fsd_GTRF_LOAN
(acct_no,
lending_ref,
branch_code,
data_date,
loan_type,
rate_type
)
select t. iictcd || t.iigmab || lpad(t. iiacb, 3, '0') || lpad(t. iiacs, 6, '0') || lpad(t. iiacx, 3, '0') acct_no, --贷款账号
trim(t.iiapty) || trim(t.iiblno)
/* || t.iidtav */
|| t. iictcd || t.iigmab || lpad(t. iiacb, 3, '0') || lpad(t. iiacs, 6, '0') || lpad(t. iiacx, 3, '0') lending_ref, --借据号
lpad(t. iiacb, 3, '0') branch_code, --分行号
t.data_date,
'' load_type,
case
when trim(d.jfincp) = '1' then
'F'
when trim(d.jfincp) = '2' then
'S'
end rate_type
from ods.ipblmsp t --327
left join (select jfctcd, jfgmab, jfacb, jfacs, jfacx, jfapty, jfblno, jfincp, jfngrf, jfrmmd
from (select jfctcd,
jfgmab,
jfacb,
jfacs,
jfacx,
jfapty,
jfblno,
jfincp,
jfngrf,
jfrmmd,
row_number() over(partition by jfctcd, jfgmab, jfacb, jfacs, jfacx, jfapty, jfblno order by jfavdt || jfavtm desc) max_rno
from ods.ipbldhp_all f
where data_date<='20141128'
)
where max_rno = 1) d on
t.iictcd = d.jfctcd
and t.iigmab = d.jfgmab
and t.iiacb = d.jfacb
and t.iiacs = d.jfacs
and t.iiacx = d.jfacx
and t.iiapty = d.jfapty
and t.iiblno = d.jfblno
where t.data_date = '20141128' --vdate
and nvl(t.iirmmd, ' ') <> 'DP'
and not exists (select 1
from ods.iplnmsp f
where nvl(f.ilapty, ' ') <> 'MIR'
and f.ilrref = t.iiapty || t.iiblno);
commit;
--
--
----------------------贷款明细表(TRS部EP)
insert into tmp_fsd_GTRF_LOAN
(acct_no,
lending_ref,
branch_code,
data_date,
loan_type,
rate_type
)
select t. bbctcd || t.bbgmab || lpad(t. bbacb, 3, '0') || lpad(t. bbacs, 6, '0') || lpad(t. bbacx, 3, '0') acct_no, --贷款账号
trim(t.bbapty) || trim(t.bbblno)
-- /*|| t.bbdtav */
|| t. bbctcd || t.bbgmab || lpad(t. bbacb, 3, '0') || lpad(t. bbacs, 6, '0') || lpad(t. bbacx, 3, '0') lending_ref, --借据号
lpad(t. bbacb, 3, '0') branch_code, --分行号
-- t. bbctcd || t.bbgmab || lpad(b.dcb, 3, '0') || lpad(b.dcs, 6, '0') customer_id --客户号
t.data_date,
'' load_type,
case
when e.atincp = 1 then
'F'
when e.atincp = 2 then
'S'
end rate_type
from ods.epblmsp t
left join (select atctcd, atgmab, atacb, atacs, atacx, atapty, atblno, atincp
from (select atctcd,
atgmab,
atacb,
atacs,
atacx,
atapty,
atblno,
atvldt,
atincp,
row_number() over(partition by atctcd, atgmab, atacb, atacs, atacx, atapty, atblno order by atavdt || atavtm desc) max_atvldt
from ods.epbldhp f
where data_date<='20141128')
where max_atvldt = 1) e on
t.bbctcd = e.atctcd
and t.bbgmab = e.atgmab
and t.bbacb = e.atacb
and t.bbacs = e.atacs
and t.bbacx = e.atacx
and t.bbapty = e.atapty
and trim(t.bbblno) = trim(e.atblno)
where t.data_date = '20141128' -- vdate
and t.bbavaa <> '0';
commit;
select * from fsd1.ta13_loan_balance t where t.data_date='2014-11-30'
and t.bank_id like 'GTRF%' and t.validate_status<>'3';
substr(t.loan_iou_code,1,length(loan_iou_code)-4)
update fsd1.ta13_loan_balance t set t.int_rate_is_fixed = (select
case when rate_type = 'S' then
'RF01'
else
'RF02'
end as int_rate_is_fixed from tmp_fsd_GTRF_LOAN a
where a.lending_ref = substr(t.loan_iou_code,1,length(loan_iou_code)-4)
)
where t.data_date='2014-11-30'
and t.bank_id like 'GTRF%' and t.validate_status<>'3';
,
