P_ServiceOrderIssueKey

@AbapCatalog:
    {
        sqlViewName: 'PSRVCORDISSKEYS',
        compiler.compareFilter: true,
        preserveKey: true,
        dataMaintenance: #DISPLAY_ONLY
    }
@AccessControl:
    {
        authorizationCheck: #NOT_REQUIRED
       // personalData.blocking: #('TRANSACTIONAL_DATA')

    }
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM:
    {
        private: true,
        viewType: #COMPOSITE
    }

define view P_ServiceOrderIssueKey
  as

  select from I_ServiceDocumentItemEnhcd
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key cast('B001' as crms4_service_issue) as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)  as SrvcConfIssue,

      _ServiceDocument.ServiceDocumentType,
      _ServiceDocument.SoldToPartyCountry as Country,
      _ServiceDocument.Region,
      _ServiceDocument.ServiceRisk,
      _ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      _ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLADuebyDate

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,
      
      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,
      
      _ServiceDocument._TransactionCurrency
}
where
      ServiceObjectType                           =  'BUS2000116'
  and BillingBlockReason                          <> ' '
  and _ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

//  and RefInHouseRepairIsExisting                  is initial

  and _ServiceDocument.ServiceDocumentTemplateType = ' '

union all

//---------------------------------------------------------------------------//

// Relevant items that are not billed at all or only partially billed

//---------------------------------------------------------------------------//

select from I_ServiceDocumentItemEnhcd
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key cast('B002' as crms4_service_issue) as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)  as SrvcConfIssue,

      _ServiceDocument.ServiceDocumentType,
      _ServiceDocument.SoldToPartyCountry as Country,
      _ServiceDocument.Region,
      _ServiceDocument.ServiceRisk,
      _ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      _ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLADuebyDate

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,

      _ServiceDocument._TransactionCurrency
}
where
       ServiceObjectType                           =  'BUS2000116'
  and(
       ServiceDocItemBillingStatus                 =  'A' // Not billed

    or ServiceDocItemBillingStatus                 =  'B'
  )                                                       // ... or only partially billed

  and  _ServiceDocument.ServiceDocumentStatus      =  'C' // Completed

  and  _ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

//  and  RefInHouseRepairIsExisting                  is initial

  and _ServiceDocument.ServiceDocumentTemplateType = ' '
union all

//-----------------------------------------------------------------------------------

// All items with the exchange error flag set

//-----------------------------------------------------------------------------------

select from I_ServiceDocumentItemEnhcd as OrderItem
{
  key OrderItem.ServiceObjectType,
  key OrderItem.ServiceDocument,
  key OrderItem.ServiceDocumentItem,
  key cast('B003' as crms4_service_issue)           as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)            as SrvcConfIssue,

      OrderItem._ServiceDocument.ServiceDocumentType,
      OrderItem._ServiceDocument.SoldToPartyCountry as Country,
      OrderItem._ServiceDocument.Region,
      OrderItem._ServiceDocument.ServiceRisk,
      OrderItem._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderItem._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderItem.ServiceOrganization,
      OrderItem.SalesOrganization,
      OrderItem.SalesOffice,
      OrderItem.SalesGroup,
      OrderItem.DistributionChannel,
      OrderItem.Division,

      // Sales related fields (ex. BP)

      OrderItem.SoldToPartyCountry,
      OrderItem.SoldToPartyRegion,
      OrderItem.SoldToParty,
      OrderItem.ResponsibleEmployee,
      OrderItem.ContactPerson,
      OrderItem.ServiceTeam,
      OrderItem.ServiceEmployee,

      // Status fields

      OrderItem.ServiceDocItemRejectionReason,
      OrderItem.ServiceDocumentItemStatus,
      OrderItem.ServiceDocumentItemHasError,
      OrderItem.BillingBlockReason,
      OrderItem.ServiceDocItemBillingStatus,
      OrderItem.SrvcDocItmIsReleasedForBilling,
      OrderItem.SrvcDocItemTransferStatus,

      // Various attributes

      OrderItem.RequestedServiceStartDateTime,
      OrderItem.RequestedServiceEndDateTime,
      OrderItem.PlannedServiceStartDateTime,
      OrderItem.PlannedServiceEndDateTime,

      //SLADuebyDate

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderItem.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderItem._ServiceDocument._TransactionCurrency
}
where
      OrderItem.ServiceObjectType                           =  'BUS2000116'
  and (   OrderItem.SrvcDocItemTransferStatus               =  'B' // Items with transfer error set (B = Started, but not completed; E = Ended with error)

       or OrderItem.SrvcDocItemTransferStatus               =  'E' )
  and OrderItem.ServiceDocumentItemIsRejected               <> 'X'
  and OrderItem._ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

//  and RefInHouseRepairIsExisting                            is initial

  and OrderItem._ServiceDocument.ServiceDocumentTemplateType = ' '
union all

//-----------------------------------------------------------------------------------

// Headers with the erroneous-marker set where there is no item with that flag set

//

// Headers are treated as items with item number 0.

//-----------------------------------------------------------------------------------

select from       I_ServiceDocumentEnhcd     as OrderHeader

  left outer to many join 
                  I_ServiceDocumentItemEnhcd as OrderItem on  OrderItem.ServiceObjectType             =  OrderHeader.ServiceObjectType
                                                          and OrderItem.ServiceDocument               =  OrderHeader.ServiceDocument
                                                          and OrderItem.ServiceDocumentItemIsRejected <> 'X'
                                                          and (   OrderItem.SrvcDocItemTransferStatus = 'B' 
                                                               or OrderItem.SrvcDocItemTransferStatus = 'E' )
{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem,
  key cast('B003' as crms4_service_issue)        as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)         as SrvcConfIssue,

      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount        as SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,

      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      //SLA Issue

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116'
  and (   OrderHeader.SrvcDocTransferStatus  =  'B'
       or OrderHeader.SrvcDocTransferStatus  =  'E' )
  and OrderHeader.ServiceDocumentIsRejected  <> 'X'
  and OrderHeader.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

  and OrderItem.ServiceObjectType            is null
