SELECT *FROM (select 入账原币金额*财务做账汇率-预提金额 as 调整金额,b.F_VOUCHER_ID as 总账凭证 ,c.F_VOUCHERID_YF as 应付凭证,e.F_JFKMMC as 损益科目,d.F_JXDM as 机型, --1、加会计年度 case when b.F_VOUCHER_ID='×' then to_date('2012-01','YYYY-MM') else TO_DATE(substr(b.F_VOUCHER_ID,0,4)||'-'||substr(b.F_VOUCHER_ID,5,2),'YYYY-MM') end as 会计年度, --2、加民航快报统计口径 case when instr(e.F_JFKMMC,'640108')>0 then '起降费' when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640102')>0 then '航油成本'when instr(e.F_JFKMMC,'640116')>0 then '餐食成本'when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640119')>0 then '不正常航班费用'when instr(e.F_JFKMMC,'640104')>0 then '民航发展基金' when instr(e.F_JFKMMC,'640135')>0 then '机组费用'when instr(e.F_JFKMMC,'640105')>0 then '非例行检查费'else '其他' end as 民航快报统计口径, --3、加账单类型 case when t.F_ZDLX='3' then '核销账单' else '结算账单' end as 账单类型, --4、加机型（客货机） case when t.F_JXID='2' THEN '货机' ELSE '客机' END as 客货机, t.*from ( SELECT F_ZDID as 账单编号,F_DWMC as 单位名称,F_ZDLX,F_FSD as 发生地,F_JH as 机号,F_HBH as 航班号 ,F_FSRQ as 航班日期,F_HD as 航段 , F_JXID,F_ZDMXID,F_FYMXID,F_FYMC as 费用名称,F_ZDSL as 账单数量,F_BZ as 账单标准,F_JE as 账单金额,F_SSSL as 系统数量,F_SSBZ as 系统标准,F_SSJE as 系统金额,F_SSJG as 试算结果,F_ZZSDSL as 审定数量,F_ZZSDBZ as 审定标准,F_ZZSDJE as 审定金额,F_CYCL as 差异处理,F_SHYJ as 审核意见,F_ZZSDJE_CB as 入账原币金额,F_ZZSDJE_ZZS as 增值税,F_CB_ZZSL as 增值税率, (select nvl(max(f_sjje),0) from VIEW_YT_CBMXB_01 where f_zdmxid=a.f_zdmxid and f_zdid=a.f_zdid) as 预提金额, nvl(f_cwzzhl,0) as 财务做账汇率,F_FSRQ_RWS as 匹配航班日期,F_HBH_RWS as 匹配航班号, F_HD_RWS as 匹配航段,F_JH_RWS as 匹配机号 from VIEW_JSD a WHERE F_FYMC <> 'passenger service charge(代收代付)' AND F_FYMC <> 'security screening for passenger and baggage(代收代付)' AND F_FYMC <> 'International Tourist Tax(代收代付)' AND F_FYMC <> '机组早餐费' AND F_FYMC <> '机组正餐费' AND F_FYMC <> '机组晚餐费' AND F_FYMC <> '机组夜宵费' AND F_FYMC <> '机组交通费' AND F_FYMC <> '机组单间住宿费' AND F_FYMC <> '机组标间住宿费' AND F_FYMC <> '机组其它费用' ) t --5、加总账凭证号 left join(select DISTINCT b.f_zdid,b.F_VOUCHER_ID,b.F_PZSCSJ from ZW_PZZB b)b on b.f_zdid=t.账单编号 --6、加应付凭证号 left join(select DISTINCT c.f_zdid,c.F_VOUCHERID_YF from ZW_PZZB c)c on c.f_zdid=t.账单编号 --7、加机型代码（A320/B737等） left join(SELECT DISTINCT d.F_JXDM,d.F_JXID FROM GG_FJJX d)d on d.F_JXID=t.F_JXID --8、加损益类科目 left join(SELECT DISTINCT e.F_FYMC,e.F_JFKMMC,e.F_YWLBMC FROM VI_ZW_FYDZ_KM e where F_YWLBMC='成本预提' )e on e.F_FYMC=t.费用名称 where b.F_VOUCHER_ID is not null and b.F_PZSCSJ>=to_date('2021-1-1','YYYY-MM-DD'))A --合并预提明细 full join SELECT *from (SELECT v.*,e.F_JFKMMC as 预提损益类科目, case when instr(e.F_JFKMMC,'640108')>0 then '起降费' when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640102')>0 then '航油成本'when instr(e.F_JFKMMC,'640116')>0 then '餐食成本'when instr(e.F_JFKMMC,'640137')>0 then '航务管理费'when instr(e.F_JFKMMC,'640119')>0 then '不正常航班费用'when instr(e.F_JFKMMC,'640104')>0 then '民航发展基金' when instr(e.F_JFKMMC,'640135')>0 then '机组费用'when instr(e.F_JFKMMC,'640105')>0 then '非例行检查费'else '其他' end as "民航快报统计口径" from(SELECT F_YRQ,F_RQ_FX,F_RQ as 航班日期,F_FJJH as 机号,F_FJJX as 机型,F_HBBH as 航班号,F_HBXZMC as 航班性质,F_HXBH as 航线名称,F_HXFL as 航线分类,F_HDBH as 航段,F_HDFL as 航段分类,F_FYID,F_FYMC as 费用名称,F_PZFL as 预提分类,F_JSDWQC as 结算单位名称,F_FSD as 发生地,F_FLG_VR1,F_YTSL as 预提数量,F_YTBZ as 预提标准,F_YBJE as 预提原币金额,F_HBMC as 币种,F_RATE as 汇率,F_SJJE as 预提本币金额,F_ZZSL as 增值税率,F_YTMXID,F_TZNY,F_ZDID,F_ZDMXID,F_VOUCHER_ID,F_YCDPZJE from VIEW_YT_CBMXB_01 WHERE F_YRQ>=TO_DATE('2020-01-01','YYYY-MM-DD') AND F_YRQ<=sysdate and F_FYMC <> 'passenger service charge(代收代付)' AND F_FYMC <> 'security screening for passenger and baggage(代收代付)' and F_FYMC <> 'International Tourist Tax(代收代付)' and F_PZFL is not null and F_YCDPZJE=0 )v --加损益类科目 left join(SELECT DISTINCT e.F_FYMC,e.F_JFKMMC,e.F_YWLBMC FROM VI_ZW_FYDZ_KM e where F_YWLBMC='成本预提' )e on e.F_FYMC=v.费用名称)B on A.F_ZDMXID=B.F_ZDMXID AND A.账单编号=B.F_ZDID |
1min目标场景问卷
立即参与
最佳回答 |
||||
|
0
|
|
|||
|
0
|
|
|||
