@AbapCatalog.sqlViewName: 'PFROVRDINVCINVCI'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
define view P_FR_OverdueInvoiceItem
with parameters
P_KeyDate : vdm_v_key_date,
@Consumption.defaultValue: 'M'
P_ExchangeRateType : kurst
as select from I_OperationalAcctgDocItem as BSEGITEM
left outer to many join P_FR_OverduePaymentItem(
P_KeyDate : :P_KeyDate,
P_ExchangeRateType : :P_ExchangeRateType ) as PAYTBSEG on PAYTBSEG.CompanyCode = BSEGITEM.CompanyCode
and PAYTBSEG.InvoiceReferenceFiscalYear = BSEGITEM.FiscalYear
and PAYTBSEG.InvoiceReference = BSEGITEM.AccountingDocument
and PAYTBSEG.InvoiceItemReference = BSEGITEM.AccountingDocumentItem
and PAYTBSEG.PostingDate <= $parameters.P_KeyDate
{
@ObjectModel.foreignKey.association: '_CompanyCode'
key BSEGITEM.CompanyCode,
@ObjectModel.foreignKey.association: '_FiscalYear'
key BSEGITEM.FiscalYear,
@ObjectModel.foreignKey.association: '_JournalEntry'
key BSEGITEM.AccountingDocument,
key BSEGITEM.AccountingDocumentItem,
@ObjectModel.foreignKey.association: '_AccountingDocumentType'
BSEGITEM.AccountingDocumentType,
BSEGITEM.IsUsedInPaymentTransaction, // should be empty
@ObjectModel.foreignKey.association: '_FinancialAccountType'
BSEGITEM.FinancialAccountType,
case
when BSEGITEM.FinancialAccountType = 'D' then
BSEGITEM._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner
when BSEGITEM.FinancialAccountType = 'K' then
BSEGITEM._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner
end as BusinessPartner,
BSEGITEM.PostingDate,
BSEGITEM.DocumentDate,
@ObjectModel.foreignKey.association: '_FiscalPeriod'
BSEGITEM.FiscalPeriod,
@ObjectModel.foreignKey.association: '_GLAccountInCompanyCode'
BSEGITEM.GLAccount,
BSEGITEM.DueCalculationBaseDate, //zfbdt
BSEGITEM.NetDueDate, //netdt
BSEGITEM.PaymentTerms, //zterm
case
when BSEGITEM.SpecialGLTransactionType <> 'A'
then dats_days_between( NetDueDate, :P_KeyDate )
else
case
when BSEGITEM.FinancialAccountType = 'K' and BSEGITEM.DebitCreditCode = 'S'
//NetDueArrearsDays is set to zero, see special logic in FM ITEM_DERIVE_FIELDS
then dats_days_between( NetDueDate, NetDueDate )
when BSEGITEM.FinancialAccountType = 'D' and BSEGITEM.DebitCreditCode = 'H'
//NetDueArrearsDays is set to zero, see special logic in FM ITEM_DERIVE_FIELDS
then dats_days_between( NetDueDate, NetDueDate )
else dats_days_between( NetDueDate, :P_KeyDate )
end
end as NetDueArrearsDaysAsInt,
BSEGITEM.PaymentBlockingReason,
@ObjectModel.foreignKey.association: '_SpecialGLCode'
BSEGITEM.SpecialGLCode, //umskz
BSEGITEM.SpecialGLTransactionType, //umsks
// @ObjectModel.foreignKey.association: '_ClearingAccountingDocument' "STW
//BSEGITEM.ClearingAccountingDocument, "STW
// BSEGITEM.ClearingDate, "STW
@ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
@Semantics.currencyCode:true
BSEGITEM.CompanyCodeCurrency,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
BSEGITEM.AmountInCompanyCodeCurrency,
@ObjectModel.foreignKey.association: '_TransactionCurrency'
@Semantics.currencyCode:true
BSEGITEM.TransactionCurrency,
@Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
BSEGITEM.AmountInTransactionCurrency,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case
when BSEGITEM.CompanyCodeCurrency = BSEGITEM.TransactionCurrency
then BSEGITEM.AmountInCompanyCodeCurrency
else
cast( currency_conversion(
amount => BSEGITEM.AmountInTransactionCurrency,
source_currency => BSEGITEM.TransactionCurrency,
target_currency => BSEGITEM.CompanyCodeCurrency,
exchange_rate_date => :P_KeyDate,
exchange_rate_type => :P_ExchangeRateType,
// error_handling => 'FAIL_ON_ERROR',
error_handling => 'SET_TO_NULL',
round => #CDSBoolean.true
) as bykdtevaldinvcamt )
end as ByKDtEvaldInvcAmtInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
cast( sum( PAYTBSEG.ByKDtEvaldInvcAmtInCoCodeCrc ) as invoicealreadypaidamt ) as InvoiceAlreadyPaidAmtInCCCrcy,
BSEGITEM._CompanyCode,
BSEGITEM._FiscalYear,
BSEGITEM._JournalEntry,
BSEGITEM._AccountingDocumentType,
BSEGITEM._FiscalPeriod,
BSEGITEM._FinancialAccountType,
BSEGITEM._GLAccountInCompanyCode,
BSEGITEM._SpecialGLCode,
//BSEGITEM._ClearingAccountingDocument, "STW
BSEGITEM._CompanyCodeCurrency,
BSEGITEM._TransactionCurrency
}
where
(
BSEGITEM.IsUsedInPaymentTransaction = ' ' // no payments
or(
BSEGITEM.IsUsedInPaymentTransaction = 'X'
and BSEGITEM.FollowOnDocumentType = 'V'
)
)
and(
BSEGITEM.FinancialAccountType = 'K'
or BSEGITEM.FinancialAccountType = 'D'
)
and(
(
BSEGITEM.ClearingDate <> '00000000'
and BSEGITEM.ClearingDate >= $parameters.P_KeyDate
and BSEGITEM.PostingDate <= $parameters.P_KeyDate
)
or(
BSEGITEM.ClearingDate = '00000000'
and BSEGITEM.PostingDate <= $parameters.P_KeyDate
)
)
and BSEGITEM.AccountingDocumentCategory = ' '
group by
BSEGITEM.CompanyCode,
BSEGITEM.FiscalYear,
BSEGITEM.AccountingDocument,
BSEGITEM.AccountingDocumentItem,
BSEGITEM.AccountingDocumentType,
BSEGITEM.IsUsedInPaymentTransaction,
BSEGITEM.FinancialAccountType,
BSEGITEM._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner,
BSEGITEM._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner,
BSEGITEM.PostingDate,
BSEGITEM.DocumentDate,
BSEGITEM.FiscalPeriod,
BSEGITEM.GLAccount,
BSEGITEM.DueCalculationBaseDate,
BSEGITEM.NetDueDate,
BSEGITEM.PaymentTerms,
BSEGITEM.PaymentBlockingReason,
BSEGITEM.SpecialGLCode,
BSEGITEM.SpecialGLTransactionType,
//BSEGITEM.ClearingAccountingDocument, "STW
//BSEGITEM.ClearingDate, "STW
BSEGITEM.CompanyCodeCurrency,
BSEGITEM.AmountInCompanyCodeCurrency,
BSEGITEM.TransactionCurrency,
BSEGITEM.AmountInTransactionCurrency,
BSEGITEM.DebitCreditCode,
BSEGITEM.AccountingDocumentCategory
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BUSINESSPARTNER",
"I_CUSTOMER",
"I_CUSTOMERTOBUSINESSPARTNER",
"I_OPERATIONALACCTGDOCITEM",
"I_SUPPLIER",
"I_SUPPLIERTOBUSINESSPARTNER",
"P_FR_OVERDUEPAYMENTITEM"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTTYPE",
"I_COMPANYCODE",
"I_CURRENCY",
"I_FINANCIALACCOUNTTYPE",
"I_FISCALYEARFORCOMPANYCODE",
"I_FISCALYEARPERIODFORCMPNYCODE",
"I_GLACCOUNTINCOMPANYCODE",
"I_JOURNALENTRY",
"I_SPECIALGLCODE"
],
"BASE":
[
"I_OPERATIONALACCTGDOCITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/