@AbapCatalog.sqlViewName: 'FCOV_MIG_DERIV_P'
@EndUserText.label: 'CO Delta Entries (COSP+COSS) - Derivation of Dep. Fields'
@ObjectModel.usageType.serviceQuality: #P
define view FCO_MIG_DERIV_P
with parameters
iv_mig_source_1 : abap.char(1),
iv_mig_source_2 : abap.char(1),
iv_mig_source_3 : abap.char(1),
iv_mig_source_4 : abap.char(1),
iv_mig_source_5 : abap.char(1),
iv_mig_source_6 : abap.char(1)
as select
rclnt,
rldnr,
c.objnr,
gjahr,
c.racct,
hrkft,
vrgng,
rassc,
co_belkz,
@Semantics.currencyCode
rtcur,
@Semantics.currencyCode
rwcur,
@Semantics.currencyCode
rkcur,
@Semantics.currencyCode
cast(rocur as abap.cuky(5)) as rocur,
@Semantics.currencyCode
cast(rvcur as abap.cuky(5)) as rvcur,
@Semantics.currencyCode
cast(rbcur as abap.cuky(5)) as rbcur,
@Semantics.currencyCode
cast(rccur as abap.cuky(5)) as rccur,
@Semantics.currencyCode
cast(rdcur as abap.cuky(5)) as rdcur,
@Semantics.currencyCode
cast(recur as abap.cuky(5)) as recur,
@Semantics.currencyCode
cast(rfcur as abap.cuky(5)) as rfcur,
@Semantics.currencyCode
cast(rgcur as abap.cuky(5)) as rgcur,
case when tka01.ctyp != '10' then ''
when ( accasty = 'NV' or accasty = 'OV' ) then af.waers
when ( accasty = 'PR' ) then p.pwpos
when ( accasty = 'KS'
or accasty = 'KL'
or accasty = 'BP' ) then '' // postprocessing in ABAP (unable to derive date for CSKS and CBPR)
when ( accasty = 'OR' ) then au.waers
when ( accasty = 'OP' ) then auop.waers
when ( accasty = 'IS' ) then vicn01.recncurr
when ( accasty = 'VB' // see FM SD_DOCUMENT_ACCOUNT_ASSIGNMENT line 618
or accasty = 'AO' // no own object currency
or accasty = 'EO' // no own object currency
or accasty = 'IA' // RE: see cl_reex_services_fico=>get_object_acc_data
or accasty = 'IB'
or accasty = 'IC'
or accasty = 'ID'
or accasty = 'IG'
or accasty = 'IH'
or accasty = 'II'
or accasty = 'IJ'
or accasty = 'IM'
or accasty = 'IO'
or accasty = 'IR'
or accasty = 'IT'
or accasty = 'IU'
or accasty = 'IV'
or accasty = 'IW'
or accasty = 'IK'
or accasty = 'IX'
or accasty = 'IY'
or accasty = 'IZ' ) then t001.waers
else ''
end as rco_ocur,
t001.waers as rhcur,
case when accasty != '' then t001.waers
when tka01.ctyp = '10' then t001.waers
when ( objnr2 = 'NV' or objnr2 = 'OV' ) then af.waers
when ( objnr2 = 'PR' ) then p.pwpos
when ( objnr2 = 'KS'
or objnr2 = 'KL' ) then '' // postprocessing in ABAP (unable to derive date for CSKS)
when ( objnr2 = 'BP' ) then '' // postprocessing in ABAP (unable to derive date for CBPR)
when ( objnr2 = 'OR' ) then au.waers
when ( objnr2 = 'OP' ) then auop.waers
when ( objnr2 = 'IS' ) then vicn01.recncurr
when ( objnr2 = 'VB' // see FM SD_DOCUMENT_ACCOUNT_ASSIGNMENT line 618
or objnr2 = 'AO' // no own object currency
or objnr2 = 'EO' // no own object currency
or objnr2 = 'IA' // RE: see cl_reex_services_fico=>get_object_acc_data
or objnr2 = 'IB'
or objnr2 = 'IC'
or objnr2 = 'ID'
or objnr2 = 'IG'
or objnr2 = 'IH'
or objnr2 = 'II'
or objnr2 = 'IJ'
or objnr2 = 'IM'
or objnr2 = 'IO'
or objnr2 = 'IR'
or objnr2 = 'IT'
or objnr2 = 'IU'
or objnr2 = 'IV'
or objnr2 = 'IW'
or objnr2 = 'IK'
or objnr2 = 'IX'
or objnr2 = 'IY'
or objnr2 = 'IZ' ) then t001.waers
else t001.waers
end as rhcur_stat,
rvunit,
rvunit as co_meinh,
tka01.ctyp as tka01_ctyp, // needed for object currency postprocessing in ABAP for KS/KL
beltp,
rbukrs,
cast(rfarea as abap.char(16)) as rfarea,
segment,
sbusa,
beknz as co_beknz,
// mark cases with positive and negative amounts in one row
case when wsl > 0 and ( hsl < 0 or ksl < 0 )
or hsl > 0 and ( wsl < 0 or ksl < 0 )
or ksl > 0 and ( wsl < 0 or hsl < 0 ) then 'X' else ''
end as flg_mix,
// calculate DRCRK by sign of amount (FI logic)
case when tsl < 0 or wsl2 < 0 or wsl3 < 0 or hsl < 0 or ksl < 0 or osl < 0 or vsl < 0
or bsl < 0 or csl < 0 or dsl < 0 or esl < 0 or fsl < 0 or gsl < 0 or co_osl < 0 then 'H' else 'S'
end as drcrk,
c.rfund,
c.rgrant_nbr,
c.rbudget_pd,
poper,
c.kokrs,
tsl,
wsl,
wsl2,
wsl3,
hsl,
ksl,
osl,
vsl,
bsl,
csl,
dsl,
esl,
fsl,
gsl,
kfsl,
kfsl2,
kfsl3,
psl,
psl2,
psl3,
pfsl,
pfsl2,
pfsl3,
co_osl,
vmsl,
vmsl as co_megbtr,
vmfsl,
vmfsl as co_mefbtr,
muvflg,
muvflg_co,
muvflg_ac,
cast(timestmp as abap.dec( 15, 0 ) ) as timestamp,
uspob,
accasty,
paccasty,
'' as bstat, // -- obsolete --
'' as mig_source, // -- obsolete --
// derived fields:
gjahr as ryear,
'0' as rrcty,
cast(concat(gjahr, poper) as abap.numc(7)) as fiscyearper,
c.periv,
ska1.glaccount_type,
'S' as koart,
--vrgng as bttype, note -3339317
case vrgng when 'COIN' then 'RFBU'
when 'COIE' then 'RFBU'
else vrgng end as bttype,
case
when uspob2 = 'KS' then substring( c.uspob, 7, 10 )
when uspob2 = 'KL' then substring( c.uspob, 7, 10 )
else ''
end as ukostl,
case
when uspob2 = 'KL' then substring( c.uspob, 17, 6 )
else ''
end as ulstar,
case
when uspob2 = 'BP' then substring( c.uspob, 7, 12 )
else ''
end as uprznr,
// account assignments derived from OBJNR:
case objnr2
when 'KS' then substring( c.objnr, 7, 10 )
when 'KL' then substring( c.objnr, 7, 10 )
else ''
end as rcntr,
case objnr2
when 'KL' then substring( c.objnr, 17, 6 )
else ''
end as lstar,
case
when accasty = '' then ''
when objnr2 = 'KS' then substring( c.objnr, 7, 10 )
when objnr2 = 'KL' then concat( concat( substring( c.objnr, 7, 10 ), '/' ), substring( c.objnr, 17, 6 ) )
when objnr2 = 'VB' then concat( concat( substring( c.objnr, 3, 10 ), '/' ), substring( c.objnr, 13, 6 ) )
when objnr2 = 'EO' then substring( c.objnr, 7, 10 )
when objnr2 = 'BP' then substring( c.objnr, 7, 12 )
when objnr2 = 'OR' then substring( c.objnr, 3, 12 )
when objnr2 = 'OP' then concat( concat( substring( c.objnr, 3, 12 ), '/' ), substring( c.objnr, 15, 4 ) )
when objnr2 = 'NP' then substring( c.objnr, 3, 12 )
else ''
end as accas,
case objnr2
when 'OR' then substring( c.objnr, 3, 12 )
else ''
end as aufnr,
case objnr2
when 'VB' then substring( c.objnr, 3, 10 )
else ''
end as kdauf,
case objnr2
when 'VB' then substring( c.objnr, 13, 6 )
else '000000'
end as kdpos,
case objnr2
when 'EO' then substring( c.objnr, 3, 4 )
else ''
end as erkrs,
case objnr2
when 'EO' then substring( c.objnr, 7, 10 )
else ''
end as paobjnr,
case
when p.posid is null then ''
else cast(p.posid as abap.char(24))
end as ps_posid,
case
when p.pspnr is null then '00000000'
else cast(p.pspnr as abap.numc(8))
end as ps_psp_pnr,
case
when pro.pspid is null then ''
else cast(pro.pspid as abap.char(24))
end as ps_pspid,
case
when au.autyp is null then '00'
else au.autyp
end as autyp,
case when npl.nplnr is not null then npl.nplnr
else case when objnr2 = 'NP' then substring( c.objnr,3,12 )
else ''
end
end as nplnr,
coalesce(npl.vornr,'') as nplnr_vorgn,
case when pnpl.nplnr is not null then pnpl.nplnr
else case when objnr2 = 'NP' then substring( c.parob,3,12 )
else ''
end
end as pnplnr,
coalesce(pnpl.vornr,'') as pnplnr_vorgn,
// industry fields
coalesce(re.swenr,'') as swenr,
coalesce(re.sgenr,'') as sgenr,
coalesce(re.sgrnr,'') as sgrnr,
coalesce(re.smenr,'') as smenr,
coalesce(re.recnnr,'') as recnnr,
coalesce(re.snksl,'') as snksl,
coalesce(re.sempsl,'') as sempsl,
// account assignments derived from PAROB:
case
when parob is null then ''
when parob = '' then ''
when parob = parob2 and parob = paccasty then '2'
else '1'
end as parobsrc,
parob as parob1,
case parob2
when 'KS' then substring( parob, 7, 10 )
when 'KL' then substring( parob, 7, 10 )
else ''
end as scntr,
case parob2
when 'KL' then substring( parob, 17, 6 )
else ''
end as plstar,
case
when parob2 = parob then ''
when parob2 = 'KS' then substring( parob, 7, 10 )
when parob2 = 'KL' then concat( concat( substring( parob, 7, 10 ), '/' ), substring( parob, 17, 6 ) )
when parob2 = 'VB' then concat( concat( substring( parob, 3, 10 ), '/' ), substring( parob, 13, 6 ) )
when parob2 = 'EO' then substring( parob, 7, 10 )
when parob2 = 'BP' then substring( parob, 7, 12 )
when parob2 = 'OR' then substring( parob, 3, 12 )
when parob2 = 'OP' then concat( concat( substring( parob, 3, 12 ), '/' ), substring( parob, 15, 4 ) )
when parob2 = 'NP' then substring( parob, 3, 12 )
else ''
end as paccas,
case parob2
when 'OR' then cast(substring( parob, 3, 12 ) as abap.char(12))
else ''
end as paufnr,
case parob2
when 'VB' then substring( parob, 3, 10 )
else ''
end as pkdauf,
case parob2
when 'VB' then substring( parob, 13, 6 )
else '000000'
end as pkdpos,
case parob2
when 'EO' then substring( c.objnr, 7, 10 )
else ''
end as ppaobjnr,
case
when parob_p.posid is null then ''
else cast(parob_p.posid as abap.char(24))
end as pps_posid,
case
when parob_pro.pspid is null then ''
else cast(parob_pro.pspid as abap.char(24))
end as pps_pspid,
case
when ( parob_au.autyp is null or c.objnr2 <> 'OR' ) then '00'
else parob_au.autyp
end as pautyp,
t001.ktopl,
t001.ktop2,
skb1.altkt as lokkt,
skb1.xopvw
from FCO_MIG_CNDNS_P
( iv_mig_source_1 : $parameters.iv_mig_source_1,
iv_mig_source_2 : $parameters.iv_mig_source_2,
iv_mig_source_3 : $parameters.iv_mig_source_3,
iv_mig_source_4 : $parameters.iv_mig_source_4,
iv_mig_source_5 : $parameters.iv_mig_source_5,
iv_mig_source_6 : $parameters.iv_mig_source_6 ) as c
left outer join prps as p on p.mandt = c.rclnt
and p.objnr = c.objnr
--and c.objnr2 = 'PR' -> commented out because condition not needed and causing optimizer to go a wrong way
left outer join proj as pro on pro.mandt = p.mandt
and pro.pspnr = p.psphi
left outer join aufk as au on au.mandt = c.rclnt
and au.objnr = c.objnr
left outer join fco_mig_nplnr as npl on npl.mandt = c.rclnt
and npl.objnr = c.objnr
left outer join prps as parob_p on parob_p.mandt = c.rclnt
and parob_p.objnr = c.parob
and parob_p.objnr <> ''
left outer join proj as parob_pro on parob_pro.mandt = parob_p.mandt
and parob_pro.pspnr = parob_p.psphi
left outer join aufk as parob_au on parob_au.mandt = c.rclnt
and parob_au.objnr = c.parob
and parob_au.objnr <> ''
left outer join fco_mig_nplnr as pnpl on pnpl.mandt = c.rclnt
and pnpl.objnr = c.parob
and pnpl.objnr <> ''
left outer join t001 on t001.mandt = c.rclnt
and t001.bukrs = c.rbukrs
left outer join ska1 on ska1.mandt = c.rclnt
and ska1.ktopl = t001.ktopl
and ska1.saknr = c.racct
left outer join skb1 on skb1.mandt = c.rclnt
and skb1.bukrs = c.rbukrs
and skb1.saknr = c.racct
left outer join fco_mig_re_obj as re on re.mandt = c.rclnt
and re.objnr = c.objnr
inner join tka02 on tka02.mandt = c.rclnt
and tka02.bukrs = c.rbukrs
and tka02.gsber = ''
inner join tka01 on tka02.mandt = tka01.mandt
and tka02.kokrs = tka01.kokrs
// tables needed to derive object currency key
left outer join afvc as af on af.mandt = c.rclnt
and af.objnr = c.objnr
and af.objnr <> ''
left outer join vicn01 on vicn01.mandt = c.rclnt
and vicn01.recnobjnr = c.objnr
and vicn01.recnobjnr <> ''
--and c.objnr2 = 'IS' -> commented out because condition not needed and causing optimizer to go a wrong way
left outer join aufk as auop on auop.mandt = c.rclnt
and auop.objnr = c.objnr_OP_to_OR_tmp
and auop.objnr <> ''