@AbapCatalog.sqlViewName: 'CSDDPLSLSDOC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'Duplicate Sales Documents'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #M
@ObjectModel.updateEnabled
@Metadata.allowExtensions: true
define view C_DuplicateSalesDocument
with parameters
@Consumption.defaultValue: 'MONTHTODATE'
@Consumption.valueHelpDefinition: [{
entity: {
name: 'C_GregorianCalDateFuncVH',
element: 'DateFunction'
}
}]
P_DateFunction : datefunctionid
as select from I_SalesDocument as SalesDocument
inner join P_DuplicateSalesDocumentGrpg( P_DateFunction: $parameters.P_DateFunction ) as DuplicateSalesDocument
on SalesDocument.SDDocumentCategory = DuplicateSalesDocument.SDDocumentCategory
and SalesDocument.SalesDocumentType = DuplicateSalesDocument.SalesDocumentType
and SalesDocument.SoldToParty = DuplicateSalesDocument.SoldToParty
and SalesDocument.CreationDate = DuplicateSalesDocument.CreationDate
and SalesDocument.TotalNetAmount = DuplicateSalesDocument.TotalNetAmount
and SalesDocument.TransactionCurrency = DuplicateSalesDocument.TransactionCurrency
and SalesDocument.SalesOrganization = DuplicateSalesDocument.SalesOrganization
and SalesDocument.DistributionChannel = DuplicateSalesDocument.DistributionChannel
and SalesDocument.OrganizationDivision = DuplicateSalesDocument.OrganizationDivision
and SalesDocument.PurchaseOrderByCustomer = DuplicateSalesDocument.PurchaseOrderByCustomer
inner join C_SglGregorianCalDateFunction( P_DateFunction: $parameters.P_DateFunction, P_Language: $session.system_language ) as Range
on SalesDocument.CreationDate >= Range.DateFunctionStartDate
and SalesDocument.CreationDate <= Range.DateFunctionEndDate
left outer to one join I_SDDocumentCompletePartners as SoldToPartyAddressInfo
on SalesDocument.SalesDocument = SoldToPartyAddressInfo.SDDocument
and SoldToPartyAddressInfo.SDDocumentItem = '000000'
and SoldToPartyAddressInfo.PartnerFunction = 'AG'
left outer to one join I_SDDocumentCompletePartners as ShipToPartyAddressInfo
on SalesDocument.SalesDocument = ShipToPartyAddressInfo.SDDocument --AT 21.09.2018: new CE1902
and ShipToPartyAddressInfo.SDDocumentItem = '000000'
and ShipToPartyAddressInfo.PartnerFunction = 'WE'
association [0..*] to C_DuplicateSalesDocumentItem as _Item on $projection.SalesDocument = _Item.SalesDocument
association [1..1] to P_SalesDocumentItemCount as _SalesDocumentItemCount on $projection.SalesDocument = _SalesDocumentItemCount.SalesDocument
association [0..1] to C_DplSlsDocCatVH as _SalesDocumentCategory on $projection.SDDocumentCategory = _SalesDocumentCategory.SDDocumentCategory
association [0..1] to C_DplSlsDocTypeVH as _SalesDocumentType on $projection.SDDocumentCategory = _SalesDocumentType.SDDocumentCategory
and $projection.SalesDocumentType = _SalesDocumentType.SalesDocumentType
association [0..1] to C_SoldToValueHelp as _SoldToParty on $projection.SoldToParty = _SoldToParty.Customer
association [0..1] to C_SalesDocumentUserVH as _CreatedByUser on $projection.CreatedByUser = _CreatedByUser.UserID
association [0..1] to C_Dischannelvaluehelp as _DistributionChannel on $projection.SalesOrganization = _DistributionChannel.SalesOrganization
and $projection.DistributionChannel = _DistributionChannel.DistributionChannel
association [0..1] to C_OrgDivisionValueHelp as _OrganizationDivision on $projection.SalesOrganization = _OrganizationDivision.SalesOrganization
and $projection.DistributionChannel = _OrganizationDivision.DistributionChannel
and $projection.OrganizationDivision = _OrganizationDivision.Division
association [0..1] to C_SalesOfficeValueHelp as _SalesOffice on $projection.SalesOrganization = _SalesOffice.SalesOrganization
and $projection.DistributionChannel = _SalesOffice.DistributionChannel
and $projection.OrganizationDivision = _SalesOffice.OrganizationDivision
and $projection.SalesOffice = _SalesOffice.SalesOffice
association [0..1] to C_SalesGroupValueHelp as _SalesGroup on $projection.SalesOffice = _SalesGroup.SalesOffice
and $projection.SalesGroup = _SalesGroup.SalesGroup
association [0..1] to I_SalesDocumentRjcnReason as _SalesDocumentRjcnReason on $projection.SalesDocumentRjcnReason = _SalesDocumentRjcnReason.SalesDocumentRjcnReason
association [0..1] to C_ShipToPartyValueHelp as _ShipToParty on $projection.ShipToParty = _ShipToParty.Customer
and $projection.ShipToPartyAddressID = _ShipToParty.AddressID
//Extension Association
association [0..1] to E_SalesDocumentBasic as _Extension on $projection.SalesDocument = _Extension.SalesDocument
{
@ObjectModel.text.element: ['SDDocumentCategoryName']
@UI.textArrangement: #TEXT_LAST
key SalesDocument.SalesDocument,
@Consumption.filter.hidden: true
concat_with_space(SalesDocument._SDDocumentCategory._Text[1: Language=$session.system_language].SDDocumentCategoryName, ltrim(DuplicateSalesDocument.DuplicateSalesDocumentGroup, '0'), 1)
as DuplicateSalesDocumentGroup,
@Consumption.filter.hidden: true
@DefaultAggregation: #COUNT
cast( '1' as numberofduplicateslsdocuments) as NumberOfDuplicateSlsDocuments,
@UI.hidden: true
cast( DuplicateSalesDocument.NumberOfDuplicateSlsDocuments as numberofduplicateslsdocuments) as NumberOfDuplicatesInGroup,
@Consumption.filter.hidden: true
cast(_SalesDocumentItemCount.NmbrOfSlsDocItems as slsdoc_numberofitems preserving type) as NumberOfItems,
@Consumption.valueHelp: '_SalesDocumentCategory'
@ObjectModel.foreignKey.association: '_SalesDocumentCategory'
@Consumption.labelElement: 'SDDocumentCategoryName'
SalesDocument.SDDocumentCategory,
@Consumption.filter.hidden: true
_SalesDocumentCategory,
@UI.hidden: true
@Semantics.text: true
SalesDocument._SDDocumentCategory._Text[1: Language=$session.system_language].SDDocumentCategoryName as SDDocumentCategoryName,
@Consumption.valueHelp: '_SalesDocumentType'
@ObjectModel.foreignKey.association: '_SalesDocumentType'
SalesDocument.SalesDocumentType,
@Consumption.filter.hidden: true
_SalesDocumentType,
@UI.textArrangement: #TEXT_ONLY
@ObjectModel.foreignKey.association: '_OverallSDProcessStatus'
SalesDocument.OverallSDProcessStatus,
@Consumption.filter.hidden: true
SalesDocument._OverallSDProcessStatus,
@Consumption.semanticObject: 'Customer'
@Consumption.valueHelpDefinition: [{association: '_SoldToParty'}]
@ObjectModel.foreignKey.association: '_SoldToParty'
@UI.textArrangement: #TEXT_FIRST
SalesDocument.SoldToParty,
@Consumption.filter.hidden: true
_SoldToParty,
@Consumption.filter.hidden: true
SoldToPartyAddressInfo.AddressID,
@Consumption.semanticObject: 'Customer'
@Consumption.valueHelpDefinition: [{association: '_ShipToParty'}]
@ObjectModel.foreignKey.association: '_ShipToParty'
@UI.textArrangement: #TEXT_FIRST
cast(ShipToPartyAddressInfo.Customer as kunwe preserving type) as ShipToParty,
@Consumption.filter.hidden: true
_ShipToParty,
@Consumption.filter.hidden: true
ShipToPartyAddressInfo.AddressID as ShipToPartyAddressID,
@Consumption.filter.hidden: true
@Semantics.address.city: true
ShipToPartyAddressInfo._DfltAddrRprstn.Country,
@Consumption.filter.hidden: true
@Semantics.address.zipCode: true
ShipToPartyAddressInfo._DfltAddrRprstn.PostalCode,
@Consumption.filter.hidden: true
@Semantics.address.city: true
ShipToPartyAddressInfo._DfltAddrRprstn.CityName,
@Consumption.filter.hidden: true
@Semantics.address.streetNoNumber: true
ShipToPartyAddressInfo._DfltAddrRprstn.StreetName,
@Consumption.filter.hidden: true
@Semantics.address.number: true
ShipToPartyAddressInfo._DfltAddrRprstn.HouseNumber,
@Consumption.filter.hidden: true
ShipToPartyAddressInfo.AddressPersonID,
ShipToPartyAddressInfo._DfltAddrRprstn,
@Consumption.filter.hidden: true
@Semantics.systemDate.createdAt: true
SalesDocument.CreationDate,
@Consumption.semanticObject: 'UserID'
@Consumption.valueHelp: '_CreatedByUser'
@ObjectModel.foreignKey.association: '_CreatedByUser'
@UI.textArrangement: #TEXT_FIRST
SalesDocument.CreatedByUser,
@Consumption.filter.hidden: true
_CreatedByUser,
@DefaultAggregation: #NONE
@Semantics.amount.currencyCode: 'TransactionCurrency'
SalesDocument.TotalNetAmount,
@UI.hidden: true
@ObjectModel.foreignKey.association: '_TransactionCurrency'
@Semantics.currencyCode: true
SalesDocument.TransactionCurrency,
@Consumption.filter.hidden: true
SalesDocument._TransactionCurrency,
@Consumption.filter.hidden: true
SalesDocument.PurchaseOrderByCustomer,
@Consumption.valueHelpDefinition: [{entity:{name:'C_SalesDocSalesOrganizationVH', element:'SalesOrganization'} }]
@Consumption.labelElement: 'SalesOrganizationName'
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesDocument.SalesOrganization,
@Consumption.filter.hidden: true
SalesDocument._SalesOrganization,
@UI.hidden: true
@Semantics.text: true
SalesDocument._SalesOrganization._Text[1: Language=$session.system_language].SalesOrganizationName,
@Consumption.valueHelp: '_DistributionChannel'
@Consumption.labelElement: 'DistributionChannelName'
SalesDocument.DistributionChannel,
@Consumption.filter.hidden: true
_DistributionChannel,
@Consumption.valueHelp: '_OrganizationDivision'
@Consumption.labelElement: 'DivisionName'
SalesDocument.OrganizationDivision,
@Consumption.filter.hidden: true
_OrganizationDivision,
@Consumption.valueHelp: '_SalesOffice'
@Consumption.labelElement: 'SalesOfficeName'
SalesDocument.SalesOffice,
@Consumption.filter.hidden: true
_SalesOffice,
@Consumption.valueHelp: '_SalesGroup'
@Consumption.labelElement: 'SalesGroupName'
SalesDocument.SalesGroup,
@Consumption.filter.hidden: true
_SalesGroup,
@Consumption.filter.hidden: true
@ObjectModel.foreignKey.association: '_SalesDocumentRjcnReason'
cast( ' ' as abgru_va ) as SalesDocumentRjcnReason,
_SalesDocumentRjcnReason,
@UI.textArrangement: #TEXT_ONLY
@ObjectModel.foreignKey.association: '_OverallSDDocumentRejectionSts'
SalesDocument.OverallSDDocumentRejectionSts,
SalesDocument._OverallSDDocumentRejectionSts,
@Semantics.businessDate.at: true
SalesDocument.RequestedDeliveryDate,
_Item
}
where
SalesDocument.SalesDocumentProcessingType <> 'S'
and
SalesDocument.SalesDocumentProcessingType <> 'P'
and
SalesDocument.OverallSDDocumentRejectionSts <> 'C'
and(
SalesDocument.SDDocumentCategory = 'A' // Inquiry
or SalesDocument.SDDocumentCategory = 'B' // Quotation
or SalesDocument.SDDocumentCategory = 'C' // Order
or SalesDocument.SDDocumentCategory = 'G' // Contract
or SalesDocument.SDDocumentCategory = 'H' // Returns
or SalesDocument.SDDocumentCategory = 'I' // Order w/o charge
or SalesDocument.SDDocumentCategory = 'K' // Credit memo request
or SalesDocument.SDDocumentCategory = 'L' // Debit memo request
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_SGLGREGORIANCALDATEFUNCTION",
"I_ADDRESS_2",
"I_SALESDOCUMENT",
"I_SALESORGANIZATION",
"I_SALESORGANIZATIONTEXT",
"I_SDDOCUMENTCATEGORY",
"I_SDDOCUMENTCATEGORYTEXT",
"I_SDDOCUMENTCOMPLETEPARTNERS",
"P_DUPLICATESALESDOCUMENTGRPG",
"P_SALESDOCUMENTITEMCOUNT"
],
"ASSOCIATED":
[
"C_DISCHANNELVALUEHELP",
"C_DPLSLSDOCCATVH",
"C_DPLSLSDOCTYPEVH",
"C_DUPLICATESALESDOCUMENTITEM",
"C_ORGDIVISIONVALUEHELP",
"C_SALESDOCUMENTUSERVH",
"C_SALESGROUPVALUEHELP",
"C_SALESOFFICEVALUEHELP",
"C_SHIPTOPARTYVALUEHELP",
"C_SOLDTOVALUEHELP",
"E_SALESDOCUMENTBASIC",
"I_ADDRESS_2",
"I_CURRENCY",
"I_OVERALLSDDOCUMENTRJCNSTATUS",
"I_OVERALLSDPROCESSSTATUS",
"I_SALESDOCUMENTRJCNREASON",
"I_SALESORGANIZATION",
"P_SALESDOCUMENTITEMCOUNT"
],
"BASE":
[
"I_SALESDOCUMENT",
"I_SDDOCUMENTCOMPLETEPARTNERS"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/