@AbapCatalog.sqlViewName: 'CSHPDPRDVRWEEK'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Production Versions In Week Bucket'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@UI.headerInfo: {
description: { type: #STANDARD, value: 'Operationseqno' },
title: { type: #STANDARD, value: 'OrderID' },
typeName: 'Available Time Buckets',
typeNamePlural: 'Available Time Buckets' }
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #D
@Search.searchable: false
define view C_SchedProdnProdVersWeek
as select from I_ProductionVersion as _prodvers
// inner join I_BOOMaterialAssgmtChangeState as _MaterialTaskList on _prodvers.BillOfOperationsGroup = _MaterialTaskList.BillOfOperationsGroup
// and _MaterialTaskList.BillOfOperationsType = _prodvers.BillOfOperationsType
// and _prodvers.BillOfOperationsVariant = _MaterialTaskList.BillOfOperationsVariant
// and _MaterialTaskList.IsDeleted = ''
// inner join I_BOOSqncOperationAssgmtChgSt as _OperationActivities on _MaterialTaskList.BillOfOperationsGroup = _OperationActivities.BillOfOperationsGroup
// and _MaterialTaskList.BillOfOperationsVariant = _OperationActivities.BillOfOperationsVariant
// and _OperationActivities.IsDeleted = ''
// and _OperationActivities.BillOfOperationsType = _prodvers.BillOfOperationsType
// inner join I_BillOfOperationsOpBasic as _OperationActTasks on _MaterialTaskList.BillOfOperationsGroup = _OperationActTasks.BillOfOperationsGroup
// and _OperationActivities.BOOSqncOpAssgmtIntVersionCntr = _OperationActTasks.BOOOpInternalVersionCounter
// and _OperationActTasks.IsDeleted = ''
// and _OperationActTasks.BillOfOperationsType = _prodvers.BillOfOperationsType
inner join I_WorkCenter as _WorkCenter on _WorkCenter.WorkCenterIsToBeDeleted = ''
and _WorkCenter.WorkCenter = _prodvers.ProductionLine
and _WorkCenter.Plant = _prodvers.Plant
and _WorkCenter.WorkCenterTypeCode = 'A'
inner join I_Capacity as _Capacity on _WorkCenter.CapacityInternalID = _Capacity.CapacityInternalID
and _Capacity.CapacityIsFinite = 'X'
left outer join I_SchedProdnOperations as _order on _order.Product = _prodvers.Material
and _order.Plant = _prodvers.Plant
left outer to one join I_WrkCtrUtilznPrsnlzn as _prsnlzn on _prsnlzn.UserName = $session.user
left outer to one join I_CapPlngWrkCtrGrpRespyArea as _wrkgrpaor on _wrkgrpaor.WorkCenterInternalID = _WorkCenter.WorkCenterInternalID
and _wrkgrpaor.Plant = _prodvers.Plant
and _wrkgrpaor.UserID = $session.user
//// association [1..1] to I_Plant as _plant on _Plant.Plant = _order.plant
// association [0..1] to ppcfs_detail_h as _ppdsdetail on user_name = $session.user
association [0..1] to I_UnitOfMeasure as _Uom on _Uom.UnitOfMeasure = 'H'
association [1..*] to I_CalendarDate as _calenderdate on _calenderdate.CalendarDate >= _prodvers.ValidityStartDate
and _calenderdate.CalendarDate <= _prodvers.ValidityEndDate
and _calenderdate.WeekDay = '7' // to get only one row per calender week
association [0..1] to C_SchedProdnOrderCategoryVH as _OrderCategory on $projection.OrderCategory = _OrderCategory.OrderCategory
association [0..*] to I_WorkCenterText as _WorkCenterText on $projection.WorkCenterInternalID = _WorkCenterText.WorkCenterInternalID
association [1..1] to I_SOSTypeText as _SOSTypeText on _SOSTypeText.SOSType = 'VERID'
and _SOSTypeText.Language = $session.system_language
{
key _order.SimulationSessionID,
key _order.OrderID,
key _order.CapacityRequirementItem,
key _order.OrderCategory,
key _order.CapacityRqmtItemCapacity,
key _order.CapacityRequirement,
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
key _prodvers.Material as Product,
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
key _prodvers.Plant as Plant,
@EndUserText: {
label: 'Source Of Supply',
quickInfo: 'Source Of Supply'
}
key _prodvers.ProductionVersion as ProductionVersion,
@EndUserText: {
label: 'Work Center',
quickInfo: 'Work Center'
}
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
key _prodvers.ProductionLine as WorkCenter,
key _calenderdate.CalendarWeek as CalendarWeek,
key cast(substring(_calenderdate.YearWeek,1,4)as abap.numc(4)) as CalendarYear,
_calenderdate.FirstDayOfWeekDate as FirstDayOfWeekDate,
dats_add_days( _calenderdate.FirstDayOfWeekDate,6,'FAIL') as LastDayOfWeekDate,
@EndUserText: {
label: 'Assign',
quickInfo: 'Assign'
}
cast ( case when _order.ProductionVersion = _prodvers.ProductionVersion
then cast ('X' as flag)
end as flag preserving type ) as ProductionVersionIsAssigned,
_prodvers.ProductionVersionText as ProductionVersionText,
_WorkCenter.WorkCenterInternalID as WorkCenterInternalID,
_WorkCenterText[1:Language=$session.system_language].WorkCenterText as WorkCenterDesc,
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
_order.Operation,
_order.OperationLatestStartDate,
_order.OperationLatestEndDate,
_order.OperationLatestStartTime,
_order.OperationLatestEndTime,
_order.LtstSchedldExecStrtDteTme,
_order.LtstSchedldExecEndDteTme,
_order.SchedldOrdStartDteTme,
_order.SchedldOrdEndDteTme,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
RemainingCapReqExecutionDurn,
_order.OperationPlanningStatusCode,
// cast( case when _order.OperationPlanningStatusCode = 'DSPT' and _order.ProductionVersion = _prodvers.ProductionVersion and
// ( _order.OperationLatestStartDate <= DATS_ADD_DAYS( _calenderdate.FirstDayOfWeekDate,6,'FAIL') and _calenderdate.FirstDayOfWeekDate <= _order.OperationLatestEndDate )
// then cast ('X' as flag)
// end as flag preserving type ) as OperationIsDispatched,
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
cast('' as flag preserving type ) as OperationIsDispatched,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
fltp_to_dec(0.00 as pph_cfs_sch_remaining_cap) as RemainingCapacityDurn, // remavailablecapacity
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
fltp_to_dec(0.00 as pph_cfs_sch_consumed_cap) as ConsumedCapacityDurn,
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
cast(0 as pph_cfs_sch_criticality_status) as StatusCriticality,
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
@Semantics.timeZoneReference: 'PlantTimeZone'
cast(0 as pph_cfs_sch_bucket_starttmstmp) as PlanningBucketStartDateTime,
@ObjectModel: {virtualElement: true, virtualElementCalculatedBy: 'ABAP:CL_PPSCH_PROPOSAL_WK_VIRT_ELE', readOnly: true}
@Semantics.timeZoneReference: 'PlantTimeZone'
cast(0 as pph_cfs_sch_bucket_end_tmstmp) as PlanningBucketEndDateTime,
@Semantics.unitOfMeasure: true
@ObjectModel.readOnly: true
@ObjectModel: { text.element: ['UnitOfMeasure_E'] }
_Uom.UnitOfMeasure,
@ObjectModel.readOnly: true
_Uom.UnitOfMeasure_E,
@Semantics.text: true
_OrderCategory.OrderCategoryName,
BOOExplosionDate,
BOMExplosionDate,
PlndOrderPlannedStartDate,
PlndOrderPlannedEndDate,
@UI.hidden: true
@Semantics.timeZoneReference: 'PlantTimeZone'
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
cast('000000000000000' as timestamp) as OrderOperationEndDateTime,
@UI.hidden: true
@Semantics.timeZoneReference: 'PlantTimeZone'
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
cast('000000000000000' as timestamp) as OrderOperationStartDateTime,
@UI.hidden: true
@ObjectModel: { filter:{ transformedBy: 'ABAP:CL_PPSCH_PRODDAY_FILTER_EXIT'}}
cast('000000000000000' as timestamp) as RequirementDateTime,
@Semantics.timeZone: true
case
when _WorkCenter._Plant._OrganizationAddress[1: AddressRepresentationCode = '' ].AddressTimeZone is null or _WorkCenter._Plant._OrganizationAddress[1: AddressRepresentationCode = '' ].AddressTimeZone is initial
then abap_system_timezone($session.client, 'NULL')
when _WorkCenter._Plant._OrganizationAddress[1: AddressRepresentationCode = '' ].AddressTimeZone is not null
then _WorkCenter._Plant._OrganizationAddress[1: AddressRepresentationCode = '' ].AddressTimeZone
end as PlantTimeZone
}
where
MainProductionLine = MainWorkCenter
and Operation != ''
and StatusCode != 'I0009'
and(
OperationIsPhase = '0'
or OperationIsPhase = '1'
)
and OrderOperationIsFirstInSqnc = 'X'
and
_prodvers.ProductionLine != ' '
//Already this condition is there in I_SchedWorklistOperation
// and _order.ProductionLine = _order.WorkCenter
and(
(
_order.OrderCategory = '1'
and PlndOrderPlannedStartDate >= _prodvers.ValidityStartDate
and PlndOrderPlannedEndDate <= _prodvers.ValidityEndDate
)
or(
_order.OrderCategory = '10'
and _prodvers.ValidityStartDate <= BOOExplosionDate
and BOOExplosionDate <= _prodvers.ValidityEndDate
and _prodvers.ValidityStartDate <= BOMExplosionDate
and BOMExplosionDate <= _prodvers.ValidityEndDate
)
or _order.OrderCategory = '40'
)
and(
(_WorkCenter.WorkCenterInternalID = _wrkgrpaor.WorkCenterInternalID and _prodvers.Plant = _wrkgrpaor.Plant and _prsnlzn.WorkCenterGroupRespyAreaIsRqd = 'X')
or (_prsnlzn.PlantMRPCtrlrRespyAreaIsRqd = 'X')
or ( _prsnlzn.PlantMRPCtrlrRespyAreaIsRqd = ' ' and _prsnlzn.WorkCenterGroupRespyAreaIsRqd = ' ')
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_SCHEDPRODNORDERCATEGORYVH",
"I_CALENDARDATE",
"I_CAPACITY",
"I_CAPPLNGWRKCTRGRPRESPYAREA",
"I_ORGANIZATIONADDRESS",
"I_PLANT",
"I_PRODUCTIONVERSION",
"I_SCHEDPRODNOPERATIONS",
"I_UNITOFMEASURE",
"I_WORKCENTER",
"I_WORKCENTERTEXT",
"I_WRKCTRUTILZNPRSNLZN"
],
"ASSOCIATED":
[
"C_SCHEDPRODNORDERCATEGORYVH",
"I_CALENDARDATE",
"I_SOSTYPETEXT",
"I_UNITOFMEASURE",
"I_WORKCENTERTEXT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/