//  and OrderHeader.RefInHouseRepairIsExisting is initial

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Confirmation to order item has been cancelled

//---------------------------------------------------------------------------//

select from  I_ServiceDocumentItemEnhcd     as Item
  inner join I_ServiceDocumentSuccessor     as DocLink   on  DocLink.ServiceObjectType             = Item.ServiceObjectType
                                                         and DocLink.ServiceDocumentUUID           = Item.ServiceDocumentUUID
                                                         and DocLink.ServiceDocSuccessorBusObjType = 'BUS2000117'
                                                         and DocLink.ServiceDocumentRelationType   = 'VONA'
  inner join I_ServiceDocumentItemSuccssr_2 as ItemLink  on  ItemLink.ServiceDocumentItemCharUUID    = Item.ServiceDocumentItemCharUUID
                                                         and ItemLink.ServiceDocumentItmRelationType = 'VONA'
  inner join I_ServiceDocumentItemEnhcd     as Successor on  Successor.ServiceDocumentItemObjectType = ItemLink.ServiceDocItmSuccssrBusObjType
                                                         and Successor.ServiceDocumentItemCharUUID   = ItemLink.ServiceDocItmSuccssrCharUUID
                                                         and Successor.ServiceDocumentItemIsRejected = 'X'
                                                         and Successor.ServiceDocumentUUID           = DocLink.ServiceDocumentSuccessorUUID
{
  key Item.ServiceObjectType,
  key Item.ServiceDocument,
  key Item.ServiceDocumentItem,
  key cast('C001' as crms4_service_issue)      as CustMgmtIssue,
  key cast('C006' as crms4_srvc_conf_issue)    as SrvcConfIssue,

      Item._ServiceDocument.ServiceDocumentType,
      Item._ServiceDocument.SoldToPartyCountry as Country,
      Item._ServiceDocument.Region,
      Item._ServiceDocument.ServiceRisk,
      Item._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      Item._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Item.Industry,

      // Organization Unit fields

      Item.ServiceOrganization,
      Item.SalesOrganization,
      Item.SalesOffice,
      Item.SalesGroup,
      Item.DistributionChannel,
      Item.Division,

      // Sales related fields (ex. BP)

      Item.SoldToPartyCountry,
      Item.SoldToPartyRegion,
      Item.SoldToParty,
      Item.ResponsibleEmployee,
      Item.ContactPerson,
      Item.ServiceTeam,
      Item.ServiceEmployee,

      // Status fields

      Successor.ServiceDocItemRejectionReason, // Not directly from the item in this case

      Item.ServiceDocumentItemStatus,
      Item.ServiceDocumentItemHasError,
      Item.BillingBlockReason,
      Item.ServiceDocItemBillingStatus,
      Item.SrvcDocItmIsReleasedForBilling,
      Item.SrvcDocItemTransferStatus,

      // Various attributes

      Item.RequestedServiceStartDateTime,
      Item.RequestedServiceEndDateTime,
      Item.PlannedServiceStartDateTime,
      Item.PlannedServiceEndDateTime,

      //SLADuebyDate

      Item.SrvcDocItemSLADueByDateTime,
      Item.ResponseProfile,
      Item.ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      Item.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      Item._ServiceDocument._TransactionCurrency
}
where
      Item.ServiceObjectType                           =  'BUS2000116'
  and Item._ServiceDocument.ServiceDocumentIsRejected  <> 'X'           // Service transaction is not rejected

  and Item._ServiceDocument.ServiceDocumentIsReleased  =  'X'           // Service Order is Released

  and Item._ServiceDocument.ServiceDocumentIsQuotation <> 'X'           // Service Order is not a quotation

//  and Item.RefInHouseRepairIsExisting                  is initial

  and Item._ServiceDocument.ServiceDocumentTemplateType = ' '
union all

select distinct from I_ServiceDocumentItemEnhcd     as Item

  inner join         I_ServiceDocumentItemSuccssr_2 as Link      on  Link.ServiceDocumentItemObjectType  = Item.ServiceDocumentItemObjectType
                                                                 and Link.ServiceDocumentItemCharUUID    = Item.ServiceDocumentItemCharUUID
                                                                 and Link.ServiceDocumentItmRelationType = 'VONA'           // Predecessor/Sucessor Relationship


  inner join         I_ServiceDocumentItemEnhcd     as Successor on  Successor.ServiceDocumentItemObjectType = Link.ServiceDocItmSuccssrBusObjType
                                                                 and Successor.ServiceDocumentItemCharUUID   = Link.ServiceDocItmSuccssrCharUUID
                                                                 and Successor.ServiceObjectType             = 'BUS2000117' // Successor is a service confirmation

