@AbapCatalog.sqlViewName: 'PCCRELDOCVAL2'
@VDM.viewType: #CONSUMPTION
@VDM.private : true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
define view P_CntrlContrRelDocnValues
with parameters
P_DisplayCurrency : displaycurrency,
P_StartDate : bedat,
P_EndDate : bedat
as select from I_CntrlPurchaseContractItem as CentralContractItem
inner join R_CentralPurchaseContract as CentralContract on CentralContractItem.CentralPurchaseContract = CentralContract.CentralPurchaseContract
inner join I_CntrlPurContrDistribution as Distribution on CentralContractItem.CentralPurchaseContract = Distribution.CentralPurchaseContract
and CentralContractItem.CentralPurchaseContractItem = Distribution.CentralPurchaseContractItem
left outer join I_CntrlPurContrReleaseHistory as ReleaseHistory on CentralContractItem.CentralPurchaseContract = ReleaseHistory.CentralPurchaseContract
and CentralContractItem.CentralPurchaseContractItem = ReleaseHistory.CentralPurchaseContractItem
and Distribution.DistributionKey = ReleaseHistory.DistributionKey
association [1..1] to I_CntrlPurContrClassification as ContractClassification on CentralContract.PurchaseContractType = ContractClassification.PurchasingDocumentType
{
key CentralContractItem.CentralPurchaseContract,
key CentralContractItem.CentralPurchaseContractItem,
key Distribution.DistributionKey,
CentralContract.PurchasingDocumentCategory,
PurchasingDocumentItemCategory,
CentralContract.PurchasingOrganization,
CentralContract.PurchasingGroup,
CentralContract.Supplier,
CentralContract.DocumentCurrency,
CentralContract.CreationDate,
CentralContract.ValidityStartDate,
CentralContract.ValidityEndDate,
CentralContract.PurchaseContractType,
CentralContract.CompanyCode,
CentralContract.CreatedByUser,
:P_DisplayCurrency as DisplayCurrency,
CentralContract.PurgDocChangeRequestStatus,
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( case when CreationDate is not null and CreationDate != '00000000'
and CentralContractItem.NetPriceQuantity > 0 and Distribution.TargetQuantity > 0
then cast(currency_conversion(
amount => CentralContractItem.ContractNetPriceAmount ,
source_currency => CentralContractItem.DocumentCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => CreationDate )
as mm_a_target_amount) *
cast( division( Distribution.TargetQuantity, CentralContractItem.NetPriceQuantity, 3 ) as ktmng )
else 0 end as mm_a_target_amount ) as TargetAmount,
@Semantics.amount.currencyCode: 'DisplayCurrency'
@DefaultAggregation: #SUM
case when ReleaseHistory.ReleaseOrderDate is not null and ReleaseHistory.ReleaseOrderDate != '00000000'
then currency_conversion( amount => ReleaseHistory.ReleaseOrderItemNetAmount,
source_currency => ReleaseHistory.ReleaseOrderCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => ReleaseHistory.ReleaseOrderDate )
else 0 end as ReleaseOrderItemNetAmount,
CentralContract.PurchasingDocumentSubtype
}
where
CentralContract.ValidityEndDate >= $parameters.P_StartDate
and CentralContract.ValidityEndDate <= $parameters.P_EndDate
and CentralContractItem.PurchasingContractDeletionCode = ''
and(
ContractClassification.PurchaseContractClassification = '1'
or ContractClassification.PurchaseContractClassification = '0'
)
and ( CentralContract.PurgDocChangeRequestStatus = '' or CentralContract.PurgDocChangeRequestStatus = '1' )
and ( CentralContract.PurchasingDocumentSubtype = 'C' or CentralContract.PurchasingDocumentSubtype is initial )
union all select from I_CntrlPurchaseContractItem as CentralContractItem
inner join R_CentralPurchaseContract as CentralContract on CentralContractItem.CentralPurchaseContract = CentralContract.CentralPurchaseContract
left outer join I_CntrlPurContrClassification as ContractClassification on CentralContract.PurchaseContractType = ContractClassification.PurchasingDocumentType
{
key CentralContractItem.CentralPurchaseContract,
key CentralContractItem.CentralPurchaseContractItem,
key cast ('' as mm_purgdoc_distr_num) as DistributionKey,
CentralContract.PurchasingDocumentCategory,
PurchasingDocumentItemCategory,
CentralContract.PurchasingOrganization,
CentralContract.PurchasingGroup,
CentralContract.Supplier,
CentralContract.DocumentCurrency,
CentralContract.CreationDate,
CentralContract.ValidityStartDate,
CentralContract.ValidityEndDate,
CentralContract.PurchaseContractType,
CentralContract.CompanyCode,
CentralContract.CreatedByUser,
:P_DisplayCurrency as DisplayCurrency,
CentralContract.PurgDocChangeRequestStatus,
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( 0 as mm_a_target_amount ) as TargetAmount,
@Semantics.amount.currencyCode: 'DisplayCurrency'
@DefaultAggregation: #SUM
cast( 0 as netwr ) as ReleaseOrderItemNetAmount,
CentralContract.PurchasingDocumentSubtype
}
where
CentralContract.ValidityEndDate >= $parameters.P_StartDate
and CentralContract.ValidityEndDate <= $parameters.P_EndDate
and CentralContract.PurchasingDocumentDeletionCode = ''
and CentralContract.PurchasingDocumentSubtype = 'H'
and(
ContractClassification.PurchaseContractClassification = '1'
or ContractClassification.PurchaseContractClassification = '0'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CNTRLPURCHASECONTRACTITEM",
"I_CNTRLPURCONTRCLASSIFICATION",
"I_CNTRLPURCONTRDISTRIBUTION",
"I_CNTRLPURCONTRRELEASEHISTORY",
"R_CENTRALPURCHASECONTRACT"
],
"ASSOCIATED":
[
"I_CNTRLPURCONTRCLASSIFICATION"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/