50 lines
2.6 KiB
SQL
50 lines
2.6 KiB
SQL
DELIMITER $$
|
||
|
||
ALTER ALGORITHM=UNDEFINED DEFINER=`apzl`@`%` SQL SECURITY DEFINER VIEW `vi_business_info` AS (
|
||
SELECT
|
||
`lci`.`ID` AS `id`,
|
||
`lci`.`PROJECT_ID` AS `project_id`,
|
||
`lci`.`PRODUCT_ID` AS `PRODUCT_ID`,
|
||
`lci`.`FLOWUNID` AS `flowunid`,
|
||
`cs`.`business_status` AS `CONTRACT_STAT`,
|
||
`lci`.`CONTRACT_NO` AS `contract_no`,
|
||
`lul`.`CUSTOMER_NAME` AS `CUSTOMER_NAME`,
|
||
`lpi`.`PROJECT_NO` AS `PROJECT_NO`,
|
||
`lec`.`FRAME_NUMBER` AS `FRAME_NUMBER`,
|
||
`lec`.`VNDR_NAME` AS `VNDR_NAME`,
|
||
`lfi`.`APPLYPAY_DATE` AS `APPLYPAY_DATE`,
|
||
`lci`.`PROJECT_DATE` AS `PROJECT_DATE`
|
||
FROM (((((`apzl`.`lb_contract_info` `lci`
|
||
LEFT JOIN (SELECT
|
||
`bs`.`contract_number` AS `contract_number`,
|
||
`c`.`itemname` AS `business_status`
|
||
FROM (`apzl`.`business_status` `bs`
|
||
LEFT JOIN `apzl`.`code_library` `c`
|
||
ON (((`c`.`itemno` = `bs`.`business_status`)
|
||
AND (`c`.`codeno` = 'BusinessStatus'))))
|
||
WHERE EXISTS(SELECT
|
||
1
|
||
FROM (SELECT
|
||
`apzl`.`business_status`.`contract_number` AS `contract_number`,
|
||
MAX(`apzl`.`business_status`.`inputtime`) AS `inputtime`
|
||
FROM `apzl`.`business_status`
|
||
GROUP BY `apzl`.`business_status`.`contract_number`) `t`
|
||
WHERE ((`t`.`contract_number` = `bs`.`contract_number`)
|
||
AND (`t`.`inputtime` = `bs`.`inputtime`)))) `cs`
|
||
ON ((`cs`.`contract_number` = `lci`.`CONTRACT_NO`)))
|
||
LEFT JOIN `apzl`.`lb_union_lessee` `lul`
|
||
ON ((`lci`.`PROJECT_ID` = `lul`.`PROJECT_ID`)))
|
||
LEFT JOIN `apzl`.`lb_project_info` `lpi`
|
||
ON ((`lpi`.`ID` = `lci`.`PROJECT_ID`)))
|
||
LEFT JOIN `apzl`.`lb_equipment_car` `lec`
|
||
ON ((`lec`.`CONTRACT_ID` = `lci`.`ID`)))
|
||
LEFT JOIN (SELECT
|
||
`apzl`.`flow_bussiness_object`.`contract_id` AS `contract_id`,
|
||
LEFT(`apzl`.`flow_bussiness_object`.`InputTime`,10) AS `APPLYPAY_DATE`
|
||
FROM `apzl`.`flow_bussiness_object`
|
||
WHERE (`apzl`.`flow_bussiness_object`.`flow_name` = '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>(<28><><EFBFBD><EFBFBD>)')
|
||
GROUP BY `apzl`.`flow_bussiness_object`.`contract_id`) `lfi`
|
||
ON ((`lci`.`ID` = `lfi`.`contract_id`)))
|
||
WHERE (`lci`.`BUSINESSTYPE` = '1' AND lec.car_attribute='head'))$$
|
||
|
||
DELIMITER ; |