project_group/langjiu-project
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
master
langjiu-project/FineReport/销售主题/看板/首页.sql

1961 lines
71 KiB
MySQL
Raw Permalink Normal View History

数据权限控制+排序功能
2024-11-24 15:21:04 +08:00
-- ======================================
-- 报表上新
-- ======================================
SELECT
op1.entry_id AS "报表id",
op2.displayname AS "报表名称",
TO_CHAR(op1.create_time,'YYYY-MM-DD') AS "上新时间"
FROM
sdi_fine.input_fr_report_list op1
LEFT JOIN sdi_fine.fine_authority_object op2 ON op1.entry_id = op2.id
WHERE
report_status = '0'
-- AND STRING_TO_ARRAY(op1.role_list,',') && STRING_TO_ARRAY('${fine_role}',',')
AND op1.create_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY
op1.create_time DESC
-- ======================================
-- 收藏目录表
-- ======================================
-- 收藏目录表
SELECT
IF(LENGTH(LEFT(op3.path,POSITION('/' IN op3.path) - 1)) > 0,LEFT(op3.path,POSITION('/' IN op3.path) - 1),"其他") AS `所属根目录名称`,
op3.path AS `报表全路径`,
op1.id,
op1.entryId AS `报表ID`,
op1.userId AS `用户ID`,
op2.realName AS `姓名`,
op2.userName AS `用户名`,
op3.displayName AS `报表名称`
FROM
fine_favorite_entry op1
JOIN fine_user op2 ON op1.userId = op2.id
JOIN fine_authority_object op3 ON op1.entryId = op3.id
WHERE
op2.userName = '${fine_username}'
-- 展示终端类型:此处需要再讨论一下PC及移动端定义
AND op3.deviceType = 1
-- 平台类型:此处只展示链接/FR报表/BI报表
AND op3.expandType IN (5,102,201)
-- AND IFNULL(LEFT(op3.path,POSITION('/' IN op3.path) - 1),"其他") = '${sDefaultFile}'
-- ======================================
-- 最近浏览
-- ======================================
-- 近10天近30个目录
SELECT
op1.tname AS `报表全路径`,
op2.id AS `报表ID`,
SUBSTRING_INDEX(op2.displayName, '/', -1) AS `模版名称`,
-- FROM_UNIXTIME(MAX(op1.time) / 1000,'%Y-%m-%d %H:%i:%s') AS `访问时间`
FROM_UNIXTIME(MAX(op1.time) / 1000,'%Y-%m-%d') AS `访问时间`
FROM
logdb.fine_record_execute op1
LEFT JOIN finedb.fine_authority_object op2 ON op1.tname = op2.path
WHERE
op1.username = (SELECT CONCAT(realName,'(',userName,')') AS username FROM finedb.fine_user WHERE userName = '${fine_username}')
AND op1.time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 10 DAY)) * 1000
GROUP BY
op2.displayName,
op2.id
ORDER BY
op1.time DESC
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
LIMIT 30
-- ======================================
-- 合同量
-- ======================================
select
'当期' AS time_type
,sum(ct_amt)/10000 AS amt
from dm.v_ag_sal_contract a
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
where exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
OR per_code = a.region_code -- 大区
OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
AND ct_status = '已终审'
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
${IF(len(sStart_date) > 0,"AND ct_year ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod=1,"AND ct_year ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
--- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION ALL
select
'同期' AS time_type
,sum(ct_amt)/10000 AS amt
from dm.v_ag_sal_contract a
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
where exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
OR per_code = a.region_code -- 大区
OR per_code = a.office_code -- 办事处
OR per_code = a.city_unit_code -- 城市单元
)
)
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
AND ct_status = '已终审'
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
${IF(len(sStart_date) > 0,"AND ct_year = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- 去年
-- ${IF(sPeriod=1,"AND ct_year = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
-- 去年同季
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同月
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=3,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
-- 去年同日
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') >='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(signed_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
UNION ALL
select
'当期回款' time_type
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='回款' OR indi_type='电商订单')
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
${IF(len(sStart_date) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 年
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
-- ======================================
-- 回款
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='回款' OR indi_type='电商订单')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='回款' OR indi_type='电商订单')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION
select
'目标' time_type,
sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where ${switch(sPeriod
,1,"indi_type='年度目标_事业部_回款'"
,2,"indi_type='季度目标_事业部_回款'"
,3,"indi_type='月度目标_事业部_回款'"
,4,"indi_type='不存在'")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
-- ======================================
-- 经销商出货
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='出货' OR indi_type='电商订单')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='出货' OR indi_type='电商订单')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 去年
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同季
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION
select
'目标' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where ${switch(sPeriod
,1,"indi_type='年度目标_事业部_出货'"
,2,"indi_type='季度目标_事业部_出货'"
,3,"indi_type='月度目标_事业部_出货'"
,4,"indi_type='不存在'")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
-- ======================================
-- 发货量
-- ======================================
select
'当期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='发货' OR indi_type='费用发货')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION ALL
select
'同期' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where (indi_type='发货' OR indi_type='费用发货')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 去年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') = '" + left(yeardelta(sStart_date,-1),4) + "'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(YEARDELTA(sStart_date,-1),4),roundup(month(YEARDELTA(sStart_date,-1))/3)) +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM') +"'","")}
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(yeardelta(sStart_date,-1),'yyyy-MM-dd') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 去年同日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ yeardelta(sStart_date,-1) +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ yeardelta(sEnd_date,-1) +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
UNION
select
'目标' time_type
,sum(amt_bu) AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.v_sal_indicator_bu a
郎酒首页指标数据绑定
2024-11-25 21:23:57 +08:00
where ${switch(sPeriod
,1,"indi_type IN('年度目标_事业部_费用发货','年度目标_事业部_销售发货')"
,2,"indi_type IN('季度目标_事业部_费用发货','季度目标_事业部_销售发货')"
,3,"indi_type IN('月度目标_事业部_费用发货','月度目标_事业部_销售发货')"
,4,"indi_type IN('不存在')")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.bu_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- 季
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页区间筛选调整
2024-11-27 17:06:11 +08:00
${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
电商KA看板新增大区筛选+首页看板新增库存指标+营销指标汇总BI
2024-12-10 13:57:49 +08:00
-- ======================================
-- 经销商库存
-- ======================================
资金看板权限+BI看板调整+首页取数逻辑优化+电商KA驾驶舱同环比取值优化
2024-12-12 14:10:32 +08:00
-- 要取时点值
电商KA看板新增大区筛选+首页看板新增库存指标+营销指标汇总BI
2024-12-10 13:57:49 +08:00
with base_data AS
(
select
indi_type
,stat_date
,sum(amt_bu) AS amt
from dm.v_sal_indicator_bu a
where (indi_type='实物库存'OR indi_type='在途库存'OR indi_type='融资未填仓'
OR indi_type='已填仓未下单'OR indi_type='未下单往来余额' OR indi_type='费用余额' OR indi_type='未发货')
AND EXISTS (
SELECT
1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}' -- 替换为fine_username
AND per_model IN ('all','sal')
AND (
is_all = 1
OR per_code = a.bu_code
)
)
GROUP BY
indi_type,stat_date
)
SELECT
SUM(amt) AS amt -- 库存总量
,SUM(CASE WHEN indi_type = '在途库存' THEN amt ELSE 0 END) AS onway_amt -- 在途库存
,SUM(CASE WHEN indi_type = '未发货' THEN amt ELSE 0 END) AS not_ship_amt -- 未发货帐余
,SUM(CASE WHEN indi_type = '融资未填仓' THEN amt ELSE 0 END) AS not_fill_amt -- 融资未填仓
FROM base_data
WHERE 1=1
-- 年
资金看板权限+BI看板调整+首页取数逻辑优化+电商KA驾驶舱同环比取值优化
2024-12-12 14:10:32 +08:00
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date,'yyyy') +"'","")}
-- ${IF(sPeriod=1,"AND to_char(stat_date,'yyyy-MM-dd') = '"+ FORMAT(ENDOFMONTH(sStart_date),'yyyy-MM-dd') +"'","")}
电商KA看板新增大区筛选+首页看板新增库存指标+营销指标汇总BI
2024-12-10 13:57:49 +08:00
-- 季
资金看板权限+BI看板调整+首页取数逻辑优化+电商KA驾驶舱同环比取值优化
2024-12-12 14:10:32 +08:00
-- ${IF(sPeriod=2,"AND CONCAT(to_char(stat_date,'yyyy'),EXTRACT(QUARTER FROM stat_date)) ='"+ CONCATENATE(left(sStart_date,4),roundup(month(sStart_date)/3)) +"'","")}
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date,'yyyy-MM-dd') +"'","")}
电商KA看板新增大区筛选+首页看板新增库存指标+营销指标汇总BI
2024-12-10 13:57:49 +08:00
-- ${IF(sPeriod=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 月
资金看板权限+BI看板调整+首页取数逻辑优化+电商KA驾驶舱同环比取值优化
2024-12-12 14:10:32 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date,'yyyy-MM') +"'","")}
${IF(len(sStart_date) > 0,"AND to_char(stat_date,'yyyy-MM-dd') = '" + FORMAT(DATEINMONTH(sStart_date,1),'yyyy-MM-dd') + "'","")}
电商KA看板新增大区筛选+首页看板新增库存指标+营销指标汇总BI
2024-12-10 13:57:49 +08:00
-- ${IF(sPeriod=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
-- 日
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') ='"+ sStart_date +"'","")}
-- ${IF(sPeriod=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sEnd_date +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- ======================================
-- 财务资金状况
-- ======================================
-- 资金计划与执行
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
'当期' AS time_type
,'收入' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入','取得贷款')
AND owner_bank_account <> '22195101040012008'
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
AND exists (
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
1
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
UNION ALL
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
'当期' AS time_type
,'支出' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出','偿还贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
1
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(sStart_date_fin,'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
UNION ALL
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
'当期' AS time_type
,'余额' AS fee_type
,sum(amt) / 10000 AS AMT
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from dm.dm_fim_fud_balance a
where corp_code not in ('0101','0103','0102')
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- AND owner_bank_account NOT IN ('20351059900100000512291'
-- ,'22195101040012008'
-- ,'信用证保证金'
-- ,'22195162750000012'
-- ,'22195162650000017'
-- ,'22195162350000014'
-- ,'22195162050000015'
-- ,'951018033000447470'
-- ,'22195162950000025')
AND a.is_usable = 1
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
AND exists (
select
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
1
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 年
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 日
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
UNION ALL
-- 资金计划与执行
select
'上期' AS time_type
,'收入' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入','取得贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 上年
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(yeardelta(sStart_mon,-1),'yyyy-MM') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 上月
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 昨日
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
UNION ALL
select
'上期' AS time_type
,'支出' AS fee_type -- 收支方向 收入/支出
,SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
from dm.dm_fim_fud_indicator a
where corp_code not in ('0101','0103','0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出','偿还贷款')
AND owner_bank_account <> '22195101040012008'
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
-- 上年
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') <='"+ FORMAT(yeardelta(sStart_mon,-1),'yyyy-MM') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 上月
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 昨日
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(sStart_date_fin,-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
UNION ALL
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
select
'上期' AS time_type
,'余额' AS fee_type
,sum(amt) / 10000 AS AMT
from dm.dm_fim_fud_balance a
where corp_code not in ('0101','0103','0102')
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- AND owner_bank_account NOT IN ('20351059900100000512291'
-- ,'22195101040012008'
-- ,'信用证保证金'
-- ,'22195162750000012'
-- ,'22195162650000017'
-- ,'22195162350000014'
-- ,'22195162050000015'
-- ,'951018033000447470'
-- ,'22195162950000025')
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
AND exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','sal')
and (
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
AND a.is_usable = 1
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 上年
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(YEARDELTA(sStart_mon,-1),'yyyy') +"'","")}
${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(YEARDELTA(MONTHDELTA(sStart_mon,-1),-1),'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 上月
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(MONTHDELTA(sStart_mon,-1),'yyyy-MM') +"'","")}
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ FORMAT(MONTHDELTA(sStart_date_fin,-2),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- 昨日
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ FORMAT(DATEDELTA(MONTHDELTA(sStart_date_fin,-1),-1),'yyyy-MM-dd') +"'","")}
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ FORMAT(DATEDELTA(MONTHDELTA(sStart_date_fin,-1),-1),'yyyy-MM-dd') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- ======================================
-- 财务资金月度
-- ======================================
SELECT
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
t.stat_date AS stat_date
,to_char(t.stat_date,'MM') as month
,CASE WHEN t.fee_type = '收入' THEN amt ELSE 0 END AS revenue_fee -- 收入
,CASE WHEN t.fee_type = '支出' THEN amt ELSE 0 END AS expend_fee -- 支出
,CASE WHEN t.fee_type = '余额' THEN amt ELSE 0 END AS balance_fee -- 余额
首页区间筛选调整
2024-11-27 17:06:11 +08:00
FROM (SELECT stat_date
, '收入' AS fee_type -- 收支方向 收入/支出
, SUM(CASE WHEN trade_direction = '收入' THEN amt ELSE 0 END) / 10000 AS amt
FROM dm.dm_fim_fud_indicator a
WHERE corp_code NOT IN ('0101', '0103', '0102')
AND trade_direction = '收入'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨收入', '取得贷款')
AND owner_bank_account <> '22195101040012008'
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
首页区间筛选调整
2024-11-27 17:06:11 +08:00
AND exists(select 1
FROM
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
dm.dm_bi_user_permisson
首页区间筛选调整
2024-11-27 17:06:11 +08:00
WHERE
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
bi_user = '${fine_username}'
首页区间筛选调整
2024-11-27 17:06:11 +08:00
AND per_model IN ('all','sal')
AND (
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(len(sStart_mon) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- 年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date_fin,'yyyy') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') = '"+ FORMAT(sStart_date_fin,'yyyy-MM') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >= '"+ sStart_date_fin +"'","")}
-- ${IF(sPeriod_fin=4,"AND to_char(stat_date,'yyyy-MM-dd') <= '"+ sStart_date_fin +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
GROUP BY stat_date
UNION ALL
SELECT stat_date
, '支出' AS fee_type -- 收支方向 收入/支出
, SUM(CASE WHEN trade_direction = '支出' THEN amt ELSE 0 END) / 10000 AS amt
FROM dm.dm_fim_fud_indicator a
WHERE corp_code NOT IN ('0101', '0103', '0102')
AND trade_direction = '支出'
AND indi_type = '执行'
AND plan_item_name NOT IN ('内部调拨支出', '偿还贷款')
AND owner_bank_account <> '22195101040012008'
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 权限控制
首页区间筛选调整
2024-11-27 17:06:11 +08:00
AND exists(select 1
FROM
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
dm.dm_bi_user_permisson
首页区间筛选调整
2024-11-27 17:06:11 +08:00
WHERE
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
bi_user = '${fine_username}'
首页区间筛选调整
2024-11-27 17:06:11 +08:00
AND per_model IN ('all','sal')
AND (
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
is_all = 1
OR per_code = a.corp_code -- 事业部
-- OR per_code = a.region_code -- 大区
-- OR per_code = a.office_code -- 办事处
-- OR per_code = a.city_unit_code -- 城市单元
)
)
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
${IF(len(sStart_mon) > 0,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
-- 年
-- ${IF(sPeriod_fin=1,"AND to_char(stat_date,'yyyy') ='"+ FORMAT(sStart_date_fin,'yyyy') +"'","")}
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月
-- ${IF(sPeriod_fin=2,"AND to_char(stat_date,'yyyy-MM') ='"+ FORMAT(sStart_date_fin,'yyyy-MM') +"'","")}
-- 日
-- ${IF(sPeriod_fin=3,"AND to_char(stat_date,'yyyy-MM-dd') >='"+ sStart_date_fin +"'","")}
-- ${IF(sPeriod_fin=4,"AND to_char(stat_date,'yyyy-MM-dd') <='"+ sStart_date_fin +"'","")}
首页区间筛选调整
2024-11-27 17:06:11 +08:00
GROUP BY stat_date
UNION ALL
首页指标优化调整+财务预算费用手工数据
2024-11-27 09:03:05 +08:00
-- 月初资金余额要取上月的月初的，年初要取上年末12月份的
首页区间筛选调整
2024-11-27 17:06:11 +08:00
SELECT
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
stat_date AS stat_date
首页关键指标时间参数调整优化
2024-11-29 18:04:02 +08:00
, '余额' AS fee_type
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
,SUM(amt) / 10000 AS AMT
FROM dm.dm_fim_fud_balance a
WHERE corp_code NOT IN ('0101', '0103', '0102')
-- AND owner_bank_account NOT IN
-- ('20351059900100000512291', '22195101040012008', '信用证保证金', '22195162750000012', '22195162650000017',
-- '22195162350000014', '22195162050000015', '951018033000447470','22195162950000025')
AND a.is_usable = 1
-- 权限控制
AND EXISTS (SELECT 1
FROM dm.dm_bi_user_permisson
WHERE bi_user = '${fine_username}'
AND per_model IN ('all', 'fim')
AND (
is_all = 1
OR per_code = a.corp_code -- 事业部
))
${IF(len(sStart_mon) > 0,"AND to_char(a.stat_date,'yyyy') ='"+ FORMAT(sStart_mon,'yyyy') +"'","")}
GROUP BY stat_date
首页区间筛选调整
2024-11-27 17:06:11 +08:00
)t
电商KA看板+资金看板
2024-12-08 21:18:00 +08:00
${IF(len(sStart_mon) > 0,"where to_char(t.stat_date,'yyyy-MM') <= '"+ FORMAT(sStart_mon,'yyyy-MM') +"'","")}
首页财务部分+庄园之旅城市经理权限+资金日报完善
2024-12-20 15:37:08 +08:00
ORDER BY to_char(t.stat_date,'MM')
-- ======================================
-- 财务
-- ======================================
WITH amt_base AS
(
-- 映射日报科目对照关系
SELECT
stat_date -- 统计日期
,plan_item_name
,trade_direction
,SUM(amt) AS amt -- 成品酒销售回款
FROM dm.dm_fim_fud_indicator fp
where fp.trade_direction IN('收入','支出')
and fp.indi_type='执行'
AND fp.plan_item_name NOT IN ('内部调拨支出','内部调拨收入')
and EXISTS (
SELECT
1
FROM
dm.dm_bi_user_permisson
WHERE
bi_user = '${fine_username}'
AND per_model IN ('all','fim')
AND (
is_all = 1
OR per_code = fp.corp_code
)
)
${IF(len(sCorp)=0," ","AND fp.corp_code in ('"+sCorp+"')")}
AND stat_date >= date '2023-01-01'
GROUP BY stat_date,plan_item_name,trade_direction
UNION ALL
select
date(concat_ws(',',account_year,account_month,'1')) as stat_date
,'其他收入（含未确认收入）' as plan_item_name
,'收入' AS trade_direction
,losses_amt AS amt
from sdi_hzg.exchange_gains_losses
where 1=1
${IF(len(sCorp)=0," ","AND corp_code in ('"+sCorp+"')")}
)
SELECT
plan_item_name
,trade_direction as fee_class
,SUM(amt) / 10000 AS amt -- 本日
FROM amt_base
WHERE 1=1
AND to_char(stat_date,'yyyy-MM-dd') = '${sEnd_date}'
GROUP BY plan_item_name,trade_direction
-- 期初余额
-- 年
${IF(sPeriod_fin=1,"","/*")}
--本月期初
with md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date_trunc('year',date '${sEnd_date}')
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as current_balance,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
) , rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date_trunc('year',date '${sEnd_date}')-1
)
select
sum(current_balance*nvl(rto.rate,1))/10000 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 0
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,sum(current_balance*nvl(rto.rate,1))/10000 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 1
${IF(sPeriod_fin=1,"","*/")}
-- 月
${IF(sPeriod_fin=2,"","/*")}
--本月期初
with md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date_trunc('month',date '${sEnd_date}')
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as current_balance,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
) , rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date_trunc('month', date '${sEnd_date}')-1
)
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,sum(current_balance*nvl(rto.rate,1))/10000 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND if((dba.category_name in ('专用户','保证金户') and re.bank_account!='22195901040000147' ) or re.bank_account='信用证保证金',1,0) = 1
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,sum(current_balance*nvl(rto.rate,1))/10000 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from xj re
inner join dwr.dim_corp c on re.corp_code=c.corp_code and c.corp_group='股份'
left join rto on re.currency=rto.currency_name and rto.ridx=1
left join dwr.dim_bank_account dba on decode(length(re.bank_account), 17,
substring(re.bank_account, 3),
re.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND re.corp_code = '0223'
UNION ALL
SELECT
0 as free_amt -- 其中：可用存款
, sum(balance) / 10000 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',add_months(date'${sEnd_date}',-1))
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=2,"","*/")}
-- 日
${IF(sPeriod_fin=3,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time<date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date<date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
group by bank_account
),re as ( -- 汇总票据和银行余额
select
a.corp_code,
a.current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from dwi.dwi_fim_bank_account_balance a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${FORMAT(sEnd_date,"yyyy-MM-dd")}'
)
select
current_balance*nvl(rto.rate,1)/10000 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 0
AND amt_type = '现金'
AND corp_code <> '0223'
UNION ALL
select
0 as free_amt -- 其中：可用存款
, current_balance*nvl(rto.rate,1)/10000 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND amt_type = '票据'
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,0 AS hk_amt -- 香港公司账户资金
,current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
select
0 as free_amt -- 其中：可用存款
,0 AS bank_amt -- 银行承兑汇票
,current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
,0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 0
AND amt_type = '现金'
AND corp_code = '0223'
${IF(sPeriod_fin=3,"","*/")}
-- 期末
-- 年
${IF(sPeriod_fin=1,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=1,"","*/")}
-- 月
${IF(sPeriod_fin=2,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=2,"","*/")}
-- 日
${IF(sPeriod_fin=3,"","/*")}
with tpj as (
select
a.corp_code,
'收入' as trade_direction,
draftcode,
a.owner_bank_account,
sum(a.amt) as amt
from sdi_oth.fim_draft_balance a
group by a.corp_code,
draftcode,
owner_bank_account
union all
select
a.corp_code,
trade_direction,
a.draftcode,
a.owner_bank_account,
amt
from dwr.fact_fim_draft_flow a
where a.trade_time>=date'2024-08-01'
and a.trade_time< date'${sEnd_date}' + interval '1 day'
and nvl(a.draft_state,'x') not in ('未签收','背书中','登记中','已作废','承兑中','出票中','贴现中','质押中','收款中','被拒付','拒绝签收')
) ,pj as ( -- 票据余额
select
a.corp_code,
sum(decode(trade_direction,'支出',-1*amt,amt)) as balance
from tpj a
inner join dwr.dim_corp c on a.corp_code =c.corp_code and c.corp_group='股份'
group by a.corp_code
having sum(decode(trade_direction,'支出',-1*amt,amt))>0
), md as (
select
bank_account,
max(report_date) as max_report_date
from dwi.dwi_fim_bank_account_balance
where report_date< date'${sEnd_date}' + interval '1 day'
group by bank_account
),xj as (
select
a.corp_code,
a.bank_account,
sum(a.current_balance) as amt,
a.currency
from dwi.dwi_fim_bank_account_balance a
where exists (
select 1 from md
where a.bank_account=md.bank_account and a.report_date=md.max_report_date
)
and a.account_state='开户'
group by a.corp_code, a.currency ,a.bank_account
union all
select corp_code,
owner_bank_account as bank_account,
sum(decode(trade_direction,'收入',amt,-1*amt)) as amt,
'人民币' as currency
from dm.dm_fim_fud_indicator
where amt_type='现金'
and stat_date=date'${sEnd_date}'
group by corp_code,owner_bank_account
) ,re as ( -- 汇总票据和银行余额
select
a.corp_code,
sum(a.amt) as current_balance,
a.currency,
'现金' as amt_type,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0) as is_limit--受限资金金额
from xj a
inner join dwr.dim_corp c on a.corp_code=c.corp_code and c.corp_group='股份'
left join dwr.dim_bank_account dba on decode(length(a.bank_account), 17,
substring(a.bank_account, 3),
a.bank_account)=decode(length(dba.accountnumber), '17',
substring(dba.accountnumber, 3),
dba.accountnumber)
and dba.account_owner_class='langjiu'
group by a.corp_code,
a.currency,
if((dba.category_name in ('专用户','保证金户') and a.bank_account!='22195901040000147' ) or a.bank_account='信用证保证金',1,0)
union all
select
corp_code,
sum(balance) as current_balance,
'人民币' as currency,
'票据' as amt_type,
0 as is_limit
from pj
group by corp_code),
rto as ( -- 汇率表
select decode(currency_name,'加拿大元','加元',currency_name) as currency_name,
conversion_price_boc/100 as rate,
date_trunc('day',exchange_publish_time) as rto_date,
row_number() over (partition by currency_name,
date_trunc('day',exchange_publish_time)
order by exchange_publish_time desc) as ridx
from sdi_xdata.boc_exchange_rate
where date_trunc('day',exchange_publish_time)=date'${sEnd_date}'
)
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, current_balance*nvl(rto.rate,1)/10000 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND corp_code = '0223'
AND amt_type = '现金'
UNION ALL
SELECT 0 AS free_amt -- 其中：可用存款
, 0 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, current_balance*nvl(rto.rate,1)/10000 AS limit_amt -- 受限资金
from re
left join rto on re.currency=rto.currency_name and rto.ridx=1
where current_balance!=0
${IF(len(sCorp)=0," ","AND re.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
AND is_limit = 1
AND amt_type = '现金'
UNION ALL
SELECT
0 AS free_amt -- 其中：可用存款
, sum(balance)/10000 AS bank_amt -- 银行承兑汇票
, 0 AS hk_amt -- 香港公司账户资金
, 0 AS limit_amt -- 受限资金
from dwr.ag_fim_fud_draft_balance a
where stat_date=date_trunc('month',date '${sEnd_date}')
and exists(
select 1 from dwr.dim_corp cp
where a.corp_code=cp.corp_code
and cp.corp_group='股份'
)
${IF(len(sCorp)=0," ","AND a.corp_code in ('"+sCorp+"')")}
and exists (
select
1
from
dm.dm_bi_user_permisson
where
bi_user = '${fine_username}'
and per_model IN ('all','fim')
and (
is_all = 1
)
)
${IF(sPeriod_fin=3,"","*/")}
Reference in New Issue Copy Permalink