P_SalesDocumentByObjectStatus

@AbapCatalog.sqlViewName: 'PSLSDOCBYOBJSTS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Sales Documents and Items by Object Status'

@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #CONSUMPTION
@VDM.private: true
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L

define view P_SalesDocumentByObjectStatus
  as select from I_SalesDocument          as SalesDocument

    inner join I_StatusObjectActiveStatus as _StatusObjectActiveStatus on SalesDocument.ControllingObject = _StatusObjectActiveStatus.StatusObject

  association [0..1] to I_StatusCode      as _StatusCode     on  $projection.SystemStatus  = _StatusCode.StatusCode
                                                             and $projection.StatusProfile = _StatusCode.StatusProfile
  // Retrieve system status short name and description

  association [0..1] to I_StatusCodeText  as _StatusCodeText on  _StatusObjectActiveStatus.StatusCode   = _StatusCodeText.StatusCode
                                                             and $projection.StatusProfile              = _StatusCodeText.StatusProfile
                                                             and _StatusObjectActiveStatus.IsUserStatus = ' '
                                                             and _StatusCodeText.Language               = $session.system_language

  association [0..1] to I_UserStatus      as _UserStatus     on  $projection.StatusProfile = _UserStatus.StatusProfile
                                                             and $projection.UserStatus    = _UserStatus.UserStatus
  // Retrieve user status short name and description

  association [0..1] to I_UserStatusText  as _UserStatusText on  $projection.StatusProfile              = _UserStatusText.StatusProfile
                                                             and _StatusObjectActiveStatus.StatusCode   = _UserStatusText.UserStatus
                                                             and _StatusObjectActiveStatus.IsUserStatus = 'X'
                                                             and _UserStatusText.Language               = $session.system_language

  association [0..1] to I_StatusProfile   as _StatusProfile  on  $projection.StatusProfile = _StatusProfile.StatusProfile

{

  key   SalesDocument.SalesDocument,

  key   '000000'                          as SalesDocumentItem,

        @ObjectModel.foreignKey.association: '_StatusCode'
  key   _StatusCodeText.StatusCode        as SystemStatus,

        @ObjectModel.foreignKey.association: '_UserStatus'
  key   _UserStatusText.UserStatus        as UserStatus,

        @ObjectModel.foreignKey.association: '_StatusProfile'
  key   _StatusObjectActiveStatus.StatusProfile,

        _StatusCodeText.StatusName        as SystemStatusName,
        _StatusCodeText.StatusShortName   as SystemStatusShortName,

        _UserStatusText.UserStatusName,
        _UserStatusText.UserStatusShortName,

        _StatusCode,
        _UserStatus,
        _StatusProfile

} where
     SalesDocument.SDDocumentCategory = 'A' // Inquiry

  or SalesDocument.SDDocumentCategory = 'B' // Quotation

  or SalesDocument.SDDocumentCategory = 'C' // Order

  or SalesDocument.SDDocumentCategory = 'D' // Item Proposal

  or SalesDocument.SDDocumentCategory = 'G' // Contract

  or SalesDocument.SDDocumentCategory = 'H' // Returns

  or SalesDocument.SDDocumentCategory = 'I' // Order w/o charge

  or SalesDocument.SDDocumentCategory = 'K' // Credit memo request

  or SalesDocument.SDDocumentCategory = 'L' // Debit memo request



union

  select from I_SalesDocumentItem         as SalesDocumentItem

    inner join I_StatusObjectActiveStatus as _StatusObjectActiveStatus on SalesDocumentItem.ControllingObject = _StatusObjectActiveStatus.StatusObject

  association [0..1] to I_StatusCode      as _StatusCode     on  $projection.SystemStatus  = _StatusCode.StatusCode
                                                             and $projection.StatusProfile = _StatusCode.StatusProfile
  // Retrieve system status short name and description

  association [0..1] to I_StatusCodeText  as _StatusCodeText on  _StatusObjectActiveStatus.StatusCode   = _StatusCodeText.StatusCode
                                                             and $projection.StatusProfile              = _StatusCodeText.StatusProfile
                                                             and _StatusObjectActiveStatus.IsUserStatus = ' '
                                                             and _StatusCodeText.Language               = $session.system_language

  association [0..1] to I_UserStatus      as _UserStatus     on  $projection.StatusProfile = _UserStatus.StatusProfile
                                                             and $projection.UserStatus    = _UserStatus.UserStatus
  // Retrieve user status short name and description

  association [0..1] to I_UserStatusText  as _UserStatusText on  $projection.StatusProfile              = _UserStatusText.StatusProfile
                                                             and _StatusObjectActiveStatus.StatusCode   = _UserStatusText.UserStatus
                                                             and _StatusObjectActiveStatus.IsUserStatus = 'X'
                                                             and _UserStatusText.Language               = $session.system_language

  association [0..1] to I_StatusProfile   as _StatusProfile  on  $projection.StatusProfile = _StatusProfile.StatusProfile

{

  key   SalesDocumentItem.SalesDocument,

  key   SalesDocumentItem.SalesDocumentItem,

        @ObjectModel.foreignKey.association: '_StatusCode'
  key   _StatusCodeText.StatusCode        as SystemStatus,

        @ObjectModel.foreignKey.association: '_UserStatus'
  key   _UserStatusText.UserStatus        as UserStatus,

        @ObjectModel.foreignKey.association: '_StatusProfile'
  key   _StatusObjectActiveStatus.StatusProfile,

        _StatusCodeText.StatusName        as SystemStatusName,
        _StatusCodeText.StatusShortName   as SystemStatusShortName,

        _UserStatusText.UserStatusName,
        _UserStatusText.UserStatusShortName,

        _StatusCode,
        _UserStatus,
        _StatusProfile

} where
     SalesDocumentItem.SDDocumentCategory = 'A' // Inquiry

  or SalesDocumentItem.SDDocumentCategory = 'B' // Quotation

  or SalesDocumentItem.SDDocumentCategory = 'C' // Order

  or SalesDocumentItem.SDDocumentCategory = 'D' // Item Proposal

  or SalesDocumentItem.SDDocumentCategory = 'G' // Contract

  or SalesDocumentItem.SDDocumentCategory = 'H' // Returns

  or SalesDocumentItem.SDDocumentCategory = 'I' // Order w/o charge

  or SalesDocumentItem.SDDocumentCategory = 'K' // Credit memo request

  or SalesDocumentItem.SDDocumentCategory = 'L' // Debit memo request