@AbapCatalog.sqlViewName: 'PPROJPROCMTPRPO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
define view P_ProjProcmtPurReqnOrd
as select from I_PurchaseReqnAcctAssgmt as _PR
left outer join I_Purchaserequisitionitem as _PRDetails on _PR.PurchaseRequisition = _PRDetails.PurchaseRequisition
and _PR.PurchaseRequisitionItem = _PRDetails.PurchaseRequisitionItem
// association [0..1] to I_Purchaserequisitionitem as _PRDetails on $projection.PurchaseRequisition = _PRDetails.PurchaseRequisition
// and $projection.PurchaseRequisitionItem = _PRDetails.PurchaseRequisitionItem
association [0..1] to I_WBSElement as _WBSElement on $projection.WBSElementInternalID = _WBSElement.WBSElementInternalID
association [0..1] to I_PurchaseOrderItem as _POStatus on _PRDetails.PurchasingDocument = _POStatus.PurchaseOrder
and _PRDetails.PurchasingDocumentItem = _POStatus.PurchaseOrderItem
{
key WBSElementInternalID,
key _PR.PurchaseRequisition,
key _PR.PurchaseRequisitionItem,
key _PRDetails.PurchasingDocument as PurchaseOrder,
key _PRDetails.PurchasingDocumentItem as PurchaseOrderItem,
key PurReqnAcctAssgmtNmbr as AccountAssignmentNumber,
_PR.ProfitCenter as ProfitCenter,
_WBSElement.Project,
_WBSElement.ProjectType,
_WBSElement._Project.ProjectProfileCode,
_WBSElement.CompanyCode,
_WBSElement.ControllingArea,
_WBSElement.ProfitCenter as WBSElementProfitCenter,
_WBSElement.WBSElement,
_WBSElement.WBSDescription,
_WBSElement._Project.ProjectDescription,
_WBSElement.ProjectInternalID,
concat(concat(_PR.PurchaseRequisition, '/'), _PR.PurchaseRequisitionItem) as FormattedPurchasingDocItem,
_PRDetails.PurchaseRequisitionType as PurchasingDocumentType,
_PRDetails.PurchasingDocumentCategory,
_PRDetails.PurchaseRequisitionItemText,
_PRDetails.PurReqnReleaseStatus as PurchasingReleaseStatus,
_PRDetails.CreationDate,
_PRDetails.Supplier as Supplier,
_PRDetails.Material,
_PRDetails.MaterialGroup,
_PRDetails.Plant,
_PRDetails.PurchasingOrganization,
_PRDetails.PurchasingGroup,
_PRDetails.StorageLocation,
_PRDetails.OrderedQuantity as OrderedQuantity,
_PRDetails.RequestedQuantity as TotalQuantity,
//*******All the case statement available in this stack are modified by removing the ELSE condition
//*******to improve the performance as part of CE 2308 Release
case
when _PRDetails.PurReqnPriceQuantity = 0
then cast(_PRDetails.PurchaseRequisitionPrice * _PRDetails.RequestedQuantity as abap.curr( 25, 2 ))
//else
when _PRDetails.PurReqnPriceQuantity <> 0 and _PRDetails.RequestedQuantity <> 0
then
cast(division(
cast(_PRDetails.PurchaseRequisitionPrice * _PRDetails.RequestedQuantity as abap.dec( 25, 10 )),
cast(_PRDetails.PurReqnPriceQuantity as abap.dec( 25, 10 )),5
) as abap.curr( 25, 2 ))
when _PRDetails.PurReqnPriceQuantity <> 0 and _PRDetails.RequestedQuantity = 0
then _PRDetails.PurchaseRequisitionPrice //2002 fix for limit PR -> net amount = pur req price
end as NetAmount,
_PRDetails.DeliveryDate as DeliveryDate,
_PRDetails.PurReqnItemCurrency as DocumentCurrency,
_PRDetails.BaseUnit as OrderQuantityUnit,
'PR' as PurchasingDocumentPartnerType,
cast(' ' as abap.char( 1 )) as IsCompletelyDelivered,
_PRDetails.DeliveryDate as ScheduleLineDeliveryDate,
// Correction to fix duplicate records fetching for ScheduleLine - Begin
_PRDetails.DeliveryDate as ScheduleLineLatestDeliveryDate,
// Correction to fix duplicate records fetching for ScheduleLine – End
_PRDetails.AccountAssignmentCategory
// _PRDetails._PurchaseOrder._PurchaseOrderItem.IsCompletelyDelivered
}
where
(
(
_PRDetails.OrderedQuantity != _PRDetails.RequestedQuantity
)
or(
_PRDetails.RequestedQuantity = 0
and _PRDetails.PurchasingDocument is not initial
and _POStatus.IsCompletelyDelivered != 'X'
)
or(
_PRDetails.RequestedQuantity = 0
and _PRDetails.PurchasingDocument is initial
)
)
//and _PRDetails.PurchasingDocumentItemCategory = 'A' //2002 fix to display the limit PR items
and _PR.IsDeleted != 'X'
and WBSElementInternalID != '00000000'
and _PRDetails.IsDeleted != 'X'
and _PRDetails.IsClosed != 'X'
and _PRDetails.IsPurReqnBlocked = ' '
and _PRDetails.IsPurReqnCmplt != 'X'
union all select from I_PurOrdAccountAssignment as _PO
association [1..1] to I_PurchaseOrder as _PurchaseOrder on $projection.PurchaseOrder = _PurchaseOrder.PurchaseOrder
association to I_ProjProcmtPOSchedLinesDte as _DelDate on $projection.PurchaseOrder = _DelDate.PurchaseOrder
and $projection.PurchaseOrderItem = _DelDate.PurchaseOrderItem
association to I_ProjProcmtPurOrdSchedLines as _SchedLines on $projection.PurchaseOrder = _SchedLines.PurchaseOrder
and $projection.PurchaseOrderItem = _SchedLines.PurchaseOrderItem
association [0..1] to I_WBSElement as _WBSElement on $projection.WBSElementInternalID = _WBSElement.WBSElementInternalID
{
key WBSElementInternalID,
key _PurchaseOrderItem.PurchaseRequisition,
key _PurchaseOrderItem.PurchaseRequisitionItem,
key PurchaseOrder,
key PurchaseOrderItem,
key AccountAssignmentNumber as AccountAssignmentNumber,
_PO.ProfitCenter as ProfitCenter,
_WBSElement.Project,
_WBSElement.ProjectType,
_WBSElement._Project.ProjectProfileCode,
_WBSElement.CompanyCode,
_WBSElement.ControllingArea,
_WBSElement.ProfitCenter as WBSElementProfitCenter,
_WBSElement.WBSElement,
_WBSElement.WBSDescription,
_WBSElement._Project.ProjectDescription,
_WBSElement.ProjectInternalID,
concat(concat(PurchaseOrder, '/'), PurchaseOrderItem) as FormattedPurchasingDocItem,
_PurchaseOrder.PurchaseOrderType as PurchasingDocumentType,
_PurchaseOrderItem.PurchaseOrderCategory as PurchasingDocumentCategory,
_PurchaseOrderItem.PurchaseOrderItemText as PurchaseRequisitionItemText,
cast(' ' as abap.char(8)) as PurchasingReleaseStatus,
_PurchaseOrder.CreationDate,
_PurchaseOrder.Supplier as Supplier,
_PurchaseOrderItem.Material,
_PurchaseOrderItem.MaterialGroup,
_PurchaseOrderItem.Plant,
_PurchaseOrder.PurchasingOrganization,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrderItem.StorageLocation,
_DelDate.RoughGoodsReceiptQty as OrderedQuantity,
// _PurchaseOrderItem.OrderQuantity as TotalQuantity,
// _PurchaseOrderItem.NetAmount as NetAmount,
/* When the PO is split for the same account assignment twice, the Amount for each Account Assignment is calculated as the Net Amount*/
_PO.Quantity as TotalQuantity,
case
when _PO.MultipleAcctAssgmtDistrPercent <> 0
then div(_PurchaseOrderItem.NetAmount * _PO.MultipleAcctAssgmtDistrPercent,100)
when _PO.MultipleAcctAssgmtDistrPercent = 0
then _PurchaseOrderItem.NetAmount end as NetAmount,
case
when _PurchaseOrderItem.IsCompletelyDelivered = 'X' and _DelDate.DeliveryDate = '99991231'
then _SchedLines.ScheduleLineDeliveryDate
when _PurchaseOrderItem.IsCompletelyDelivered <> 'X' and _DelDate.DeliveryDate <> '99991231'
then _DelDate.DeliveryDate
end as DeliveryDate,
_PurchaseOrderItem.DocumentCurrency as DocumentCurrency,
_PurchaseOrderItem.PurchaseOrderQuantityUnit as OrderQuantityUnit,
'PO' as PurchasingDocumentPartnerType,
_PurchaseOrderItem.IsCompletelyDelivered,
_SchedLines.ScheduleLineDeliveryDate as ScheduleLineDeliveryDate,
// Correction to fix duplicate record for ScheduleLine - Begin
_DelDate.ScheduleLineLatestDeliveryDate as ScheduleLineLatestDeliveryDate,
// Correction to fix duplicate record for ScheduleLine - End
_PurchaseOrderItem.AccountAssignmentCategory
}
where
// _PurchaseOrderItem.AccountAssignmentCategory = 'P'
//and
IsDeleted != 'X'
and WBSElementInternalID != '00000000'
and _PurchaseOrder.PurchasingDocumentDeletionCode = ''
and _PurchaseOrderItem.PurchasingDocumentDeletionCode = ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PROJECTBYINTERNALKEY",
"I_PROJPROCMTPOSCHEDLINESDTE",
"I_PROJPROCMTPURORDSCHEDLINES",
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM",
"I_PURCHASEREQNACCTASSGMT",
"I_PURCHASEREQUISITIONITEM",
"I_PURORDACCOUNTASSIGNMENT",
"I_WBSELEMENT"
],
"ASSOCIATED":
[
"I_PURCHASEORDERITEM",
"I_WBSELEMENT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/