P_CollabnApplFreightOrdForConf

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'PCOLLFRTORDCONF'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@EndUserText.label: 'Private view for confirm freight order'

define view P_CollabnApplFreightOrdForConf

  as select from I_TransportationOrder    as TransportationOrder
    inner join   P_CollabnApplCarrierUser as CollabnApplCarrierUser on TransportationOrder.Carrier = CollabnApplCarrierUser.BusinessPartnerCompany

{
      //TransportationOrder.TransportationOrderUUID                                                                                       as CollabnApplFrtOrdConfUUID,

  key TransportationOrder.TransportationOrderUUID,
      hextobin('00000000000000000000000000000000')                                                                                      as FreightRequestForQuotationUUID,
      TransportationOrder.TransportationOrder                                                                                           as FreightOrder,
      TransportationOrder.TransportationOrderCategory,
      TransportationOrder.TransportationOrderType,

      TransportationOrder.PurchasingOrgExternalID,
      TransportationOrder._PurchasingOrganization._OrganizationalUnitText[1:Language = $session.system_language].OrganizationalUnitName as PurchasingOrganizationName,
      TransportationOrder._SourceStop.TransportationOrderStopUUID,
      TransportationOrder._SourceStop.LocationId                                                                                        as SourceLocation,
      TransportationOrder._DestinationStop.LocationId                                                                                   as DestinationLocation,

      TransportationOrder._SourceStop._Locationdescr.LocationDescription                                                                as SourceLocationDescription,
      TransportationOrder._DestinationStop._Locationdescr.LocationDescription                                                           as DestinationLocationDescription,

      TransportationOrder._SourceStop._Locationdescr.AddressObjectDescription                                                           as SourceLocationAddressDesc,
      TransportationOrder._DestinationStop._Locationdescr.AddressObjectDescription                                                      as DestinationLocationAddressDesc,

      TransportationOrder.Carrier,
      TransportationOrder.TranspOrdResponsiblePerson,

      TransportationOrder.MeansOfTransport,
      TransportationOrder._MeansOfTransport._Text[1: Language = $session.system_language].MeansOfTransportDesc,
      TransportationOrder.TranspOrdDistance,
      TransportationOrder.TranspOrdDistanceUnit,

      TransportationOrder._SourceStop.TranspOrdStopPlanTranspDteTme                                                                     as TranspOrdDepartureDateTime,
      TransportationOrder._DestinationStop.TranspOrdStopPlanTranspDteTme                                                                as TranspOrdArrivalDateTime,

      TransportationOrder.TranspOrdGrossWeightUnit                                                                                      as TranspOrdGrossWeightUnit,
      TransportationOrder.TranspOrdGrossWeight                                                                                          as TranspOrdGrossWeight,
      TransportationOrder.TranspOrdGrossVolumeUnit                                                                                      as TranspOrdGrossVolumeUnit,
      TransportationOrder.TranspOrdGrossVolume                                                                                          as TranspOrdGrossVolume,
      TransportationOrder.TranspOrdQuantityUnit                                                                                         as TranspOrdQuantityUnit,
      TransportationOrder.TranspOrdQuantity                                                                                             as TranspOrdQuantity,
      TransportationOrder.TranspOrderDngrsGdsSts                                                                                        as TranspOrderDngrsGdsSts,
      TransportationOrder._TranspOrdDngrsGdsStatus._Text[1: Language = $session.system_language].TranspOrderDngrsGdsStsDesc             as TranspOrderDngrsGdsStsDesc,


      cast(case when TransportationOrder.TranspOrdLifeCycleStatus = '05' //Completed

                  or TransportationOrder.TranspOrdLifeCycleStatus = '10' //Canceled

                 then '04' //Closed

                when TransportationOrder.TransportationOrderConfSts = '04' //Confirmed

                  or TransportationOrder.TransportationOrderConfSts = '05' //Confirmed with Changes

                 then '03' //Confirmed

                when  TransportationOrder.TransportationOrderConfSts = '03' //Rejected

                then '02' //Rejected

                when TransportationOrder.TransportationOrderConfSts = '01' //No Confirmation yet

                  or TransportationOrder.TransportationOrderConfSts = '06' //Update sent, No confirmation yet

                  or TransportationOrder.TransportationOrderConfSts = '10' //Changed after confirmation

                 then '01' //For Confirmation

      end   as /scmtms/vdm_frt_ord_cnf_status )                                                                                         as FreightOrderConfirmationStatus,
      //TranspOrdConfirmationStatus

      cast ( '' as abap.cuky( 05 ))                                                                                                     as TenderingPreferredCurrency,
      cast ( '' as abap.cuky( 05 ))                                                                                                     as TndrgRspSubmdAmtCrcy,
      cast( '' as abap.char( 02 ))                                                                                                      as TenderingSubmdPriceVisibility,
      cast( '' as abap.char( 02 ))                                                                                                      as TenderingSubmdPrCrcyVisibility,
      cast('' as abap.char( 02 ))                                                                                                       as TenderingStopDateVisibility,
      cast( '' as abap.char( 02 ))                                                                                                      as TenderingPriceLimitVisibility,
      cast( '' as abap.char( 02 ))                                                                                                      as TenderingLowestPriceVisibility,
      cast( 0 as abap.curr( 31, 2))                                                                                                     as TndrgReqAbsolutePriceLimitAmt


}
where
       TransportationOrder.TransportationOrderCategory = 'TO' //Transportation Order

  and  TransportationOrder.TransportationModeCategory  = '1' //Transportation Mode Road

  and(
       TransportationOrder.TranspOrderSubcontrgSts     = '04' //Sent

    or TransportationOrder.TranspOrderSubcontrgSts     = '03' //Carrier assigned

  )