{
  key Item.ServiceObjectType,
  key Item.ServiceDocument,
  key Item.ServiceDocumentItem,
  key cast ( 'C001' as crms4_service_issue )   as CustMgmtIssue,
  key cast ( 'C007' as crms4_srvc_conf_issue ) as SrvcConfIssue,

      Item._ServiceDocument.ServiceDocumentType,
      Item._ServiceDocument.SoldToPartyCountry as Country,
      Item._ServiceDocument.Region,
      Item._ServiceDocument.ServiceRisk,
      Item._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      Item._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,

      Item.Industry,

      // Organization Unit fields

      Item.ServiceOrganization,
      Item.SalesOrganization,
      Item.SalesOffice,
      Item.SalesGroup,
      Item.DistributionChannel,
      Item.Division,

      // Sales related fields (ex. BP)

      Item.SoldToPartyCountry,
      Item.SoldToPartyRegion,
      Item.SoldToParty,
      Item.ResponsibleEmployee,
      Item.ContactPerson,
      Item.ServiceTeam,
      Item.ServiceEmployee,

      // Status fields

      Item.ServiceDocItemRejectionReason,
      Item.ServiceDocumentItemStatus,
      Item.ServiceDocumentItemHasError,
      Successor.BillingBlockReason,
      Successor.ServiceDocItemBillingStatus,
      Successor.SrvcDocItmIsReleasedForBilling,
      Item.SrvcDocItemTransferStatus,

      // Various attributes

      Item.RequestedServiceStartDateTime,
      Item.RequestedServiceEndDateTime,
      Item.PlannedServiceStartDateTime,
      Item.PlannedServiceEndDateTime,

      //SLADuebyDate

      Item.SrvcDocItemSLADueByDateTime,
      Item.ResponseProfile,
      Item.ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      Item.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      Item._ServiceDocument._TransactionCurrency
}
where
       Item.ServiceObjectType                           =  'BUS2000116' // Service Order

  and  Item._ServiceDocument.ServiceDocumentIsRejected  <> 'X'          // Service transaction is not rejected

  and  Item._ServiceDocument.ServiceDocumentIsReleased  =  'X'          // Service Order is Released

  and  Item._ServiceDocument.ServiceDocumentIsQuotation <> 'X'          // Service Order is not a quotation

  and  Successor.ServiceDocumentItemStatus              =  'C'          // Confirmation is completed

//  and  Item.RefInHouseRepairIsExisting                  is initial

  and  Item._ServiceDocument.ServiceDocumentTemplateType = ' '
  and(
       Successor.ServiceDocItemBillingStatus            =  'A'          // But not billed at all

    or Successor.ServiceDocItemBillingStatus            =  'B'
  )                                                                     // ... or only partially billed


union all

//---------------------------------------------------------------------------//

// Order items have been rejected

//---------------------------------------------------------------------------//

select from I_ServiceDocumentItemEnhcd
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key cast('E003' as crms4_service_issue) as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)  as SrvcConfIssue,

      _ServiceDocument.ServiceDocumentType,
      _ServiceDocument.SoldToPartyCountry as Country,
      _ServiceDocument.Region,
      _ServiceDocument.ServiceRisk,
      _ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      _ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLADuebyDate

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,

      _ServiceDocument._TransactionCurrency
}
where
      ServiceObjectType                           =  'BUS2000116'
  and ServiceDocumentItemIsRejected               =  'X'
  and _ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

//  and RefInHouseRepairIsExisting                  is initial

  and _ServiceDocument.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Service order items which have confirmation items with error

//---------------------------------------------------------------------------//

select from P_SrvcTransactionIssueItemC002
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key CustMgmtIssue,
  key SrvcConfIssue,

      ServiceDocumentType,
      Country,
      Region,
      ServiceRisk,
      ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLADuebyDate

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,

      _TransactionCurrency
}
    
union all

//---------------------------------------------------------------------------//

// Service order items with open confirmation items

//---------------------------------------------------------------------------//

select from P_SrvcTransactionIssueItemC003
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key CustMgmtIssue,
  key SrvcConfIssue,

      ServiceDocumentType,
      Country,
      Region,
      ServiceRisk,
      ServiceDocumentProblemCategory,
      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLA Dueby

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,

      _TransactionCurrency
}
    
union all

//---------------------------------------------------------------------------//

// Service order items with no final confirmations

//---------------------------------------------------------------------------//

select from P_SrvcTransactionIssueItemC004
{
  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key CustMgmtIssue,
  key SrvcConfIssue,

      ServiceDocumentType,
      Country,
      Region,
      ServiceRisk,
      ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLADuebyDate

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,
      

      _TransactionCurrency
}
    
union all

//-----------------------------------------------------------------------------------

// All items with the error flag set

//-----------------------------------------------------------------------------------

select from I_ServiceDocumentItemEnhcd as OrderItem
{
  key OrderItem.ServiceObjectType,
  key OrderItem.ServiceDocument,
  key OrderItem.ServiceDocumentItem,
  key cast('E001' as crms4_service_issue)           as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)            as SrvcConfIssue,

      OrderItem._ServiceDocument.ServiceDocumentType,
      OrderItem._ServiceDocument.SoldToPartyCountry as Country,
      OrderItem._ServiceDocument.Region,
      OrderItem._ServiceDocument.ServiceRisk,
      OrderItem._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderItem._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderItem.ServiceOrganization,
      OrderItem.SalesOrganization,
      OrderItem.SalesOffice,
      OrderItem.SalesGroup,
      OrderItem.DistributionChannel,
      OrderItem.Division,

      // Sales related fields (ex. BP)

      OrderItem.SoldToPartyCountry,
      OrderItem.SoldToPartyRegion,
      OrderItem.SoldToParty,
      OrderItem.ResponsibleEmployee,
      OrderItem.ContactPerson,
      OrderItem.ServiceTeam,
      OrderItem.ServiceEmployee,

      // Status fields

      OrderItem.ServiceDocItemRejectionReason,
      OrderItem.ServiceDocumentItemStatus,
      OrderItem.ServiceDocumentItemHasError,
      OrderItem.BillingBlockReason,
      OrderItem.ServiceDocItemBillingStatus,
      OrderItem.SrvcDocItmIsReleasedForBilling,
      OrderItem.SrvcDocItemTransferStatus,

      // Various attributes

      OrderItem.RequestedServiceStartDateTime,
      OrderItem.RequestedServiceEndDateTime,
      OrderItem.PlannedServiceStartDateTime,
      OrderItem.PlannedServiceEndDateTime,

      //SLADuebyDate

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderItem.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderItem._ServiceDocument._TransactionCurrency
}
where
      OrderItem.ServiceObjectType                           =  'BUS2000116'
  and OrderItem.ServiceDocumentItemHasError                 =  'X' // Items with error flag

  and OrderItem.ServiceDocumentItemIsRejected               <> 'X' // .. that are not rejected (=cancelled)

  and OrderItem.ServiceDocumentItemStatus                   <> 'C' // .. and not Completed

  and OrderItem._ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

