408 lines
12 KiB
SQL
408 lines
12 KiB
SQL
DELIMITER $$
|
||
|
||
USE `apzl`$$
|
||
|
||
DROP PROCEDURE IF EXISTS `proc_terminate_bill_income`$$
|
||
|
||
CREATE DEFINER=`apzl`@`%` PROCEDURE `proc_terminate_bill_income`(
|
||
l_contract_id VARCHAR (200),
|
||
l_flowunid VARCHAR (200),
|
||
L_INPUTUSERID VARCHAR (200),
|
||
L_INPUTORGID VARCHAR (200)
|
||
)
|
||
BEGIN
|
||
DECLARE l_bill_money DOUBLE (22, 2) ;
|
||
-- 可核销金额
|
||
DECLARE l_adjust_type VARCHAR (32) ;
|
||
-- 提前结清类型
|
||
DECLARE l_payday_adjust VARCHAR (32) ;
|
||
-- 提前结清日
|
||
DECLARE l_handling_charge DOUBLE (22, 2) ; -- 违约手续费
|
||
DECLARE l_SDHANDLING_CHARGE DOUBLE(22,2); -- 商定违约金
|
||
DECLARE l_SDNOMINAL_PRICE DOUBLE(22,2); -- 商定留购价
|
||
DECLARE l_FIST_CORPUS DOUBLE (22, 2) ;-- 一年内剩余利息
|
||
DECLARE l_caution_money DOUBLE (22, 2) ;-- 客户保证金
|
||
DECLARE l_OTHER_IN DOUBLE (22, 2) ;-- 其他应收
|
||
DECLARE l_AGREED_DATEPENALTY DOUBLE (22, 2); -- 还款日差利息
|
||
-- 本方银行、账户、账号、流水号
|
||
DECLARE l_acc_number VARCHAR (32) ;
|
||
DECLARE l_acc_bank VARCHAR (32) ;
|
||
DECLARE l_acc_name VARCHAR (32) ;
|
||
DECLARE l_ebank_sn VARCHAR (32) ;
|
||
-- 对方本方银行、账户、账号
|
||
DECLARE l_client_bank VARCHAR (32) ;
|
||
DECLARE l_client_account VARCHAR (32) ;
|
||
DECLARE l_client_accnum VARCHAR (32) ;
|
||
DECLARE l_hire_date VARCHAR (32) ; -- 核销网银日期
|
||
-- 查询可核销的挂账金额
|
||
|
||
-- DECLARE done BOOLEAN DEFAULT FALSE;
|
||
-- DECLARE cur CURSOR FOR
|
||
SELECT
|
||
bill.fact_date,
|
||
bill.had_money,
|
||
bill.ownacc_number,
|
||
bill.own_bank,
|
||
bill.own_account,
|
||
-- bill.ebank_sn,
|
||
bill.id,
|
||
bill.client_bank,
|
||
bill.client_account,
|
||
bill.client_acc_number
|
||
INTO
|
||
l_hire_date,
|
||
l_bill_money,
|
||
l_acc_number,
|
||
l_acc_bank,
|
||
l_acc_name,
|
||
l_ebank_sn,
|
||
l_client_bank,
|
||
l_client_account,
|
||
l_client_accnum
|
||
FROM
|
||
(SELECT
|
||
lcebank.id,
|
||
-- le.ebank_number,
|
||
-- le.mayope_money,
|
||
-- le.contract_id,
|
||
-- le.payment_number,
|
||
MAX(le.fact_date) fact_date,
|
||
MAX(le.ownacc_number) ownacc_number,
|
||
MAX(le.own_bank) own_bank,
|
||
MAX(le.own_account) own_account,
|
||
-- le.client_name,
|
||
-- le.client_bank,
|
||
-- le.client_account,
|
||
-- le.client_acc_number,
|
||
SUM(le.had_money ) had_money,
|
||
MAX(le.ebank_sn) ebank_sn,
|
||
MAX(le.client_bank) client_bank,
|
||
MAX(le.client_account) client_account,
|
||
MAX(le.client_acc_number) client_acc_number
|
||
FROM
|
||
lc_ebank_temp le LEFT JOIN lc_ebank lcebank ON le.ebank_sn=lcebank.ebank_sn
|
||
WHERE le.flowunid = l_flowunid
|
||
AND NOT EXISTS
|
||
(SELECT
|
||
1
|
||
FROM
|
||
lc_occupy_rent_list lorl,
|
||
lc_calc_condition_status lccs
|
||
WHERE lorl.payment_number = lccs.payment_number
|
||
AND lccs.contract_id = le.contract_id)
|
||
) bill ;
|
||
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
|
||
-- 提前结清是完整的核销,如果合同项下有租金被占用,则不核销
|
||
|
||
-- 获取提前结清信息
|
||
SELECT lfra.adjust_type,
|
||
lfra.payday_adjust,
|
||
lfra.SDHANDLING_CHARGE,
|
||
lfra.FIST_CORPUS,
|
||
lfra.OTHER_IN,
|
||
lfra.AGREED_DATEPENALTY,
|
||
lfra.OTHER_OUT,
|
||
lfra.SDNOMINAL_PRICE
|
||
INTO l_adjust_type,
|
||
l_payday_adjust,
|
||
l_SDHANDLING_CHARGE,
|
||
l_FIST_CORPUS,
|
||
l_OTHER_IN,
|
||
l_AGREED_DATEPENALTY,
|
||
l_caution_money,
|
||
l_SDNOMINAL_PRICE
|
||
FROM lc_fund_rent_adjust_temp lfra
|
||
WHERE lfra.flowunid=l_flowunid;
|
||
-- 如果有保证金指其他应退，在资金计划实收表核销保证金抵扣
|
||
IF (l_caution_money > 0) THEN
|
||
INSERT INTO lc_fund_income
|
||
(id,
|
||
project_id,
|
||
project_plan_number,
|
||
contract_id,
|
||
contract_plan_number,
|
||
payment_number,
|
||
plan_id,
|
||
fee_type,
|
||
plan_list,
|
||
settle_method,
|
||
charge_list,
|
||
pay_type,
|
||
fact_date,
|
||
fact_money,
|
||
fee_adjust,
|
||
EBANK_NUMBER,
|
||
bank,
|
||
account,
|
||
acc_number,
|
||
client_bank,
|
||
client_account,
|
||
client_accnumber,
|
||
pay_status,
|
||
ROLL_BACK,
|
||
INPUTUSERID,
|
||
INPUTORGID,
|
||
inputtime,
|
||
DEDUCTION_ID
|
||
|
||
)(
|
||
SELECT REPLACE(UUID(),'-',''),
|
||
lfi.project_id,
|
||
lfi.project_plan_number,
|
||
lfi.contract_id,
|
||
lfi.contract_plan_number,
|
||
lfi.payment_number,
|
||
lfi.id,
|
||
lfi.fee_type,
|
||
lfi.plan_list,
|
||
'settlemethod7',
|
||
'1',
|
||
'pay_type_out',
|
||
l_payday_adjust,
|
||
lfi.plan_money,
|
||
IFNULL(lfi.fee_adjust,0),
|
||
l_ebank_sn,
|
||
l_acc_bank,
|
||
l_acc_name,
|
||
l_acc_number,
|
||
l_client_bank,
|
||
l_client_account,
|
||
l_client_accnum,
|
||
'have_paid',
|
||
'0',
|
||
L_INPUTUSERID,
|
||
L_INPUTORGID,
|
||
REPLACE(CURDATE(),'-','/'),
|
||
''
|
||
FROM lc_fund_plan lfi
|
||
WHERE (lfi.fee_type='feetype16' OR lfi.fee_type='feetype17') AND lfi.contract_id=l_contract_id AND lfi.plan_list=1
|
||
) ;
|
||
-- COMMIT;
|
||
END IF;
|
||
-- 如果有其他应收，在资金计划表加入其他应收资金计划
|
||
IF l_OTHER_IN > 0 THEN
|
||
INSERT INTO lc_fund_income
|
||
(id,
|
||
project_id,
|
||
project_plan_number,
|
||
contract_id,
|
||
contract_plan_number,
|
||
payment_number,
|
||
plan_id,
|
||
fee_type,
|
||
plan_list,
|
||
settle_method,
|
||
charge_list,
|
||
pay_type,
|
||
fact_date,
|
||
fact_money,
|
||
fee_adjust,
|
||
EBANK_NUMBER,
|
||
bank,
|
||
account,
|
||
acc_number,
|
||
client_bank,
|
||
client_account,
|
||
client_accnumber,
|
||
pay_status,
|
||
ROLL_BACK,
|
||
INPUTUSERID,
|
||
INPUTORGID,
|
||
inputtime,
|
||
DEDUCTION_ID
|
||
|
||
)(
|
||
SELECT REPLACE(UUID(),'-',''),
|
||
lfi.project_id,
|
||
lfi.project_plan_number,
|
||
lfi.contract_id,
|
||
lfi.contract_plan_number,
|
||
lfi.payment_number,
|
||
lfi.id,
|
||
lfi.fee_type,
|
||
lfi.plan_list,
|
||
'settlemethod11',
|
||
'1',
|
||
'pay_type_in',
|
||
l_payday_adjust,
|
||
lfi.plan_money,
|
||
IFNULL(lfi.fee_adjust,0),
|
||
l_ebank_sn,
|
||
l_acc_bank,
|
||
l_acc_name,
|
||
l_acc_number,
|
||
l_client_bank,
|
||
l_client_account,
|
||
l_client_accnum,
|
||
'have_paid',
|
||
'0',
|
||
L_INPUTUSERID,
|
||
L_INPUTORGID,
|
||
REPLACE(CURDATE(),'-','/'),
|
||
''
|
||
FROM lc_fund_plan lfi
|
||
WHERE lfi.fee_type='feetype1' AND lfi.contract_id=l_contract_id AND lfi.plan_list=1
|
||
AND lfi.id NOT IN (SELECT plan_id FROM lc_fund_income)
|
||
) ;
|
||
-- COMMIT;
|
||
END IF;
|
||
-- 核销商定留购价
|
||
IF l_SDNOMINAL_PRICE > 0 THEN
|
||
INSERT INTO lc_fund_income
|
||
(id,
|
||
project_id,
|
||
project_plan_number,
|
||
contract_id,
|
||
contract_plan_number,
|
||
payment_number,
|
||
plan_id,
|
||
fee_type,
|
||
plan_list,
|
||
settle_method,
|
||
charge_list,
|
||
pay_type,
|
||
fact_date,
|
||
fact_money,
|
||
fee_adjust,
|
||
EBANK_NUMBER,
|
||
bank,
|
||
account,
|
||
acc_number,
|
||
client_bank,
|
||
client_account,
|
||
client_accnumber,
|
||
pay_status,
|
||
ROLL_BACK,
|
||
INPUTUSERID,
|
||
INPUTORGID,
|
||
inputtime,
|
||
DEDUCTION_ID
|
||
|
||
)(
|
||
SELECT REPLACE(UUID(),'-',''),
|
||
lfi.project_id,
|
||
lfi.project_plan_number,
|
||
lfi.contract_id,
|
||
lfi.contract_plan_number,
|
||
lfi.payment_number,
|
||
lfi.id,
|
||
lfi.fee_type,
|
||
lfi.plan_list,
|
||
'settlemethod11',
|
||
'1',
|
||
'pay_type_in',
|
||
l_payday_adjust,
|
||
l_SDNOMINAL_PRICE,
|
||
lfi.plan_money-l_SDNOMINAL_PRICE,
|
||
l_ebank_sn,
|
||
l_acc_bank,
|
||
l_acc_name,
|
||
l_acc_number,
|
||
l_client_bank,
|
||
l_client_account,
|
||
l_client_accnum,
|
||
'have_paid',
|
||
'0',
|
||
L_INPUTUSERID,
|
||
L_INPUTORGID,
|
||
REPLACE(CURDATE(),'-','/'),
|
||
''
|
||
FROM lc_fund_plan lfi
|
||
WHERE lfi.fee_type='feetype4' AND lfi.contract_id=l_contract_id AND lfi.plan_list=1
|
||
AND lfi.id NOT IN (SELECT plan_id FROM lc_fund_income)
|
||
) ;
|
||
-- COMMIT;
|
||
END IF;
|
||
-- 核销租金计划
|
||
|
||
INSERT INTO LC_RENT_INCOME (
|
||
ID,
|
||
QUOT_ID,
|
||
CUST_ID,
|
||
PROJECT_ID,
|
||
PROJECT_PLAN_NUMBER,
|
||
CONTRACT_ID,
|
||
CONTRACT_PLAN_NUMBER,
|
||
PAYMENT_NUMBER,
|
||
EBANK_NUMBER,
|
||
PLAN_ID,
|
||
PLAN_LIST,
|
||
INTEREST_ADJUST,
|
||
SETTLE_METHOD,
|
||
HIRE_LIST,
|
||
HIRE_DATE,
|
||
RENT,
|
||
CORPUS,
|
||
INTEREST,
|
||
PENALTY,
|
||
CORPUS_ADJUST,
|
||
PENALTY_ADJUST,
|
||
ROLL_BACK,
|
||
COIN,
|
||
hire_number,
|
||
hire_bank,
|
||
hire_account,
|
||
own_bank,
|
||
own_account,
|
||
own_number
|
||
)
|
||
(SELECT
|
||
REPLACE (UUID(), '-', '') AS id,
|
||
O.QUOT_ID AS QUOT_ID,
|
||
O.CUST_ID AS CUST_ID,
|
||
O.PROJECT_ID AS PROJECT_ID,
|
||
O.PROJECT_PLAN_NUMBER AS PROJECT_PLAN_NUMBER,
|
||
O.CONTRACT_ID AS CONTRACT_ID,
|
||
O.CONTRACT_PLAN_NUMBER AS CONTRACT_PLAN_NUMBER,
|
||
O.PAYMENT_NUMBER AS PAYMENT_NUMBER,
|
||
l_ebank_sn AS EBANK_NUMBER,
|
||
lrp.id AS PLAN_ID,
|
||
O.PLAN_LIST AS PLAN_LIST,
|
||
'0' AS INTEREST_ADJUST,
|
||
'settlemethod6' AS SETTLE_METHOD,
|
||
'1' AS HIRE_LIST,
|
||
DATE_FORMAT (l_hire_date,'%Y/%m/%d') AS HIRE_DATE,
|
||
o.rent AS RENT,
|
||
o.corpus AS CORPUS,
|
||
o.interest AS INTEREST,
|
||
'0' AS PENALTY,
|
||
'0' AS CORPUS_ADJUST,
|
||
'0' AS PENALTY_ADJUST,
|
||
'0' AS ROLL_BACK,
|
||
O.COIN AS COIN ,
|
||
l_client_accnum,
|
||
l_client_bank,
|
||
l_client_account,
|
||
l_acc_bank,
|
||
l_acc_name,
|
||
l_acc_number
|
||
FROM
|
||
LC_RENT_PLAN_temp O
|
||
LEFT JOIN lC_RENT_PLAN lrp ON o.contract_id=lrp.contract_id AND o.plan_list=lrp.plan_list
|
||
WHERE O.contract_id =l_contract_id
|
||
AND o.flowunid = l_flowunid
|
||
AND IFNULL(o.collect_status,'111')<>'手工收款'
|
||
AND IFNULL(o.collect_status,'111')<>'批量收款'
|
||
AND IFNULL(o.collect_status,'111')<>'微信'
|
||
AND IFNULL(o.collect_status,'1111')<>'网银收款'
|
||
AND IFNULL(o.collect_status,'1111')<>'提前结清'
|
||
) ;
|
||
-- 更新租金计划表核销状态
|
||
|
||
UPDATE LC_RENT_PLAN_temp SET collect_status='提前结清',collect_msg='提前结清'
|
||
WHERE flowunid = l_flowunid
|
||
AND IFNULL(collect_status,'1111')<>'手工收款'
|
||
AND IFNULL(collect_status,'111')<>'批量收款'
|
||
AND IFNULL(collect_status,'111')<>'微信'
|
||
AND IFNULL(collect_status,'1111')<>'网银收款'
|
||
AND IFNULL(collect_status,'1111')<>'提前结清'
|
||
;
|
||
|
||
-- 调试结果：参数赋值
|
||
SELECT '步骤1：参数赋值','可核销金额',l_bill_money,'本方账号',l_acc_number,
|
||
'本分银行',l_acc_bank,'本分账户',l_acc_name,'应退保证金',l_caution_money,'违约金',l_SDHANDLING_CHARGE,'其他应收',l_OTHER_IN;
|
||
-- end loop myLoop;
|
||
-- close cur;
|
||
END$$
|
||
|
||
DELIMITER ; |