说明 / 示例
# 1.新增支出记录
!
这里以差旅交通费为例
# 2.目标报销单关联申请正确展示类型为自定义类型
# 3.修正映射
http://63f9f7fd.wiz03.com/wapp/pages/view/share/s/1z-vvZ1XI4az2KaA9n0_QGk10TUEEH1FsQoR2QiFSi2MN1Cs
## 3.1. 查找映射id
a70db87bd4a411e99457c9a35da8a414
Other _其他费用_Other Expenses,
SelfDrive _自驾车_Self Drive,
Ship _轮船_Ship,
Traffic _差旅交通费_Urban Transport,
CoachBus _长途汽车_Long-distance Bus,
TravelMeals _差旅餐费_Travel Meals Fees,
AviationInsurance _航空保险_Aviation Insurance
## 3.2. 编写脚本，执行脚本
### 3.21 处理表：
T_TRAVEL_SCHEDULE_EXPENSE_TYPE
T_TRAVEL_SCHEDULE_EXPENSE_FD
### 3.22 修正T_TRAVEL_SCHEDULE_EXPENSE_TYPE：
```
INSERT INTO INSERT INTO T_TRAVEL_SCHEDULE_EXPENSE_TYPE(ID,SCHEDULE_TYPE,AMOUNT_TYPE,EXPENSE_TYPE_ID,REF_DISPLAY_DATA,BX_DISPLAY_DATA,CREATE_DATE,UPDATE_DATE,REF_DISPLAY_TITLE,BX_DISPLAY_TITLE)
values('b70db87bd4a411e99457c9a35da8a414','OTHER','Traffic','a70db87bd4a411e99457c9a35da8a414','<li> {expenseType} </li> <li>出行人：{TRIP_PERSON} </li> <li>说明：{DESCRIPTION} </li>','<li> {expenseType} | {DESCRIPTION} </li> <li>出行人：{TRIP_PERSON},{EXTERNAL_STAFF} </ li>','2019-12-30','2019-12-30','','');
```
### 3.23 修正T_TRAVEL_SCHEDULE_EXPENSE_FD：
```
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('a4f505f88d8311e9a07f81831434664a', '差旅餐费主键', 'CreateTime', '创建日期', 'DEFAULT', 'BUSINESS_DATE', '业务日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('bb947bffa3d711e9836889259b517ddf', '差旅餐费主键', 'UserInfoList', '出行人', 'EXTERNAL_USER_LIST', 'EXTERNAL_STAFF', '外部人员', 'EXTERNAL_USER_LIST', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('d4f4dee68d8311e9a07ff3e1ff60f672', '差旅餐费主键', 'Amount', '金额', 'AMOUNT', 'AMOUNT', '金额', 'TRAVEL_AMOUNT', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('c4f505f78d8311e9a07fbbc0863aaf21', '差旅餐费主键', 'AmountDescription', '费用说明', 'DEFAULT', 'DESCRIPTION', '说明', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('e46e505f88d8311e9a07f8183128462a', '差旅餐费主键', 'StartDate', '开始日期', 'DEFAULT', 'START_DATE', '开始日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('f4f505f98d8311e9a07f931d2b4a0b78', '差旅餐费主键', 'EndDate', '结束日期', 'DEFAULT', 'END_DATE', '结束日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('g3f505fa8d8311e9a07f43e1556ce65e', '差旅餐费主键', 'DepartureGeoId', '出发城市', 'CITY', 'START_CITY', '出发城市', 'DIM_CODE', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('h4f505fb8d8311e9a07fa3e5a4b41a33', '差旅餐费主键', 'ArrivalGeoId', '到达城市', 'CITY', 'END_CITY', '到达城市', 'DIM_CODE', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('i4f52d0c8d8311e9a07fd3bd93f37cc0', '差旅餐费主键', 'StartCityDetail', '出发详细地址', 'DEFAULT', 'START_ADDRESS', '出发详细地址', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('j4f52d0d8d8311e9a07f69bab4cd1064', '差旅餐费主键', 'EndCityDetail', '到达详细地址', 'DEFAULT', 'END_ADDRESS', '到达详细地址', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('k4f52d0e8d8311e9a07fbd9a41803df9', '差旅餐费主键', 'UserInfoList', '出行人', 'USER_LIST', 'TRIP_PERSON', '出行人', 'USER_LIST', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('l4f52d0f8d8311e9a07f695414f98054', '差旅餐费主键', 'ExpenseType', '支出类型', 'EXPENSE_TYPE', 'expenseType', '支出类型', 'DIM_ID', '2019-12-27', '2019-12-27');
```
```
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('a2f505f88d8311e9a07f81831434664a', '航空保险主键', 'CreateTime', '创建日期', 'DEFAULT', 'BUSINESS_DATE', '业务日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('b3947bffa3d711e9836889259b517ddf', '航空保险主键', 'UserInfoList', '出行人', 'EXTERNAL_USER_LIST', 'EXTERNAL_STAFF', '外部人员', 'EXTERNAL_USER_LIST', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('d5f4dee68d8311e9a07ff3e1ff60f672', '航空保险主键', 'Amount', '金额', 'AMOUNT', 'AMOUNT', '金额', 'TRAVEL_AMOUNT', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('c5f505f78d8311e9a07fbbc0863aaf21', '航空保险主键', 'AmountDescription', '费用说明', 'DEFAULT', 'DESCRIPTION', '说明', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('e76e505f88d8311e9a07f8183128462a', '航空保险主键', 'StartDate', '开始日期', 'DEFAULT', 'START_DATE', '开始日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('fef505f98d8311e9a07f931d2b4a0b78', '航空保险主键', 'EndDate', '结束日期', 'DEFAULT', 'END_DATE', '结束日期', 'STR_DATE_TIME', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('g1f505fa8d8311e9a07f43e1556ce65e', '航空保险主键', 'DepartureGeoId', '出发城市', 'CITY', 'START_CITY', '出发城市', 'DIM_CODE', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('h2f505fb8d8311e9a07fa3e5a4b41a33', '航空保险主键', 'ArrivalGeoId', '到达城市', 'CITY', 'END_CITY', '到达城市', 'DIM_CODE', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('i1f52d0c8d8311e9a07fd3bd93f37cc0', '航空保险主键', 'StartCityDetail', '出发详细地址', 'DEFAULT', 'START_ADDRESS', '出发详细地址', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('jaf52d0d8d8311e9a07f69bab4cd1064', '航空保险主键', 'EndCityDetail', '到达详细地址', 'DEFAULT', 'END_ADDRESS', '到达详细地址', 'STRING', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('k5f52d0e8d8311e9a07fbd9a41803df9', '航空保险主键', 'UserInfoList', '出行人', 'USER_LIST', 'TRIP_PERSON', '出行人', 'USER_LIST', '2019-12-27', '2019-12-27');
insert into T_TRAVEL_SCHEDULE_EXPENSE_FD (ID, SCHEDULE_EXPENSE_TP_ID, FIELD_CODE, FIELD_NAME, FIELD_FORMAT, EXPENSE_FIELD_CODE, EXPENSE_FIELD_NAME, EXPENSE_FIELD_FORMAT, CREATE_DATE, UPDATE_DATE)
values ('l1f52d0f8d8311e9a07f695414f98054', '航空保险主键', 'ExpenseType', '支出类型', 'EXPENSE_TYPE', 'expenseType', '支出类型', 'DIM_ID', '2019-12-27', '2019-12-27');
# 主键更新
UPDATE T_TRAVEL_SCHEDULE_EXPENSE_FD set SCHEDULE_EXPENSE_TP_ID='603100a0d4a411e9945789dc16f9c1bc' WHERE SCHEDULE_EXPENSE_TP_ID='差旅餐费主键';
UPDATE T_TRAVEL_SCHEDULE_EXPENSE_FD set SCHEDULE_EXPENSE_TP_ID='d9dc93d6278011eabcb52b7684ec8dac' WHERE SCHEDULE_EXPENSE_TP_ID='航空保险主键';
# 更新航空
UPDATE T_TRAVEL_SCHEDULE_EXPENSE_TYPE SET AMOUNT_TYPE='AviationInsurance' WHERE ID='d9dc93d6278011eabcb52b7684ec8dac';
# 更新差旅
UPDATE T_TRAVEL_SCHEDULE_EXPENSE_TYPE SET AMOUNT_TYPE='TravelMeals' WHERE ID='603100a0d4a411e9945789dc16f9c1bc';
# 更新映射表：
UPDATE t_travel_schedule_expense_type set REF_DISPLAY_TITLE='{DEF_CFCS_001}→{DEF_DDCS_001}',BX_DISPLAY_TITLE='{DEF_CFCS_001}→{DEF_DDCS_001}' WHERE ID='d9dc93d6278011eabcb52b7684ec8dac'
UPDATE t_travel_schedule_expense_type set REF_DISPLAY_TITLE='{DEF_CFCS_002}→{DEF_DDCS_002}',BX_DISPLAY_TITLE='{DEF_CFCS_002}→{DEF_DDCS_002}' WHERE ID='603100a0d4a411e9945789dc16f9c1bc'
UPDATE t_travel_schedule_expense_type set REF_DISPLAY_DATA='<li> {expenseType} | {START_DATE}至{DEF_FHSJ_001} </ li> <li>出行人：{TRIP_PERSON} < / li> <li>说明：{DESCRIPTION} </ li>' where ID='603100a0d4a411e9945789dc16f9c1bc'
```
# 4.修正结果
正确结果
## 4.2 申请单
接口：
/fssc/travel/travel/generateScheduleApplyBill
# 5.差旅餐费：
```sql
UPDATE t_travel_schedule_expense_type SET REF_DISPLAY_DATA='<li>{expenseType} | {DEF_CFSJ_003}至{DEF_FHSJ_001}</li><li>出行人：{DEF_CXR_001} </li><li>说明：{DESCRIPTION}</li>',
BX_DISPLAY_DATA='<li>{expenseType} | {DEF_CFSJ_003} {DESCRIPTION}</li> <li>出行人：{DEF_CXR_001},{EXTERNAL_STAFF}</li>'
WHERE ID='603100a0d4a411e9945789dc16f9c1bc';
UPDATE t_travel_schedule_expense_fd set EXPENSE_FIELD_CODE='DEF_CXR_001' where ID='k4f52d0e8d8311e9a07fbd9a41803df9';
UPDATE t_travel_schedule_expense_fd set EXPENSE_FIELD_CODE='DEF_CFSJ_003' where ID='e46e505f88d8311e9a07f8183128462a';
```
# 6.航空保险等
```sql
select * from T_TRAVEL_SCHEDULE_EXPENSE_TYPE where ID='d9dc93d6278011eabcb52b7684ec8dac';
UPDATE t_travel_schedule_expense_type set REF_DISPLAY_DATA='<li>{expenseType} | {START_DATE}至{END_DATE}</li><li>出行人：{DEF_CXR_002}</li><li>说明：{DESCRIPTION} </li>'
,BX_DISPLAY_TITLE='<li>{expenseType} | {START_DATE}{DESCRIPTION}</li> <li>出行人：{DEF_CXR_002},{EXTERNAL_STAFF} </li>'
where ID='d9dc93d6278011eabcb52b7684ec8dac';
select * from T_TRAVEL_SCHEDULE_EXPENSE_FD WHERE SCHEDULE_EXPENSE_TP_ID='d9dc93d6278011eabcb52b7684ec8dac';
UPDATE t_travel_schedule_expense_fd set EXPENSE_FIELD_CODE='DEF_CXR_002' where id='k5f52d0e8d8311e9a07fbd9a41803df9';
```