@AbapCatalog: { sqlViewName: 'PMMCNTRITMMNTR',
compiler.compareFilter: true
}
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'Contract Item Monitoring'
@VDM: { private: true,
viewType: #CONSUMPTION
}
define view P_ContractItemMonitoring
with parameters
P_DisplayCurrency : displaycurrency
as select from I_PurchaseContractItem as PurchaseContractItem
association [0..1] to P_PurContrItmCalcdFields as _ItmCalcdFields on $projection.PurchaseContract = _ItmCalcdFields.PurchaseContract
and $projection.PurchaseContractItem = _ItmCalcdFields.PurchaseContractItem
// /* Association to _ContractAmount to retrieve casted values for currency conversion */
association [0..1] to P_CONTRACTAMOUNT as _CONTRACTAMOUNT on _CONTRACTAMOUNT.PurchaseContract = $projection.PurchaseContract
and _CONTRACTAMOUNT.PurchaseContractItem = $projection.PurchaseContractItem
association [0..1] to I_AccountAssignmentCategory as _AccountAssignmentCategory on $projection.AccountAssignmentCategory = _AccountAssignmentCategory.AccountAssignmentCategory
association [0..1] to I_PurgDocumentItemCategory as _PurgDocumentItemCategory on $projection.PurchasingDocumentItemCategory = _PurgDocumentItemCategory.PurchasingDocumentItemCategory
association [0..1] to I_ProcmtBlockedSupplierTxt as _DomainText on _DomainText.PurchasingIsBlockedForSupplier = 'X'
and _DomainText.Language = $session.system_language
and _DomainText.DomainValue = 'X'
// association [0..1] to P_PurContrValdtySts as _ValdtySts on $projection.PurchaseContract = _ValdtySts.PurchaseContract
{
key PurchaseContractItem.PurchaseContract as PurchaseContract,
key PurchaseContractItem.PurchaseContractItem as PurchaseContractItem,
AccountAssignmentCategory,
_AccountAssignmentCategory._Text [1: Language = $session.system_language ].AcctAssignmentCategoryName,
PurchasingDocumentItemCategory,
_PurgDocumentItemCategory._Text[1: Language = $session.system_language ].PurgDocExternalItemCategory,
cast(_PurgDocumentItemCategory._Text[1: Language = $session.system_language ].PurgDocItemCategoryName as vdm_purdocitencategoryname) as PurgDocItemCategoryName,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).Supplier as Supplier,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurchasingGroup as PurchasingGroup,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurchasingOrganization as PurchasingOrganization,
// CompanyCode,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).CompanyCode as CompanyCode,
_PurchaseContract.PurchaseContractType,
Material,
IsStatisticalItem,
PurchaseContractItem.PurchaseContractItemText,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).MaterialGroup as MaterialGroup,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).Plant as Plant,
_PurchaseContract.ReleaseCode as ReleaseCode,
_PurchaseContract.CreationDate,
_PurchaseContract.CreatedByUser,
_PurchaseContract._CreatedByUser.UserDescription,
// _PurchaseContract.ValidityStartDate,
//
// _PurchaseContract.ValidityEndDate,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).ValidityStartDate as ValidityStartDate,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).ValidityEndDate as ValidityEndDate,
case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).ValidityEndDate < $session.system_date //and ContractStatus.PurchaseContractValidityStatus > 1.000
then '03'
-- when ValidityEndDate >= $session.system_date and PurchaseContractValidityStatus > 0.900
when _PurchaseContract.ValidityEndDate >= $session.system_date
and _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).NoOfDaysFromValidityStart >
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).NoOfDaysBtwnValidityStrtAndEnd
then '01'
// when ValidityEndDate is not null then '02'
else '02'
end as PurchaseContractValidityStatus,
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
TargetQuantity as TargetQuantity,
// Currecncy conversion function for Target Amount
@Semantics.amount.currencyCode: 'DisplayCurrency'
@DefaultAggregation: #SUM
cast(currency_conversion( amount => _CONTRACTAMOUNT.TargetAmount,
source_currency => DocumentCurrency,
round => 'X',
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => _PurchaseContract.CreationDate,
error_handling => 'SET_TO_NULL' ) as abap.curr(21,5) ) as TargetAmount,
_ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency , P_Date: $session.system_date ).ReleaseOrderDate as ReleaseOrderDate,
cast( case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurContrLatestRelease is not null
then case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurContrLatestRelease >= 0
and _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurContrLatestRelease <= 28 // Last 4 weeks
then '01'
when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurContrLatestRelease > 28
and _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).PurContrLatestRelease <= 365 // 2-12 months
then '02'
else '03' end // > 12 months
else '04' end as mm_contr_latest_release_id ) // None
as PurContrLatestRelease,
//Fields needed for Total Value Calculation S4CE 2002
OrdPriceUnitToOrderUnitDnmntr,
OrderPriceUnitToOrderUnitNmrtr,
// @Semantics.amount.currencyCode: 'DocumentCurrency'
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(currency_conversion( amount => _CONTRACTAMOUNT.ContractNetPriceAmount,
source_currency => DocumentCurrency,
round => 'X',
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => _PurchaseContract.CreationDate,
error_handling => 'SET_TO_NULL' ) as abap.curr(21,5) ) as ContractNetPriceAmount,
@Semantics.quantity.unitOfMeasure: 'OrderPriceUnit'
NetPriceQuantity,
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(currency_conversion( amount => _CONTRACTAMOUNT.PurchaseContractTargetAmount,
source_currency => DocumentCurrency,
round => 'X',
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => _PurchaseContract.CreationDate,
error_handling => 'SET_TO_NULL' ) as abap.curr(21,5) ) as PurchaseContractTargetAmount,
StorageLocation,
@Semantics.text: true
_StorageLocation.StorageLocationName,
TaxCode,
@Semantics.unitOfMeasure: true
OrderPriceUnit,
@Semantics.unitOfMeasure: true
OrderQuantityUnit,
@Semantics.currencyCode: true
cast( $parameters.P_DisplayCurrency as displaycurrency ) as DisplayCurrency,
@Semantics.currencyCode: true
DocumentCurrency,
RequirementTracking,
PurchasingInfoRecord,
// @Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
// case when _ReleasedQuantity.QuantityReleasedTillDate > TargetQuantity
// then cast ('0' as abap.quan(13,3))
// when _ReleasedQuantity.QuantityReleasedTillDate is null or _ReleasedQuantity.QuantityReleasedTillDate < 0
// then TargetQuantity
// else
// cast (TargetQuantity - _ReleasedQuantity.QuantityReleasedTillDate as abap.quan(13,3))
// end as OpenTargetQuantity,
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).QuantityReleasedTillDate > TargetQuantity
then cast ('0' as abap.quan(13,3))
when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).QuantityReleasedTillDate is null
or _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).QuantityReleasedTillDate < 0
then TargetQuantity
else
cast (TargetQuantity - _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).QuantityReleasedTillDate as abap.quan(13,3))
end as OpenTargetQuantity,
case when
_PurchaseContract._Supplier.PurchasingIsBlocked = 'X'
then
_DomainText.SupplierName
else
' '
end as PurchasingIsBlockedForSupplier,
@Semantics.amount.currencyCode: 'DisplayCurrency'
case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).ReleaseOrderItemNetAmount is null
then cast( 0 as abap.curr(21,5))
else _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).ReleaseOrderItemNetAmount end as ReleaseOrderItemNetAmount,
@Semantics.amount.currencyCode: 'DisplayCurrency'
case when _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).InvoicedAmountInDspCurrency is null //( P_DisplayCurrency: $parameters.P_DisplayCurrency )
then cast( 0 as abap.curr(21,5))
else _ItmCalcdFields( P_DisplayCurrency: $parameters.P_DisplayCurrency, P_Date: $session.system_date ).InvoicedAmountInDspCurrency end as InvoicedAmountInDspCurrency,
_PurchaseContract
}
where
(
PurchaseContractItem.PurchasingContractDeletionCode = ' '
and PurchaseContractItem._PurchaseContract.PurchasingDocumentDeletionCode = ' '
and(
(
PurchaseContractItem._PurchaseContract.Supplier = ' '
)
or(
PurchaseContractItem._PurchaseContract._Supplier.IsBusinessPurposeCompleted <> 'X'
)
)
// filter out statistical Items (excluding Retail i.e Material = '')
and not(
PurchaseContractItem.Material = ''
and PurchaseContractItem.IsStatisticalItem = 'X'
)
//// Filter out version enabled documents and Revision documents.
// and ( _PurchaseContract.VersionIsEnabled = '' )
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTASSIGNMENTCATEGORY",
"I_ACCTASSIGNMENTCATEGORYTEXT",
"I_PROCMTBLOCKEDSUPPLIERTXT",
"I_PURCHASECONTRACT",
"I_PURCHASECONTRACTITEM",
"I_PURGDOCUMENTITEMCATEGORY",
"I_PURGDOCUMENTITEMCATEGORYTEXT",
"I_STORAGELOCATION",
"I_SUPPLIER",
"I_USER",
"P_CONTRACTAMOUNT",
"P_PURCONTRITMCALCDFIELDS"
],
"ASSOCIATED":
[
"I_ACCOUNTASSIGNMENTCATEGORY",
"I_PROCMTBLOCKEDSUPPLIERTXT",
"I_PURCHASECONTRACT",
"I_PURGDOCUMENTITEMCATEGORY",
"P_CONTRACTAMOUNT",
"P_PURCONTRITMCALCDFIELDS"
],
"BASE":
[
"I_PURCHASECONTRACTITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/