报表分类:
报表编号:
RPT_FINANCE_1074
报表名称:
适用终端:
---请选择---
桌面端
移动端
通用
配置文件上传:
版本号:
配置内容:
选择模板
<report xmlns="http://www.sagframe.com/schema/nebula/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:do="http://www.sagframe.com/schema/nebula/convert" xmlns:chart="http://www.sagframe.com/schema/nebula/charts" xmlns:sqltoy="http://www.sagframe.com/schema/sqltoy" xsi:schemaLocation="http://www.sagframe.com/schema/nebula/core http://www.sagframe.com/schema/nebula/core/nebula-core.xsd http://www.sagframe.com/schema/nebula/convert http://www.sagframe.com/schema/nebula/convert/nebula-convert.xsd http://www.sagframe.com/schema/nebula/charts http://www.sagframe.com/schema/nebula/charts/nebula-charts.xsd http://www.sagframe.com/schema/sqltoy http://www.sagframe.com/schema/sqltoy/sqltoy.xsd" id="RPT_FINANCE_1074" name="RPT_FINANCE_1074" export-types="xlsx" asyn-export-limit="20000" asyn-page-size="5000"> <report-template> <![CDATA[ <table class="sag_listtable"> <thead> <tr class="titlist"> <th colspan="13">企业授信账单明细表</th> </tr> <tr class="titlist"> <th>类型</th> <th>单据编号</th> <th>企业名称</th> <th>单据日期</th> <th>订单编号</th> <th>订单日期</th> <th>产品组</th> <th>业务员</th> <th>跟单员</th> <th>币种</th> <th>金额</th> <th>授信到期日</th> <th>帐套</th> </tr> </thead> <tbody></tbody> </table> ]]> </report-template> <report-data> <data-sql save-as="report_data" query-method="pageQuery"> <sqltoy:sql id="rpt_datasql1"> <sqltoy:sharding-datasource strategy="skylineStrategy" ></sqltoy:sharding-datasource> <sqltoy:translate cache="staffIdName" columns="BIZ_STAFF_ID,DOCUMENTARY_BY" uncached-template="${value}" ></sqltoy:translate> <sqltoy:translate cache="bizTeamIdName" columns="BIZ_TEAM_ID" uncached-template="${value}" ></sqltoy:translate> <sqltoy:translate cache="companyIdName" columns="company_id" uncached-template="${value}"></sqltoy:translate> <sqltoy:translate cache="dictKeyName" cache-type="EC_CURRENCY" columns="CURRENCY" cache-indexs="1" uncached-template="${value}" ></sqltoy:translate> <sqltoy:translate cache="corpIdName" columns="CORP_ID" cache-indexs="1" uncached-template="${value}"></sqltoy:translate> <sqltoy:filters> <sqltoy:eq value="-1" params="*" ></sqltoy:eq> <sqltoy:cache-arg param="companyName" cache-name="companyIdName" alias-name="companyIds" cache-mapping-max="999" cache-mapping-indexes="1" ></sqltoy:cache-arg> </sqltoy:filters> <sqltoy:value> <![CDATA[ with t as ( SELECT DISTINCT company_id, CURRENCY, credit_kind, CORP_ID, ACCOUNT_DAY, if(COMPANY_TYPE=1,'P','S') order_type from ubmpdb.fin_credit_info where CREDIT_END_DATE > curdate() and CREDIT_TYPE = 1 #[and COMPANY_ID =:companyId] #[and CORP_ID =:cropIdA] #[and credit_kind =:creditKind] #[and CURRENCY =:currencyA] ), a as( -- 审批占用 SELECT t.order_type, t.company_id, t.ORDER_ID, t.rpt_submit_time, t.biz_team_id, t.biz_staff_id, t.documentary_by, t.currency, t.TOTAL_ORDER_AMT, date_add(t.rpt_submit_time,interval t.PAY_EXPIRE_DAYS day) expire_day, t.CORP_ID FROM ubmpdb.od_order_info t where t.STATUS IN( 'RPT_AUDITING','WAIT_AUDIT','RPT_AUDIT_PASS','WAIT_SIGN','WAIT_CONFIRM','AUDIT_UNPASS','SINGLE_SIGNED','DOUBLE_SIGNED','PRE_AUDIT') and (( t.ORDER_TYPE= 'P' AND t.PAY_TYPE in('1','2','4')) OR ( t.ORDER_TYPE= 'S' AND t.PAY_TYPE in ('3','5'))) and t.PERIOD_TYPE='S' and t.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) #[and t.corp_id =:cropIdA] #[and t.company_id =:companyId] #[and t.currency =:currencyA] ), audit_use as ( -- 审批占用 SELECT '审批占用' type, a.ORDER_ID orderNo, a.company_id, a.rpt_submit_time submitTime, a.ORDER_ID, a.rpt_submit_time, a.biz_team_id, a.biz_staff_id, a.documentary_by, a.currency, a.TOTAL_ORDER_AMT, a.expire_day, a.CORP_ID FROM t LEFT JOIN a on a.company_id = t.company_id and a.order_type = t.order_type and a.CURRENCY = t.CURRENCY and t.CORP_ID = a.CORP_ID ), b as( -- 合同占用 SELECT SUM(IF(ORDER_AMOUNT>0,IF(USE_PAY_RECEIPT_AMOUNT>USE_STORAGE_AMOUNT,USE_STORAGE_AMOUNT,USE_PAY_RECEIPT_AMOUNT),0)) ORDER_AMOUNTa, -- 金额 t.ORDER_TYPE, t.COMPANY_ID, t.currency, t.CORP_ID, t.ORDER_ID, t.sign_time, t.RPT_SUBMIT_TIME, t.BIZ_TEAM_ID, t.BIZ_STAFF_ID, t.DOCUMENTARY_BY, t.expire_day FROM ( SELECT t1.COMPANY_ID, -- 企业名称 t1.ORDER_TYPE, t1.CURRENCY CURRENCY1, t1.ORDER_AMOUNT, t1.STORAGE_AMOUNT, t1.PAY_RECEIPT_AMOUNT, IF(t1.ORDER_AMOUNT-t1.STORAGE_AMOUNT > 0 , t1.ORDER_AMOUNT-t1.STORAGE_AMOUNT ,0 ) USE_STORAGE_AMOUNT, IF(t1.ORDER_AMOUNT-t1.PAY_RECEIPT_AMOUNT > 0 , t1.ORDER_AMOUNT-t1.PAY_RECEIPT_AMOUNT ,0 ) USE_PAY_RECEIPT_AMOUNT, t2.currency, -- 币种 t1.CORP_ID, -- 帐套 t2.ORDER_ID, -- 单据编号/订单编号 t2.sign_time, -- 单据日期 t2.RPT_SUBMIT_TIME, -- 订单日期 t2.BIZ_TEAM_ID, -- 产品组 t2.BIZ_STAFF_ID, -- 业务员 t2.DOCUMENTARY_BY, -- 跟单员 date_add(t2.sign_time,interval t2.PAY_EXPIRE_DAYS day) expire_day -- 授信到期日 FROM ubmpdb.fin_capital_summary t1 LEFT JOIN ubmpdb.od_order_info t2 ON t1.ORDER_ID=t2.ORDER_ID and t1.CORP_ID = t2.CORP_ID WHERE t2.STATUS IN ('EXECUTING','COLL_GD','DLVR_GD') and ((t2.ORDER_TYPE= 'P' AND t2.PAY_TYPE in ('1','2','4')) OR ( t2.ORDER_TYPE= 'S' AND t2.PAY_TYPE in('3','5'))) and t2.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) #[and t1.corp_id =:cropIdA] #[and t1.company_id =:companyId] #[and t2.currency =:currencyA] ) t where IF(t.ORDER_AMOUNT>0,IF(USE_PAY_RECEIPT_AMOUNT>USE_STORAGE_AMOUNT,USE_STORAGE_AMOUNT,USE_PAY_RECEIPT_AMOUNT),0) > 0 GROUP BY t.company_id, t.ORDER_TYPE, t.currency, t.CORP_ID, t.ORDER_ID, t.sign_time, t.RPT_SUBMIT_TIME, t.BIZ_TEAM_ID, t.BIZ_STAFF_ID, t.DOCUMENTARY_BY, t.expire_day ), contract_use as ( SELECT '合同占用' type, b.ORDER_ID orderNo, b.COMPANY_ID, b.sign_time, b.ORDER_ID, b.rpt_submit_time, b.biz_team_id, b.biz_staff_id, b.documentary_by, b.currency, b.ORDER_AMOUNTa, b.expire_day, b.CORP_ID FROM t LEFT JOIN b on b.company_id = t.company_id and b.order_type = t.order_type and b.CURRENCY = t.CURRENCY and t.CORP_ID = b.CORP_ID ), c as ( -- 实际占用 SELECT t2.company_id, -- 企业名称 t2.ORDER_TYPE, t2.currency, -- 币种 d.CORP_ID, -- 帐套 d.source_code, -- 单据编号 d.source_date, -- 单据日期 t2.order_id, -- 订单号 t2.rpt_submit_time, -- 订单日期 t2.biz_team_id, -- 产品组 t2.biz_staff_id, -- 业务员 t2.documentary_by, -- 跟单员 if(t2.order_type = 'P', date_add(d.source_date, interval t2.delivery_expire_days day), date_add(d.source_date, interval t2.pay_expire_days day)) overdue_date, -- 授信到期日 sum(ifnull(d.NOT_CHECK_AMOUNT,0)) NOT_CHECK_AMOUNT from ubmpdb.fin_capital_detail d LEFT JOIN ubmpdb.od_order_info t2 on t2.order_id = d.order_id and d.CORP_ID = t2.CORP_ID where d.IS_COMPLETE_CHECK = 0 and t2.STATUS IN ('EXECUTING','COLL_GD','DLVR_GD') and d.SOURCE_TYPE in ('CK','FK') and t2.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) #[and d.corp_id =:cropIdA] #[and t2.company_id =:companyId] #[and t2.currency =:currencyA] GROUP BY t2.company_id, t2.ORDER_TYPE, t2.currency, d.CORP_ID, d.source_code, -- 单据编号 d.source_date, -- 单据日期 t2.order_id, -- 订单号 t2.rpt_submit_time, -- 订单日期 t2.biz_team_id, -- 产品组 t2.biz_staff_id, -- 业务员 t2.documentary_by, -- 跟单员 if(t2.order_type = 'P', date_add(d.source_date, interval t2.delivery_expire_days day), date_add(d.source_date, interval t2.pay_expire_days day)) -- 授信到期日 ), d as ( -- 自由款占用 SELECT h.payment_code, -- 单据编号 h.COMPANY_ID, -- 企业 h.PAYMENT_DATE, -- 单据日期 h.CURRENCY, -- 币种 h.CORP_ID, -- 帐套 sum(ifnull(h.PAYMENT_AMOUNT,0) - ifnull(h.CAPITAL_AMOUNT,0)) free_amount -- 自由款 from ubmpdb.fin_payment_head h where h.IS_FINISH_ORDER = '0' and h.PAYMENT_STATUS = '40' and h.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) #[and h.corp_id =:cropIdA] #[and h.company_id =:companyId] #[and h.currency =:currencyA] GROUP BY h.payment_code, -- 单据编号 h.COMPANY_ID, -- 企业 h.PAYMENT_DATE, -- 单据日期 h.CURRENCY, -- 币种 h.CORP_ID -- 帐套 ), actual_use as ( -- 实际占用汇总 SELECT '实际占用' type, c.source_code, c.COMPANY_ID, c.source_date, c.ORDER_ID, c.rpt_submit_time, c.biz_team_id, c.biz_staff_id, c.documentary_by, c.currency, c.NOT_CHECK_AMOUNT, -- 实际 c.overdue_date, c.CORP_ID FROM t LEFT JOIN c on c.company_Id = t.company_id and c.order_type = t.order_type and c.CURRENCY = t.CURRENCY and t.CORP_ID = c.CORP_ID UNION ALL SELECT '实际占用', d.payment_code, d.COMPANY_ID, d.payment_date, 'ZYK' orderNO, null, null, null, null, d.currency, d.free_amount, -- 自由款 DATE_ADD(d.payment_code,INTERVAL t.ACCOUNT_DAY DAY) overdue_date, d.CORP_ID FROM t LEFT JOIN d on d.company_id = t.company_id and t.order_type = 'P' and d.CURRENCY = t.CURRENCY and t.CORP_ID = d.CORP_ID ), e as( -- 变更中占用 SELECT t.COMPANY_ID, t.ORDER_TYPE, t.currency, t.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 t.order_id, -- 订单编号 t.rpt_submit_time, -- 订单日期 t.BIZ_TEAM_ID, -- 产品组 t.BIZ_STAFF_ID, -- 业务员 t.DOCUMENTARY_BY, -- 跟单员 t.expire_day, -- 授信结束日期 sum(t.amount) amount from( SELECT o.COMPANY_ID, -- 企业 o.ORDER_TYPE, o.currency, -- 币种 o.CORP_ID, -- 帐套 t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 date_add(o.sign_time,interval o.PAY_EXPIRE_DAYS day) expire_day, -- 授信结束日期 sum(IFNULL(i.PRICE,0) * ifnull(t.quantity,0)) amount from( SELECT l.CHANGE_OBJECT_ID, l.CORP_ID, l.change_id, l.CREATE_TIME, sum(l.AFTER_VALUE - l.BEFORE_VALUE) quantity from ubmpdb.od_order_changes_line l LEFT JOIN ubmpdb.od_order_changes_type t on l.change_type_id = t.CHANGE_TYPE_ID LEFT JOIN ubmpdb.od_order_changes_head h on h.CHANGE_ID = l.change_id where t.change_type='GOODS_QUANTITY' and cast(l.AFTER_VALUE as DECIMAL) > cast(l.BEFORE_VALUE as DECIMAL) and h.CHANGE_STATUS not in ('EXECUTED','ABANDONED','UNPASS','EDIT') and t.status = 1 and l.status = 1 and h.PERIOD_TYPE = 'S' and h.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) GROUP BY l.CHANGE_OBJECT_ID, l.CORP_ID, l.change_id, l.CREATE_TIME )t LEFT JOIN ubmpdb.od_order_item i on t.CHANGE_OBJECT_ID = i.ITEM_ID and t.CORP_ID = i.CORP_ID LEFT JOIN ubmpdb.od_order_info o on o.ORDER_ID = i.ORDER_ID and t.CORP_ID = o.CORP_ID where ((o.ORDER_TYPE= 'P' AND o.PAY_TYPE in ('1','2','4')) OR ( o.ORDER_TYPE= 'S' AND o.PAY_TYPE in('3','5')) ) #[and o.corp_id =:cropIdA] #[and o.company_id =:companyId] #[and o.currency =:currencyA] GROUP BY o.COMPANY_ID, o.ORDER_TYPE, o.currency, o.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 expire_day -- 授信结束日期 union all SELECT o.COMPANY_ID, o.ORDER_TYPE, o.currency, o.CORP_ID, -- 帐套 t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 date_add(o.sign_time,interval o.PAY_EXPIRE_DAYS day) expire_day, -- 授信结束日期 sum(IFNULL(i.QUANTITY,0) * ifnull(t.price,0)) amount from( SELECT l.CHANGE_OBJECT_ID, l.CORP_ID, l.change_id, l.CREATE_TIME, sum(l.AFTER_VALUE - l.BEFORE_VALUE) price from ubmpdb.od_order_changes_line l LEFT JOIN ubmpdb.od_order_changes_type t on l.change_type_id = t.CHANGE_TYPE_ID LEFT JOIN ubmpdb.od_order_changes_head h on h.CHANGE_ID = l.change_id where t.change_type in ('GOODS_PRICE') and cast(l.AFTER_VALUE as DECIMAL) > cast(l.BEFORE_VALUE as DECIMAL) and h.CHANGE_STATUS not in ('EXECUTED','ABANDONED','UNPASS','EDIT') and h.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) and t.status = 1 and l.status = 1 and h.PERIOD_TYPE = 'S' GROUP BY l.CHANGE_OBJECT_ID, l.CORP_ID, l.change_id, l.CREATE_TIME )t LEFT JOIN ubmpdb.od_order_item i on t.CHANGE_OBJECT_ID = i.ITEM_ID and t.CORP_ID = i.CORP_ID LEFT JOIN ubmpdb.od_order_info o on o.ORDER_ID = i.ORDER_ID and t.CORP_ID = o.CORP_ID where ((o.ORDER_TYPE= 'P' AND o.PAY_TYPE in ('1','2','4')) OR ( o.ORDER_TYPE= 'S' AND o.PAY_TYPE in('3','5')) ) #[and o.corp_id =:cropIdA] #[and o.company_id =:companyId] #[and o.currency =:currencyA] GROUP BY o.COMPANY_ID, o.ORDER_TYPE, o.currency, o.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 expire_day -- 授信结束日期 union all SELECT o.COMPANY_ID, o.ORDER_TYPE, o.currency, o.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 date_add(o.sign_time,interval o.PAY_EXPIRE_DAYS day) expire_day, -- 授信结束日期 sum(IFNULL(o.TOTAL_ORDER_AMT,0)) amount from( SELECT DISTINCT h.ORDER_ID, l.CORP_ID, l.change_id, l.create_time from ubmpdb.od_order_changes_line l LEFT JOIN ubmpdb.od_order_changes_type t on l.change_type_id = t.CHANGE_TYPE_ID and l.CORP_ID = t.CORP_ID LEFT JOIN ubmpdb.od_order_changes_head h on h.CHANGE_ID = l.change_id and l.CORP_ID= h.CORP_ID where t.change_type in ('PAY_INFO') and ((l.AFTER_VALUE in ('1','2','4') and l.BEFORE_VALUE in ('3','5') and h.ORDER_TYPE = 'P') or (l.AFTER_VALUE in ('3','5') and l.BEFORE_VALUE in ('1','2','4') and h.ORDER_TYPE = 'S')) and h.CHANGE_STATUS not in ('EXECUTED','ABANDONED','UNPASS','EDIT') and h.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) and t.status = 1 and l.status = 1 and h.PERIOD_TYPE = 'S' )t LEFT JOIN ubmpdb.od_order_info o on o.ORDER_ID = t.ORDER_ID and o.CORP_ID = t.CORP_ID where 1=1 #[and o.corp_id =:cropIdA] #[and o.company_id =:companyId] #[and o.currency =:currencyA] GROUP BY o.COMPANY_ID, o.ORDER_TYPE, o.currency, o.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 o.order_id, -- 订单编号 o.rpt_submit_time, -- 订单日期 o.BIZ_TEAM_ID, -- 产品组 o.BIZ_STAFF_ID, -- 业务员 o.DOCUMENTARY_BY, -- 跟单员 expire_day -- 授信结束日期 )t GROUP BY t.COMPANY_ID, t.ORDER_TYPE, t.currency, t.CORP_ID, t.change_id, -- 变更单号 t.CREATE_TIME, -- 单据日期 t.order_id, -- 订单编号 t.rpt_submit_time, -- 订单日期 t.BIZ_TEAM_ID, -- 产品组 t.BIZ_STAFF_ID, -- 业务员 t.DOCUMENTARY_BY, -- 跟单员 t.expire_day -- 授信结束日期 ), change_use as ( -- 变更中占用 SELECT '变更中占用' type, e.change_id, e.COMPANY_ID, e.CREATE_TIME, e.ORDER_ID, e.rpt_submit_time, e.biz_team_id, e.biz_staff_id, e.documentary_by, e.currency, e.amount, e.expire_day, e.CORP_ID FROM t LEFT JOIN e on e.COMPANY_ID = t.company_id and e.order_type = t.order_type and e.CURRENCY = t.CURRENCY and t.CORP_ID = e.CORP_ID ), f as ( -- 大类框架审批占用 SELECT t.order_id, t.company_id, t.order_type, t.currency, t.CORP_ID, IF(t.order_type='P','YF','SX') credit_kind, t.RPT_SUBMIT_TIME, t.BIZ_TEAM_ID, t.BIZ_STAFF_ID, t.DOCUMENTARY_BY, IF(t.order_type='P',date_add(t.RPT_SUBMIT_TIME,interval t.delivery_expire_days day),date_add(t.RPT_SUBMIT_TIME,interval t.pay_expire_days day)) expire_day, sum(t.TOTAL_ORDER_AMT) BIG_TOTAL_ORDER_AMT FROM ubmpdb.od_order_info t where t.STATUS NOT IN ('RPT_DRAFT','ABANDONED','RPT_AUDIT_UNPASS','EXECUTING','COLL_GD','DLVR_GD','COMPLETED') and (( t.ORDER_TYPE= 'P' AND t.PAY_TYPE in('1','2','4')) OR ( t.ORDER_TYPE= 'S' AND t.PAY_TYPE in ('3','5'))) and t.PERIOD_TYPE='L' and t.LONG_TYPE = 2 and t.corp_id in ( select c.CORP_ID from ubmpdb.sys_corp_info c where find_in_set('HX01',c.NODE_ROUTE) and c.ENABLED=1 ) #[and t.corp_id =:cropIdA] #[and t.company_id =:companyId] #[and t.currency =:currencyA] GROUP BY t.order_id, t.company_id, t.order_type, t.currency, t.CORP_ID, IF(t.order_type='P','YF','SX'), t.RPT_SUBMIT_TIME, t.BIZ_TEAM_ID, t.BIZ_STAFF_ID, t.DOCUMENTARY_BY, IF(t.order_type='P',date_add(t.RPT_SUBMIT_TIME,interval t.delivery_expire_days day),date_add(t.RPT_SUBMIT_TIME,interval t.pay_expire_days day)) ), big_audit_use as ( -- 大类框架审批占用 SELECT '大类框架审批占用' type, f.order_id, f.COMPANY_ID, f.RPT_SUBMIT_TIME, f.biz_team_id, f.biz_staff_id, f.documentary_by, f.currency, f.BIG_TOTAL_ORDER_AMT, f.expire_day, f.CORP_ID FROM t LEFT JOIN f on f.COMPANY_ID = t.company_id and f.credit_kind = t.credit_kind and f.CURRENCY = t.CURRENCY and t.CORP_ID = f.CORP_ID ), big_contract_use as ( SELECT '大类框架合同占用' type, g.order_id, g.COMPANY_ID, g.sign_time, g.RPT_SUBMIT_TIME, g.biz_team_id, g.biz_staff_id, g.documentary_by, g.currency, g.sale_quantity, g.expire_day, g.CORP_ID FROM t LEFT JOIN g on g.company_id = t.company_id and g.credit_kind = t.credit_kind and g.CURRENCY = t.CURRENCY and g.CORP_ID = t.CORP_ID ) select a.type, a.orderNo, a.company_id, a.submitTime, a.ORDER_ID, a.rpt_submit_time, a.biz_team_id, a.biz_staff_id, a.documentary_by, a.currency, a.TOTAL_ORDER_AMT, a.expire_day, a.CORP_ID from ( select a.type, a.ORDER_ID orderNo, a.company_id, a.rpt_submit_time submitTime, a.ORDER_ID, a.rpt_submit_time, a.biz_team_id, a.biz_staff_id, a.documentary_by, a.currency, a.TOTAL_ORDER_AMT, a.expire_day, a.CORP_ID from audit_use a UNION ALL select b.type, b.ORDER_ID orderNo, b.COMPANY_ID, b.sign_time, b.ORDER_ID, b.rpt_submit_time, b.biz_team_id, b.biz_staff_id, b.documentary_by, b.currency, b.ORDER_AMOUNTa, b.expire_day, b.CORP_ID from contract_use b UNION ALL SELECT c.type, c.source_code, c.COMPANY_ID, c.source_date, c.ORDER_ID, c.rpt_submit_time, c.biz_team_id, c.biz_staff_id, c.documentary_by, c.currency, c.NOT_CHECK_AMOUNT, c.overdue_date, c.CORP_ID FROM actual_use c UNION ALL SELECT e.type, e.change_id, e.COMPANY_ID, e.CREATE_TIME, e.ORDER_ID, e.rpt_submit_time, e.biz_team_id, e.biz_staff_id, e.documentary_by, e.currency, e.amount, e.expire_day, e.CORP_ID FROM change_use e UNION ALL SELECT h.type, h.order_id orderNo, h.COMPANY_ID, h.RPT_SUBMIT_TIM ]]> </sqltoy:value> </sqltoy:sql> </data-sql> </report-data> <report-assemble value="report_data" columns-align="10:right" columns-format="11:{yyyy-MM-dd},10:{#,##0.00},3:{yyyy-MM-dd HH:mm:ss},3:{yyyy-MM-dd HH:mm:ss},5:{yyyy-MM-dd HH:mm:ss}"> </report-assemble> </report>
配置查询条件:
---请选择---
是
否
查询条件列数:
布局模式:
默认模式
左右模式
自动显示:
是
否
异步下载:
是
否
二级报表:
是
否
支持订阅:
是
否
用户相关:
是
否
呈现仪表盘:
是
否
责任人:
搜索
显示顺序:
启用标识:
启用
停用
是否外链报表:
是
否
频率类型:
无
日报
月报
年报
报表说明:
保存