P_PCLblProdCmbndByBusProc

@AbapCatalog.sqlViewName: 'PPRODCMBND'
@AbapCatalog.compiler.compareFilter: true
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #MANDATORY

@ObjectModel: {
  usageType: {
    serviceQuality: #B,
    sizeCategory: #XL,
    dataClass: #MIXED
  }
}
@Metadata.ignorePropagatedAnnotations: true

// Union Products by Business Processes

define view P_PCLblProdCmbndByBusProc
  as select from I_Product
{
  key cast( '01' as ehlbl_bus_proc_code preserving type )                                            as PCLblBusProc, // Generic

  key cast( Product as abap.char( 40 ) )                                                             as PCLblBusProcSearchElementText,

  key cast( Product as  ehlbl_product_number_nc preserving type )                                    as Product,
  key cast( '          ' as vbeln_vl preserving type )                                               as DeliveryDocument,
      // Blocking ATC: Field UniqueItemIdentifier: Data type length 000040 does not match 000072 of GFN default type UII_CHAR72

  key cast( Product as abap.char( 72 ) )                                                             as UniqueItemIdentifier,
  key cast( '            ' as ehfnd_maint_order_id_nc preserving type )                              as ManufacturingOrder,
  key cast( '          ' as vdm_sales_order preserving type )                                        as SalesOrder,
      //_ESHProductPlant.Plant                                            as Plant,

      cast( '    ' as werks_d preserving type )                                                      as Plant,
      cast( '          ' as kunnr preserving type )                                                  as Customer,
      cast( '    ' as vkorg preserving type )                                                        as SalesOrganization,
      cast( _Text[1: Language = $session.system_language ].ProductName as ehfnd_cci_internal_name  ) as ProductName,
      @Semantics.quantity.unitOfMeasure:'BaseUnit'
      cast( cast( '1' as abap.quan( 13, 3 ) ) as mfgorderplannedtotalqty preserving type )           as ActualDeliveryQuantity,
      @Semantics.unitOfMeasure: true
      //cast ('' as abap.unit( 3 )) as BaseUnit,

      BaseUnit,
      '   '                                                                                          as Country,
      cast( '               ' as abap.char(15)   )                                                   as Batch,
      ///required for authorization

      // I_Product

      AuthorizationGroup,
      ProductType,
      _ProductType,
      ProductGroup,
      _ProductGroup_2,
      // I_DeliveryDocument

      cast('    ' as vstel preserving type )                                                         as ShippingPoint,
      // I_ManufacturingOrder

      cast('00' as pph_autyp preserving type )                                                       as ManufacturingOrderCategory,
      cast('    ' as aufart preserving type )                                                        as ManufacturingOrderType,
      cast('    ' as pwwrk preserving type )                                                         as ProductionPlant,
      //I_ChmlCmplncInfo

      cast(' ' as ehfnd_cci_is_transported preserving type )                                         as MaterialIsTransported,
      cast(' ' as ehfnd_cci_is_produced preserving type )                                            as MaterialIsProduced,
      cast(' ' as ehfnd_cci_is_sold preserving type )                                                as MaterialIsSold,
      cast(' ' as ehfnd_cci_is_sourced preserving type )                                             as MaterialIsSourced,
      cast('          ' as ehfnd_cci_responsible_unit_pss preserving type  )                         as ProdStewardshipRespUnit,
      cast(' ' as ehfnd_cci_is_disposed preserving type )                                            as MaterialIsDisposed,
      cast(' ' as ehfnd_cci_is_emission_rlvt preserving type )                                       as MaterialIsEmissionRelevant
}
where
  IsMarkedForDeletion <> 'X'

