【SAP Abap】X档案：SAP 快速提供基础数据给第三方系统访问的几种方法（附常用基础数据）
SAP 快速提供基础数据给第三方系统访问的几种方法
1、数据封装
在企业信息系统建设过程中，少不了的就是系统集成数据对接。 尤其是SAP系统中大量的基础数据集成，如各种字段值域，需要提供给第三方系统做下拉列表，如果都通过ABAP开发接口的话，无疑会增加双方系统的大量的对接工作量。
一般很少会直接开放整张表的数据访问权限，故会加以封装处理，以下以SAP系统中的中国省份基础数据来举例说明。
首先进行数据封装，定义CDS：
@AbapCatalog.sqlViewName: 'ZV_DIM_PROVINCE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '中国省份'
define view ZCDS_DIM_PROVINCE as select from t005u
{
key bland as code, --省份代号
bezei as name --省份
}
where land1='CN'
and spras = $session.system_language ;
SE11查看视图数据：ZV_DIM_PROVINCE
数据已处理，并且更改了友好的英文字段名称。
2、开放RFC访问
外部系统，通过 RFC 来访问 SAP 视图数据，需要具备 SAP 访问账号。
调用 SAP 系统自带的 RFC：RFC_READ_TABLE，访问参数如下：
执行，可以获得视图 ZV_DIM_PROVINCE 的数据
视图字段清单：
视图记录（通过自定义的分隔符#分隔）：
3、开放接口服务
发布ODATA数据服务，在创建 CDS 时，增加如下注解：
@OData.publish: true
同样，需要具备 SAP 访问账号。
在SAP端相关TCODE： /IWFND/MAINT_SERVICE：激活并维护服务 /IWFND/GW_CLIENT：测试OData服务 /IWFND/ERROR_LOG：分析错误
在SAP执行TCODE：/n/IWFND/MAINT_SERVICE，添加服务
激活服务
测试服务
URL 中加入 Entity Set 和相应的 URI Option，可以获得 CDS 对应数据：
#metadata
#entityset
4、开放DB访问
如果是限定内网访问，且可开放 Hana 数据库只读访问账号的，可以使用以下语句访问数据：
select * from saphanadb.ZV_DIM_PROVINCE where mandt = 200
5、常用基础数据
@AbapCatalog.sqlViewName: 'ZV_DIM_ACCT_TB'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '科目表'
define view ZCDS_DIM_ACCT_TB as select from t004t
{
key ktopl as code,
ktplt as name
}
where ktopl between '1000' and '9999'
and spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_CHANNEL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '分销渠道'
define view zcds_dim_channel as select from tvtwt
{
key vtweg as code,
vtext as name
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_COMPANY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '公司代码'
define view ZCDS_DIM_COMPANY as select from t001
{
key bukrs as code,
butxt as name,
ort01 as city,
land1 as country_code,
waers as currency,
spras as lang_code,
ktopl as account_table
}
where bukrs between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_COST_AREA'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '成本控制范围'
define view zcds_dim_cost_area as select from tka01
{
key kokrs as code,
bezei as name,
waers as currency,
ktopl as account_table
}
where kokrs between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_COST_CT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '成本中心'
define view zcds_dim_cost_ct as select from csks a
left outer join cskt b on b.mandt = a.mandt and b.kostl = a.kostl
and b.datbi = a.datbi and b.kokrs = a.kokrs
{
a.kostl as code,
b.ktext as name,
a.datab as valid_from,
a.datbi as valid_to,
a.kokrs as cost_area,
a.bukrs as company,
a.waers as currency,
a.prctr as profit_center,
--trim(leading '0' from prctr) as short_profit_center_code,
a.ersda as create_date
}
where b.spras = $session.system_language
and a.kokrs between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_COUNTRY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '国家地区'
@OData.publish: true
define view ZCDS_DIM_COUNTRY
-- with parameters p_language : syst_langu @<Environment.systemField: #SYSTEM_LANGUAGE
as select from t005 as a
--方法一：左外连接
left outer join t005t as _desc on a.land1 = _desc.land1
{
key a.land1 as code,
a.spras as lang_code,
--_desc
--_desc[1:spras = $session.system_language].spras, --当前描述的语言
_desc.landx as name,
_desc.natio as nation,
_desc.landx50 as name_long,
_desc.natio50 as nation_long
}
where --_desc.spras = :p_language
_desc.spras = $session.system_language
/*
--方法二：关联
association[1..*] to t005t as _desc on $projection.code = _desc.land1
{
key a.land1 as code,
a.spras as lang_code,
--_desc
--_desc[1:spras = $session.system_language].spras, --当前描述的语言
_desc[1:spras = $session.system_language].landx as name,
_desc[1:spras = $session.system_language].natio as nation,
_desc[1:spras = $session.system_language].landx50 as name_long,
_desc[1:spras = $session.system_language].natio50 as nation_long
}
*/
;
@AbapCatalog.sqlViewName: 'ZV_DIM_COUNTRY_P'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '国家视图（带语言参数）'
define view ZCDS_DIM_COUNTRY_P
with parameters
p_language : syst_langu @<Environment.systemField: #SYSTEM_LANGUAGE
as select from t005 as a
left outer join t005t as _desc on a.land1 = _desc.land1
{
key a.land1 as code,
a.spras as lang_code,
--_desc
--_desc[1:spras = $session.system_language].spras, --当前描述的语言
_desc.landx as name,
_desc.natio as nation,
_desc.landx50 as name_long,
_desc.natio50 as nation_long
-- HANA调用：select * from saphanadb.ZV_DIM_COUNTRY_P(p_language =>1) where mandt = 200
}
where _desc.spras = :p_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_CURRENCY'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '货币'
define view zcds_dim_currency as select from tcurt
{
key waers as code,
ktext as name
}
where spras = $session.system_language
and (waers = 'CNY' or waers = 'USD' or waers = 'HKD' or waers = 'EUR'
or waers = 'JPY' or waers = 'AUD' or waers = 'GBP');
@AbapCatalog.sqlViewName: 'ZV_DIM_EVALUATE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '评估类'
define view zcds_dim_evaluate as select from t025t
{
key bklas as code, --评估类
bkbez as name --评估类的描述
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_GL_ACCT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '总账科目记录'
define view ZCDS_DIM_GL_ACCT as select from ska1 a
left outer join skat b on b.mandt = a.mandt and b.ktopl=a.ktopl and b.saknr=a.saknr
{
a.ktopl as account_table,
a.saknr as code,
b.txt50 as name,
a.ktoks as account_group,
cast(a.erdat as date) as create_date,
cast(b.last_changed_ts as timestamp ) as last_changed_date --更新时间
}
where a.ktopl between '1000' and '9999'
and b.spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_MRP_CTRL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'MRP控制者'
define view zcds_dim_mrp_ctrl as select from t024d
{
key werks as plant, --工厂
key dispo as code,
dsnam as name
}
where werks >='1000';
@AbapCatalog.sqlViewName: 'ZV_DIM_MT_GROUP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '物料组'
define view zcds_dim_mt_group as select from t023t
{
key matkl as code,
wgbez60 as name
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_MT_TYPE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '物料类型'
define view zcds_dim_mt_type as select from t134t
{
key mtart as code,
mtbez as name
}
where mtart like 'Z%'
and spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_ORIGIN'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '原始组'
define view zcds_dim_origin as select from tkkh2
{
key kokrs as control_area_code, --控制范围
key koaty as Source_Type, --来源类型
key hrkft as code, --原始组
hrktx as name --原始组名称
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PAYREASON'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '付款原因'
define view zcds_dim_pay_reason as select from t053s
{
key bukrs as company,
key rstgr as code,
txt40 as name
}
where bukrs between '1000' and '9999'
and spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PAY_TERM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '付款条件'
define view zcds_dim_pay_term as select distinct from t052u
{
key zterm as code, --收付款条件代码
text1 as name --收付款条件描述
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PD_GROUP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '产品组'
define view zcds_dim_pd_group as select from tspat
{
key spart as code,
vtext as name
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PINFOTYPE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '采购信息记录类型'
define view ZCDS_DIM_PINFOTYPE
as select from dd07t
{
key domvalue_l as code, --采购信息记录类型代码
ddtext as name --采购类型记录类型名称
}
where domname = 'ESOKZ'
and ddlanguage = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PLANT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '工厂代码'
define view ZCDS_DIM_PLANT as select from t001w
{
key werks as code, --工厂代码
name1 as name, --工厂名称
land1 as country_code, --国家代码
regio as region_code, --地区代号
stras as street --街道
}
where werks > '1000';
@AbapCatalog.sqlViewName: 'ZV_DIM_PRICE_TYP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售价格类型'
define view zcds_dim_price_type as select from t189t
{
key pltyp as code, --销售价格类型代
ptext as name --销售价格类型
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_PROF_CT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '利润中心'
define view zcds_dim_prof_ct as select from cepc a
left outer join cepct b on b.mandt = a.mandt and b.prctr = a.prctr
and b.datbi = a.datbi and b.kokrs = a.kokrs
{
a.prctr as code,
b.ktext as name,
--trim(leading '0' from prctr) as short_code,
a.datab as valid_from,
a.datbi as valid_to,
a.kokrs as cost_area,
a.ersda as create_date
}
where b.spras = $session.system_language
and a.kokrs between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_PROVINCE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '中国省份'
define view ZCDS_DIM_PROVINCE as select from t005u
{
key bland as code, --省份代号
bezei as name --省份
}
where land1='CN'
and spras = $session.system_language ;
@AbapCatalog.sqlViewName: 'ZV_DIM_PUR_GROUP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '采购组'
define view zcds_dim_pur_group as select from t024
{
key ekgrp as code,
eknam as name
}
where ekgrp > '100';
@AbapCatalog.sqlViewName: 'ZV_DIM_PUR_ORG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '采购组织'
define view zcds_dim_pur_org as select from t024e
{
key bukrs as company_code,
key ekorg as code,
ekotx as name
}
where bukrs between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_SAL_DEPT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售部门'
define view zcds_dim_sal_dept as select from tvkbt
{
key vkbur as code,
bezei as name
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_SAL_DPGR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售部门&销售组关系'
define view zcds_dim_sal_dpgr as select from tvbvk
{
key vkbur as office_code,
key vkgrp as group_code
};
@AbapCatalog.sqlViewName: 'ZV_DIM_SAL_GROUP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售组'
define view zcds_dim_sal_group as select from tvkgr a
left outer join tvgrt b on b.mandt = a.mandt and b.vkgrp = a.vkgrp
{
key a.vkgrp as code,
b.bezei as name
}
where b.spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_SAL_ORG'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售组织'
define view zcds_dim_sal_org
as select from tvko a
left outer join tvkot b on b.vkorg = a.vkorg
{
key a.bukrs as company,
key a.vkorg as code,
b.vtext as name,
a.waers as currency
}
where a.vkorg >= '1000'
and b.spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_SECTION'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '加工工段'
define view zcds_dim_section as select from t024f
{
key werks as plant, --SAP工厂号
key fevor as code, --加工工段代码
txt as name --加工工段
}
where werks between '1000' and '9999';
@AbapCatalog.sqlViewName: 'ZV_DIM_SO_REASON'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '销售订单原因'
define view zcds_dim_so_reason as select from tvaut
{
key augru as code, --订单原因代码
bezei as name --订单原因
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_STOCK'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '仓库地点'
define view zcds_dim_stock as select from t001l
{
key werks as plant,
key lgort as code,
lgobe as name
}
where werks >='1000';
@AbapCatalog.sqlViewName: 'ZV_DIM_TAX_TYPE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '税号类别'
define view zcds_dim_tax_type as select from tfktaxnumtype_t
{
key taxtype as code, --税号类别代码
text as name --税号类别
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_TRANSPORT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '运输方式'
define view zcds_dim_transport as select from tvsbt
{
key vsbed as code,
vtext as name
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_UNIT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '计量单位'
define view zcds_dim_unit as select from t006a
{
key msehi as code, --单位内码
mseh3 as name, --单位名称
msehl as description --单位描述
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_DIM_VOU_TYPE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '凭证类型'
define view zcds_dim_vou_type as select from t003t
{
key blart as code, --凭证类型代码
ltext as name --凭证类型
}
where spras = $session.system_language;
@AbapCatalog.sqlViewName: 'ZV_TCURR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: '汇率（有效期日期转换）'
define view zcds_TCURR as
select
key kurst,
key fcurr,
key tcurr,
key gdatu,
ukurs,
ffact,
tfact,
@EndUserText.label: '有效日期'
cast(left(cast((99999999-cast(cast(gdatu as abap.numc(8)) as abap.int4)) as abap.char(11)),8) as abap.dats) as ZGDATU
from tcurr;
原创文章，转载请注明来源-X档案