/*---------------------------------------------------------------------*
* CDS Name : I_ACMApplicationOverview *
* CDS Description : Get Application Document Data for Latest *
* Version , along with LDC data fields *
* Whereever needed *
*---------------------------------------------------------------------*/
@AbapCatalog.sqlViewName: 'IAPPWRKCNTR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #INHERITED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #TRANSACTIONAL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@EndUserText.label: 'Interface Layer for ACM Application Overview'
@ObjectModel.supportedCapabilities: #CDS_MODELING_DATA_SOURCE
define view I_ACMApplicationOverview
// as select distinct from P_ApplicationLatestVersion2 as LatestAppVersion
// as select distinct from I_ApplicationDocLatestVersion as LatestAppVersion
as select from I_ACMApplDocLtstVers as LatestAppVersion
// association [0..1] to I_ACMApplDocHeader as AppDocHeader on $projection.ReferenceApplicationDocument = AppDocHeader.ApplicationDocument
inner join I_ACMApplDocHeader as AppDocHeader on LatestAppVersion.ReferenceApplicationDocument = AppDocHeader.ApplicationDocument
inner join I_ACMApplDocItem as AppDocDetails on LatestAppVersion.ReferenceApplicationDocument = AppDocDetails.ApplicationDocument
and LatestAppVersion.ReferenceApplicationItem = AppDocDetails.ReferenceApplicationItem
and LatestAppVersion.ReferenceApplicationSide = AppDocDetails.ReferenceApplicationSide
and LatestAppVersion.ReferenceApplicationSubItem = substring(
AppDocDetails.ReferenceApplicationSubItem, 1, 6
)
inner join I_ACMSystemStatus as status //Status table
on status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
and status.ChangeDocumentStatusIsInactive = ''
inner join I_ApplicationStatusValueHelp as StatusText on StatusText.ContractApplicationStatus = status.StatusProfileName
and status.ChangeDocumentStatusIsInactive = ''
inner join I_ACMApplDocTypeValueHelp as DocTypeText on AppDocHeader.ReferenceDocumentType = DocTypeText.ReferenceDocumentType
// inner join I_ACMLoadDataCaptureEvtData as LDCData on LDCData.LDCKey = AppDocDetails.LDCKey //Get LDC Data
inner join I_LoadDataCaptureHdr as LDCHdrData on LDCHdrData.LDCKey = AppDocDetails.LDCKey
left outer join I_ACMLoadDataCaptureData as LDCUnload on LDCUnload.LDCKey = AppDocDetails.LDCKey //Get LDC Data
and LDCUnload.LoadDataCaptureEventType = '02' //Unload LDC Data
left outer join I_ACMLoadDataCaptureData as LDCLoad on LDCLoad.LDCKey = AppDocDetails.LDCKey //Get LDC Data
and LDCLoad.LoadDataCaptureEventType = '01' //Load LDC Data
left outer join P_ApplicationDocSide_VH as AppDocSideText on AppDocSideText.DomainValue = LatestAppVersion.ReferenceApplicationSide
// left outer join I_ACMApplInstructionsValueHelp as AppInsText on AppInsText.LoadDataCaptureApplInstruction = LDCData.LoadDataCaptureApplInstruction
// left outer join P_material_value_help as MatDesc on MatDesc.Material = AppDocDetails.Material
left outer join I_MaterialText as MatDesc on MatDesc.Material = AppDocDetails.Material
and MatDesc.Language = $session.system_language
// left outer join P_TradingContractHeader as ContrHeader on ContrHeader.tkonn = TradingContractNumber
left outer join I_ContractHead as ContrHeader on ContrHeader.tkonn = LatestAppVersion.TradingContractNumber
left outer join P_TradingContractBusinessDets as Business on ContrHeader.tkonn = Business.TradingContractNumber
// left outer join I_CreatedByUser as CreatedByName on CreatedByName.UserName = LatestAppVersion.ApplDocumentCreatedBy
// left outer join I_CreatedByUser as ChangedByName on ChangedByName.UserName = LatestAppVersion.ApplDocumentChangedBy
left outer join I_User as CreatedByName on CreatedByName.UserID = AppDocDetails.ApplDocumentCreatedBy
left outer join I_User as ChangedByName on ChangedByName.UserID = AppDocDetails.ApplDocumentChangedBy
// BOC FPS2 Development
left outer join P_SpotIndicatoreValueHelp as SpotDesc on SpotDesc.SpotIndicator = AppDocDetails.ACMApplOvwSpotFlag
// EOC FPS2 Development
{
key AppDocHeader.ApplicationDocument as ApplicationDocument,
key LatestAppVersion.ReferenceApplicationItem as ReferenceApplicationItem,
LatestAppVersion.ContractApplicationUUID,
LatestAppVersion.ContractApplicationVersion,
AppDocHeader.ReferenceDocumentType as ReferenceDocumentType,
DocTypeText.ApplicationDocTypeDescription as ApplicationDocTypeDescription, //ApplDocTypeDescr,
LatestAppVersion.ReferenceApplicationSide,
LatestAppVersion.ReferenceApplicationSubItem,
// substring(AppDocDetails.ReferenceApplicationSubItem, 1,6) as ReferenceApplicationSubItem, // Union Part
AppDocSideText.DomainText as ReferenceApplicationSideName,
AppDocHeader.DocumentDate as DocumentDate,
LatestAppVersion.DeliveryDate,
Business.TradingContractNumber,
Business.TradingContractItem,
// AppDocDetails.uis_id as LoadDataCaptureObjId,
AppDocDetails.LoadDataCaptureObjId as LoadDataCaptureObjId,
status.StatusProfileName as ContractApplicationStatus,
StatusText.TrdgContrApplStsName as TrdgContrApplStsName,
AppDocDetails.Counterparty as Counterparty,
AppDocDetails.Material as Material,
MatDesc.MaterialName as MaterialName,
LatestAppVersion.OverfillID,
AppDocDetails.ApplicationHasCallOff as ApplicationHasCallOff,
@API.element.releaseState: #DEPRECATED
@API.element.successor: 'ACMPredecessorDocumentNumber'
AppDocDetails.PredecessorDocumentNumber,
AppDocDetails.ACMPredecessorDocumentNumber,
case
when AppDocDetails.PrecedingDocumentType = 'C'
then cast( AppDocDetails.PredecessorDocumentNumber as ebeln )
end as PurchaseOrder,
case
when AppDocDetails.PrecedingDocumentType = 'A'
then cast( AppDocDetails.PredecessorDocumentNumber as vbeln )
end as SalesOrder, //SalesOrderNumber,
// LDCData.BillOfLading,
// /*BillOfLading*/
// LDCData.BillOfLadingDate,
// /*BillOfLadingDate*/
// LDCData.CmmdtyTransptVesselID,
// LDCData.TruckRailBargeId,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.BillOfLading
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.BillOfLading
end as BillOfLading,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.BillOfLadingDate
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.BillOfLadingDate
end as BillOfLadingDate,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.CmmdtyTransptVesselID
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.CmmdtyTransptVesselID
end as CmmdtyTransptVesselID,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.TruckRailBargeId
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.TruckRailBargeId
end as TruckRailBargeId,
cast( LatestAppVersion.IsApplicationItemInTransit as boole_d ) as IsApplicationItemInTransit,
AppDocDetails.LDCIsMultipleBuySellRelevant as LDCIsMultipleBuySellRelevant,
AppDocDetails.ACMApplDocIsBackToBack as ACMApplDocIsBackToBack,
// LDCData.LoadDataCaptureApplInstruction,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.LoadDataCaptureApplInstruction
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.LoadDataCaptureApplInstruction
end as LoadDataCaptureApplInstruction,
// AppInsText.LDCApplicationInstructionName,
case
when LDCUnload.LoadDataCaptureEventType = '02'
then LDCUnload.LDCApplicationInstructionName
when LDCLoad.LoadDataCaptureEventType = '01'
then LDCLoad.LDCApplicationInstructionName
end as LDCApplicationInstructionName,
AppDocDetails.NominationNumber as NominationNumber,
LatestAppVersion.ApplDocumentCreatedBy as ApplDocumentCreatedBy,
CreatedByName.UserDescription as ApplDocumentCreatedByName,
LatestAppVersion.ApplicationItemCreatedOnDate,
LatestAppVersion.ApplDocumentCreatedBy as ApplDocumentChangedBy,
ChangedByName.UserDescription as ApplDocumentChangedByName,
LatestAppVersion.ApplicationItemChangedOnDate,
ContrHeader.tctyp as TradingContractType,
ContrHeader.vkorg as SalesOrganization,
ContrHeader.vtweg as DistributionChannel,
ContrHeader.spart as Division,
Business.PurchasingOrganization as PurchasingOrganization,
Business.PurchasingGroup as PurchasingGroup,
case
when status.StatusProfileName = 'I7A04'
then 1
else 0
end as ACMApplDocIsFinallyApplied, //ApplDocIsFinallyApplied,
case
when status.StatusProfileName = 'I7A02' or status.StatusProfileName = 'I7C01'
then 1
else 0
end as ACMApplDocIsReadyToApply, //ApplDocIsReadyToApply,
case
when status.StatusProfileName = 'I7A03'
then 1
else 0
end as ACMApplDocIsProvisionallyAppld, //ApplDocIsProvisionallyApplied
case
when status.StatusProfileName = 'I7L03' or status.StatusProfileName = 'I7C04' or
status.StatusProfileName = 'I7A06' or status.StatusProfileName = 'I7L03'
then cast( 1 as int4 )
when status.StatusProfileName = 'I7A01' or status.StatusProfileName = 'I7A02' or
status.StatusProfileName = 'I7A03' or status.StatusProfileName = 'I7A05' or
status.StatusProfileName = 'I7A12' or status.StatusProfileName = 'I7A13' or
status.StatusProfileName = 'I7A14' or status.StatusProfileName = 'I7C01' or
status.StatusProfileName = 'I7C02' or status.StatusProfileName = 'I7C03' or
status.StatusProfileName = 'I7C07' or status.StatusProfileName = 'I7L01' or
status.StatusProfileName = 'I7L02'
then cast( 2 as int4 )
when status.StatusProfileName = 'I7A04' or status.StatusProfileName = 'I7A15' or
status.StatusProfileName = 'I7C05'
then cast( 3 as int4 )
else cast( 2 as int4 )
end as ACMSemanticColor,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
AppDocDetails.TrdgContrItemApplicationQty,
@Semantics.unitOfMeasure: true
AppDocDetails.UnitOfMeasure,
// BOC FPS02 1909 Functionality 01/16/2020
AppDocDetails.ACMApplOvwSpotFlag,
SpotDesc.SpotIndicatorDesc as ACMApplOvwSpotFlagDesc,
// EOC FPS02 1909 Functionality 01/16/2020
LDCHdrData.ApplicationIsVehicleContrRlvt
}
//where
// status.StatusProfileName <> 'I7A06' //To eliminate Cancelled App Docs
// and status.StatusProfileName <> 'I7A05' //To eliminate Reversed App Docs
// and status.StatusProfileName <> 'I7C01' //To eliminate Unassigned App Docs
// and status.StatusProfileName <> 'I7A13' //To eliminate Spot Awaited Docs
// and LatestAppVersion.ContractApplicationStatus <> '9' //To remove duplicates in case App doc is reversed and Contract is again applied
//
//union select distinct from I_ACMApplDocHeader as AppDocHeader
//
//
// inner join I_ACMApplDocItem as AppDocDetails on AppDocHeader.ApplicationDocument = AppDocDetails.ApplicationDocument
//
// inner join I_ACMSystemStatus as status //Status table
// on status.ApplicationStatusObject = AppDocDetails.ObjectInternalID
// and status.ChangeDocumentStatusIsInactive = ''
//
// inner join I_ApplicationStatusValueHelp as StatusText on StatusText.ContractApplicationStatus = status.StatusProfileName
// and status.ChangeDocumentStatusIsInactive = ''
//
// inner join I_ACMApplDocTypeValueHelp as DocTypeText on AppDocHeader.ReferenceDocumentType = DocTypeText.ReferenceDocumentType
//
//
//
// inner join I_ACMLoadDataCaptureEvtData as LDCData on LDCData.LDCKey = AppDocDetails.LDCKey //Get LDC Data
//
// inner join I_LoadDataCaptureHdr as LDCHdrData on LDCHdrData.LDCKey = AppDocDetails.LDCKey
//
// left outer join P_ApplicationDocSide_VH as AppDocSideText on AppDocSideText.DomainValue = AppDocDetails.ReferenceApplicationSide
//
// left outer join I_ACMApplInstructionsValueHelp as AppInsText on AppInsText.LoadDataCaptureApplInstruction = LDCData.LoadDataCaptureApplInstruction
//
// left outer join P_material_value_help as MatDesc on MatDesc.Material = AppDocDetails.Material
//
//
// left outer join I_CreatedByUser as CreatedByName on CreatedByName.UserName = AppDocDetails.ApplDocumentCreatedBy
//
// left outer join I_CreatedByUser as ChangedByName on ChangedByName.UserName = AppDocDetails.ApplDocumentChangedBy
//
//// BOC FPS2 Development
// left outer join P_SpotIndicatoreValueHelp as SpotDesc on SpotDesc.SpotIndicator = AppDocDetails.ACMApplOvwSpotFlag
//// EOC FPS2 Development
//{
// key AppDocHeader.ApplicationDocument as ApplicationDocument,
// key AppDocDetails.ReferenceApplicationItem as ReferenceApplicationItem,
// AppDocDetails.ContractApplicationItemUUID as ContractApplicationUUID, //Item GUID added Just to eliminate the Syntax error because of UNION
// cast( '' as /accgo/e_appl_version ) as ContractApplicationVersion,
// AppDocHeader.ReferenceDocumentType as ReferenceDocumentType,
// DocTypeText.ApplicationDocTypeDescription as ApplicationDocTypeDescription, //ApplDocTypeDescr,
// AppDocDetails.ReferenceApplicationSide as ReferenceApplicationSide,
// substring(AppDocDetails.ReferenceApplicationSubItem, 1,6) as ReferenceApplicationSubItem,
// AppDocSideText.DomainText as ReferenceApplicationSideName,
// AppDocHeader.DocumentDate as DocumentDate,
// AppDocDetails.DeliveryDate as DeliveryDate,
// cast( '0000000000' as tkonn) as TradingContractNumber,
// cast( '000000' as tposn) as TradingContractItem,
// //AppDocHeader.ApplicationDocument
// AppDocDetails.LoadDataCaptureObjId as LoadDataCaptureObjId,
//
// status.StatusProfileName as ContractApplicationStatus,
// StatusText.TrdgContrApplStsName as TrdgContrApplStsName,
// Counterparty as Counterparty,
// AppDocDetails.Material as Material,
// MatDesc.MaterialName as MaterialName,
// '' as OverfillID,
// AppDocDetails.ApplicationHasCallOff as ApplicationHasCallOff,
//
// @API.element.releaseState: #DEPRECATED
// @API.element.successor: 'ACMPredecessorDocumentNumber'
// PredecessorDocumentNumber,
// ACMPredecessorDocumentNumber,
//
// case
// when PrecedingDocumentType = 'C'
// then cast( PredecessorDocumentNumber as ebeln )
// end as PurchaseOrder,
//
// case
// when PrecedingDocumentType = 'A'
// then cast( PredecessorDocumentNumber as vbeln )
// end as SalesOrder, //SalesOrderNumber,
//
// LDCData.BillOfLading,
// /*BillOfLading*/
// LDCData.BillOfLadingDate,
// /*BillOfLadingDate*/
// LDCData.CmmdtyTransptVesselID,
// LDCData.TruckRailBargeId,
// cast( IsApplicationItemInTransit as boole_d ) as IsApplicationItemInTransit,
// AppDocDetails.LDCIsMultipleBuySellRelevant as LDCIsMultipleBuySellRelevant,
// AppDocDetails.ACMApplDocIsBackToBack as ACMApplDocIsBackToBack,
// LDCData.LoadDataCaptureApplInstruction,
// AppInsText.LDCApplicationInstructionName,
// AppDocDetails.NominationNumber as NominationNumber,
// AppDocDetails.ApplDocumentCreatedBy as ApplDocumentCreatedBy,
// CreatedByName.UserDescription as ApplDocumentCreatedByName,
// AppDocDetails.ApplicationItemCreatedOnDate,
// AppDocDetails.ApplDocumentChangedBy as ApplDocumentChangedBy,
// ChangedByName.UserDescription as ApplDocumentChangedByName,
// AppDocDetails.ApplicationItemChangedOnDate,
//
// '' as TradingContractType,
// '' as SalesOrganization,
// '' as DistributionChannel,
// '' as Division,
// '' as PurchasingOrganization,
// '' as PurchasingGroup,
//
//
// case
// when status.StatusProfileName = 'I7A04'
// then 1
// else 0
// end as ACMApplDocIsFinallyApplied, //ApplDocIsFinallyApplied,
//
// case
// when status.StatusProfileName = 'I7A02' or status.StatusProfileName = 'I7C01'
// then 1
// else 0
// end as ACMApplDocIsReadyToApply, //ApplDocIsReadyToApply,
//
// case
// when status.StatusProfileName = 'I7A03'
// then 1
// else 0
// end as ACMApplDocIsProvisionallyAppld, //ApplDocIsProvisionallyApplied
//
// case
// when status.StatusProfileName = 'I7A06' or status.StatusProfileName = 'I7L03'
// then cast( 1 as int4 )
// when status.StatusProfileName = 'I7A01' or status.StatusProfileName = 'I7A02' or
// status.StatusProfileName = 'I7A05' or
// status.StatusProfileName = 'I7A12' or status.StatusProfileName = 'I7A13' or
// status.StatusProfileName = 'I7A14' or status.StatusProfileName = 'I7C01' or
// status.StatusProfileName = 'I7C02' or status.StatusProfileName = 'I7C03' or
// status.StatusProfileName = 'I7C07' or status.StatusProfileName = 'I7L01' or
// status.StatusProfileName = 'I7L02'
// then cast( 2 as int4 )
// when status.StatusProfileName = 'I7A04' or status.StatusProfileName = 'I7C05'
// then cast( 3 as int4 )
// else cast( 2 as int4 )
// end as ACMSemanticColor,
// @Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
// TrdgContrItemApplicationQty,
// @Semantics.unitOfMeasure: true
// UnitOfMeasure,
// // BOC FPS02 1909 Functionality 01/16/2020
// ACMApplOvwSpotFlag,
// SpotIndicatorDesc as ACMApplOvwSpotFlagDesc,
// // EOC FPS02 1909 Functionality 01/16/2020
// LDCHdrData.ApplicationIsVehicleContrRlvt
//}
//
////where AppDocHeader.DOC_TYPE = 'CAD'
//where
// status.StatusProfileName <> 'I7A04' //To eliminate Finally Applied App Docs
// and status.StatusProfileName <> 'I7A03' //To eliminate Provisionally Applied App Docs
// and status.StatusProfileName <> 'I7A15' //To eliminate Return Completed App Docs