@AbapCatalog.sqlViewName: 'PSCHEDOPERSVERS2'
@AbapCatalog.preserveKey: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #D
define view P_SchedOperations_Version_2
as select from I_LogisticsOrder as _Order
inner join I_SchedProdnCapacityRqmt as _CapacityRequirement on _Order.CapacityRequirement = _CapacityRequirement.CapacityRequirement
inner join I_SchedCapacityRqmtItemCap as CapRqmtItmCap on _CapacityRequirement.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
// left outer to many join I_OrderItem as _Orderitem on _Order.OrderID = _Orderitem.OrderID
// and _Orderitem.OrderItem = '0001'
left outer to one join I_OrderItem as _Ordermainitem on _Order.OrderID = _Ordermainitem.OrderID
and _Ordermainitem.OrderItem = '0001'
// left outer join I_ProductionVersion as _ProductionVersion on _Ordermainitem.Material = _ProductionVersion.Material
// and _Ordermainitem.ProductionPlant = _ProductionVersion.Plant
// and _Ordermainitem.ProductionVersion = _ProductionVersion.ProductionVersion
left outer to one join I_SchedProdnProductionVersion as _ProductionVersion on ( _Ordermainitem.Material = _ProductionVersion.Material
and _Ordermainitem.ProductionPlant = _ProductionVersion.Plant
and _Ordermainitem.ProductionVersion = _ProductionVersion.ProductionVersion )
left outer to one join I_BillOfOperationsType as _BillOfOperationsType on _ProductionVersion.BillOfOperationsType = _BillOfOperationsType.BillOfOperationsType
// left outer join I_CapacityRequirement as _CapacityRequirement on _CapacityRequirement.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
left outer to one join I_OrderOperation as _OrderOperation on CapRqmtItmCap.OrderInternalID = _OrderOperation.OrderInternalID
and CapRqmtItmCap.OrderOperationInternalID = _OrderOperation.OrderOperationInternalID
and _Order.Plant = _OrderOperation.Plant
and _Order.CapacityRequirement = _OrderOperation.CapacityRequirement
// left outer join I_PlannedOrder as _PlannedOrder on _PlannedOrder.PlannedOrder = CapRqmtItmCap.PlannedOrder
left outer to one join I_Capacity as _Capacity on CapRqmtItmCap.CapacityInternalID = _Capacity.CapacityInternalID
left outer to one join I_WorkCenter as _workcenter on CapRqmtItmCap.WorkCenterInternalID = _workcenter.WorkCenterInternalID
and _workcenter.WorkCenterTypeCode = 'A'
// left outer join I_WorkCenterText as _Text on _Text.WorkCenterInternalID = _workcenter.WorkCenterInternalID
// and _Text.WorkCenterTypeCode = _workcenter.WorkCenterTypeCode
left outer to one join I_Plant as _Plant on _workcenter.Plant = _Plant.Plant
left outer to one join I_Address as _Address on _Plant.AddressID = _Address.AddressID
// //--------------------------------------------------------------------------------------------------//
//// left outer to one join P_SchedFirstOperation_Vers_2 as SchedFrstOp on CapRqmtItmCap.CapacityRequirement = SchedFrstOp.CapacityRequirement
//// and CapRqmtItmCap.WorkCenterInternalID = SchedFrstOp.WorkCenterInternalID
//// and CapRqmtItmCap.Operation = SchedFrstOp.Operation
// and CapRqmtItmCap.CapacityRequirementItem = SchedFrstOp.CapacityRequirementItem
// and CapRqmtItmCap.CapacityRqmtItemCapacity = SchedFrstOp.CapacityRqmtItemCapacity
left outer to one join P_ProcCapReqOpDurn as ProcOpDurn on CapRqmtItmCap.CapacityRequirement = ProcOpDurn.CapacityRequirement
and CapRqmtItmCap.CapacityRequirementItem = ProcOpDurn.SuperiorOperationInternalID
and CapRqmtItmCap.CapacityRqmtItemCapacity = ProcOpDurn.CapacityRqmtItemCapacity
and CapRqmtItmCap.OperationIsPhase = '1'
// association [1..1] to I_StatusObjectStatus as _StatusConfirmed on CapRqmtItmCap.OrderObjectInternalID = _StatusConfirmed.StatusObject
// and _StatusConfirmed.StatusCode = 'I0009'
// and _StatusConfirmed.StatusIsActive = 'X'
association [1..1] to P_SchedOrderOperationStatus as _OrderStatus on _OrderStatus.StatusObject = CapRqmtItmCap.OrderObjectInternalID
{
key cast( _Order.OrderID as pph_cfs_ordernumber ) as OrderID,
key CapRqmtItmCap.CapacityRequirementItem as CapacityRequirementItem,
key CapRqmtItmCap.CapacityRqmtItemCapacity,
// key cast(CapRqmtItmCap._Order.OrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
key cast(_Order.OrderCategory as pph_cfs_sch_ordercategory) as OrderCategory,
key _CapacityRequirement.CapacityRequirement as CapacityRequirement,
cast( CapRqmtItmCap.Operation as vornr) as Operation,
// cast( CapRqmtItmCap._Order.Material as matnr) as Material,
cast( _Order.Material as matnr) as Material,
// CapRqmtItmCap._workcenter.Plant as Plant,
_workcenter.Plant as Plant,
case
// when CapRqmtItmCap._OrderOperation._workcenter._Plant._OrganizationAddress.AddressTimeZone is null
when _OrganizationAddress.AddressTimeZone is null
then
tstmp_to_dats( tstmp_current_utctimestamp(),
abap_system_timezone($session.client, 'NULL'),
$session.client,
'NULL' )
when _OrganizationAddress.AddressTimeZone is not null
then
tstmp_to_dats( tstmp_current_utctimestamp(),
// CapRqmtItmCap._OrderOperation._workcenter._Plant._OrganizationAddress.AddressTimeZone,
_OrganizationAddress.AddressTimeZone,
$session.client,
'NULL' )
end as PlantCurrentDate,
case
// when CapRqmtItmCap._OrderOperation._workcenter._Plant._OrganizationAddress.AddressTimeZone is null
when _OrganizationAddress.AddressTimeZone is null
then
tstmp_to_tims( tstmp_current_utctimestamp(),
abap_system_timezone($session.client, 'NULL'),
$session.client,
'NULL' )
when _OrganizationAddress.AddressTimeZone is not null
then
tstmp_to_tims( tstmp_current_utctimestamp(),
// CapRqmtItmCap._OrderOperation._workcenter._Plant._OrganizationAddress.AddressTimeZone,
_OrganizationAddress.AddressTimeZone,
$session.client,
'NULL' )
end as PlantCurrentTime,
_OrganizationAddress.AddressTimeZone,
cast( _Ordermainitem.MRPArea as berid) as MRPArea,
_Ordermainitem._MRPArea.MRPAreaText as MRPAreaText,
cast( _Order.MRPController as dispo) as MRPController,
case
when _Order.LastChangeDate = '00000000'
then cast(cast( concat( _Order.CreationDate, _Order.CreationTime)as abap.numc(15)) as timestamp)
when _Order.LastChangeDate != '00000000'
then
cast(cast( concat( _Order.LastChangeDate, _Order.LastChangeTime)as abap.numc(15)) as timestamp)
end as LastChangeDateTime,
// CapRqmtItmCap.CapacityRequirement as CapacityRequirement,
_workcenter.WorkCenterInternalID as WorkCenterInternalID,
_workcenter.WorkCenter as WorkCenter,
_workcenter.WorkCenterCategoryCode as WorkCenterCategoryCode,
_workcenter._Text[1:Language=$session.system_language].WorkCenterText as WorkCenterText,
// '' as WorkCenterText,
cast(CapRqmtItmCap.WorkCenterTypeCode as cr_objty) as WorkCenterTypeCode,
CapRqmtItmCap.CapacityInternalID,
_Capacity.CapacityCategoryCode,
_workcenter.WorkCenterResponsible as WorkCenterResponsible,
_Capacity.CapacityResponsiblePlanner as CapacityResponsiblePlanner,
CapRqmtItmCap.OrderInternalID as OrderInternalID,
cast(_Order.OrderID as aufnr) as ManufacturingOrder,
cast(CapRqmtItmCap.PlannedOrder as plnum) as PlannedOrder,
cast( _Ordermainitem.ProductionVersion as verid) as ProductionVersion,
_ProductionVersion.ProductionLine as ProductionLine,
// _workcenter.WorkCenter as ProductionLine,
// Mapping Repetetive manufacturing to Discrete Industry _BillOfOperationsType
cast( case
when _BillOfOperationsType.BillOfOperationsApplication = 'R'
then cast ('P' as plnaw)
else
cast (_BillOfOperationsType.BillOfOperationsApplication as plnaw)
end as plnaw preserving type ) as IndustryType,
case
when _OrderOperation.OpDispatchedExecStartDate = '00000000'
then cast('DALL' as pph_cfs_sch_status)
// when _OrderOperation.OpDispatchedExecStartDate != '00000000'
else
cast('DSPT' as pph_cfs_sch_status)
end as OperationPlanningStatusCode,
_Order.ScheduledBasicStartDate as ScheduledBasicStartDate,
_Order.ScheduledBasicStartTime as ScheduledBasicStartTime,
_Order.ScheduledBasicEndDate as ScheduledBasicEndDate,
_Order.ScheduledBasicEndTime as ScheduledBasicEndTime,
CapRqmtItmCap.OperationLatestStartDate as OperationLatestStartDate,
CapRqmtItmCap.OperationLatestStartTime as OperationLatestStartTime,
case
when CapRqmtItmCap.OperationLatestStartTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestStartDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
when CapRqmtItmCap.OperationLatestStartTime <= '240000'
then cast(cast(concat(CapRqmtItmCap.OperationLatestStartDate,CapRqmtItmCap.OperationLatestStartTime)as abap.numc(15)) as timestamp)
end as LtstSchedldExecStrtDteTme,
CapRqmtItmCap.OperationLatestEndDate as OperationLatestEndDate,
CapRqmtItmCap.OperationLatestEndTime as OperationLatestEndTime,
case
when CapRqmtItmCap.OperationLatestEndTime >= '240000'
then cast(cast(concat(dats_add_days(CapRqmtItmCap.OperationLatestEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
when CapRqmtItmCap.OperationLatestEndTime <= '240000'
then cast(cast(concat(CapRqmtItmCap.OperationLatestEndDate,CapRqmtItmCap.OperationLatestEndTime)as abap.numc(15)) as timestamp)
end as LtstSchedldExecEndDteTme,
CapRqmtItmCap.LtstSchedldProcStrtDte as OpLtstSchedldProcgStrtDte,
CapRqmtItmCap.LtstSchedldProcStrtTime as OpLtstSchedldProcgStrtTme,
CapRqmtItmCap.LtstSchedldTrdwnStrtDte as OpLtstSchedldTrdwnStrtDte,
CapRqmtItmCap.LtstSchedldTrdwnStrtTime as OpLtstSchedldTrdwnStrtTme,
_Order.PlannedEndDate as RequirementDate,
cast ( ( _Order.OrderPlannedTotalQty
- _Order.OrderConfirmedYieldQty ) as mgvrg ) as OrderPlannedTotalQty,
cast(_Order.BaseUnit as meins preserving type) as BaseUnit,
case
when CapRqmtItmCap.OperationIsPhase = '1'
then ProcOpDurn.CapacityRequirementUnit
when CapRqmtItmCap.OperationIsPhase != '1'
then CapRqmtItmCap.CapacityRequirementUnit
end as CapacityRequirementUnit,
case
when CapRqmtItmCap.RemainingCapReqOpSegSetupDurn = 0
then 0
when CapRqmtItmCap.RemainingCapReqOpSegSetupDurn != 0
then cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegSetupDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'H' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_krueres )
end as RemCapReqOpSegSetupDurnInHours,
case
when CapRqmtItmCap.OperationIsPhase = '1'
then
case
when ProcOpDurn.TotRemCapReqOpSegProcgDurn = 0
then 0
when ProcOpDurn.TotRemCapReqOpSegProcgDurn != 0
then
cast( unit_conversion(
QUANTITY => ProcOpDurn.TotRemCapReqOpSegProcgDurn,
SOURCE_UNIT => ProcOpDurn.CapacityRequirementUnit,
TARGET_UNIT => cast( 'H' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kbeares )
end
else
case
when CapRqmtItmCap.RemainingCapReqOpSegProcgDurn = 0
then 0
when CapRqmtItmCap.RemainingCapReqOpSegProcgDurn != 0
then
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegProcgDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'H' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kbeares )
end
end as RemCapReqOpSegProcgDurnInHours,
case
when CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn = 0
then 0
when CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn != 0
then
cast( unit_conversion(
QUANTITY => CapRqmtItmCap.RemainingCapReqOpSegTrdwnDurn,
SOURCE_UNIT => CapRqmtItmCap.CapacityRequirementUnit,
TARGET_UNIT => cast( 'H' as abap.unit(3) ),
ERROR_HANDLING => 'SET_TO_NULL' ) as cy_kabrres )
end as RemCapReqOpSegTrdwnDurnInHours,
_Order.BOOExplosionDate as BOOExplosionDate, //Routing Explosion Date for Manufacturing Orders
_Order.BOOExplosionDate as BOMExplosionDate, //BOM Explosion Date for Manufacturing Orders
// _PlannedOrder.PlndOrderPlannedStartDate, // Planned Order Start Date
// _PlannedOrder.PlndOrderPlannedEndDate, //Planned Order end date
cast('00000000' as psttr) as PlndOrderPlannedStartDate, // Planned Order Start Date
cast('00000000' as pedtr) as PlndOrderPlannedEndDate, //Planned Order end date
// '00000000' as PlndOrderPlannedStartDate, // Planned Order Start Date
// '00000000' as PlndOrderPlannedEndDate, //Planned Order end date
' ' as PlannedOrderIsFirm,
CapRqmtItmCap.OrderObjectInternalID,
CapRqmtItmCap.OperationIsPhase,
//_StatusConfirmed.StatusCode,
case
when _OrderStatus.StatusCode = 10 then 'I0001' /*Created*/
when _OrderStatus.StatusCode = 20 then 'I0002' /*Released*/
when _OrderStatus.StatusCode = 30 then 'I0009' /*Confirmed*/
when _OrderStatus.StatusCode = 40 then 'I0010' /*Partially Confirmed*/
when _OrderStatus.StatusCode != 10 and _OrderStatus.StatusCode != 20 and
_OrderStatus.StatusCode != 30 and _OrderStatus.StatusCode != 40
then ' ' /*Should mainly in case maintainence order*/
end as StatusCode,
// cast(case
// when SchedFrstOp.Operation is not initial
// then
// 'X'
// when SchedFrstOp.Operation is initial
// then ' '
// end as ppsch_is_first_operation_ind ) as OrderOperationIsFirstInSqnc,
'' as OrderOperationIsFirstInSqnc, // this is dummy field
// this field logic is movied to I_schedorderOperations
OperationLatestStartDate as OriginallyScheduledStartDate,
OperationLatestEndDate as OriginallyScheduledEndDate,
// _Order.ScheduledBasicStartDate as OriginallyScheduledStartDate,
// _Order.ScheduledBasicEndDate as OriginallyScheduledEndDate,
/*Set up Type Key, Setup Group Category and Set Group Key */
_OrderOperation.OperationSetupGroup,
_OrderOperation.OperationSetupGroupCategory,
_OrderOperation.BillOfOperationsType,
_OrderOperation.BillOfOperationsGroup,
/* AVC configuration */
case when _Ordermainitem.ProductConfiguration is not initial and _Order.OrderCategory = '10' then
cast('X' as boole_d preserving type )
when _Ordermainitem.ProductConfiguration is initial and _Order.OrderCategory != '10'
then cast('' as boole_d preserving type )
end as OrderHasConfiguration,
//Receipt date calculation correction for manufacturing Orders
// case
// when _Order.PlannedEndTime >= '240000'
// then cast(cast(concat(dats_add_days(_Order.PlannedEndDate,1,'NULL'),'000000')as abap.numc(15)) as timestamp)
// when _Order.ScheduledBasicEndTime <= '240000'
// then cast(cast(concat(_Order.PlannedEndDate,_Order.PlannedEndTime)as abap.numc(15)) as timestamp)
// end as ScheduledOrderReceiptDateTime,
// cast(cast(concat(_Order.PlannedEndDate,_Order.PlannedEndTime)as abap.numc(15)) as timestamp) as ScheduledOrderReceiptDateTime,
//
case
when _OrganizationAddress.AddressTimeZone is null or _OrganizationAddress.AddressTimeZone is initial
then
case
when _Order.PlannedEndTime >= '240000'
then cast(dats_tims_to_tstmp(dats_add_days(_Order.PlannedEndDate,1,'NULL'),
'000000',
abap_system_timezone($session.client, 'NULL'),$session.client,'NULL')as timestamp )
when _Order.ScheduledBasicEndTime <= '240000'
then cast(dats_tims_to_tstmp(_Order.PlannedEndDate,
_Order.PlannedEndTime,
abap_system_timezone($session.client, 'NULL'),$session.client,'NULL')as timestamp )
end
when _OrganizationAddress.AddressTimeZone is not null
then
case
when _Order.PlannedEndTime >= '240000'
then cast(dats_tims_to_tstmp(dats_add_days(_Order.PlannedEndDate,1,'NULL'),
'000000',
_OrganizationAddress.AddressTimeZone,$session.client,'NULL')as timestamp )
when _Order.ScheduledBasicEndTime <= '240000'
then cast(dats_tims_to_tstmp(_Order.PlannedEndDate,
_Order.PlannedEndTime,
_OrganizationAddress.AddressTimeZone,$session.client,'NULL')as timestamp )
end
end as ScheduledOrderReceiptDateTime,
CapRqmtItmCap.OrderOperationInternalID, // for manufacturing Order Operation Extensibility
_Ordermainitem.ProductConfiguration, //exposing for AVC configuration
case
when _Order.MfgOrderIsPartOfCollvOrder = 'X' then
case
when _Order.LeadingOrder != _Order.OrderID then
cast('X' as ppsch_collv_order_ind)
when _Order.LeadingOrder = _Order.OrderID then
cast(' ' as ppsch_collv_order_ind)
end
when _Order.MfgOrderIsPartOfCollvOrder = ' ' then
cast(' ' as ppsch_collv_order_ind)
end as OrderIsDependentCollvOrder,
_Ordermainitem.SalesOrder as SalesOrder,
_Ordermainitem.SalesOrderItem as SalesOrderItem,
case
when _OrganizationAddress.AddressTimeZone is null or _OrganizationAddress.AddressTimeZone is initial
then abap_system_timezone($session.client, 'NULL')
when _OrganizationAddress.AddressTimeZone is not null
then _OrganizationAddress.AddressTimeZone
end as PlantTimeZone
}
where
_CapacityRequirement.CapacityRequirementOrigin = '1'
//and CapRqmtItmCap.Operation != ''
and CapRqmtItmCap.CapacityInternalID = _workcenter.CapacityInternalID
and _workcenter.AdvancedPlanningIsSupported = ''
and _Capacity.CapacityIsFinite = 'X'
and(
_Order.OrderCategory = '10'
and _OrderOperation.Sequence = '000000'
or _Order.OrderCategory = '40'
and _OrderOperation.Sequence = '000000'
or _Order.OrderCategory = '30'
)
//and _StatusConfirmed.StatusCode is null
and(
CapRqmtItmCap.OperationIsPhase <> '2'
// CapRqmtItmCap.OperationIsPhase = '1' or
// CapRqmtItmCap.OperationIsPhase = '0'
)
union all select from I_PlannedOrder as PlannedOrder
inner join I_SchedProdnCapacityRqmt as _CapacityRequirement on PlannedOrder.CapacityRequirement = _CapacityRequirement.CapacityRequirement
inner join I_SchedCapacityRqmtItemCap as CapRqmtItmCap on _CapacityRequirement.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
and PlannedOrder.PlannedOrder = CapRqmtItmCap.PlannedOrder
// inner join ZDUMMY_TEST as CapRqmtItmCap on _CapacityRequirement.CapacityRequirement = CapRqmtItmCap.CapacityRequirement
// and PlannedOrder.PlannedOrder = CapRqmtItmCap.PlannedOrder
left outer to one join I_StatusObjectStatus as _OrderStatusObjectStatus on CapRqmtItmCap.OrderObjectInternalID = _OrderStatusObjectStatus.StatusObject
and _OrderStatusObjectStatus.StatusCode = 'I0117'
left outer to one join I_BillOfOperationsOpBasic as _BOOOperationInternalVersion on _BOOOperationInternalVersion.BillOfOperationsType = CapRqmtItmCap.BillOfOperationsType
and _BOOOperationInternalVersion.BillOfOperationsGroup = CapRqmtItmCap.BillOfOperationsGroup
and _BOOOperationInternalVersion.BOOOperationInternalID = CapRqmtItmCap.BOOOperationInternalID
and _BOOOperationInternalVersion.BOOOpInternalVersionCounter = CapRqmtItmCap.BOOOpInternalVersionCounter
// left outer join I_ProductionVersion as _ProductionVersion on PlannedOrder.Material = _ProductionVersion.Material
// and PlannedOrder.MRPPlant = _ProductionVersion.Plant
// and PlannedOrder.ProductionVersion = _ProductionVersion.ProductionVersion
left outer to one join I_SchedProdnProductionVersion as _ProductionVersion on
( PlannedOrder.Material = _ProductionVersion.Material
/* changing to Production plant to handle when production plant and MRP plant can be same or different*/
/* Productionversion plant will always match with Production Plant of Order*/
and PlannedOrder.ProductionPlant = _ProductionVersion.Plant
and PlannedOrder.ProductionVersion = _ProductionVersion.ProductionVersion )
left outer to one join I_BillOfOperationsType as _BillOfOperationsType on CapRqmtItmCap.BillOfOperationsType = _BillOfOperationsType.BillOfOperationsType
//left outer to one join I_LogisticsOrder as _Order on CapRqmtItmCap.CapacityRequirement = _Order.CapacityRequirement
// and _Order.OrderID = CapRqmtItmCap.OrderID
// left outer join