union all select from I_DeliveryPickViewItem
inner join I_Product as _Product on _Product.Product = I_DeliveryPickViewItem.Material 
{
  key       cast( '02'  as ehlbl_bus_proc_code preserving type )                   as PCLblBusProc, // Delivery

  key       cast( I_DeliveryPickViewItem.DeliveryDocument as abap.char( 40 ) )                            as PCLblBusProcSearchElementText,
  key       cast( I_DeliveryPickViewItem.Material as  ehlbl_product_number_nc preserving type )           as Product,
  key       I_DeliveryPickViewItem.DeliveryDocument,
  key       cast( I_DeliveryPickViewItem.DeliveryDocumentItem as abap.char( 72 ) )                        as UniqueItemIdentifier,
  key       cast( '            ' as ehfnd_maint_order_id_nc preserving type )      as ManufacturingOrder,
  key       cast( '          ' as vdm_sales_order preserving type )                as SalesOrder,
            I_DeliveryPickViewItem.Plant,
            I_DeliveryPickViewItem._DeliveryDocument.ShipToParty                                          as Customer,
            I_DeliveryPickViewItem._DeliveryDocument.SalesOrganization                                    as SalesOrganization,
            cast( I_DeliveryPickViewItem.DeliveryDocumentItemText as ehfnd_cci_internal_name )            as ProductName,
            @Semantics.quantity.unitOfMeasure:'BaseUnit'
            I_DeliveryPickViewItem.ActualDeliveryQuantity,
            @Semantics.unitOfMeasure: true
            I_DeliveryPickViewItem.BaseUnit,
            I_DeliveryPickViewItem._DeliveryDocument._ShipToParty.Country,
            coalesce( I_DeliveryPickViewItem.Batch, I_DeliveryPickViewItem.BatchBySupplier )              as Batch,
            ///required for authorization

            // I_Product

            _Product.AuthorizationGroup,
            _Product.ProductType,
            _Product._ProductType,
            _Product.ProductGroup,
            _Product._ProductGroup_2,
            // I_DeliveryDocument

            cast('    ' as vstel preserving type )                                 as ShippingPoint,
            // I_ManufacturingOrder

            cast('00' as pph_autyp preserving type )                               as ManufacturingOrderCategory,
            cast('    ' as aufart preserving type )                                as ManufacturingOrderType,
            cast('    ' as pwwrk preserving type )                                 as ProductionPlant,
            //I_ChmlCmplncInfo

            cast(' ' as ehfnd_cci_is_transported preserving type )                 as MaterialIsTransported,
            cast(' ' as ehfnd_cci_is_produced preserving type )                    as MaterialIsProduced,
            cast(' ' as ehfnd_cci_is_sold preserving type )                        as MaterialIsSold,
            cast(' ' as ehfnd_cci_is_sourced preserving type )                     as MaterialIsSourced,
            cast('          ' as ehfnd_cci_responsible_unit_pss preserving type  ) as ProdStewardshipRespUnit,
            cast(' ' as ehfnd_cci_is_disposed preserving type )                    as MaterialIsDisposed,
            cast(' ' as ehfnd_cci_is_emission_rlvt preserving type )               as MaterialIsEmissionRelevant
}

union all select from I_ManufacturingOrder
{
  key cast( '03'  as ehlbl_bus_proc_code preserving type )                                                   as PCLblBusProc, // Make To Stock

  key cast( ManufacturingOrder as abap.char( 40 ) )                                                          as PCLblBusProcSearchElementText,
  key cast( Product as  ehlbl_product_number_nc preserving type )                                            as Product,
  key cast( '          ' as vbeln_vl preserving type )                                                       as DeliveryDocument,
  key cast( ManufacturingOrderItem as abap.char( 72 ) )                                                      as UniqueItemIdentifier,
  key cast( ManufacturingOrder as ehfnd_maint_order_id_nc preserving type  )                                 as ManufacturingOrder,
  key SalesOrder,
      ProductionPlant                                                                                        as Plant,
      cast( '          ' as kunnr preserving type )                                                          as Customer,
      cast( '    ' as vkorg preserving type )                                                                as SalesOrganization,
      cast( _Product._Text[1: Language = $session.system_language ].ProductName as ehfnd_cci_internal_name ) as ProductName,
      @Semantics.quantity.unitOfMeasure: 'BaseUnit'
      MfgOrderPlannedTotalQty                                                                                as ActualDeliveryQuantity,
      @Semantics.unitOfMeasure: true
      ProductionUnit                                                                                         as BaseUnit,
      '   '                                                                                                  as Country,
      Batch,
      //required for authorization

      // I_Product

      _Product.AuthorizationGroup,
      _Product.ProductType,
      _Product._ProductType,
      _Product.ProductGroup,
      _Product._ProductGroup_2,
      // I_DeliveryDocument

      cast('    ' as vstel preserving type )                                                                 as ShippingPoint,
      // I_ManufacturingOrder

      cast('00' as pph_autyp preserving type )                                                               as ManufacturingOrderCategory,
      cast('    ' as aufart preserving type )                                                                as ManufacturingOrderType,
      cast('    ' as pwwrk preserving type )                                                                 as ProductionPlant,
      //I_ChmlCmplncInfo

      cast(' ' as ehfnd_cci_is_transported preserving type )                                                 as MaterialIsTransported,
      cast(' ' as ehfnd_cci_is_produced preserving type )                                                    as MaterialIsProduced,
      cast(' ' as ehfnd_cci_is_sold preserving type )                                                        as MaterialIsSold,
      cast(' ' as ehfnd_cci_is_sourced preserving type )                                                     as MaterialIsSourced,
      cast('          ' as ehfnd_cci_responsible_unit_pss preserving type  )                                 as ProdStewardshipRespUnit,
      cast(' ' as ehfnd_cci_is_disposed preserving type )                                                    as MaterialIsDisposed,
      cast(' ' as ehfnd_cci_is_emission_rlvt preserving type )                                               as MaterialIsEmissionRelevant
}
where
      SalesOrder          is initial
  and IsMarkedForDeletion <> 'X'

union all select from I_ManufacturingOrder
{
  key cast(  '04'  as ehlbl_bus_proc_code preserving type )                                                  as PCLblBusProc, // Make To Order

