@AbapCatalog.sqlViewName: 'IACMAPPWRKCNTR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #INHERITED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Interface ACM Application Overview1'
@ObjectModel.supportedCapabilities: #CDS_MODELING_DATA_SOURCE
define view I_ACMApplicationOverview1
as select from I_ACMAppDocLatestVersion1 as LatestAppVersion
inner join I_ACMApplDocHeader as AppDocHeader on LatestAppVersion.ReferenceApplicationDocument = AppDocHeader.ApplicationDocument
inner join I_ACMApplicationDocItem1 as AppDocDetails on LatestAppVersion.ReferenceApplicationDocument = AppDocDetails.ApplicationDocument
and LatestAppVersion.ReferenceApplicationItem = AppDocDetails.ReferenceApplicationItem
and LatestAppVersion.ReferenceApplicationSide = AppDocDetails.ReferenceApplicationSide
and LatestAppVersion.ReferenceApplicationSubItem = substring(
AppDocDetails.ACMApplicationDocumentSubItem, 1, 6
)
inner join I_ACMSystemStatus as Status on Status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
and Status.ChangeDocumentStatusIsInactive = ''
inner join I_ACMSystemStatus as SettlementStatus on SettlementStatus.ApplicationStatusObject = AppDocDetails.ACMSettlementStatusObject
and SettlementStatus.ChangeDocumentStatusIsInactive = ''
inner join I_ACMSystemStatus as StorageStatus on StorageStatus.ApplicationStatusObject = AppDocDetails.ACMStorageStatusObject
and StorageStatus.ChangeDocumentStatusIsInactive = ''
inner join I_ACMLoadDataCaptureDet as LDCData on LDCData.LDCKey = AppDocDetails.LDCKey
inner join I_ACMAppDocRootItem as AppDocRootItem on AppDocRootItem.ApplicationDocument = AppDocDetails.ApplicationDocument
inner join I_ACMLoadDataCapQuantity as LDCQty on LDCQty.ApplicationDocument = AppDocDetails.ApplicationDocument
left outer to one join P_TradingContractHeader as ContrHeader on ContrHeader.tkonn = LatestAppVersion.TradingContractNumber
// left outer to one join P_TradingContractBusinessDets as Business on ContrHeader.tkonn = Business.TradingContractNumber
left outer to one join I_ACMAppDocBusinessData as BusinessData on BusinessData.ApplicationDocument = AppDocDetails.ApplicationDocument
and BusinessData.ReferenceApplicationItem = AppDocDetails.ReferenceApplicationItem
left outer to one join I_CreatedByUser as CreatedByName on CreatedByName.UserName = LatestAppVersion.ApplDocumentCreatedBy
left outer to one join I_CreatedByUser as ChangedByName on ChangedByName.UserName = LatestAppVersion.ApplDocumentChangedBy
left outer to one join P_SpotIndicatoreValueHelp as SpotDesc on SpotDesc.SpotIndicator = AppDocDetails.ACMApplOvwSpotFlag
and LatestAppVersion.ReferenceApplicationSubItem = substring(
AppDocDetails.ACMApplicationDocumentSubItem, 1, 6
)
left outer to one join I_ACMLoadDataCaptureWeightsDet as LDCWeightdet on LDCWeightdet.ACMRepositoryWeightAppUUID = LatestAppVersion.ACMRepositoryWeightAppUUID
and LDCWeightdet.LatestGroupVersion = LatestAppVersion.LatestGroupVersion
{
key AppDocHeader.ApplicationDocument as ApplicationDocument,
key LatestAppVersion.ReferenceApplicationItem as ReferenceApplicationItem,
LatestAppVersion.ContractApplicationUUID,
LatestAppVersion.ContractApplicationVersion,
AppDocHeader.ReferenceDocumentType as ApplicationDocType,
LatestAppVersion.ReferenceApplicationSide,
LatestAppVersion.ReferenceApplicationSubItem,
LatestAppVersion.DeliveryDate,
LatestAppVersion.TradingContractNumber as TradingContractNumber,
LatestAppVersion.TradingContractItem,
AppDocDetails.LoadDataCaptureObjId as LoadDataCaptureObjId,
LDCData.ACMLoadDataCaptureEventKeyUUID,
LDCData.EventDate,
LDCData.BillOfLadingDate,
LDCData.BillOfLading,
LDCData.ACMScaleTicketNumber,
LDCData.Material,
LDCData.Commodity,
Status.StatusProfileName as ACMAppDocStatus,
SettlementStatus.StatusProfileName as ACMAppDocSettlementStatus,
StorageStatus.StatusProfileName as ACMAppDocStorageStatus,
AppDocDetails.Counterparty as Counterparty,
AppDocDetails.Plant as Plant,
LatestAppVersion.ApplDocumentCreatedBy as ApplDocumentCreatedBy,
CreatedByName.UserDescription as ApplDocumentCreatedByName,
LatestAppVersion.ApplicationItemCreatedOnDate,
LatestAppVersion.ApplDocumentCreatedBy as ApplDocumentChangedBy,
ChangedByName.UserDescription as ApplDocumentChangedByName,
LatestAppVersion.ApplicationItemChangedOnDate,
ContrHeader.tctyp as TradingContractType,
BusinessData.CompanyCode,
BusinessData.SalesOrganization,
BusinessData.DistributionChannel,
BusinessData.Division,
BusinessData.PurchasingOrganization,
BusinessData.PurchasingGroup,
// LDC Unconverted Qty - Item_qty from the root item
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
case
when AppDocDetails.ACMAppDocBaseUoM = LDCWeightdet.ACMLoadDataCaptureUoM
then LDCQty.ACMLoadDataCapQuantity
when LatestAppVersion.ConversionFactorNmrtrValue <> 0
then division( (LDCQty.ACMLoadDataCapQuantity * LatestAppVersion.ConversionFactorDnmntrValue) ,LatestAppVersion.ConversionFactorNmrtrValue, 3 )
when AppDocDetails.ConversionFactorNmrtrValue <> 0
then division( (LDCQty.ACMLoadDataCapQuantity * AppDocDetails.ConversionFactorDnmntrValue) ,AppDocDetails.ConversionFactorNmrtrValue, 3 )
else LDCQty.ACMLoadDataCapQuantity
end as ACMLoadDataCapQuantity,
@Semantics.unitOfMeasure: true
AppDocDetails.UnitOfMeasure,
/* uom of LDC unconverted qty */
// LDC Converted Qty
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocRootItem.ACMLoadDataCaptureConvertQty,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocDetails.ACMDecimalGrossQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocDetails.ACMNetQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
LatestAppVersion.ACMOverfillQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
LatestAppVersion.ACMUnderFillQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMLoadDataCaptureUoM'
LDCWeightdet.ACMLoadDtaCaptureGrossQuantity,
@Semantics.unitOfMeasure: true
AppDocDetails.ACMAppDocBaseUoM,
AppDocDetails.ACMApplOvwSpotFlag,
SpotDesc.SpotIndicatorDesc as ACMApplOvwSpotFlagDesc,
LDCWeightdet.ACMLoadDataCaptureUoM
}
where
Status.StatusProfileName <> 'I7A06' //Eliminate Cancelled App Docs
and LatestAppVersion.ContractApplicationStatus <> '9'
union
select distinct from I_ACMApplDocHeader as AppDocHeader
inner join I_ACMApplicationDocItem1 as AppDocDetails on AppDocHeader.ApplicationDocument = AppDocDetails.ApplicationDocument
inner join I_ACMSystemStatus as Status on Status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
and Status.ChangeDocumentStatusIsInactive = ''
inner join I_ACMSystemStatus as SettlementStatus on SettlementStatus.ApplicationStatusObject = AppDocDetails.ACMSettlementStatusObject
and SettlementStatus.ChangeDocumentStatusIsInactive = ''
inner join I_ACMSystemStatus as StorageStatus on StorageStatus.ApplicationStatusObject = AppDocDetails.ACMStorageStatusObject
and StorageStatus.ChangeDocumentStatusIsInactive = ''
inner join I_ACMLoadDataCaptureDet as LDCData on LDCData.LDCKey = AppDocDetails.LDCKey
inner join I_ACMAppDocRootItem as AppDocRootItem on AppDocRootItem.ApplicationDocument = AppDocDetails.ApplicationDocument
inner join I_ACMLoadDataCapQuantity as LDCQty on LDCQty.ApplicationDocument = AppDocDetails.ApplicationDocument
left outer to one join I_CreatedByUser as CreatedByName on CreatedByName.UserName = AppDocDetails.ApplDocumentCreatedBy
left outer to one join I_CreatedByUser as ChangedByName on ChangedByName.UserName = AppDocDetails.ApplDocumentCreatedBy
left outer to one join P_SpotIndicatoreValueHelp as SpotDesc on SpotDesc.SpotIndicator = AppDocDetails.ACMApplOvwSpotFlag
left outer to one join P_TradingContractOverview as ACMContractData on ACMContractData.TradingContractNumber = AppDocHeader.ApplicationDocument
// left outer to one join P_ACMLDCWeightsDet as LDCWeightdet on LDCWeightdet.ACMEventKeyUUID = LDCData.ACMLoadDataCaptureEventKeyUUID
{
key AppDocHeader.ApplicationDocument as ApplicationDocument,
key AppDocDetails.ReferenceApplicationItem as ReferenceApplicationItem,
AppDocDetails.ContractApplicationItemUUID as ContractApplicationUUID,
cast( '' as /accgo/e_appl_version ) as ContractApplicationVersion,
AppDocHeader.ReferenceDocumentType as ApplicationDocType,
AppDocDetails.ReferenceApplicationSide as ReferenceApplicationSide,
substring(AppDocDetails.ACMApplicationDocumentSubItem, 1,6) as ReferenceApplicationSubItem,
AppDocDetails.DeliveryDate as DeliveryDate,
cast( '0000000000' as tkonn) as TradingContractNumber,
cast( '000000' as tposn) as TradingContractItem,
AppDocDetails.LoadDataCaptureObjId as LoadDataCaptureObjId,
LDCData.ACMLoadDataCaptureEventKeyUUID,
LDCData.EventDate,
LDCData.BillOfLadingDate,
LDCData.BillOfLading,
LDCData.ACMScaleTicketNumber,
LDCData.Material,
LDCData.Commodity,
Status.StatusProfileName as ACMAppDocStatus,
SettlementStatus.StatusProfileName as ACMAppDocSettlementStatus,
StorageStatus.StatusProfileName as ACMAppDocStorageStatus,
AppDocDetails.Counterparty as Counterparty,
AppDocDetails.Plant as Plant,
AppDocDetails.ApplDocumentCreatedBy as ApplDocumentCreatedBy,
CreatedByName.UserDescription as ApplDocumentCreatedByName,
AppDocDetails.ApplicationItemCreatedOnDate,
AppDocDetails.ApplDocumentChangedBy as ApplDocumentChangedBy,
ChangedByName.UserDescription as ApplDocumentChangedByName,
AppDocDetails.ApplicationItemChangedOnDate,
'' as TradingContractType,
ACMContractData.CompanyCode,
'' as SalesOrganization,
'' as DistributionChannel,
'' as Division,
'' as PurchasingOrganization,
'' as PurchasingGroup,
// LDC Unconverted Qty - Item_qty from the root item
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
case
when AppDocDetails.ACMAppDocBaseUoM = '' //LDCWeightdet.ACMLoadDataCaptureUoM
then LDCQty.ACMLoadDataCapQuantity
when AppDocDetails.ConversionFactorNmrtrValue <> 0
then division( (LDCQty.ACMLoadDataCapQuantity * AppDocDetails.ConversionFactorDnmntrValue) ,AppDocDetails.ConversionFactorNmrtrValue, 3 )
else LDCQty.ACMLoadDataCapQuantity
end as ACMLoadDataCapQuantity,
@Semantics.unitOfMeasure: true
AppDocDetails.UnitOfMeasure,
/* uom of LDC unconverted qty */
// LDC Converted Qty
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocRootItem.ACMLoadDataCaptureConvertQty,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocDetails.ACMDecimalGrossQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
AppDocDetails.ACMNetQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
cast(0 as abap.dec(13,3) ) as ACMOverfillQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMAppDocBaseUoM'
cast(0 as abap.dec(13,3) ) as ACMUnderFillQuantity,
@Semantics.quantity.unitOfMeasure: 'ACMLoadDataCaptureUoM'
//LDCWeightdet.ACMLoadDtaCaptureGrossQuantity,
cast(0 as abap.dec( 13,3 ) ) as ACMLoadDtaCaptureGrossQuantity,
@Semantics.unitOfMeasure: true
AppDocDetails.ACMAppDocBaseUoM,
AppDocDetails.ACMApplOvwSpotFlag,
SpotDesc.SpotIndicatorDesc as ACMApplOvwSpotFlagDesc,
//LDCWeightdet.ACMLoadDataCaptureUoM
cast( '' as meins ) as ACMLoadDataCaptureUoM
}
where
Status.StatusProfileName <> 'I7A04' //To eliminate Finally Applied App Docs
and Status.StatusProfileName <> 'I7A03' //To eliminate Provisionally Applied App Docs
and Status.StatusProfileName <> 'I7A15' //To eliminate Return Completed App Docs
and Status.StatusProfileName <> 'I7A20' //to eliminate spot records
and Status.StatusProfileName <> 'I7A13'
and Status.StatusProfileName <> 'I7A08' //To eliminate Collated App Docs
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACMAPPDOCBUSINESSDATA",
"I_ACMAPPDOCLATESTVERSION1",
"I_ACMAPPDOCROOTITEM",
"I_ACMAPPLDOCHEADER",
"I_ACMAPPLICATIONDOCITEM1",
"I_ACMLOADDATACAPQUANTITY",
"I_ACMLOADDATACAPTUREDET",
"I_ACMLOADDATACAPTUREWEIGHTSDET",
"I_ACMSYSTEMSTATUS",
"I_CREATEDBYUSER",
"P_SPOTINDICATOREVALUEHELP",
"P_TRADINGCONTRACTHEADER",
"P_TRADINGCONTRACTOVERVIEW"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/