union select from        I_TenderingRequest             as TenderingRequest
  inner join             P_CollabnApplCarrierUser       as CollabnApplCarrierUser on TenderingRequest.TenderingRequestedCarrier = CollabnApplCarrierUser.BusinessPartnerCompany

  inner join             I_TenderingStep                as TenderingStep          on  TenderingRequest.TenderingStepUUID = TenderingStep.TenderingStepUUID
                                                                                  and TenderingStep.TenderingProcessMode = 'D' //Direct Tendering

  inner join             I_Tendering                    as Tendering              on TenderingStep.TenderingUUID = Tendering.TenderingUUID
  inner join             I_TransportationOrder          as TransportationOrder    on Tendering.TransportationOrderUUID = TransportationOrder.TransportationOrderUUID
  inner join             I_TransportationOrder          as RefTransportationOrder on  Tendering.RefTransportationOrderUUID               = RefTransportationOrder.TransportationOrderUUID
                                                                                  and RefTransportationOrder.TransportationOrderCategory = 'TC' //Copy of Transportation Order

  left outer to one join P_CollabnApplLatestFrtQtanSqnc as LatestFreightQuotation on TenderingRequest.TenderingRequestUUID = LatestFreightQuotation.TenderingRequestUUID
  left outer to one join I_TenderingResponse            as TenderingResponse      on  TenderingRequest.TenderingRequestUUID              = TenderingResponse.TenderingRequestUUID
                                                                                  and LatestFreightQuotation.TenderingResponseSqncNumber = TenderingResponse.TenderingResponseSqncNumber

