@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 = ' '