//  and RefInHouseRepairIsExisting                            is initial

  and OrderItem._ServiceDocument.ServiceDocumentTemplateType = ' '
union all

//-----------------------------------------------------------------------------------

// Headers with the erroneous-marker set where there is no item with that flag set

//

// Headers are treated as items with item number 0.

//-----------------------------------------------------------------------------------

select from       I_ServiceDocumentEnhcd     as OrderHeader

  left outer to many join 
                  I_ServiceDocumentItemEnhcd as OrderItem on  OrderItem.ServiceObjectType             =  OrderHeader.ServiceObjectType
                                                          and OrderItem.ServiceDocument               =  OrderHeader.ServiceDocument
                                                          and OrderItem.ServiceDocumentItemHasError   =  'X'
                                                          and OrderItem.ServiceDocumentItemIsRejected <> 'X'
                                                          and OrderItem.ServiceDocumentItemStatus     <> 'C'
{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem,
  key cast('E001' as crms4_service_issue)        as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)         as SrvcConfIssue,

      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount        as SrvcOrdGrossAmount,

      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,

      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      //SLA Issue

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116'
  and OrderHeader.ServiceDocumentHasError    =  'X' // Headers with error flag and no items with an error flag

  and OrderHeader.ServiceDocumentIsRejected  <> 'X' // .. that are not rejected (=cancelled)

  and OrderHeader.ServiceDocumentStatus      <> 'C' // .. and not completed

  and OrderHeader.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

  and OrderItem.ServiceObjectType            is null
//  and OrderHeader.RefInHouseRepairIsExisting is initial

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//-----------------------------------------------------------------------------------

// All items with no associated confirmation

//-----------------------------------------------------------------------------------

select distinct from I_ServiceDocumentItemEnhcd     as OrderItem

// The INNER JOIN serves to eliminate links to anything else than items of a confirmation

  left outer to many join(
                     I_ServiceDocumentItemSuccssr_2 as ItemLink
      inner join     I_ServiceDocumentItemEnhcd     as ConfItem on  ConfItem.ServiceDocumentItemObjectType = ItemLink.ServiceDocItmSuccssrBusObjType
                                                                and ConfItem.ServiceDocumentItemCharUUID   = ItemLink.ServiceDocItmSuccssrCharUUID
                                                                and ConfItem.ServiceObjectType             = 'BUS2000117'
  )
                                                                on  ItemLink.ServiceDocumentItemObjectType  = OrderItem.ServiceDocumentItemObjectType
                                                                and ItemLink.ServiceDocumentItemCharUUID    = OrderItem.ServiceDocumentItemCharUUID
                                                                and ItemLink.ServiceDocumentItmRelationType = 'VONA'
{
  key OrderItem.ServiceObjectType,
  key OrderItem.ServiceDocument,
  key OrderItem.ServiceDocumentItem,
  key cast('E004' as crms4_service_issue) as CustMgmtIssue,
  key cast(' ' as crms4_srvc_conf_issue)  as SrvcConfIssue,

      OrderItem.ServiceDocumentType,
      OrderItem.SoldToPartyCountry        as Country,
      OrderItem._ServiceDocument.Region,
      OrderItem._ServiceDocument.ServiceRisk,
      OrderItem._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderItem._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount as SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderItem.ServiceOrganization,
      OrderItem.SalesOrganization,
      OrderItem.SalesOffice,
      OrderItem.SalesGroup,
      OrderItem.DistributionChannel,
      OrderItem.Division,

      // Sales related fields (ex. BP)

      OrderItem.SoldToPartyCountry,
      OrderItem.SoldToPartyRegion,
      OrderItem.SoldToParty,
      OrderItem.ResponsibleEmployee,
      OrderItem.ContactPerson,
      OrderItem.ServiceTeam,
      OrderItem.ServiceEmployee,

      // Status fields

      OrderItem.ServiceDocItemRejectionReason,
      OrderItem.ServiceDocumentItemStatus,
      OrderItem.ServiceDocumentItemHasError,
      OrderItem.BillingBlockReason,
      OrderItem.ServiceDocItemBillingStatus,
      OrderItem.SrvcDocItmIsReleasedForBilling,
      OrderItem.SrvcDocItemTransferStatus,

      // Various attributes

      OrderItem.RequestedServiceStartDateTime,
      OrderItem.RequestedServiceEndDateTime,
      OrderItem.PlannedServiceStartDateTime,
      OrderItem.PlannedServiceEndDateTime,

      //SLA Issue

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderItem.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderItem._ServiceDocument._TransactionCurrency
}
where
      OrderItem.ServiceObjectType                           =  'BUS2000116' // Service Order

  and OrderItem.SrvcOrderConfirmationRelevance              <> ' '          // .. relevant for confirmations

  and OrderItem._ServiceDocument.ServiceDocumentIsRejected  <> 'X'          // .. not rejected

  and OrderItem._ServiceDocument.ServiceDocumentIsReleased  =  'X'          // .. released

  and OrderItem._ServiceDocument.ServiceDocumentIsQuotation <> 'X' // Service Order is not a quotation

  and ItemLink.ServiceDocumentItemObjectType                is null // Items there but no successor

