P_ServiceOrderFulfillment

@AbapCatalog:
{
    sqlViewName: 'PSRVORDFU',
    compiler.compareFilter: true,
    preserveKey: true   
}
@VDM: {
    private: true,
    viewType: #COMPOSITE
}
@ObjectModel: {
   usageType: {
     dataClass:      #MIXED,
     serviceQuality: #D,
     sizeCategory:   #XXL
   }
}
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'View for service order fulfillment monitor'

define view P_ServiceOrderFulfillment as select from I_ServiceDocument as ServiceDoc
    left outer to many join  I_BillingDocument as billingDoc  on ServiceDoc.ServiceDocument = billingDoc.DocumentReferenceID
                                                     and billingDoc.BillingDocumentIsTemporary = ' '
    left outer to one join P_SrvcTransWthSituation as srvcSit on ServiceDoc.ServiceDocument = srvcSit.ServiceDocument
    
  //  association [1..*] to ZSituationCountForSrvcDoc as _SitCount on _SitCount.ServiceObjectType =  ServiceDoc.ServiceObjectType

   //                                                     and  _SitCount.ServiceDocument = ServiceDoc.ServiceDocument


{
  //  key cast('SrvcOrd' as crms4_ana_srvc_proc_def)                        as ServiceProcessCategoryID,

    key ServiceDoc.ServiceObjectType,
    key ServiceDoc.ServiceDocument,
    ServiceDoc.ServiceDocumentDescription,
    ServiceDoc.ServiceDocumentUUID,
    ServiceDoc.ServiceDocumentType,
 //   srvcSit.ServiceDocumentItem,

 //   srvcSit.ServiceDocumentItemUUID,

    
 //   case ServiceDoc.ServiceDocumentIsOpen

 //       when 'X'

 //           then 

 //               case ServiceDoc.ServiceDocumentIsReleased

 //                   when 'X'

 //                   then cast('SRVCORD020' as crms4_ana_srvc_proc_phase)

 //                   else cast('SRVCORD010' as crms4_ana_srvc_proc_phase)

 //               end

 //           else 

 //               case ServiceDoc.ServiceDocBillingStatus

 //                   when 'C'

 //                   then cast('SRVCORD040' as crms4_ana_srvc_proc_phase)

 //                   else cast('SRVCORD030' as crms4_ana_srvc_proc_phase)

 //               end

 //        end                                                               as ServiceProcessPhaseName,

     // as the srvcSit.NrHeaderSitn column is null (because the join condition is not met), we need to add the case statement to get the desired result    

     case when srvcSit.NrHeaderSitn is not null 
          then  srvcSit.NrItemSitn + srvcSit.NrHeaderSitn   
          else  srvcSit.NrItemSitn end                                      as SitnNumberOfInstances,
     ServiceDoc.SoldToParty,
     ServiceDoc.SoldToPartyCountry,
     ServiceDoc.SoldToPartyRegion,
     ServiceDoc.SalesOrganization,
     ServiceDoc.SalesGroup,
     ServiceDoc.SalesOffice,
     ServiceDoc.DistributionChannel,
     ServiceDoc.Division,
     ServiceDoc.ResponsibleEmployee,
     ServiceDoc.ServiceDocNetAmount,
     sum(billingDoc.TotalNetAmount) as TotalNetAmount,
     ServiceDoc.ServiceDocumentStatus,
     ServiceDoc.ServiceDocumentHasError,
     ServiceDoc.ServiceDocumentIsReleased,
     ServiceDoc.ServiceDocBillingStatus,
     ServiceDoc.ServiceDocIsReleasedForBilling,
     ServiceDoc.ServiceDocumentIsRejected,
     ServiceDoc.ServiceDocumentIsOpen,
     ServiceDoc.ServiceDocumentIsQuotation,
     ServiceDoc.TransactionCurrency,
     
     //DCL Purpose

     ServiceDoc.SalesGroupOrgUnitID,
     ServiceDoc.SalesOfficeOrgUnitID,
     ServiceDoc.SalesOrganizationOrgUnitID,
     ServiceDoc.ServiceOrganization,     
     billingDoc.BillingDocumentType,
     
     //associations

     ServiceDoc._SoldToParty,
     ServiceDoc._SoldToPartyCountry,
     ServiceDoc._SoldToPartyRegion,
     ServiceDoc._SalesOrganization,
     ServiceDoc._SalesGroup,
     ServiceDoc._SalesOffice,
     ServiceDoc._DistributionChannel,
     ServiceDoc._Division,
     ServiceDoc._RespEmployee,
     @API.element.releaseState: #DEPRECATED
     @API.element.successor: '_SrvcDocLifecycleStatus'
     ServiceDoc._ServiceDocumentStatus,
     ServiceDoc._SrvcDocLifecycleStatus,     
     ServiceDoc._ServiceDocHasError,
     ServiceDoc._ServiceDocBillingStatus,
     ServiceDoc._ServiceDocumentType,
     ServiceDoc._ServiceObjType,
     ServiceDoc._ServiceDocumentIsOpen,
     ServiceDoc._SrvcDocIsRelForBillgSts,
     
     ServiceDoc._SalesGroupOrgUnit,
     ServiceDoc._SalesOfficeOrgUnit,
     ServiceDoc._SalesOrganizationOrgUnit,
     ServiceDoc._ServiceOrganizationOrgUnit,
     
     ServiceDoc._SalesGroupOrgUnit_2,
     ServiceDoc._SalesOfficeOrgUnit_2,
     ServiceDoc._SalesOrganizationOrgUnit_2,
     ServiceDoc._ServiceOrganizationOrgUnit_2,
          
    // _SitCount

     billingDoc._BillingDocumentType
     
    
} where ServiceDoc.ServiceObjectType = 'BUS2000116' //and ServiceDoc.ServiceDocument = '8000001625'

  group by ServiceDoc.ServiceDocument,
           ServiceDoc.ServiceDocumentDescription,
           // srvcSit.NrItemSitn,

     ServiceDoc.SoldToParty,
     ServiceDoc.SoldToPartyCountry,
     ServiceDoc.SoldToPartyRegion,
     ServiceDoc.SalesOrganization,
     ServiceDoc.SalesGroup,
     ServiceDoc.SalesOffice,
     ServiceDoc.DistributionChannel,
     ServiceDoc.Division,
     ServiceDoc.ResponsibleEmployee,
     ServiceDoc.ServiceDocNetAmount,
     ServiceDoc.ServiceDocumentStatus,
     ServiceDoc.ServiceDocumentHasError,
     ServiceDoc.ServiceDocumentIsReleased,
     ServiceDoc.ServiceDocBillingStatus,
     ServiceDoc.ServiceDocIsReleasedForBilling,
     ServiceDoc.ServiceDocumentIsRejected,
     ServiceDoc.ServiceDocumentIsOpen,
     ServiceDoc.ServiceDocumentIsQuotation,
     ServiceDoc.ServiceObjectType,
    // srvcSit.ServiceDocumentItem,

    // srvcSit.ServiceDocumentItemUUID,

     ServiceDoc.ServiceDocumentUUID,
     ServiceDoc.ServiceDocumentType,
     ServiceDoc.SalesGroupOrgUnitID,
     ServiceDoc.SalesOfficeOrgUnitID,
     ServiceDoc.SalesOrganizationOrgUnitID,
     ServiceDoc.ServiceOrganization,
     billingDoc.BillingDocumentType,
     ServiceDoc.TransactionCurrency,
     srvcSit.NrItemSitn,
     srvcSit.NrHeaderSitn
             //and billingDoc.OverallBillingStatus != 'A'

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLINGDOCUMENT",
"I_SERVICEDOCUMENT",
"P_SRVCTRANSWTHSITUATION"
],
"ASSOCIATED":
[
"I_BILLINGDOCUMENTTYPE",
"I_BUSINESSPARTNER",
"I_COUNTRY",
"I_CUSTMGMTBUSOBJTYPE",
"I_DISTRIBUTIONCHANNEL",
"I_DIVISION",
"I_REGION",
"I_SALESGROUP",
"I_SALESOFFICE",
"I_SALESORGANIZATION",
"I_SALESORGANIZATIONUNIT",
"I_SERVICEDOCBILLINGSTATUS",
"I_SERVICEDOCERRORSTATUS",
"I_SERVICEDOCOPENSTATUS",
"I_SERVICEDOCUMENTSTATUS",
"I_SERVICEDOCUMENTTYPE",
"I_SRVCDOCLIFECYCLESTATUS",
"I_SRVCDOCRELFORBILLGSTS",
"I_SRVCMGMTORGANIZATIONUNIT"
],
"BASE":
[
"I_BILLINGDOCUMENT",
"I_SERVICEDOCUMENT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/