F_Mmim_Matdoc_Union

@AbapCatalog.preserveKey: true
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.sqlViewName: 'FMMIMMATDOCUNION'
@EndUserText.label: 'F1077 Find Material Document'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass:#MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE

define view F_Mmim_Matdoc_Union

  as

  --------------------------------------------------------------------------------------------------------
  -- Increase - plant
  --------------------------------------------------------------------------------------------------------
  select from matdoc //nsdm_d_mtdcsa


{
  cast('01' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'INCPLANT'                              as gmselection,
  case when  umwrk_cid = ''
             then   'INC'
       when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr <> '' then lifnr
       when emlif <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,   
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,       
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,  
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/  
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   >  0
  and(
    (
          umwrk_cid   =  ''
    )
    or -- no stock transfer
    (
          umwrk_cid   <> ''
      and matbf       <> ummab_cid
      and werks       =  umwrk_cid
    )
    or -- stock transfer
    (
          umwrk_cid   <> ''
      and werks       <> umwrk_cid
    )
  )    -- stock transfer

union all
// Complementary posting in case of stock transfers

select from matdoc //nsdm_d_mtdcsa


{
  cast('01' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'INCPLANT'                              as gmselection,
  case when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  ummab_cid                               as matnr,
  umwrk_cid                               as werks,
  charg_whs_sg                            as charg,
  case when lgort_cid <> '' then lgort_cid
       else lgort
       end                                as lgort,

       //CE2105

       xauto,

  case when lifnr_cid <> '' then lifnr_cid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
  case when kunnr_cid <> '' then kunnr_cid
       when kunnr     <> '' then kunnr
       else ''
       end                                as kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_cid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im,  
  aufnr,
  // stock transfers

  //CE2105

  matbf                                   as ummat,
  werks                                   as umwrk,
  lgort_sid                               as umlgo,
  lbbsa_sid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   <  0
  and     umwrk_cid   <> ''
  and(
    (
          matbf       <> ummab_cid
      and werks       =  umwrk_cid
    )
    or -- stock transfer
    (
          werks       <> umwrk_cid
    )
  )    -- stock transfer

union all
--------------------------------------------------------------------------------------------------------
-- Increase - storage location
--------------------------------------------------------------------------------------------------------
select from matdoc // nsdm_d_mtdcsa


{
  cast('01' as mmim_stock_change_type)    as StockChangeType,
  cast('02' as mmim_stock_change_context) as StockChangeContext,
  'INCSTLOC'                              as gmselection,
  case when  umwrk_cid = ''
             then   'INC'
       when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,
  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   >  0
  and     lgort_sid   <> ''
  and(
    (
          lgort_sid   <> lgort_cid
    )
    or -- no stock stransfer or stock transfer
    (
          umwrk_cid   <> ''
      and matbf       <> ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
    )
    or -- stock transfer
    (
          umwrk_cid   <> ''
      and matbf       =  ummab_cid
      and werks       <> umwrk_cid
      and lgort_sid   =  lgort_cid
    )
  )    -- stock transfer

union all

// Complementary posting in case of stock transfers

select from matdoc //nsdm_d_mtdcsa


{
  cast('01' as mmim_stock_change_type)    as StockChangeType,
  cast('02' as mmim_stock_change_context) as StockChangeContext,
  'INCSTLOC'                              as gmselection,
  case when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  ummab_cid                               as matnr,
  umwrk_cid                               as werks,
  charg_whs_sg                            as charg,
  case when lgort_cid <> '' then lgort_cid
       else lgort
       end                                as lgort,

   //CE2105

   xauto,

  case when lifnr_cid <> '' then lifnr_cid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
  case when kunnr_cid <> '' then kunnr_cid
       when kunnr     <> '' then kunnr
       else ''
       end                                as kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_cid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  matbf                                   as ummat,
  werks                                   as umwrk,
  lgort_sid                               as umlgo,
  lbbsa_sid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   <  0
  and     umwrk_cid   <> ''
  and     lgort_cid   <> ''
  and(
    (
          lgort_sid   <> lgort_cid
    )
    or -- stock transfer
    (
          matbf       <> ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
    )
    or -- stock transfer
    (
          matbf       =  ummab_cid
      and werks       <> umwrk_cid
      and lgort_sid   =  lgort_cid
    )
  )    -- stock transfer

union all

--------------------------------------------------------------------------------------------------------
-- Decrease - plant
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('02' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'DECPLANT'                              as gmselection,
  case when  umwrk_cid = ''
             then   'DEC'
       when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im,  
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   <  0
  and(
    (
          umwrk_cid   =  ''
    )
    or -- no stock transfer
    (
          umwrk_cid   <> ''
      and matbf       <> ummab_cid
      and werks       =  umwrk_cid
    )
    or -- stock transfer
    (
          umwrk_cid   <> ''
      and werks       <> umwrk_cid
    )
  )    -- stock transfer
union all
// Complementary posting in case of stock transfers

select from matdoc //nsdm_d_mtdcsa


{
  cast('02' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'DECPLANT'                              as gmselection,
  case when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  ummab_cid                               as matnr,
  umwrk_cid                               as werks,
  charg_whs_sg                            as charg,
  case when lgort_cid <> '' then lgort_cid
       else lgort
       end                                as lgort,

       //CE2105

       xauto,

  case when lifnr_cid <> '' then lifnr_cid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
  case when kunnr_cid <> '' then kunnr_cid
       when kunnr     <> '' then kunnr
       else ''
       end                                as kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,       
  lbbsa_cid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im,  
  aufnr,
  // stock transfers

  //CE2105

  matbf                                   as ummat,
  werks                                   as umwrk,
  lgort_sid                               as umlgo,
  lbbsa_sid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   >  0
  and     umwrk_cid   <> ''
  and(
    (
          werks       <> umwrk_cid
    )
    or -- stock transfer
    (
          matbf       <> ummab_cid
      and werks       =  umwrk_cid
    )
  )    -- stock transfer

union all
--------------------------------------------------------------------------------------------------------
-- Decrease - storage location
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('02' as mmim_stock_change_type)    as StockChangeType,
  cast('02' as mmim_stock_change_context) as StockChangeContext,
  'DECSTLOC'                              as gmselection,
  case when  umwrk_cid = ''
             then   'DEC'
       when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,
  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   <  0
  and     lgort_sid   <> ''
  and(
    (
          lgort_sid   <> lgort_cid
    )
    or -- no stock transfer or stock transfer
    (
          umwrk_cid   <> ''
      and matbf       <> ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
    )
    or -- stock transfer
    (
          umwrk_cid   <> ''
      and matbf       =  ummab_cid
      and werks       <> umwrk_cid
      and lgort_sid   =  lgort_cid
    )
  )    -- stock transfer

union all

// Complementary posting in case of stock transfers

select from matdoc //nsdm_d_mtdcsa


{
  cast('02' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'DECSTLOC'                              as gmselection,
  case when  werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       else
             'TRA'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  ummab_cid                               as matnr,
  umwrk_cid                               as werks,
  charg_whs_sg                            as charg,
  case when lgort_cid <> '' then lgort_cid
       else lgort
       end                                as lgort,

       //CE2105

       xauto,

  case when lifnr_cid <> '' then lifnr_cid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
  case when kunnr_cid <> '' then kunnr_cid
       when kunnr     <> '' then kunnr
       else ''
       end                                as kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_cid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im,  
  aufnr,
  // stock transfers

  //CE2105

  matbf                                   as ummat,
  werks                                   as umwrk,
  lgort_sid                               as umlgo,
  lbbsa_sid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
  and     xauto       <> 'X'
  and     stock_qty   >  0
  and     umwrk_cid   <> ''
  and     lgort_cid   <> ''
  and(
    (
          lgort_sid   <> lgort_cid
    )
    or -- stock transfer
    (
          matbf       <> ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
    )
    or -- stock transfer
    (
          matbf       =  ummab_cid
      and werks       <> umwrk_cid
      and lgort_sid   =  lgort_cid
    )
  )    -- stock transfer

union all

--------------------------------------------------------------------------------------------------------
-- Transfer - plant
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('04' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'TRAPLANT'                              as gmselection,
  case when  lgort_sid = lgort_cid
             then   'TRL'
       else
             'TRP'
       end                                as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr, 
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
       record_type =  'MDOC'
  //and  xauto       <> 'X'

  and  stock_qty   <> 0
  and  umwrk_cid   <> ''
  and  matbf       =  ummab_cid
  and  werks       =  umwrk_cid
  and(
       lgort_sid   <> lgort_cid
    or lbbsa_sid   <> lbbsa_cid
    or sobkz       <> umsok_cid
    or charg_sid   <> charg_cid
    or lifnr_sid   <> lifnr_cid
    or mat_kdauf   <> mat_kdauf_cid
    or mat_kdpos   <> mat_kdpos_cid
    or mat_pspnr   <> mat_pspnr_cid
    or kunnr_sid   <> kunnr_cid
  )

union all
--------------------------------------------------------------------------------------------------------
-- Transfer - plant / storage location
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('04' as mmim_stock_change_type)    as StockChangeType,
  cast('02' as mmim_stock_change_context) as StockChangeContext,
  'TRASTLOC'                              as gmselection,
  'TRL'                                   as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
       record_type =  'MDOC'
//  and  xauto       <> 'X'

  and  stock_qty   <> 0
  and  matbf       =  ummab_cid
  and  werks       =  umwrk_cid
  and  lgort_sid   =  lgort_cid
  and(
       lbbsa_sid   <> lbbsa_cid
    or sobkz       <> umsok_cid
    or charg_sid   <> charg_cid
    or lifnr_sid   <> lifnr_cid
    or mat_kdauf   <> mat_kdauf_cid
    or mat_kdpos   <> mat_kdpos_cid
    or mat_pspnr   <> mat_pspnr_cid
    or kunnr_sid   <> kunnr_cid
  )

union all
--------------------------------------------------------------------------------------------------------
-- no stock change - plant
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('03' as mmim_stock_change_type)    as StockChangeType,
  cast('01' as mmim_stock_change_context) as StockChangeContext,
  'NOPLANT'                               as gmselection,
  'NOT'                                   as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
//  and     xauto       <> 'X'

  and(
    (
          stock_qty   =  0
    )
    or(
          stock_qty   <> 0
      and matbf       =  ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
      and lbbsa_sid   =  lbbsa_cid
      and sobkz       =  umsok_cid
      and charg_sid   =  charg_cid
      and lifnr_sid   =  lifnr_cid
      and mat_kdauf   =  mat_kdauf_cid
      and mat_kdpos   =  mat_kdpos_cid
      and mat_pspnr   =  mat_pspnr_cid
      and kunnr_sid   =  kunnr_cid
    )
  )

union all
--------------------------------------------------------------------------------------------------------
-- no stock change - storage location
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('03' as mmim_stock_change_type)    as StockChangeType,
  cast('02' as mmim_stock_change_context) as StockChangeContext,
  'NOSTLOC'                               as gmselection,
  'NOT'                                   as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                   as matnr,
  werks,
  charg_whs_sg                            as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                                as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                                as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                                as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                                as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                                as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr         //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                               as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im, 
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                               as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
          record_type =  'MDOC'
//  and     xauto       <> 'X'

  and(
    (
          stock_qty   =  0
    )
    or(
          stock_qty   <> 0
      and matbf       =  ummab_cid
      and werks       =  umwrk_cid
      and lgort_sid   =  lgort_cid
      and lbbsa_sid   =  lbbsa_cid
      and sobkz       =  umsok_cid
      and charg_sid   =  charg_cid
      and lifnr_sid   =  lifnr_cid
      and mat_kdauf   =  mat_kdauf_cid
      and mat_kdpos   =  mat_kdpos_cid
      and mat_pspnr   =  mat_pspnr_cid
      and kunnr_sid   =  kunnr_cid
    )
  )

union all
--------------------------------------------------------------------------------------------------------
-- free style
--------------------------------------------------------------------------------------------------------
select from matdoc //nsdm_d_mtdcsa


{
  cast('05' as mmim_stock_change_type)  as StockChangeType,
  cast('' as mmim_stock_change_context) as StockChangeContext,
  'ALL'                                 as gmselection,
  case when  ((stock_qty = 0) or
              (stock_qty <> 0 and matbf = ummab_cid and werks = umwrk_cid and lgort_sid = lgort_cid and
               lbbsa_sid = lbbsa_cid and sobkz = umsok_cid and
               charg_sid = charg_cid and lifnr_sid = lifnr_cid and mat_kdauf = mat_kdauf_cid and mat_kdpos = mat_kdpos_cid and
               mat_pspnr = mat_pspnr_cid and kunnr_sid = kunnr_cid))
             then   'NOT'
       when  stock_qty > 0 and umwrk_cid = ''
             then   'INC'
       when  stock_qty < 0 and umwrk_cid = ''
             then   'DEC'
       when  stock_qty <> 0 and umwrk_cid <> '' and werks = umwrk_cid and lgort_sid = lgort_cid and matbf = ummab_cid
             then   'TRL'
       when  stock_qty <> 0 and umwrk_cid <> '' and werks = umwrk_cid and matbf = ummab_cid
             then   'TRP'
       when  stock_qty <> 0 and umwrk_cid <> ''
             then   'TRA'
       else
             '  '
       end                              as changetype,
  mblnr,
  mjahr,
  zeile,
  // Item data - stock identifier

  matbf                                 as matnr,
  werks,
  charg_whs_sg                          as charg,
//  case when lgort_sid <> '' then lgort_sid

//       else lgort

//       end                              as 

       lgort,

       //CE2105

       xauto,

  case when lifnr_sid <> '' then lifnr_sid
       when lifnr     <> '' then lifnr
       when emlif     <> '' then emlif
       else ''
       end                              as lifnr,
  case when mat_kdauf <> '' then mat_kdauf
       else kdauf
       end                              as kdauf,
  case when mat_kdpos <> '000000' then mat_kdpos
       else kdpos
       end                              as kdpos,
//  case when kunnr_sid <> '' then kunnr_sid

//       when kunnr     <> '' then kunnr

//       else ''

//       end                              as 

       kunnr,
  case when mat_pspnr is not initial then mat_pspnr       //2838890

       else ps_psp_pnr
       end                                as ps_psp_pnr,      
  lbbsa_sid                             as lbbsa,
  // Item data - movement type

  bwart,
  // Item data - movement type data text

  sobkz,
  kzbew,
  kzzug,
  kzvbr,
  // Enhancements because of Multiple Accounting  

  weunb,
  xmacc,
//  kostl,

  anln1,
  anln2,
  bukrs,
//  sakto,

  // Item data - GLAccount

  knttp_gr,
  sakto,
  fkber,
  prctr,
  // Item data - Additional

  work_item_id,
  pernr,
  sgtxt,
  grund,
  cancelled,
  // Header data

  bldat,
  budat,
  cpudt,
  cputm,
  resourcename_sid,
  resourcename_cid,
  usnam,
  xblnr,
  bktxt,
  frbnr,
  blart,
  vgart,
  // Item data - references

  ebeln,
  ebelp,
  wempf,
  ablad,
  kostl,
  kokrs,
  vbeln_im,
  vbelp_im,  
  aufnr,
  // stock transfers

  //CE2105

  ummat,
  umwrk,
  umlgo,
  lbbsa_cid                             as umlbbsa,
  // cancelled item

  sjahr,
  smbln,
  smblp,
  // Item data - quantity and value

  shkzg,
  erfme,
  erfmg,
  meins,
  stock_qty,
/*
Start Fashion specific fields
*/  
  sgt_scat,
  fsh_season_year,
  fsh_season,
  fsh_collection,
  fsh_theme,
/*
End Fashion specific fields
*/
ewm_gmdoc,
ewm_lgnum
}
where
      record_type =  'MDOC'
//  and xauto       <> 'X'