@AbapCatalog:
{
sqlViewName: 'PSRVCONTRISSKEYS',
compiler.compareFilter: true,
preserveKey: true
}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM:
{
private: true,
viewType: #COMPOSITE
}
define view P_ServiceContractIssueKey
as
//---------------------------------------------------------------------------//
// B001 - Billing block based on billing request items
//---------------------------------------------------------------------------//
select from I_BillingRequestItem as BillgReqItm
{
// Key Fields
key BillgReqItm.ServiceObjectType,
key BillgReqItm.ServiceDocument as ServiceContract,
key BillgReqItm.ServiceDocumentItem as ServiceContractItem,
key cast('B001' as crms4_service_issue preserving type ) as CustMgmtIssue,
key BillgReqItm.BillingRequestItem,
// Service Contract Attributes
@Semantics.currencyCode:true
BillgReqItm.TransactionCurrency,
BillgReqItm._ServiceDocItem._ServiceDocument.ServiceDocumentType as ServiceContractType,
BillgReqItm._ServiceDocItem.ServiceContrItemStartDateTime,
BillgReqItm._ServiceDocItem.ServiceContrItemEndDateTime,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
BillgReqItm._ServiceDocItem.ServiceDocumentItemNetAmount as ServiceContractItemNetAmount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
BillgReqItm._ServiceDocItem.ServiceDocItemGrossAmount as ServiceContractItemGrossAmount,
BillgReqItm._ServiceDocItem.SrvcContrItemRnwlDuration,
BillgReqItm._ServiceDocItem.SrvcContrItemRnwlDurationUnit,
BillgReqItm._ServiceDocItem.SrvcContrItemExtensionDuration,
BillgReqItm._ServiceDocItem.SrvcContrItemExtnDurationUnit,
BillgReqItm._ServiceDocItem.ResponseProfile,
BillgReqItm._ServiceDocItem.ServiceProfile,
BillgReqItm._ServiceDocItem.ServiceDocItemCategory as ServiceContractItemCategory,
bintohex( BillgReqItm._ServiceDocItem.ServiceDocumentUUID ) as ServiceContractUUID,
// Organization Unit fields
BillgReqItm._ServiceDocItem.ServiceOrganization,
BillgReqItm._ServiceDocItem.SalesOrganization,
BillgReqItm._ServiceDocItem.SalesOffice,
BillgReqItm._ServiceDocItem.SalesGroup,
BillgReqItm._ServiceDocItem.DistributionChannel,
BillgReqItm._ServiceDocItem.Division,
// Partner Fields
BillgReqItm._ServiceDocItem.SoldToPartyCountry,
BillgReqItm._ServiceDocItem.SoldToPartyRegion,
BillgReqItm._ServiceDocItem.SoldToParty,
BillgReqItm._ServiceDocItem.ResponsibleEmployee,
BillgReqItm._ServiceDocItem.ContactPerson,
// Status fields
BillgReqItm._ServiceDocItem.ServiceDocItemRejectionReason as ServiceContractItmCanclnReason,
BillgReqItm._ServiceDocItem.ServiceDocumentItemStatus as ServiceContractItemStatus,
BillgReqItm._ServiceDocItem.ServiceDocumentItemHasError as ServiceContractItemHasError,
BillgReqItm._ServiceDocItem.ServiceDocumentItemIsReleased as ServiceContractItemIsReleased,
BillgReqItm._ServiceDocItem.ServiceDocumentItemIsRejected as ServiceContractItemIsCanceled,
// Billing Information
BillgReqItm.BillingBlockReason as BillingBlockReason,
BillgReqItm._ServiceDocItem.ServiceDocItemBillingStatus,
BillgReqItm._ServiceDocItem.SrvcDocItmIsReleasedForBilling,
BillgReqItm.BillgReqItemBillingStatus,
cast( cast(substring( cast(BillgReqItm.BillgReqItemBillingDateTime as abap.char(30)) , 1 , 8)
as abap.dats(8)) as crms4_created_on preserving type ) as BillgReqItemBillingDate,
BillgReqItm._ServiceDocItem.RefBusinessSolutionOrder,
BillgReqItm._ServiceDocItem.RefBusinessSolutionOrderItem,
// BillgReqItm._ServiceDocItem._ServiceOrganizationOrgUnit
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_ServiceOrganizationOrgUnit_2'
BillgReqItm._ServiceDocItem._ServiceOrganizationOrgUnit,
BillgReqItm._ServiceDocItem._ServiceOrganizationOrgUnit_2
}
where
BillgReqItm.ServiceObjectType = 'BUS2000112' //Only Contracts
and BillgReqItm.BillgReqItemBillingStatus <> 'F' //Billing Plan Item Not Fully Billed
and BillgReqItm.BillingBlockReason <> ' '
//---------------------------------------------------------------------------//
// E001 - Items Contain Error
//---------------------------------------------------------------------------//
union all select from I_ServiceDocumentItemEnhcd as SrvcContrItm
{
// Key Fields
key SrvcContrItm.ServiceObjectType,
key SrvcContrItm.ServiceDocument as ServiceContract,
key SrvcContrItm.ServiceDocumentItem as ServiceContractItem,
key cast('E001' as crms4_service_issue preserving type) as CustMgmtIssue,
key cast('000000' as crms4_billing_item_no preserving type) as BillingRequestItem,
// Service Contract Attributes
@Semantics.currencyCode:true
SrvcContrItm.TransactionCurrency,
SrvcContrItm._ServiceDocument.ServiceDocumentType as ServiceContractType,
SrvcContrItm.ServiceContrItemStartDateTime,
SrvcContrItm.ServiceContrItemEndDateTime,
// Service Contract Item Attributes
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrItm.ServiceDocumentItemNetAmount as ServiceContractItemNetAmount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrItm.ServiceDocItemGrossAmount as ServiceContractItemGrossAmount,
SrvcContrItm.SrvcContrItemRnwlDuration,
SrvcContrItm.SrvcContrItemRnwlDurationUnit,
SrvcContrItm.SrvcContrItemExtensionDuration,
SrvcContrItm.SrvcContrItemExtnDurationUnit,
SrvcContrItm.ResponseProfile,
SrvcContrItm.ServiceProfile,
SrvcContrItm.ServiceDocItemCategory as ServiceContractItemCategory,
bintohex( SrvcContrItm.ServiceDocumentUUID ) as ServiceContractUUID,
// Organization Unit fields
SrvcContrItm.ServiceOrganization,
SrvcContrItm.SalesOrganization,
SrvcContrItm.SalesOffice,
SrvcContrItm.SalesGroup,
SrvcContrItm.DistributionChannel,
SrvcContrItm.Division,
// Partner Fields
SrvcContrItm.SoldToPartyCountry,
SrvcContrItm.SoldToPartyRegion,
SrvcContrItm.SoldToParty,
SrvcContrItm.ResponsibleEmployee,
SrvcContrItm.ContactPerson,
// Status fields
SrvcContrItm.ServiceDocItemRejectionReason as ServiceContractItmCanclnReason,
SrvcContrItm.ServiceDocumentItemStatus as ServiceContractItemStatus,
SrvcContrItm.ServiceDocumentItemHasError as ServiceContractItemHasError,
SrvcContrItm.ServiceDocumentItemIsReleased as ServiceContractItemIsReleased,
SrvcContrItm.ServiceDocumentItemIsRejected as ServiceContractItemIsCanceled,
// Billing Information
cast( ' ' as crmt_bus_bill_block_reason preserving type) as BillingBlockReason,
SrvcContrItm.ServiceDocItemBillingStatus,
SrvcContrItm.SrvcDocItmIsReleasedForBilling,
cast(' ' as crm_billing_settlement_status preserving type) as BillgReqItemBillingStatus,
cast( '00000000' as abap.dats ) as BillgReqItemBillingDate,
SrvcContrItm.RefBusinessSolutionOrder,
SrvcContrItm.RefBusinessSolutionOrderItem,
// SrvcContrItm._ServiceOrganizationOrgUnit
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_ServiceOrganizationOrgUnit_2'
SrvcContrItm._ServiceOrganizationOrgUnit,
SrvcContrItm._ServiceOrganizationOrgUnit_2
}
where
SrvcContrItm.ServiceObjectType = 'BUS2000112'
and SrvcContrItm.ServiceDocumentItemHasError = 'X'
and SrvcContrItm.ServiceDocumentItemIsRejected = ' '
and SrvcContrItm.ServiceDocumentItemStatus <> 'C' //Not Completed
and SrvcContrItm.ServiceDocumentTemplateType is initial
and SrvcContrItm.ServiceDocumentItemIsQuotation != 'X'
//---------------------------------------------------------------------------//
// E001 - Header Contains Error
// Headers are treated as items with item number 0
//---------------------------------------------------------------------------//
union all select from I_ServiceDocumentEnhcd as SrvcContrHeader
left outer join I_ServiceDocumentItemEnhcd as SrvcContrItm on SrvcContrItm.ServiceObjectType = SrvcContrHeader.ServiceObjectType
and SrvcContrItm.ServiceDocument = SrvcContrHeader.ServiceDocument
and SrvcContrItm.ServiceDocumentItemHasError = 'X'
and SrvcContrItm.ServiceDocumentItemIsRejected = ' '
and SrvcContrItm.ServiceDocumentItemStatus <> 'C' //Not Completed
{
// Key Fields
key SrvcContrHeader.ServiceObjectType,
key SrvcContrHeader.ServiceDocument as ServiceContract,
key cast('000000' as crms4_number_int preserving type) as ServiceContractItem,
key cast('E001' as crms4_service_issue preserving type) as CustMgmtIssue,
key cast('000000' as crms4_billing_item_no preserving type) as BillingRequestItem,
// Service Contract Attributes
@Semantics.currencyCode:true
SrvcContrItm.TransactionCurrency,
SrvcContrHeader.ServiceDocumentType as ServiceContractType,
SrvcContrHeader.ServiceContractStartDateTime as ServiceContrItemStartDateTime,
SrvcContrHeader.ServiceContractEndDateTime as ServiceContrItemEndDateTime,
// Service Contract Item Attributes
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrHeader.ServiceDocNetAmount as ServiceContractItemNetAmount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrHeader.ServiceDocGrossAmount as ServiceContractItemGrossAmount,
// Fields wich are only available on item level - will be NULL here because item does not exist
SrvcContrItm.SrvcContrItemRnwlDuration,
SrvcContrItm.SrvcContrItemRnwlDurationUnit,
SrvcContrItm.SrvcContrItemExtensionDuration,
SrvcContrItm.SrvcContrItemExtnDurationUnit,
SrvcContrItm.ResponseProfile,
SrvcContrItm.ServiceProfile,
SrvcContrItm.ServiceDocItemCategory as ServiceContractItemCategory,
bintohex( SrvcContrHeader.ServiceDocumentUUID ) as ServiceContractUUID,
// Organization Unit fields
SrvcContrHeader.ServiceOrganization,
SrvcContrHeader.SalesOrganization,
SrvcContrHeader.SalesOffice,
SrvcContrHeader.SalesGroup,
SrvcContrHeader.DistributionChannel,
SrvcContrHeader.Division,
// Partner Fields
SrvcContrHeader.SoldToPartyCountry,
SrvcContrHeader.SoldToPartyRegion,
SrvcContrHeader.SoldToParty,
SrvcContrHeader.ResponsibleEmployee,
SrvcContrHeader.ContactPerson,
// Status fields
SrvcContrHeader.ServiceDocumentRejectionReason as ServiceContractItmCanclnReason,
SrvcContrHeader.ServiceDocumentStatus as ServiceContractItemStatus,
SrvcContrHeader.ServiceDocumentHasError as ServiceContractItemHasError,
SrvcContrHeader.ServiceDocumentIsReleased as ServiceContractItemIsReleased,
SrvcContrHeader.ServiceDocumentIsRejected as ServiceContractItemIsCanceled,
// Billing Information
cast( ' ' as crmt_bus_bill_block_reason preserving type) as BillingBlockReason,
SrvcContrItm.ServiceDocItemBillingStatus,
SrvcContrItm.SrvcDocItmIsReleasedForBilling,
cast(' ' as crm_billing_settlement_status preserving type) as BillgReqItemBillingStatus,
cast( '00000000' as abap.dats ) as BillgReqItemBillingDate,
SrvcContrHeader.RefBusinessSolutionOrder,
SrvcContrItm.RefBusinessSolutionOrderItem,
// SrvcContrHeader._ServiceOrganizationOrgUnit
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_ServiceOrganizationOrgUnit_2'
SrvcContrHeader._ServiceOrganizationOrgUnit,
SrvcContrHeader._ServiceOrganizationOrgUnit_2
}
where
SrvcContrHeader.ServiceObjectType = 'BUS2000112'
and SrvcContrHeader.ServiceDocumentHasError = 'X' // Headers with error flag and no items with an error flag
and SrvcContrItm.ServiceObjectType is null
and SrvcContrHeader.ServiceDocumentIsRejected = ' '
and SrvcContrHeader.ServiceDocumentStatus <> 'C' //Not Completed
and SrvcContrHeader.ServiceDocumentTemplateType is initial
and SrvcContrHeader.ServiceDocumentIsQuotation != 'X'
//---------------------------------------------------------------------------//
// E003 - Rejected - Relevant only for items
//---------------------------------------------------------------------------//
union all select from I_ServiceDocumentItemEnhcd as SrvcContrItm
{
// Key Fields
key ServiceObjectType,
key ServiceDocument as ServiceContract,
key ServiceDocumentItem as ServiceContractItem,
key cast('E003' as crms4_service_issue preserving type ) as CustMgmtIssue,
key cast('000000' as crms4_billing_item_no preserving type) as BillingRequestItem,
// Service Contract Attributes
@Semantics.currencyCode:true
SrvcContrItm.TransactionCurrency,
SrvcContrItm._ServiceDocument.ServiceDocumentType as ServiceContractType,
SrvcContrItm.ServiceContrItemStartDateTime,
SrvcContrItm.ServiceContrItemEndDateTime,
// Service Contract Item Attributes
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrItm.ServiceDocumentItemNetAmount as ServiceContractItemNetAmount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
SrvcContrItm.ServiceDocItemGrossAmount as ServiceContractItemGrossAmount,
SrvcContrItm.SrvcContrItemRnwlDuration,
SrvcContrItm.SrvcContrItemRnwlDurationUnit,
SrvcContrItm.SrvcContrItemExtensionDuration,
SrvcContrItm.SrvcContrItemExtnDurationUnit,
SrvcContrItm.ResponseProfile,
SrvcContrItm.ServiceProfile,
SrvcContrItm.ServiceDocItemCategory as ServiceContractItemCategory,
bintohex( SrvcContrItm.ServiceDocumentUUID ) as ServiceContractUUID,
// Organization Unit fields
SrvcContrItm.ServiceOrganization,
SrvcContrItm.SalesOrganization,
SrvcContrItm.SalesOffice,
SrvcContrItm.SalesGroup,
SrvcContrItm.DistributionChannel,
SrvcContrItm.Division,
// Partner Fields
SrvcContrItm.SoldToPartyCountry,
SrvcContrItm.SoldToPartyRegion,
SrvcContrItm.SoldToParty,
SrvcContrItm.ResponsibleEmployee,
SrvcContrItm.ContactPerson,
// Status fields
SrvcContrItm.ServiceDocItemRejectionReason as ServiceContractItmCanclnReason,
SrvcContrItm.ServiceDocumentItemStatus as ServiceContractItemStatus,
SrvcContrItm.ServiceDocumentItemHasError as ServiceContractItemHasError,
SrvcContrItm.ServiceDocumentItemIsReleased as ServiceContractItemIsReleased,
SrvcContrItm.ServiceDocumentItemIsRejected as ServiceContractItemIsCanceled,
// Billing Information
cast( ' ' as crmt_bus_bill_block_reason preserving type) as BillingBlockReason,
SrvcContrItm.ServiceDocItemBillingStatus,
SrvcContrItm.SrvcDocItmIsReleasedForBilling,
cast(' ' as crm_billing_settlement_status preserving type) as BillgReqItemBillingStatus,
cast( '00000000' as abap.dats ) as BillgReqItemBillingDate,
SrvcContrItm.RefBusinessSolutionOrder,
SrvcContrItm.RefBusinessSolutionOrderItem,
// SrvcContrItm._ServiceOrganizationOrgUnit
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_ServiceOrganizationOrgUnit_2'
SrvcContrItm._ServiceOrganizationOrgUnit,
SrvcContrItm._ServiceOrganizationOrgUnit_2
}
where
SrvcContrItm.ServiceObjectType = 'BUS2000112'
and SrvcContrItm.ServiceDocumentItemIsRejected = 'X'
and SrvcContrItm.ServiceDocumentTemplateType is initial
and SrvcContrItm.ServiceDocumentItemIsQuotation != 'X'