  key cast( ManufacturingOrder as abap.char( 40 )  )                                                         as PCLblBusProcSearchElementText,
  key cast( Product as  ehlbl_product_number_nc preserving type )                                            as Product,
  key cast( '          ' as vbeln_vl preserving type )                                                       as DeliveryDocument,
  key cast( ManufacturingOrderItem as abap.char( 72 ) )                                                      as UniqueItemIdentifier,
  key cast( ManufacturingOrder as ehfnd_maint_order_id_nc preserving type  )                                 as ManufacturingOrder,
  key SalesOrder,
      ProductionPlant                                                                                        as Plant,
      _SalesOrder.SoldToParty                                                                                as Customer,
      _SalesOrder.SalesOrganization                                                                          as SalesOrganization,
      cast( _Product._Text[1: Language = $session.system_language ].ProductName as ehfnd_cci_internal_name ) as ProductName,
      @Semantics.quantity.unitOfMeasure: 'ProductionUnit'
      MfgOrderPlannedTotalQty                                                                                as ActualDeliveryQuantity,
      @Semantics.unitOfMeasure: true
      ProductionUnit                                                                                         as BaseUnit,
      _SalesOrder._SoldToParty.Country,
      Batch,
      //required for authorization

      // I_Product

      _Product.AuthorizationGroup,
      _Product.ProductType,
      _Product._ProductType,
      _Product.ProductGroup,
      _Product._ProductGroup_2,
      // I_DeliveryDocument

      cast('    ' as vstel preserving type )                                                                 as ShippingPoint,
      // I_ManufacturingOrder

      cast('00' as pph_autyp preserving type )                                                               as ManufacturingOrderCategory,
      cast('    ' as aufart preserving type )                                                                as ManufacturingOrderType,
      cast('    ' as pwwrk preserving type )                                                                 as ProductionPlant,
      //I_ChmlCmplncInfo

      cast(' ' as ehfnd_cci_is_transported preserving type )                                                 as MaterialIsTransported,
      cast(' ' as ehfnd_cci_is_produced preserving type )                                                    as MaterialIsProduced,
      cast(' ' as ehfnd_cci_is_sold preserving type )                                                        as MaterialIsSold,
      cast(' ' as ehfnd_cci_is_sourced preserving type )                                                     as MaterialIsSourced,
      cast('          ' as ehfnd_cci_responsible_unit_pss preserving type  )                                 as ProdStewardshipRespUnit,
      cast(' ' as ehfnd_cci_is_disposed preserving type )                                                    as MaterialIsDisposed,
      cast(' ' as ehfnd_cci_is_emission_rlvt preserving type )                                               as MaterialIsEmissionRelevant
}
where
      SalesOrder          is not initial
  and IsMarkedForDeletion <> 'X'


union all select from I_ChmlCmplncInfo
{

  key cast(  '05'  as ehlbl_bus_proc_code preserving type )                                as PCLblBusProc, // R&D Sample

  key cast( Specification as abap.char( 40 )  )                                            as PCLblBusProcSearchElementText,
  key cast( Specification as ehlbl_specification_nc preserving type )                      as Product,
  key cast( '          ' as vbeln_vl preserving type )                                     as DeliveryDocument,
  key cast( Specification as abap.char( 72 ) )                                             as UniqueItemIdentifier,
  key cast( '            ' as ehfnd_maint_order_id_nc preserving type )                    as ManufacturingOrder,
  key cast( '          ' as vdm_sales_order preserving type )                              as SalesOrder,
      cast( '    ' as werks_d preserving type )                                            as Plant,
      cast( '          ' as kunnr preserving type )                                        as Customer,
      cast( '    ' as vkorg preserving type )                                              as SalesOrganization,
      cast( ChmlCmplncInternalName as ehfnd_cci_internal_name preserving type )            as ProductName,
      @Semantics.quantity.unitOfMeasure:'BaseUnit'
      cast( cast( '1' as abap.quan( 13, 3 ) ) as mfgorderplannedtotalqty preserving type ) as ActualDeliveryQuantity,
      @Semantics.unitOfMeasure: true
      cast ('' as abap.unit( 3 ))                                                          as BaseUnit,
      '   '                                                                                as Country,
      cast ( '          ' as abap.char(15) )                                               as Batch,
      //required for authorization

      // I_Product

      _PrimaryMaterial._Product.AuthorizationGroup,
      _PrimaryMaterial._Product.ProductType,
      _PrimaryMaterial._Product._ProductType,
      _PrimaryMaterial._Product.ProductGroup,
      _PrimaryMaterial._Product._ProductGroup_2,
      // I_DeliveryDocument

      cast('    ' as vstel preserving type )                                               as ShippingPoint,
      // I_ManufacturingOrder

      cast('00' as pph_autyp preserving type )                                             as ManufacturingOrderCategory,
      cast('    ' as aufart preserving type )                                              as ManufacturingOrderType,
      cast('    ' as pwwrk preserving type )                                               as ProductionPlant,
      MaterialIsTransported,
      MaterialIsProduced,
      MaterialIsSold,
      MaterialIsSourced,
      ProdStewardshipRespUnit,
      MaterialIsDisposed,
      MaterialIsEmissionRelevant

}
where
  ChmlCmplncProdIsResearched = 'X'