//  and OrderItem.RefInHouseRepairIsExisting                  is initial

  and OrderItem._ServiceDocument.ServiceDocumentTemplateType = ' '
union all

select from       I_ServiceDocumentEnhcd         as OrderHeader

  left outer to many join 
                  I_ServiceDocumentSuccessor     as Link      on  Link.ServiceObjectType             = OrderHeader.ServiceObjectType
                                                              and Link.ServiceDocumentUUID           = OrderHeader.ServiceDocumentUUID
                                                              and Link.ServiceDocSuccessorBusObjType = 'BUS2000117' // Service confirmation

                                                              and Link.ServiceDocumentRelationType   = 'VONA'
  inner join      I_ServiceDocumentEnhcd         as Successor on  Successor.ServiceObjectType         = Link.ServiceDocSuccessorBusObjType
                                                              and Successor.ServiceDocumentUUID       = Link.ServiceDocumentSuccessorUUID
                                                              and Successor.ServiceDocumentIsRejected = 'X'

  left outer to many join 
                  P_SrvcTransactionIssueItemC002 as Item      on  Item.ServiceObjectType = OrderHeader.ServiceObjectType
                                                              and Item.ServiceDocument   = OrderHeader.ServiceDocument // Item defined to render SLADuebyDate



{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem, // We want headers with issues to appear as items with number 0

  key cast('C001' as crms4_service_issue)        as CustMgmtIssue,
  key cast('C006' as crms4_srvc_conf_issue)      as SrvcConfIssue,

      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      ''                                         as Industry,

      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,

      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      //SLA Dueby

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116' // Service order

  and OrderHeader.ServiceDocumentIsRejected  <> 'X'          // Service transaction is not rejected

  and OrderHeader.ServiceDocumentIsReleased  =  'X'          // Service Order is Released

  and OrderHeader.ServiceDocumentIsQuotation <> 'X'          // Service Order is not a quotation

//  and OrderHeader.RefInHouseRepairIsExisting is initial

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Service Orders with erroneous service confirmations on header level

// for which the same issue does not occur on item level

//---------------------------------------------------------------------------//

select distinct from I_ServiceDocumentEnhcd         as OrderHeader

  inner join         I_ServiceDocumentSuccessor     as Link      on  Link.ServiceObjectType             = OrderHeader.ServiceObjectType
                                                                 and Link.ServiceDocumentUUID           = OrderHeader.ServiceDocumentUUID
                                                                 and Link.ServiceDocumentRelationType   = 'VONA'
                                                                 and Link.ServiceDocSuccessorBusObjType = 'BUS2000117'

  inner join         I_ServiceDocumentEnhcd         as Successor on  Successor.ServiceObjectType       = Link.ServiceDocSuccessorBusObjType
                                                                 and Successor.ServiceDocumentUUID     = Link.ServiceDocumentSuccessorUUID
                                                                 and Successor.ServiceDocumentHasError = 'X' // Confirmation has error


  left outer to many join    
                     P_SrvcTransactionIssueItemC002 as Item      on  Item.ServiceObjectType = OrderHeader.ServiceObjectType
                                                                 and Item.ServiceDocument   = OrderHeader.ServiceDocument
{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem, // We want headers with issues to appear as items with number 0

  key cast('C001' as crms4_service_issue)        as CustMgmtIssue,
  key cast('C002' as crms4_srvc_conf_issue)      as SrvcConfIssue,

      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      ''                                         as Industry,
      //        Item.Industry                                                as Industry,


      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,

      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116' // Service Order

  and OrderHeader.ServiceDocumentIsRejected  <> 'X'          // Service order not rejected

  and OrderHeader.ServiceDocumentIsReleased  =  'X'          // Service order is released

  and OrderHeader.ServiceDocumentStatus      <> 'C'          // Not completed

  and OrderHeader.ServiceDocumentIsQuotation <> 'X'          // Service Order is not a quotation

  and Successor.ServiceDocumentIsRejected    <> 'X'          // Confirmation not rejected

  and Successor.ServiceDocumentStatus        <> 'C'          // Confirmation not completed

  and Item.ServiceDocumentItem               is null         // The field being NULL means it is not there, so this implements a NOT EXIST clause without a sub query.

//  and OrderHeader.RefInHouseRepairIsExisting is initial

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Service Orders with open service confirmations on header level

// for which the same issue does not occur on item level

//---------------------------------------------------------------------------//

select distinct from I_ServiceDocumentEnhcd         as OrderHeader

  inner join         I_ServiceDocumentSuccessor     as Link      on  Link.ServiceObjectType             = OrderHeader.ServiceObjectType
                                                                 and Link.ServiceDocumentUUID           = OrderHeader.ServiceDocumentUUID
                                                                 and Link.ServiceDocumentRelationType   = 'VONA'
                                                                 and Link.ServiceDocSuccessorBusObjType = 'BUS2000117'

  inner join         I_ServiceDocumentEnhcd         as Successor on  Successor.ServiceObjectType     = Link.ServiceDocSuccessorBusObjType
                                                                 and Successor.ServiceDocumentUUID   = Link.ServiceDocumentSuccessorUUID
                                                                 and Successor.ServiceDocumentIsOpen = 'X'

  left outer to many join    
                     P_SrvcTransactionIssueItemC003 as Item      on  Item.ServiceObjectType = OrderHeader.ServiceObjectType
                                                                 and Item.ServiceDocument   = OrderHeader.ServiceDocument
{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem, // We want headers with issues to appear as items with number 0

  key cast('C001' as crms4_service_issue)        as CustMgmtIssue,
  key cast('C003' as crms4_srvc_conf_issue)      as SrvcConfIssue,

      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      ''                                         as Industry,
      //        Item.Industry                                                as Industry,


      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,

      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116' // Service Order

  and OrderHeader.ServiceDocumentIsRejected  <> 'X'          // Service order not rejected

  and OrderHeader.ServiceDocumentIsReleased  =  'X'          // Service order is released

  and OrderHeader.ServiceDocumentIsQuotation <> 'X'          // Service Order is not a quotation

  and Item.ServiceDocumentItem               is null        // The field being NULL means it is not there, so this implements a NOT EXIST clause without a sub query.

//  and OrderHeader.RefInHouseRepairIsExisting is initial 

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Service Orders with no service confirmations on header level

// for which the same issue does not occur on item level

//---------------------------------------------------------------------------//

select distinct from I_ServiceDocumentEnhcd         as OrderHeader

  inner join         I_ServiceDocumentSuccessor     as Link      on  Link.ServiceObjectType             = OrderHeader.ServiceObjectType
                                                                 and Link.ServiceDocumentUUID           = OrderHeader.ServiceDocumentUUID
                                                                 and Link.ServiceDocumentRelationType   = 'VONA'
                                                                 and Link.ServiceDocSuccessorBusObjType = 'BUS2000117'

  inner join         I_ServiceDocumentEnhcd         as Successor on  Successor.ServiceObjectType            = Link.ServiceDocSuccessorBusObjType
                                                                 and Successor.ServiceDocumentUUID          = Link.ServiceDocumentSuccessorUUID
                                                                 and (
                                                                    Successor.ServiceConfirmationIsFinal    = ' '
                                                                    or Successor.ServiceConfirmationIsFinal = 'N'
                                                                  )
  left outer to many join    
                     P_SrvcTransactionIssueItemC004 as Item      on  Item.ServiceObjectType = OrderHeader.ServiceObjectType
                                                                 and Item.ServiceDocument   = OrderHeader.ServiceDocument
{
  key OrderHeader.ServiceObjectType,
  key OrderHeader.ServiceDocument,
  key cast('000000' as crms4_number_int)         as ServiceDocumentItem, // We want headers with issues to appear as items with number 0

  key cast('C001' as crms4_service_issue)        as CustMgmtIssue,
  key cast('C004' as crms4_srvc_conf_issue)      as SrvcConfIssue,
      OrderHeader.ServiceDocumentType,
      OrderHeader.SoldToPartyCountry             as Country,
      OrderHeader.Region,
      OrderHeader.ServiceRisk,
      OrderHeader.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderHeader.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      Item.SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      ''                                         as Industry,
      //        Item.Industry                                                as Industry,


      // Organization Unit fields

      OrderHeader.ServiceOrganization,
      OrderHeader.SalesOrganization,
      OrderHeader.SalesOffice,
      OrderHeader.SalesGroup,
      OrderHeader.DistributionChannel,
      OrderHeader.Division,

      // Sales related fields (ex. BP)

      OrderHeader.SoldToPartyCountry,
      OrderHeader.SoldToPartyRegion,
      OrderHeader.SoldToParty,
      OrderHeader.ResponsibleEmployee,
      OrderHeader.ContactPerson,
      OrderHeader.ServiceTeam,
      OrderHeader.ServiceEmployee,

      // Status fields

      OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
      OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
      OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
      OrderHeader.BillingBlockReason,
      OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
      OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
      OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,
      
      // Various attributes

      OrderHeader.RequestedServiceStartDateTime,
      OrderHeader.RequestedServiceEndDateTime,
      cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
      cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      Item.ServiceFirstResponseByDateTime,

      OrderHeader.RefBusinessSolutionOrder,
      Item.RefBusinessSolutionOrderItem,

      OrderHeader._TransactionCurrency
}
where
      OrderHeader.ServiceObjectType          =  'BUS2000116' // Service Order

  and OrderHeader.ServiceDocumentIsRejected  <> 'X'          // Service order not rejected

  and OrderHeader.ServiceDocumentIsReleased  =  'X'          // Service order is released

  and OrderHeader.ServiceDocumentIsQuotation <> 'X'          // Service Order is not a quotation

  and Item.ServiceDocumentItem               is null // The field being NULL means it is not there, so this implements a NOT EXIST clause without a sub query.

//  and OrderHeader.RefInHouseRepairIsExisting is initial

  and OrderHeader.ServiceDocumentTemplateType = ' '
union all

//---------------------------------------------------------------------------//

// Service order items with SLA Issues

//---------------------------------------------------------------------------//


select from P_SRVCTRANSACTIONISSUETIMESTMP
{

  key ServiceObjectType,
  key ServiceDocument,
  key ServiceDocumentItem,
  key CustMgmtIssue,
  key SrvcConfIssue,

      ServiceDocumentType,
      Country,
      Region,
      ServiceRisk,
      ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      Industry,

      // Organization Unit fields

      ServiceOrganization,
      SalesOrganization,
      SalesOffice,
      SalesGroup,
      DistributionChannel,
      Division,

      // Sales related fields (ex. BP)

      SoldToPartyCountry,
      SoldToPartyRegion,
      SoldToParty,
      ResponsibleEmployee,
      ContactPerson,
      ServiceTeam,
      ServiceEmployee,

      // Status fields

      ServiceDocItemRejectionReason,
      ServiceDocumentItemStatus,
      ServiceDocumentItemHasError,
      BillingBlockReason,
      ServiceDocItemBillingStatus,
      SrvcDocItmIsReleasedForBilling,
      SrvcDocItemTransferStatus,

      // Various attributes

      RequestedServiceStartDateTime,
      RequestedServiceEndDateTime,
      PlannedServiceStartDateTime,
      PlannedServiceEndDateTime,

      //SLA Dates

      SrvcDocItemSLADueByDateTime,
      ResponseProfile,
      ServiceProfile,
      ServiceFirstResponseByDateTime,

      RefBusinessSolutionOrder,
      RefBusinessSolutionOrderItem,
      

      _TransactionCurrency
}

where
     SrvcDocItemSLADueByDateTime < CurrentDate //SLA issue selection

//and  RefInHouseRepairIsExisting  is initial 


union all

//-----------------------------------------------------------------------------------

// Items with a problematic Transfer status

//-----------------------------------------------------------------------------------

select distinct from I_ServiceDocumentItemEnhcd     as OrderItem

  inner join         I_ServiceDocumentItemSuccssr_2 as Link             on Link.ServiceDocumentItemObjectType  = OrderItem.ServiceDocumentItemObjectType
                                                                       and Link.ServiceDocumentItemCharUUID    = OrderItem.ServiceDocumentItemCharUUID
                                                                       and Link.ServiceDocumentItmRelationType = 'VONA'           // Predecessor/Sucessor Relationship


  inner join         I_ServiceDocumentItemEnhcd     as ConfirmationItem on ConfirmationItem.ServiceDocumentItemObjectType = Link.ServiceDocItmSuccssrBusObjType
                                                                       and ConfirmationItem.ServiceDocumentItemCharUUID   = Link.ServiceDocItmSuccssrCharUUID
                                                                       and ConfirmationItem.ServiceObjectType             = 'BUS2000117' // Successor is a service confirmation

                                                                       and (   ConfirmationItem.SrvcDocItemTransferStatus =  'B' // Items with transfer error set (B = Started, but not completed; E = Ended with error)

                                                                            or ConfirmationItem.SrvcDocItemTransferStatus =  'E' )
                                                                       and ConfirmationItem.ServiceDocumentItemIsRejected <> 'X'
{
  key OrderItem.ServiceObjectType,
  key OrderItem.ServiceDocument, 
  key OrderItem.ServiceDocumentItem,
  key cast('C001' as crms4_service_issue)           as CustMgmtIssue,
  key cast('C008' as crms4_srvc_conf_issue)         as SrvcConfIssue,
  
      OrderItem._ServiceDocument.ServiceDocumentType,
      OrderItem._ServiceDocument.SoldToPartyCountry as Country,
      OrderItem._ServiceDocument.Region,
      OrderItem._ServiceDocument.ServiceRisk,
      OrderItem._ServiceDocument.ServiceDocumentProblemCategory,

      @ObjectModel.foreignKey.association: '_TransactionCurrency'
      @Semantics.currencyCode:true
      OrderItem._ServiceDocument.TransactionCurrency,

      @DefaultAggregation: #SUM
      @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
      OrderItem.ServiceDocItemGrossAmount           as SrvcOrdGrossAmount,
      // Fields that come only from the item - they will be NULL if there is no item!

      OrderItem.Industry,

      // Organization Unit fields

      OrderItem.ServiceOrganization,
      OrderItem.SalesOrganization,
      OrderItem.SalesOffice,
      OrderItem.SalesGroup,
      OrderItem.DistributionChannel,
      OrderItem.Division,

      // Sales related fields (ex. BP)

      OrderItem.SoldToPartyCountry,
      OrderItem.SoldToPartyRegion,
      OrderItem.SoldToParty,
      OrderItem.ResponsibleEmployee,
      OrderItem.ContactPerson,
      OrderItem.ServiceTeam,
      OrderItem.ServiceEmployee,

      // Status fields

      OrderItem.ServiceDocItemRejectionReason,
      OrderItem.ServiceDocumentItemStatus,
      OrderItem.ServiceDocumentItemHasError,
      OrderItem.BillingBlockReason,
      OrderItem.ServiceDocItemBillingStatus,
      OrderItem.SrvcDocItmIsReleasedForBilling,
      OrderItem.SrvcDocItemTransferStatus,

      // Various attributes

      OrderItem.RequestedServiceStartDateTime,
      OrderItem.RequestedServiceEndDateTime,
      OrderItem.PlannedServiceStartDateTime,
      OrderItem.PlannedServiceEndDateTime,

      //SLADuebyDate

      OrderItem.SrvcDocItemSLADueByDateTime,
      OrderItem.ResponseProfile,
      OrderItem.ServiceProfile,
      OrderItem.ServiceFirstResponseByDateTime,

      OrderItem.RefBusinessSolutionOrder,
      OrderItem.RefBusinessSolutionOrderItem,

      OrderItem._ServiceDocument._TransactionCurrency
}
where OrderItem.ServiceObjectType = 'BUS2000116' 
  and OrderItem.SrvcOrderConfirmationRelevance              <> ' '          // .. relevant for confirmations

  and OrderItem._ServiceDocument.ServiceDocumentIsRejected  <> 'X'          // .. not rejected

  and OrderItem._ServiceDocument.ServiceDocumentIsReleased  =  'X'          // .. released

  and OrderItem._ServiceDocument.ServiceDocumentIsQuotation <> 'X'
//  and OrderItem.RefInHouseRepairIsExisting is initial   // Service Order is not a quotation

  and OrderItem._ServiceDocument.ServiceDocumentTemplateType = ' '
union all

//-----------------------------------------------------------------------------------

// Headers with a problematic Transfer status and without items with that problem

//

// Headers are treated as items with item number 0.

//-----------------------------------------------------------------------------------

select distinct from      I_ServiceDocumentEnhcd     as OrderHeader

  inner join              I_ServiceDocumentSuccessor as link on link.ServiceDocumentUUID           = OrderHeader.ServiceDocumentUUID
                                                            and link.ServiceObjectType             = OrderHeader.ServiceObjectType
                                                            and link.ServiceDocSuccessorBusObjType = 'BUS2000117'
                                             
  inner join              I_ServiceDocumentEnhcd     as ConfirmationHeader on link.ServiceDocumentSuccessorUUID  = ConfirmationHeader.ServiceDocumentUUID
                                                                          and link.ServiceDocSuccessorBusObjType = ConfirmationHeader.ServiceObjectType
      
  left outer to many join I_ServiceDocumentItemEnhcd as ConfirmationItem   on ConfirmationItem.ServiceObjectType             = ConfirmationHeader.ServiceObjectType
                                                                          and ConfirmationItem.ServiceDocument               = ConfirmationHeader.ServiceDocument
                                                                          and ConfirmationItem.ServiceDocumentItemIsRejected <> 'X'
                                                                          and (   ConfirmationItem.SrvcDocItemTransferStatus = 'B' 
                                                                               or ConfirmationItem.SrvcDocItemTransferStatus = 'E' )
{
    key ConfirmationHeader._ServiceDocPredecessor.ServiceDocPrdcssrBusObjType as ServiceObjectType,
    key ConfirmationHeader._ServiceDocPredecessor._ServiceDoc.ServiceDocument,
    key cast('000000' as crms4_number_int)         as ServiceDocumentItem,
    key cast('C001' as crms4_service_issue)        as CustMgmtIssue,
    key cast('C008' as crms4_srvc_conf_issue)         as SrvcConfIssue,

        OrderHeader.ServiceDocumentType,
        OrderHeader.SoldToPartyCountry             as Country,
        OrderHeader.Region,
        OrderHeader.ServiceRisk,
        OrderHeader.ServiceDocumentProblemCategory,
  
        @ObjectModel.foreignKey.association: '_TransactionCurrency'
        @Semantics.currencyCode:true
        OrderHeader.TransactionCurrency,
  
  // The confirmation does not have an item (otherwise it would be reported on item level)

  // so we cannot navigate to any item.

  // Hence we must replace the values that normally come from the item with blank / zero.

        @DefaultAggregation: #SUM
        @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
        0     as SrvcOrdGrossAmount,
  
        // Fields that come only from the item - they will be NULL if there is no item!

        ''    as Industry,
  
        // Organization Unit fields

        OrderHeader.ServiceOrganization,
        OrderHeader.SalesOrganization,
        OrderHeader.SalesOffice,
        OrderHeader.SalesGroup,
        OrderHeader.DistributionChannel,
        OrderHeader.Division,
  
        // Sales related fields (ex. BP)

        OrderHeader.SoldToPartyCountry,
        OrderHeader.SoldToPartyRegion,
        OrderHeader.SoldToParty,
        OrderHeader.ResponsibleEmployee,
        OrderHeader.ContactPerson,
        OrderHeader.ServiceTeam,
        OrderHeader.ServiceEmployee,
  
        // Status fields

        OrderHeader.ServiceDocumentRejectionReason as ServiceDocItemRejectionReason,
        OrderHeader.ServiceDocumentStatus          as ServiceDocumentItemStatus,
        OrderHeader.ServiceDocumentHasError        as ServiceDocumentItemHasError,
        OrderHeader.BillingBlockReason,
        OrderHeader.ServiceDocBillingStatus        as ServiceDocItemBillingStatus,
        OrderHeader.ServiceDocIsReleasedForBilling as SrvcDocItmIsReleasedForBilling,
        OrderHeader.SrvcDocTransferStatus          as SrvcDocItemTransferStatus,
  
        // Various attributes

        OrderHeader.RequestedServiceStartDateTime,
        OrderHeader.RequestedServiceEndDateTime,
        cast( 0 as crms4_spla_planfr_tstmp )       as PlannedServiceStartDateTime,
        cast( 0 as crms4_spla_planto_tstmp )       as PlannedServiceEndDateTime,
  
        //SLA Issue

        ConfirmationItem.SrvcDocItemSLADueByDateTime,
        ConfirmationItem.ResponseProfile,
        ConfirmationItem.ServiceProfile,
        ConfirmationItem.ServiceFirstResponseByDateTime,
 
        OrderHeader.RefBusinessSolutionOrder,
        ConfirmationItem.RefBusinessSolutionOrderItem,  // null. Same as the linked predecessor item would be if it existed

  
        OrderHeader._TransactionCurrency
}
where OrderHeader.ServiceObjectType                =  'BUS2000116'
  and (   ConfirmationHeader.SrvcDocTransferStatus =  'B'
       or ConfirmationHeader.SrvcDocTransferStatus =  'E' )
  and ConfirmationHeader.ServiceDocumentIsRejected <> 'X'
//  and OrderHeader.SrvcOrderConfirmationRelevance <> ' '   // cannot be checked on header level

  and OrderHeader.ServiceDocumentIsReleased        =  'X'   // .. released

  and OrderHeader.ServiceDocumentIsQuotation       <> 'X'   // Service Order is not a quotation

  and ConfirmationItem.ServiceObjectType           is null
//  and OrderHeader.RefInHouseRepairIsExisting is initial   

  and OrderHeader.ServiceDocumentTemplateType = ' '