128 lines
3.5 KiB
SQL
128 lines
3.5 KiB
SQL
-- *****************************
|
|
-- 创建人员: 杨坤安
|
|
-- 创建时间: 2024-10-18
|
|
-- 功能描述: 财务预算金额填报 填报表
|
|
-- *****************************
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS sdi_oth.ods_finance_budget_fill;
|
|
CREATE TABLE sdi_oth.ods_finance_budget_fill (
|
|
ID SERIAL PRIMARY KEY
|
|
,years VARCHAR(50)
|
|
,corp_code VARCHAR(100)
|
|
,corp_name VARCHAR(100)
|
|
,fee_type VARCHAR(100)
|
|
,subject_code VARCHAR(100)
|
|
,subject_name VARCHAR(100)
|
|
,begin_amt VARCHAR(50)
|
|
,adj_amt VARCHAR(50)
|
|
,amt VARCHAR(50)
|
|
,update_date VARCHAR(50)
|
|
,update_by VARCHAR(50)
|
|
);
|
|
|
|
-- 为表添加注释
|
|
COMMENT ON TABLE sdi_oth.ods_finance_budget_fill IS '财务预算金额填报表';
|
|
|
|
-- 为字段添加注释
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.years IS '年度';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.corp_code IS '公司编码';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.corp_name IS '公司名称';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.fee_type IS '费用类型：销售费用|管理费用|研发费用|财务费用';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.subject_code IS '科目编码';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.subject_name IS '科目名称';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.begin_amt IS '年初预算金额';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.adj_amt IS '预算调整金额';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.amt IS '调整后预算金额';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.update_date IS '更新时间';
|
|
COMMENT ON COLUMN sdi_oth.ods_finance_budget_fill.update_by IS '更新人';
|
|
|
|
TRUNCATE TABLE sdi_oth.ods_finance_budget_fill;
|
|
|
|
SELECT COUNT(1) FROM sdi_oth.ods_finance_budget_fill WHERE begin_amt is NOT NULL;
|
|
|
|
SELECT * FROM sdi_oth.ods_finance_budget_fill WHERE begin_amt is NOT NULL;
|
|
|
|
|
|
SELECT
|
|
years
|
|
,corp_code
|
|
,corp_name
|
|
,fee_type
|
|
,subject_code
|
|
,subject_name
|
|
,begin_amt
|
|
,adj_amt
|
|
,amt
|
|
FROM sdi_oth.ods_finance_budget_fill
|
|
where years = '${sYear}'
|
|
;
|
|
|
|
|
|
SELECT
|
|
corp_code -- 公司编码
|
|
,corp_name -- 公司名称
|
|
FROM dwr.dim_corp
|
|
WHERE corp_type = '实体'
|
|
AND (parent_corp_code LIKE '02%' OR corp_code = '0301')
|
|
|
|
SELECT
|
|
subject_code
|
|
,subject_name
|
|
FROM dwr.dim_subject
|
|
WHERE (subject_code like '6601%' or subject_code like '6602%' or subject_code like '6603%' or subject_code like '6604%' )
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS sdi_oth.ods_finance_budget_data;
|
|
CREATE TABLE sdi_oth.ods_finance_budget_data (
|
|
corp_name VARCHAR(100)
|
|
,years VARCHAR(50)
|
|
,fee_type VARCHAR(100)
|
|
,subject_name VARCHAR(100)
|
|
,amt VARCHAR(50)
|
|
);
|
|
|
|
|
|
|
|
|
|
WITH corp AS (
|
|
SELECT
|
|
corp_code -- 公司编码
|
|
,corp_name -- 公司名称
|
|
FROM dwr.dim_corp a
|
|
WHERE corp_type = '实体'
|
|
AND (parent_corp_code LIKE '02%' OR corp_code = '0301')
|
|
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
|
|
)
|
|
)
|
|
${IF(LEN(sCorp)!=0," AND corp_code IN ('"+ sCorp +"')", "")}
|
|
)
|
|
SELECT
|
|
|
|
a.subject_name
|
|
,SUM(a.amt) AS amt
|
|
FROM sdi_oth.ods_finance_budget_data a
|
|
JOIN corp b
|
|
on a.corp_name = b.corp_name
|
|
WHERE fee_type = '销售费用'
|
|
AND LEFT(years,4) = '${YEAR(sStart_mon)}'
|
|
GROUP BY a.subject_name
|