@AbapCatalog.sqlViewName: 'IGLJEIVER'
@VDM.viewType: #COMPOSITE
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AbapCatalog.compiler.compareFilter: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MASTER
@ObjectModel.usageType.sizeCategory: #M
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Union View of LI in GL JE to be verified'
define view I_GLJrnlEntryItemToBeVerified
as select from I_ParkedGeneralLedgerItem
left outer to one join P_JrnlEntrWthFuncnlCrcy as _JournalEntryCust on I_ParkedGeneralLedgerItem.SourceCompanyCode = _JournalEntryCust.SourceCompanyCode
and I_ParkedGeneralLedgerItem.CompanyCode = _JournalEntryCust.CompanyCode
and I_ParkedGeneralLedgerItem.FiscalYear = _JournalEntryCust.FiscalYear
and I_ParkedGeneralLedgerItem.AccountingDocument = _JournalEntryCust.AccountingDocument
and I_ParkedGeneralLedgerItem.AccountingDocumentItem = _JournalEntryCust.AccountingDocumentItem
{
key cast( concat( '000', I_ParkedGeneralLedgerItem.AccountingDocumentItem) as buzei6 ) as AccountingDocumentItem,
key I_ParkedGeneralLedgerItem.SourceCompanyCode,
key I_ParkedGeneralLedgerItem.FiscalYear,
key I_ParkedGeneralLedgerItem.AccountingDocument,
I_ParkedGeneralLedgerItem.CompanyCode,
I_ParkedGeneralLedgerItem.GLAccount,
I_ParkedGeneralLedgerItem.DocumentItemText,
I_ParkedGeneralLedgerItem.DebitCreditCode,
AccountingDocumentCategory,
case DebitCreditCode
when 'H' then -1 * I_ParkedGeneralLedgerItem.AmountInCompanyCodeCurrency
else I_ParkedGeneralLedgerItem.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
//vbsegs.dmbtr as AmountInCompanyCodeCurrency,
case DebitCreditCode
when 'H' then -1 * I_ParkedGeneralLedgerItem.AmountInTransactionCurrency
else I_ParkedGeneralLedgerItem.AmountInTransactionCurrency
end as AmountInTransactionCurrency,
I_ParkedGeneralLedgerItem.TransactionCurrency,
I_ParkedGeneralLedgerItem.CompanyCodeCurrency,
I_ParkedGeneralLedgerItem.ExchangeRateDate,
//Global Currency
case DebitCreditCode
when 'H' then -1 * I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency1
else I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency1
end as AmountInGlobalCurrency, // Amount in 2nd FI currency
I_ParkedGeneralLedgerItem.AdditionalCurrency1 as GlobalCurrency, //2nd FI currency
//3rd FI currency
case DebitCreditCode
when 'H' then -1 * I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency2
else I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency2
end as AmountInFreeDefinedCurrency1, // Amount in 3rd FI currency
I_ParkedGeneralLedgerItem.AdditionalCurrency2 as FreeDefinedCurrency1, //3rd FI currency
case
when DebitCreditCode = 'H' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '1'
then -1 * I_ParkedGeneralLedgerItem.AmountInCompanyCodeCurrency
when DebitCreditCode = 'S' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '1'
then I_ParkedGeneralLedgerItem.AmountInCompanyCodeCurrency
when DebitCreditCode = 'H' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '2'
then -1 * I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency1
when DebitCreditCode = 'S' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '2'
then I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency1
when DebitCreditCode = 'H' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '3'
then -1 * I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency2
when DebitCreditCode = 'S' and _JournalEntryCust.JrnlEntrItemCrcyRolePosition = '3'
then I_ParkedGeneralLedgerItem.AmountInAdditionalCurrency2
else cast( 0 as fins_vfccur12 )
end as AmountInFunctionalCurrency,
case _JournalEntryCust.JrnlEntrItemCrcyRolePosition
when '1' then I_ParkedGeneralLedgerItem.CompanyCodeCurrency
when '2' then I_ParkedGeneralLedgerItem.AdditionalCurrency1
when '3' then I_ParkedGeneralLedgerItem.AdditionalCurrency2
else cast( '' as fins_currfc )
end as FunctionalCurrency,
//for inbox
I_ParkedGeneralLedgerItem.ProfitCenter,
I_ParkedGeneralLedgerItem.CostCenter,
I_ParkedGeneralLedgerItem.WBSElementInternalID
}
union all select from I_JournalEntryItem //I_OperationalAcctgDocItem
// as select from I_JournalEntryItem
inner join P_JrnlEntrWithRprstvLdgr as _JournalEntryRprstvLdgr on I_JournalEntryItem.CompanyCode = _JournalEntryRprstvLdgr.CompanyCode
and I_JournalEntryItem.FiscalYear = _JournalEntryRprstvLdgr.FiscalYear
and I_JournalEntryItem.AccountingDocument = _JournalEntryRprstvLdgr.AccountingDocument
and I_JournalEntryItem.Ledger = _JournalEntryRprstvLdgr.Ledger
left outer join I_AccountingDocument on I_AccountingDocument.AccountingDocument = I_JournalEntryItem.AccountingDocument
and I_AccountingDocument.FiscalYear = I_JournalEntryItem.FiscalYear
and I_AccountingDocument.CompanyCode = I_JournalEntryItem.CompanyCode
{
//key cast( concat( '000', I_JournalEntryItem.AccountingDocumentItem) as buzei6 ) as AccountingDocumentItem,
key cast( I_JournalEntryItem.LedgerGLLineItem as buzei6 ) as AccountingDocumentItem,
key I_JournalEntryItem.CompanyCode as SourceCompanyCode,
key I_JournalEntryItem.FiscalYear,
key I_JournalEntryItem.AccountingDocument,
// key I_JournalEntryItem.SourceLedger,
// key I_JournalEntryItem.LedgerGLLineItem,
//key I_JournalEntryItem.Ledger,
I_JournalEntryItem.CompanyCode,
I_JournalEntryItem.GLAccount,
I_JournalEntryItem.DocumentItemText,
I_JournalEntryItem.DebitCreditCode,
I_JournalEntryItem.AccountingDocumentCategory,
case DebitCreditCode
when 'H' then -1 * I_JournalEntryItem.AmountInCompanyCodeCurrency
else I_JournalEntryItem.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
case DebitCreditCode
when 'H' then -1 * I_JournalEntryItem.AmountInTransactionCurrency
else I_JournalEntryItem.AmountInTransactionCurrency
end as AmountInTransactionCurrency,
I_JournalEntryItem.TransactionCurrency as TransactionCurrency,
I_JournalEntryItem.CompanyCodeCurrency as CompanyCodeCurrency,
I_AccountingDocument.ExchangeRateDate,
case DebitCreditCode
when 'H' then -1 * I_JournalEntryItem.AmountInGlobalCurrency
else I_JournalEntryItem.AmountInGlobalCurrency
end as AmountInGlobalCurrency,
I_JournalEntryItem.GlobalCurrency as GlobalCurrency,
case DebitCreditCode
when 'H' then -1 * I_JournalEntryItem.AmountInFreeDefinedCurrency1
else I_JournalEntryItem.AmountInFreeDefinedCurrency1
end as AmountInFreeDefinedCurrency1,
I_JournalEntryItem.FreeDefinedCurrency1 as FreeDefinedCurrency1,
case DebitCreditCode
when 'H' then -1 * I_JournalEntryItem.AmountInFunctionalCurrency
else I_JournalEntryItem.AmountInFunctionalCurrency
end as AmountInFunctionalCurrency,
I_JournalEntryItem.FunctionalCurrency as FunctionalCurrency,
//for inbox
I_JournalEntryItem.ProfitCenter,
I_JournalEntryItem.CostCenter,
I_JournalEntryItem.WBSElementInternalID
}
/*union all select from I_OperationalAcctgDocItem
left outer join I_AccountingDocument on I_OperationalAcctgDocItem.AccountingDocument = I_AccountingDocument.AccountingDocument
and I_OperationalAcctgDocItem.FiscalYear = I_AccountingDocument.FiscalYear
and I_OperationalAcctgDocItem.CompanyCode = I_AccountingDocument.CompanyCode
{
key cast( concat( '000', I_OperationalAcctgDocItem.AccountingDocumentItem) as buzei6 ) as AccountingDocumentItem,
key I_OperationalAcctgDocItem.CompanyCode as SourceCompanyCode,
key I_OperationalAcctgDocItem.FiscalYear,
key I_OperationalAcctgDocItem.AccountingDocument,
I_OperationalAcctgDocItem.CompanyCode,
I_OperationalAcctgDocItem.GLAccount,
I_OperationalAcctgDocItem.DocumentItemText,
I_OperationalAcctgDocItem.DebitCreditCode,
case DebitCreditCode
when 'H' then -1 * I_OperationalAcctgDocItem.AmountInCompanyCodeCurrency
else I_OperationalAcctgDocItem.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
case DebitCreditCode
when 'H' then -1 * I_OperationalAcctgDocItem.AmountInTransactionCurrency
else I_OperationalAcctgDocItem.AmountInTransactionCurrency
end as AmountInTransactionCurrency,
I_AccountingDocument.Currency as TransactionCurrency,
I_AccountingDocument.CompanyCodeCurrency,
I_AccountingDocument.ExchangeRateDate,
//Global Currency
case DebitCreditCode
when 'H' then -1 * I_OperationalAcctgDocItem.AmountInAdditionalCurrency1
else I_OperationalAcctgDocItem.AmountInAdditionalCurrency1
end as AmountInGlobalCurrency, // Amount in 2nd FI currency
I_OperationalAcctgDocItem.AdditionalCurrency1 as GlobalCurrency, //2nd FI currency
//3rd FI currency
case DebitCreditCode
when 'H' then -1 * I_OperationalAcctgDocItem.AmountInAdditionalCurrency2
else I_OperationalAcctgDocItem.AmountInAdditionalCurrency2
end as AmountInFreeDefinedCurrency1, // Amount in 3rd FI currency
I_OperationalAcctgDocItem.AdditionalCurrency2 as FreeDefinedCurrency1, //3rd FI currency
//for inbox
I_OperationalAcctgDocItem.ProfitCenter,
I_OperationalAcctgDocItem.CostCenter,
I_OperationalAcctgDocItem.WBSElementInternalID
}
union all select from I_AddlLedgerOplAcctgDocItem
left outer join I_AccountingDocument on I_AddlLedgerOplAcctgDocItem.AccountingDocument = I_AccountingDocument.AccountingDocument
and I_AddlLedgerOplAcctgDocItem.FiscalYear = I_AccountingDocument.FiscalYear
and I_AddlLedgerOplAcctgDocItem.CompanyCode = I_AccountingDocument.CompanyCode
{
key I_AddlLedgerOplAcctgDocItem.AccountingDocumentItem,
key I_AddlLedgerOplAcctgDocItem.CompanyCode as SourceCompanyCode,
key I_AddlLedgerOplAcctgDocItem.FiscalYear,
key I_AddlLedgerOplAcctgDocItem.AccountingDocument,
I_AddlLedgerOplAcctgDocItem.CompanyCode,
I_AddlLedgerOplAcctgDocItem.GLAccount,
I_AddlLedgerOplAcctgDocItem.DocumentItemText,
I_AddlLedgerOplAcctgDocItem.DebitCreditCode,
case DebitCreditCode
when 'H' then -1 * I_AddlLedgerOplAcctgDocItem.AmountInCompanyCodeCurrency
else I_AddlLedgerOplAcctgDocItem.AmountInCompanyCodeCurrency
end as AmountInCompanyCodeCurrency,
case DebitCreditCode
when 'H' then -1 * I_AddlLedgerOplAcctgDocItem.AmountInTransactionCurrency
else I_AddlLedgerOplAcctgDocItem.AmountInTransactionCurrency
end as AmountInTransactionCurrency,
I_AccountingDocument.Currency as TransactionCurrency,
I_AccountingDocument.CompanyCodeCurrency,
I_AccountingDocument.ExchangeRateDate,
//Global Currency
case DebitCreditCode
when 'H' then -1 * I_AddlLedgerOplAcctgDocItem.AmountInAdditionalCurrency1
else I_AddlLedgerOplAcctgDocItem.AmountInAdditionalCurrency1
end as AmountInGlobalCurrency, // Amount in 2nd FI currency
I_AddlLedgerOplAcctgDocItem.AdditionalCurrency1 as GlobalCurrency, //2nd FI currency
//3rd FI currency
case DebitCreditCode
when 'H' then -1 * I_AddlLedgerOplAcctgDocItem.AmountInAdditionalCurrency2
else I_AddlLedgerOplAcctgDocItem.AmountInAdditionalCurrency2
end as AmountInFreeDefinedCurrency1, // Amount in 3rd FI currency
I_AddlLedgerOplAcctgDocItem.AdditionalCurrency2 as FreeDefinedCurrency1, //3rd FI currency
//for inbox
I_AddlLedgerOplAcctgDocItem.ProfitCenter,
I_AddlLedgerOplAcctgDocItem.CostCenter,
I_AddlLedgerOplAcctgDocItem.WBSElementInternalID
}*/
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_JOURNALENTRYITEM",
"I_PARKEDGENERALLEDGERITEM",
"P_JRNLENTRWITHRPRSTVLDGR",
"P_JRNLENTRWTHFUNCNLCRCY"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/