{
      //key TenderingRequest.TenderingRequestUUID                                                                                                as CollabnApplFrtOrdConfUUID,

  key RefTransportationOrder.TransportationOrderUUID,
      TenderingRequest.TenderingRequestUUID                                                                                                as FreightRequestForQuotationUUID,
      TransportationOrder.TransportationOrder                                                                                              as FreightOrder,
      RefTransportationOrder.TransportationOrderCategory,
      RefTransportationOrder.TransportationOrderType,

      RefTransportationOrder.PurchasingOrgExternalID,
      RefTransportationOrder._PurchasingOrganization._OrganizationalUnitText[1:Language = $session.system_language].OrganizationalUnitName as PurchasingOrganizationName,
      RefTransportationOrder._SourceStop.TransportationOrderStopUUID,
      RefTransportationOrder._SourceStop.LocationId                                                                                        as SourceLocation,
      RefTransportationOrder._DestinationStop.LocationId                                                                                   as DestinationLocation,

      RefTransportationOrder._SourceStop._Locationdescr.LocationDescription                                                                as SourceLocationDescription,
      RefTransportationOrder._DestinationStop._Locationdescr.LocationDescription                                                           as DestinationLocationDescription,

      RefTransportationOrder._SourceStop._Locationdescr.AddressObjectDescription                                                           as SourceLocationAddressDesc,
      RefTransportationOrder._DestinationStop._Locationdescr.AddressObjectDescription                                                      as DestinationLocationAddressDesc,

      TenderingRequest.TenderingRequestedCarrier                                                                                           as Carrier,
      Tendering.TenderingManager                                                                                                           as TranspOrdResponsiblePerson,

      RefTransportationOrder.MeansOfTransport,
      RefTransportationOrder._MeansOfTransport._Text[1: Language = $session.system_language].MeansOfTransportDesc,
      RefTransportationOrder.TranspOrdDistance,
      RefTransportationOrder.TranspOrdDistanceUnit,

      RefTransportationOrder._SourceStop.TranspOrdStopPlanTranspDteTme                                                                     as TranspOrdDepartureDateTime,
      RefTransportationOrder._DestinationStop.TranspOrdStopPlanTranspDteTme                                                                as TranspOrdArrivalDateTime,

      RefTransportationOrder.TranspOrdGrossWeightUnit                                                                                      as TranspOrdGrossWeightUnit,
      RefTransportationOrder.TranspOrdGrossWeight                                                                                          as TranspOrdGrossWeight,
      RefTransportationOrder.TranspOrdGrossVolumeUnit                                                                                      as TranspOrdGrossVolumeUnit,
      RefTransportationOrder.TranspOrdGrossVolume                                                                                          as TranspOrdGrossVolume,
      RefTransportationOrder.TranspOrdQuantityUnit                                                                                         as TranspOrdQuantityUnit,
      RefTransportationOrder.TranspOrdQuantity                                                                                             as TranspOrdQuantity,
      RefTransportationOrder.TranspOrderDngrsGdsSts                                                                                        as TranspOrderDngrsGdsSts,
      RefTransportationOrder._TranspOrdDngrsGdsStatus._Text[1: Language = $session.system_language].TranspOrderDngrsGdsStsDesc            as TranspOrderDngrsGdsStsDesc,

      cast(case when TransportationOrder.TranspOrdLifeCycleStatus = '05' //Completed

             or TransportationOrder.TranspOrdLifeCycleStatus = '10' //Canceled

            then '04' //Closed

           when TenderingResponse.TenderingResponseCode = 'AP' //Accepted

             or TenderingResponse.TenderingResponseCode = 'AX' //Accepted with Changes

            then '03' //Confirmed

           when  TenderingResponse.TenderingResponseCode = 'RE' //Rejected

                then '02' //Rejected

          when TenderingRequest.TndrgReqLifeCycleStatus = '02' //Open

            then '01' //For Confirmation

      end   as /scmtms/vdm_frt_ord_cnf_status )                                                                                            as FreightOrderConfirmationStatus,


      TenderingRequest._TenderingStep.TenderingPreferredCurrency                                                                           as TenderingPreferredCurrency,
      TenderingRequest._TenderingResponse.TndrgRspSubmdAmtCrcy                                                                             as TndrgRspSubmdAmtCrcy,

      TenderingRequest._TenderingVisibilitySettings.TenderingSubmdPriceVisibility                                                          as TenderingSubmdPriceVisibility,
      TenderingRequest._TenderingVisibilitySettings.TenderingSubmdPrCrcyVisibility                                                         as TenderingSubmdPrCrcyVisibility,
      TenderingRequest._TenderingVisibilitySettings.TenderingStopDateVisibility                                                            as TenderingStopDateVisibility,
      TenderingRequest._TenderingVisibilitySettings.TenderingPriceLimitVisibility                                                          as TenderingPriceLimitVisibility,
      TenderingRequest._TenderingVisibilitySettings.TenderingLowestPriceVisibility                                                         as TenderingLowestPriceVisibility,
      cast( case when TenderingRequest._TenderingVisibilitySettings.TenderingPriceLimitVisibility = 'RO' then TndrgReqAbsolutePriceLimitAmt
            else cast( 0 as abap.curr( 31, 2 )) end as /scmtms/vdm_tend_req_prc_amt preserving type )                                      as TndrgReqAbsolutePriceLimitAmt





      //TranspOrdConfirmationStatus

}
where
  TenderingRequest.TndrgReqLifeCycleStatus = '02' //Open