@AbapCatalog.sqlViewName: 'PFRAUDITFECACDOC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #P
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@EndUserText.label: 'France Audit File (FEC) – ACDOCA-data'
define view P_FR_AuditFECAccountLineItem
with parameters
P_AlternativeGLAccountIsUsed : fis_disalteracct
as select from I_GLAccountLineItem as GLAccountLineItem
left outer to one join I_GLAccountInChartOfAccounts as altGLAcc on GLAccountLineItem.CountryChartOfAccounts = altGLAcc.ChartOfAccounts
and GLAccountLineItem.AlternativeGLAccount = altGLAcc.GLAccount
association [0..1] to I_OneTimeAccountBP as _OneTimeAccountBP on GLAccountLineItem.CompanyCode = _OneTimeAccountBP.CompanyCode
and GLAccountLineItem.FiscalYear = _OneTimeAccountBP.FiscalYear
and GLAccountLineItem.AccountingDocument = _OneTimeAccountBP.AccountingDocument
and GLAccountLineItem.AccountingDocumentItem = _OneTimeAccountBP.AccountingDocumentItem
association [0..*] to I_StRpJournalEntryLog as _ReportedItemsLog on $projection.CompanyCode = _ReportedItemsLog.CompanyCode
and $projection.AccountingDocument = _ReportedItemsLog.AccountingDocument
and $projection.FiscalYear = _ReportedItemsLog.FiscalYear
{
@ObjectModel.foreignKey.association: '_SourceLedger'
key GLAccountLineItem.SourceLedger,
@ObjectModel.foreignKey.association: '_CompanyCode'
key GLAccountLineItem.CompanyCode,
@ObjectModel.foreignKey.association: '_FiscalYear'
key GLAccountLineItem.FiscalYear,
@ObjectModel.foreignKey.association: '_JournalEntry'
key GLAccountLineItem.AccountingDocument,
key GLAccountLineItem.LedgerGLLineItem,
@ObjectModel.foreignKey.association: '_Ledger'
key GLAccountLineItem.Ledger,
//ACR part
key _ReportedItemsLog.StatryRptCategory,
key _ReportedItemsLog.StatryRptgEntity,
key _ReportedItemsLog.StatryRptRunID,
LedgerFiscalYear, //ryear
GLAccountLineItem.AccountingDocumentItem as AccountingDocumentItem,
@ObjectModel.foreignKey.association: '_AccountingDocumentType'
GLAccountLineItem.AccountingDocumentType,
_AccountingDocumentTypeText[1:Language = 'F'].AccountingDocumentTypeName,
_JournalEntry.DocumentReferenceID, //xblnr
cast( case
when _JournalEntry.DocumentReferenceID <> '' then _JournalEntry.DocumentReferenceID //xblnr
when _JournalEntry.TransactionCode <> '' then _JournalEntry.TransactionCode //tcode
else ReferenceDocumentType
end as fis_xblnr ) as FR_FECPieceReference,
GLAccountLineItem.PostingDate,
GLAccountLineItem.DocumentDate,
_JournalEntry.AccountingDocumentCreationDate,
GLAccountLineItem.FiscalPeriod,
cast( case
when GLAccountLineItem.DocumentItemText <> '' then GLAccountLineItem.DocumentItemText
when GLAccountLineItem.AssignmentReference <> '' then GLAccountLineItem.AssignmentReference //zuonr
else PostingDate
end as fis_sgtxt ) as DocumentItemText,
GLAccountLineItem.AssignmentReference,
@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: 'ClearingJournalEntry'
@ObjectModel.foreignKey.association: '_ClearingAccountingDocument'
GLAccountLineItem.ClearingAccountingDocument,
@ObjectModel.foreignKey.association: '_ClearingJournalEntry'
GLAccountLineItem.ClearingJournalEntry,
@ObjectModel.foreignKey.association: '_ClearingJrnlEntryFiscalYear'
GLAccountLineItem.ClearingJournalEntryFiscalYear,
GLAccountLineItem.ClearingDate,
@ObjectModel.foreignKey.association: '_ChartOfAccounts'
GLAccountLineItem.ChartOfAccounts,
cast( GLAccountLineItem.GLAccount as fis_racct ) as GLAccount,
_GLAcctInChartOfAccountsText[1:Language = 'F'].GLAccountName, //text20
_GLAcctInChartOfAccountsText[1:Language = 'F'].GLAccountLongName, //text50
FinancialAccountType, //koart
@ObjectModel.foreignKey.association: '_CountryChartOfAccounts'
GLAccountLineItem.CountryChartOfAccounts, //ktpl2
cast( ltrim( GLAccountLineItem.AlternativeGLAccount, '0' ) as fis_altkt_skb1 ) as AlternativeGLAccount, //local account lokkt
altGLAcc._Text[1:Language = 'F'].GLAccountName as AlternativeGLAccountName,
altGLAcc._Text[1:Language = 'F'].GLAccountLongName as AlternativeGLAccountLongName,
cast(case $parameters.P_AlternativeGLAccountIsUsed
when 'X' then GLAccountLineItem.CountryChartOfAccounts
else GLAccountLineItem.ChartOfAccounts
end as fifr_operational_coa_fec) as OperationalChartOfAccounts,
cast( case $parameters.P_AlternativeGLAccountIsUsed
when 'X' then
case
when GLAccountLineItem.AlternativeGLAccount <> ' ' then GLAccountLineItem.AlternativeGLAccount
else GLAccountLineItem.GLAccount
end
else GLAccountLineItem.GLAccount
end as fifr_operational_account ) as OperationalGLAccount,
cast(case $parameters.P_AlternativeGLAccountIsUsed
when 'X' then
case
when GLAccountLineItem.AlternativeGLAccount <> ' ' then altGLAcc._Text[1:Language = 'F'].GLAccountName
else 'Num.Cpt.alt.manquant'
end
else
_GLAcctInChartOfAccountsText[1:Language = 'F'].GLAccountName
end as fifr_operational_account_name ) as OperationalGLAccountName,
cast(case $parameters.P_AlternativeGLAccountIsUsed
when 'X' then
case
when GLAccountLineItem.AlternativeGLAccount <> ' ' then altGLAcc._Text[1:Language = 'F'].GLAccountLongName
else 'Num.Cpt.alt.manquant'
end
else
_GLAcctInChartOfAccountsText[1:Language = 'F'].GLAccountLongName
end as fifr_operational_acc_long_name ) as OperationalGLAccountLongName,
cast( case
when _Customer.Customer <> '' then _Customer.Customer
when _Supplier.Supplier <> '' then _Supplier.Supplier
else ''
end as bu_partner preserving type ) as BusinessPartner,
// CustomerName, SupplierName are Text80, which is too long for the DART file (CHAR35) -> NAME1 = OrganizationBPName1
// still cast to business_partner_name for the label
cast( case
when _Customer.Customer <> '' then coalesce(_OneTimeAccountBP.BusinessPartnerName1, _Customer.OrganizationBPName1)
when _Supplier.Supplier <> '' then coalesce(_OneTimeAccountBP.BusinessPartnerName1, _Supplier.OrganizationBPName1)
else ''
end as business_partner_name ) as BusinessPartnerName,
cast( case
when _Customer.Customer <> '' then _Customer.Customer
when _Supplier.Supplier <> '' then _Supplier.Supplier
else ''
end as fifr_subledgeraccount_fec preserving type ) as FR_FECSubledgerAccount,
cast( case
when _Customer.Customer <> '' then left( coalesce(_OneTimeAccountBP.BusinessPartnerName1, _Customer.OrganizationBPName1), 35)
when _Supplier.Supplier <> '' then left( coalesce(_OneTimeAccountBP.BusinessPartnerName1, _Supplier.OrganizationBPName1), 35)
else ''
end as fifr_subledgeraccountname_fec ) as FR_FECSubledgerAccountName,
@ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
@Semantics.currencyCode:true
GLAccountLineItem.CompanyCodeCurrency,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
cast( case GLAccountLineItem.DebitCreditCode
when 'H' then (-1) * GLAccountLineItem.AmountInCompanyCodeCurrency //dmbtr
else GLAccountLineItem.AmountInCompanyCodeCurrency
end as fis_hsl) as AmountInCompanyCodeCurrency,
@ObjectModel.foreignKey.association: '_TransactionCurrency'
@Semantics.currencyCode:true
TransactionCurrency,
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
AmountInTransactionCurrency,
@Semantics.currencyCode:true
cast( case
when ( GLAccountLineItem.CompanyCodeCurrency = GLAccountLineItem.TransactionCurrency ) then ' '
when ( GLAccountLineItem.CompanyCodeCurrency <> GLAccountLineItem.TransactionCurrency ) then GLAccountLineItem.TransactionCurrency
else ''
end as waers_frgn ) as ForeignCurrency,
@Semantics: { amount : {currencyCode: 'ForeignCurrency'} }
cast(
case
when ( GLAccountLineItem.CompanyCodeCurrency <> GLAccountLineItem.TransactionCurrency )
then GLAccountLineItem.AmountInTransactionCurrency //wrbtr
else 0
end as fifr_foreigncurrencyamount_fec ) as FR_FECAmountInForeignCurrency,
cast ( case GLAccountLineItem.DebitCreditCode
when 'H' then 'C'
when 'S' then 'D'
else ''
end as fis_shkzg ) as DebitCreditCode, //shkzg
@ObjectModel.foreignKey.association: '_AccountingDocumentCategory'
AccountingDocumentCategory,
@ObjectModel.foreignKey.association: '_ReferenceDocumentType'
ReferenceDocumentType,
_SourceLedger,
_CompanyCode,
_FiscalYear,
_JournalEntry,
_Ledger,
_AccountingDocumentType,
_ClearingJrnlEntryFiscalYear,
_ClearingJournalEntry,
_ClearingAccountingDocument,
_ChartOfAccounts,
_CountryChartOfAccounts,
_CompanyCodeCurrency,
_TransactionCurrency,
_AccountingDocumentCategory,
_ReferenceDocumentType,
_ReportedItemsLog
}
where
(
GLAccountLineItem.AccountingDocumentCategory = '' //bstat, initial (normal documents)
or GLAccountLineItem.AccountingDocumentCategory = 'L' //bstat, L (postings non in leading ledger)
or GLAccountLineItem.AccountingDocumentCategory = 'U' //bstat, U (postings in general ledger only)
)
// and Ledger = SourceLedger // to avoid multiple records in case of various SourceLedeger assigned to one Ledger
and GLAccountLineItem.AccountingDocumentType <> '' // AccountingDocumentType must be filled in ACDOCA
and AmountInCompanyCodeCurrency <> 0.00
and(
GLAccountLineItem.JrnlEntryItemMigrationSource = ''
or GLAccountLineItem.JrnlEntryItemMigrationSource = 'F'
or GLAccountLineItem.JrnlEntryItemMigrationSource = 'G'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENTTYPETEXT",
"I_CUSTOMER",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_GLACCOUNTLINEITEM",
"I_GLACCOUNTTEXT",
"I_JOURNALENTRY",
"I_ONETIMEACCOUNTBP",
"I_STRPJOURNALENTRYLOG",
"I_SUPPLIER"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENT",
"I_ACCOUNTINGDOCUMENTCATEGORY",
"I_ACCOUNTINGDOCUMENTTYPE",
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_CURRENCY",
"I_FISCALYEARFORCOMPANYCODE",
"I_JOURNALENTRY",
"I_LEDGER",
"I_ONETIMEACCOUNTBP",
"I_REFERENCEDOCUMENTTYPE",
"I_STRPJOURNALENTRYLOG"
],
"BASE":
[
"I_GLACCOUNTLINEITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/