@AbapCatalog.sqlViewName: 'NSDM_V_CHK_MD_MM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@EndUserText.label: 'Chk for miss mat mast vs. MATDOC_EXTRACT'
@DataAging.noAgingRestriction: 'false'
@ObjectModel.usageType.sizeCategory: 'L'
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ClientHandling.algorithm: #AUTOMATED
@AccessControl.authorizationCheck: #NOT_ALLOWED
// Implemented with Note 2968589
/* -Check data from table MATDOC_EXTRACT with the views of associated material master data tables:
* MARC, MARD, MCHB, MSKA, MSKU, MKOL, MSLB, MSPR, MSRD, MSFD
* -The check works by a left join, which contains the entries of the MATDOC_EXTRACT without a JOIN partner (where s.matnr IS NULL)
* and by checking the respective potential values in the field SOBKZ.
* -The output are data without associated master data with inventory (sum(stock_qty_l1) <> 0 or sum(/cwm/stock_qty_l1)<>0 or sum(stock_vkwrt_l1)<>0)
* and constellations occurring in productive operation (restriction by querying the lbbsa_sid field).
* The information about the corresponding lbbsa_sid is taken from NSDM_DDL_"Name master data table"_DIFF.
* -All LEFT JOINS are combined with UNION ALL to use the feature of parallelization on HANA DB.
* -To identify each data the field master_table is hard coded with name of master data table.
* -Icon is also hard coded for various outputs.
*/
define view NSDM_DDL_CHK_MAT_MASTER
as select from matdoc_extract as m
left outer join v_marc_md as s on m.matbf = s.matnr --Key fields of MARC
and m.werks = s.werks
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MARC' as master_table
}
where
s.matnr is null
and m.lgort_sid is null
and m.sobkz = ' '
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '05'
or m.lbbsa_sid = '06'
or m.lbbsa_sid = '09'
or m.lbbsa_sid = '10'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mard_md as s on m.matbf = s.matnr --Key fields of MARD
and m.werks = s.werks
and m.lgort_sid = s.lgort
{
matbf,
m.werks,
lgort_sid,
'' as charg_sid, --3316931
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MARD' as master_table
}
where
s.matnr is null
and record_type <> 'PERFY'
--and m.charg_sid is initial --3316931
and m.lgort_sid is not initial
and(
m.sobkz = ' '
or m.sobkz = 'K'
or m.sobkz = 'M'
)
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '03'
or m.lbbsa_sid = '04'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
--charg_sid, --3316931
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mchb_md as s on m.matbf = s.matnr --Key fields of MCHB
and m.werks = s.werks
and m.lgort_sid = s.lgort
and m.charg_sid = s.charg
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MCHB' as master_table
}
where
s.matnr is null
and record_type <> 'PERFY'
and m.lgort_sid is not initial
and m.charg_sid is not initial
and(
m.sobkz = ' '
)
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '03'
or m.lbbsa_sid = '04'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mska_md as s on m.matbf = s.matnr --Key fields of MSKA
and m.werks = s.werks
and m.lgort_sid = s.lgort
and m.charg_sid = s.charg
and m.mat_kdauf = s.vbeln
and m.mat_kdpos = s.posnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSKA' as master_table
}
where
s.matnr is null
and(
m.sobkz = 'T'
or m.sobkz = 'E'
)
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_msku_md as s on m.matbf = s.matnr --Key fields of MSKU
and m.werks = s.werks
and m.charg_sid = s.charg
and m.kunnr_sid = s.kunnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSKU' as master_table
}
where
s.matnr is null
and(
m.sobkz = 'V'
or m.sobkz = 'W'
)
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '05'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mkol_md as s on m.matbf = s.matnr --Key fields of MKOL
and m.werks = s.werks
and m.lgort_sid = s.lgort
and m.charg_sid = s.charg
and m.lifnr_sid = s.lifnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MKOL' as master_table
}
where
s.matnr is null
and m.sobkz = 'K'
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mslb_md as s on m.matbf = s.matnr --Key fields of MSLB
and m.werks = s.werks
and m.charg_sid = s.charg
and m.lifnr_sid = s.lifnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSLB' as master_table
}
where
s.matnr is null
and m.sobkz = 'O'
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '05'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join v_mspr_md as s on m.matbf = s.matnr --Key fields of MSPR
and m.werks = s.werks
and m.lgort_sid = s.lgort
and m.charg_sid = s.charg
and m.mat_pspnr = s.pspnr
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSPR' as master_table
}
where
m.sobkz = 'Q'
and m.lbbsa_sid <> '06'
and m.lbbsa_sid <> '10'
and s.matnr is null
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join msrd_md as s on m.matbf = s.matnr --Key fields of MSRD
and m.werks = s.werks
and m.charg_sid = s.charg
and m.mat_pspnr = s.pspnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSRD' as master_table
}
where
s.matnr is null
and m.sobkz = 'R'
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m
left outer join msfd_md as s on m.matbf = s.matnr --Key fields of MSFD
and m.werks = s.werks
and m.charg_sid = s.charg
and m.lifnr_sid = s.lifnr
and m.mat_kdauf = s.vbeln
and m.mat_kdpos = s.posnr
and m.sobkz = s.sobkz
{
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MSFD' as master_table
}
where
s.matnr is null
and m.sobkz = 'F'
and record_type <> 'PERFY'
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '08'
)
group by
matbf,
m.werks,
lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper --3316931
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m --added with Note 3325107
left outer join mcha as s on m.matbf = s.matnr --Key fields of MCHA
and m.werks = s.werks
and m.charg_sid = s.charg
{
matbf,
m.werks,
' ' as lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MCHA' as master_table
}
where
s.matnr is null
and record_type <> 'PERFY'
and m.charg_sid is not initial
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '03'
or m.lbbsa_sid = '04'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
or m.lbbsa_sid = '10'
)
group by
matbf,
m.werks,
-- lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
union all select from matdoc_extract as m --added with Note 3325107
left outer join mch1 as s on m.matbf = s.matnr --Key fields of MCH1
and m.charg_sid = s.charg
{
matbf,
' ' as werks,
' ' as lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz,
max( m.gjper ) as gjper,
'@B6@' as icon,
'MCH1' as master_table
}
where
s.matnr is null
and record_type <> 'PERFY'
and m.charg_sid is not initial
and(
m.lbbsa_sid = '01'
or m.lbbsa_sid = '02'
or m.lbbsa_sid = '03'
or m.lbbsa_sid = '04'
or m.lbbsa_sid = '07'
or m.lbbsa_sid = '08'
or m.lbbsa_sid = '10'
)
group by
matbf,
-- m.werks,
-- lgort_sid,
charg_sid,
lifnr_sid,
mat_kdauf,
mat_kdpos,
mat_pspnr,
kunnr_sid,
m.sobkz
--gjper
having
sum(
stock_qty_l1
) <> 0
or sum(
/cwm/stock_qty_l1
) <> 0
or sum(
stock_vkwrt_l1
) <> 0
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"MATDOC_EXTRACT",
"MCH1",
"MCHA",
"MSFD_MD",
"MSRD_MD",
"V_MARC_MD",
"V_MARD_MD",
"V_MCHB_MD",
"V_MKOL_MD",
"V_MSKA_MD",
"V_MSKU_MD",
"V_MSLB_MD",
"V_MSPR_MD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/