--非额度合同
SELECT con.contract_num,
round((case when SUM(grt.guaranty_amt) >= con.contract_total_amt then
con.contract_total_amt
else
SUM(grt.guaranty_amt) end) /con.contract_total_amt*su.contract_balance,2)
FROM TB_CON_CONTRACT CON,
TB_CON_BORR_ACCT_SUMMARY BO,
TB_CON_ACCT_SUMMARY SU,
TB_CON_SUBCONTRACT SUB,
TB_GRT_BUSINESS_RELATION GRT,
TB_GRT_COLLATERAL COLL,
TB_BIZ_BUSINESS BIZ,
tb_con_payout_info_detail det,
(select Contract_Num,
Ltrim(Decode(Substr(Guaranty_Type, 12, 1), '1', '信用') || Decode(Substr(Guaranty_Type, 13, 1), '1', ';保证') ||
Decode(Substr(Guaranty_Type, 14, 1), '1', ';抵押') || Decode(Substr(Guaranty_Type, 15, 1), '1', ';质押') ||
Decode(Substr(Guaranty_Type, 16, 1), '1', ';保证金') || Decode(Substr(Guaranty_Type, 17, 1), '1', ';其他'),
';') Guaranty_Type
From Tb_Con_Contract) CO
WHERE CON.CONTRACT_NUM = SUB.CONTRACT_NUM
AND CON.CONTRACT_NUM = BO.CONTRACT_NUM
AND CON.CONTRACT_NUM = SU.CONTRACT_NUM
AND CON.CONTRACT_NUM = CO.CONTRACT_NUM
AND SUB.SUBCONTRACT_NUM = GRT.BIZ_LIMIT_CONT_NUM
AND GRT.GUARANTY_ID = COLL.GUARANTY_ID
AND CON.BIZ_NUM = BIZ.BIZ_NUM
AND bo.borrow_num = det.borrow_num
AND GRT.GUARANTY_RELATION_TYPE_CD = '4'
AND biz.biz_nature_cd not IN ('04','26') ----非额度合同项下业务
AND CO.Guaranty_Type LIKE '%抵押%'
AND GRT.VALID_IND = '1' --生效
and con.credit_product_cd <> '031402' --小企业法人按揭贷款
AND substr(COLL.COLLATERAL_CATALOG_CD,0,1) = 'C' --房地产
group by CON.CONTRACT_NUM,con.contract_total_amt,su.contract_balance;

--额度合同
SELECT con.contract_num,
round((case when SUM(grt.guaranty_amt) >= cc.contract_total_amt then
su.contract_balance
else
SUM(grt.guaranty_amt) end)/cc.contract_total_amt*su.contract_balance,2)
FROM TB_CON_CONTRACT CON,
TB_CON_BORR_ACCT_SUMMARY BO,
TB_CON_ACCT_SUMMARY SU,
TB_CON_GUARANTY_RELATION SUB,
TB_GRT_BUSINESS_RELATION GRT,
TB_GRT_COLLATERAL COLL,
TB_BIZ_BUSINESS BIZ,
tb_con_payout_info_detail det,
(select Contract_Num,
Ltrim(Decode(Substr(Guaranty_Type, 12, 1), '1', '信用') || Decode(Substr(Guaranty_Type, 13, 1), '1', ';保证') ||
Decode(Substr(Guaranty_Type, 14, 1), '1', ';抵押') || Decode(Substr(Guaranty_Type, 15, 1), '1', ';质押') ||
Decode(Substr(Guaranty_Type, 16, 1), '1', ';保证金') || Decode(Substr(Guaranty_Type, 17, 1), '1', ';其他'),
';') Guaranty_Type
From Tb_Con_Contract) CO,
tb_con_contract cc
WHERE CON.CONTRACT_NUM = SUB.CONTRACT_NUM
AND CON.CONTRACT_NUM = BO.CONTRACT_NUM
AND CON.CONTRACT_NUM = SU.CONTRACT_NUM
AND CON.CONTRACT_NUM = CO.CONTRACT_NUM
AND SUB.SUBCONTRACT_NUM = GRT.BIZ_LIMIT_CONT_NUM
AND GRT.GUARANTY_ID = COLL.GUARANTY_ID
AND CON.BIZ_NUM = BIZ.BIZ_NUM
and CON.Super_Contract_Num = CC.CONTRACT_NUM
AND bo.borrow_num = det.borrow_num
AND GRT.GUARANTY_RELATION_TYPE_CD = '4'
AND biz.biz_nature_cd IN ('04','26')
AND CO.Guaranty_Type LIKE '%抵押%'
AND GRT.VALID_IND = '1'
and con.credit_product_cd <> '031402' --小企业法人按揭贷款
AND substr(COLL.COLLATERAL_CATALOG_CD,0,1) = 'C' --房地产
group by CON.CONTRACT_NUM,cc.contract_total_amt,su.contract_balance;