248 lines
6.1 KiB
SQL
248 lines
6.1 KiB
SQL
-- ÊÖ¹¤¿¨¿ÛÊÓͼ
|
|
drop view if EXISTS vi_manual_dedution;
|
|
CREATE ALGORITHM = UNDEFINED DEFINER = `apzl`@`%` SQL SECURITY DEFINER VIEW `vi_manual_dedution` AS SELECT
|
|
`lrp`.`ID` AS `id`,
|
|
`lci`.`ID` AS `contract_id`,
|
|
`lci`.`BUSINESSTYPE` AS `businesstype`,
|
|
`lci`.`SUBJECTID` AS `subjectid`,
|
|
`lci`.`SUBJECTNAME` AS `subjectname`,
|
|
`lci`.`distributor_id` AS `distributor_id`,
|
|
`lci`.`CONTRACT_NUMBER` AS `contract_number`,
|
|
`lrp`.`PAYMENT_NUMBER` AS `payment_number`,
|
|
ifnull(`lul`.`CUSTOMER_NAME`, '') AS `customername`,
|
|
`ca`.`acc_number` AS `acc_number`,
|
|
`ca`.`collect_type` AS `collect_type`,
|
|
`ca`.`CERTID` AS `certid`,
|
|
`lrp`.`PLAN_LIST` AS `plan_list`,
|
|
`lrp`.`PLAN_DATE` AS `plan_date`,
|
|
ifnull(
|
|
`lrp`.`COLLECT_STATUS`,
|
|
'δÊÕ¿î'
|
|
) AS `collect_status`,
|
|
`lrp`.`COLLECT_MSG` AS `COLLECT_MSG`,
|
|
`lrp`.`BATCH_STATUS` AS `BATCH_STATUS`,
|
|
`lrp`.`RENT` AS `rent`,
|
|
`lrp`.`CORPUS` AS `corpus`,
|
|
`lrp`.`INTEREST` AS `interest`,
|
|
`getRentPenalty` (
|
|
`lrp`.`PAYMENT_NUMBER`,
|
|
`lrp`.`PLAN_LIST`,
|
|
date_format(now(), '%Y/%m/%d')
|
|
) AS `penalty`,
|
|
(
|
|
`lrp`.`RENT` - (
|
|
ifnull(`lcfd`.`rent`, 0) + ifnull(`lcfi`.`rent`, 0)
|
|
)
|
|
) AS `rent_over`,
|
|
(
|
|
`lrp`.`CORPUS` - (
|
|
(
|
|
(
|
|
ifnull(`lcfd`.`corpus`, 0) + ifnull(`lcfi`.`corpus`, 0)
|
|
) + ifnull(`lcfd`.`corpus_adjust`, 0)
|
|
) + ifnull(`lcfi`.`corpus_adjust`, 0)
|
|
)
|
|
) AS `corpus_over`,
|
|
(
|
|
`lrp`.`INTEREST` - (
|
|
(
|
|
(
|
|
ifnull(`lcfd`.`interest`, 0) + ifnull(`lcfi`.`interest`, 0)
|
|
) + ifnull(`lcfd`.`interest_adjust`, 0)
|
|
) + ifnull(`lcfi`.`interest_adjust`, 0)
|
|
)
|
|
) AS `interest_over`,
|
|
round(
|
|
(
|
|
(
|
|
ifnull(`lcfd`.`penalty`, 0) + ifnull(`lcfi`.`penalty`, 0)
|
|
) + (
|
|
ifnull(`lcfd`.`penalty_adjust`, 0) + ifnull(`lcfi`.`penalty_adjust`, 0)
|
|
)
|
|
),
|
|
2
|
|
) AS `penalty_sum`
|
|
FROM
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
(
|
|
SELECT
|
|
`l`.`ID` AS `ID`,
|
|
`l`.`PROJECT_ID` AS `PROJECT_ID`,
|
|
`l`.`PROJECT_PLAN_NUMBER` AS `PROJECT_PLAN_NUMBER`,
|
|
`l`.`CONTRACT_ID` AS `CONTRACT_ID`,
|
|
`l`.`CONTRACT_PLAN_NUMBER` AS `CONTRACT_PLAN_NUMBER`,
|
|
`l`.`PAYMENT_NUMBER` AS `PAYMENT_NUMBER`,
|
|
`l`.`PLAN_LIST` AS `PLAN_LIST`,
|
|
`l`.`PLAN_DATE` AS `PLAN_DATE`,
|
|
`l`.`PLAN_STATUS` AS `PLAN_STATUS`,
|
|
`l`.`RENT` AS `RENT`,
|
|
`l`.`CORPUS` AS `CORPUS`,
|
|
`l`.`INTEREST` AS `INTEREST`,
|
|
`l`.`PENALTY` AS `PENALTY`,
|
|
`l`.`CORPUS_BUSINESS` AS `CORPUS_BUSINESS`,
|
|
`l`.`INTEREST_BUSINESS` AS `INTEREST_BUSINESS`,
|
|
`l`.`RENT_ADJUST` AS `RENT_ADJUST`,
|
|
`l`.`ALL_REMAIN_CORPUS` AS `ALL_REMAIN_CORPUS`,
|
|
`l`.`SETTLE_METHOD` AS `SETTLE_METHOD`,
|
|
`l`.`COIN` AS `COIN`,
|
|
`l`.`YEAR_RATE` AS `YEAR_RATE`,
|
|
`l`.`MEMO` AS `MEMO`,
|
|
`l`.`FLOWUNID` AS `FLOWUNID`,
|
|
`l`.`INPUTUSERID` AS `INPUTUSERID`,
|
|
`l`.`INPUTORGID` AS `INPUTORGID`,
|
|
`l`.`INPUTTIME` AS `INPUTTIME`,
|
|
`l`.`INTEREST_DATE` AS `INTEREST_DATE`,
|
|
`l`.`AUDIT_STATE` AS `AUDIT_STATE`,
|
|
`l`.`COLLECT_STATUS` AS `COLLECT_STATUS`,
|
|
`l`.`COLLECT_MSG` AS `COLLECT_MSG`,
|
|
`l`.`FAILURE_TIME` AS `FAILURE_TIME`,
|
|
`l`.`FAILURE_REASON` AS `FAILURE_REASON`,
|
|
`l`.`BATCH_NO` AS `BATCH_NO`,
|
|
`l`.`BATCH_STATUS` AS `BATCH_STATUS`,
|
|
`l`.`batch_sn` AS `batch_sn`,
|
|
`l`.`SPARE_BATCH_NO` AS `SPARE_BATCH_NO`,
|
|
`l`.`CORPUS_SP` AS `CORPUS_SP`,
|
|
`l`.`INTEREST_sp` AS `INTEREST_sp`,
|
|
`l`.`splitting_status` AS `splitting_status`,
|
|
`l`.`charge_way` AS `charge_way`
|
|
FROM
|
|
`apzl`.`lc_rent_plan` `l`
|
|
WHERE
|
|
(
|
|
(
|
|
(
|
|
`l`.`COLLECT_STATUS` IN (
|
|
'δÊÕ¿î',
|
|
'²¿·ÖÊÕ¿î',
|
|
'´ú³¥'
|
|
)
|
|
)
|
|
OR isnull(`l`.`COLLECT_STATUS`)
|
|
)
|
|
AND (
|
|
`l`.`PLAN_DATE` <= CONVERT (
|
|
date_format(now(), '%Y/%m/%d') USING gbk
|
|
)
|
|
)
|
|
AND (
|
|
`l`.`ID` = (
|
|
SELECT
|
|
`t`.`ID`
|
|
FROM
|
|
`apzl`.`lc_rent_plan` `t`
|
|
WHERE
|
|
(
|
|
(
|
|
`t`.`CONTRACT_ID` = `l`.`CONTRACT_ID`
|
|
)
|
|
AND (
|
|
(
|
|
`t`.`COLLECT_STATUS` IN (
|
|
'δÊÕ¿î',
|
|
'²¿·ÖÊÕ¿î',
|
|
'´ú³¥'
|
|
)
|
|
)
|
|
OR isnull(`t`.`COLLECT_STATUS`)
|
|
)
|
|
)
|
|
ORDER BY
|
|
`t`.`PAYMENT_NUMBER`,
|
|
`t`.`PLAN_LIST`
|
|
LIMIT 1
|
|
)
|
|
)
|
|
)
|
|
)
|
|
) `lrp`
|
|
LEFT JOIN `apzl`.`lb_contract_info` `lci` ON (
|
|
(
|
|
`lrp`.`CONTRACT_ID` = `lci`.`ID`
|
|
)
|
|
)
|
|
)
|
|
LEFT JOIN `apzl`.`lb_union_lessee` `lul` ON (
|
|
(
|
|
(
|
|
`lrp`.`CONTRACT_ID` = `lul`.`CONTRACT_ID`
|
|
)
|
|
AND (`lul`.`IS_MAIN` = 'Y')
|
|
)
|
|
)
|
|
)
|
|
LEFT JOIN `apzl`.`lc_rent_income_transfer` `lcfd` ON (
|
|
(
|
|
(
|
|
`lrp`.`PAYMENT_NUMBER` = `lcfd`.`payment_number`
|
|
)
|
|
AND (
|
|
`lrp`.`PLAN_LIST` = `lcfd`.`plan_list`
|
|
)
|
|
)
|
|
)
|
|
)
|
|
LEFT JOIN `apzl`.`lc_rent_income_temp_transfer` `lcfi` ON (
|
|
(
|
|
(
|
|
`lrp`.`PAYMENT_NUMBER` = `lcfi`.`payment_number`
|
|
)
|
|
AND (
|
|
`lrp`.`PLAN_LIST` = `lcfi`.`plan_list`
|
|
)
|
|
)
|
|
)
|
|
)
|
|
LEFT JOIN `apzl`.`customer_account` `ca` ON (
|
|
(
|
|
(
|
|
`lci`.`ID` = `ca`.`CONTRACT_ID`
|
|
)
|
|
AND (`ca`.`acc_info` = 'Debit')
|
|
)
|
|
)
|
|
)
|
|
WHERE
|
|
(
|
|
(
|
|
`lci`.`CONTRACT_STATUS` = '31'
|
|
)
|
|
AND (`lci`.`BUSINESSTYPE` = '1')
|
|
AND (
|
|
NOT (
|
|
EXISTS (
|
|
SELECT
|
|
1
|
|
FROM
|
|
`apzl`.`lc_occupy_rent_list` `lorl`
|
|
WHERE
|
|
(
|
|
(
|
|
`lorl`.`PAYMENT_NUMBER` = `lrp`.`PAYMENT_NUMBER`
|
|
)
|
|
AND (
|
|
(
|
|
(
|
|
`lorl`.`PLAN_ID` = `lrp`.`ID`
|
|
)
|
|
AND (
|
|
`lorl`.`FLOW_NAME` IN (
|
|
'΢ÐÅÒøÁªÊÕ¿î',
|
|
'΢ÐÅÖ§¸¶ÊÕ¿î',
|
|
'ÍøÒøÊÕ¿î'
|
|
)
|
|
)
|
|
)
|
|
OR (
|
|
`lorl`.`FLOW_NAME` = 'ºÏͬÖÐ;ÖÕÖ¹'
|
|
)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
); |