I_PT_SAFTBillgInvcListProd

@Consumption.dbHints: [ 'USE_HEX_PLAN' ]
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@VDM.viewType: #COMPOSITE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'Product from Invoice List Document'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #D,
  sizeCategory: #XL,
  dataClass: #MIXED
}
define view entity I_PT_SAFTBillgInvcListProd
  as select from           I_PT_SAFTBillgInvcListItem  as InvLDoc
    inner join             I_PT_BillgDocDgtlSgntr      as Sig                     on  Sig.BillingDocument = InvLDoc.BillingDocumentList
                                                                                  and Sig.CompanyCode     = InvLDoc.CompanyCode

    inner join             I_PT_SAFTBillgDocMatlHist   as materialHist            on  InvLDoc.BillingDocument = materialHist.BillingDocument
                                                                                  and InvLDoc.Material        = materialHist.Material
                                                                                  and InvLDoc.CompanyCode     = materialHist.CompanyCode

    inner join             I_Product                   as product                 on product.Product = InvLDoc.Product


    left outer to one join I_PT_SAFTCommodityCode      as CommodityCode           on  InvLDoc.Product           =       CommodityCode.ProductID
                                                                                  and Sig.PortugueseInvoiceDate between CommodityCode.StartDate and CommodityCode.EndDate

    left outer to one join I_PT_SAFTDangerousGoodsCode as DangerousGoodsIdnNumber on InvLDoc.Product = DangerousGoodsIdnNumber.ProductID

    left outer to one join I_PT_SAFTProductType        as ProductType             on  InvLDoc.CompanyCode = ProductType.CompanyCode
                                                                                  and product.ProductType = ProductType.MaterialType
{
  key InvLDoc.CompanyCode                                                        as CompanyCode,
  key InvLDoc.PT_SAFTDocumentNumber                                              as PT_SAFTDocumentNumber,
  key InvLDoc.PT_SAFTLineNumber                                                  as PT_SAFTLineNumber,
  key InvLDoc.Product                                                            as Product, //Material Number


      InvLDoc.SalesDocumentDate                                                  as InvoiceDate,

      cast(case when ProductType.ProductType is not initial
      then ProductType.ProductType
      else  'O'
      end   as   producttype )                                                   as ProductType,

      coalesce(materialHist._Product.ProductGroup,'')                            as ProductGroup,

      cast(case when InvLDoc._Product.ProductStandardID is not initial
        then InvLDoc._Product.ProductStandardID
       else materialHist.PortugueseMaterialWithVersion
      end   as saft_pt_productnumbercode )                                       as PT_SAFTProductNumberCode,


      //Material History

      Sig.PortugueseInvoiceDate                                                  as PortugueseInvoiceDate,
      materialHist.Material,
      cast(materialHist.PortugueseMaterialWithVersion   as  saft_pt_productcode) as PT_SAFTProductCode, //Material Number with version

      materialHist.MaterialName                                                  as ProductDescription,

      cast(CommodityCode.CommodityCode as /sapsll/comco)                         as CommodityCode,

      DangerousGoodsIdnNumber.DangerousGoodsIdnNumber
}