// Collect related documents for a specific request
//
// ***Complex scenarios with sub-item***
// A subitem is automatically created when the amount of an item is not cleared (in our case, pay and return) in its entirety.
// For each partial clearing, a new subitem is created with the cleared amount. At the same time, the amount still open is
// reduced by the current partial clearing amount under the subitem number "0".
// As a result, the larger subitem means the latest to be cleared. For example,
//
// The origin doc is as following
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag, Cancelled flag
// 1001 100
// 1002 200
//
// When the reverse 1002 (amt 200) comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag, Cancelled flag
// 1001 100
// 1002 200 4001 X
//
// When the payment (30 out of 100) comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag
// 2001 30 02
// 1001 30 2001 1 30
// 1001 70 0(default)
//
// When pay the rest (70 out of 70) comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag
// 2002 70 02
// 2001 30 02
// 1001 30 2001 1 30
// 1001 70 2002 0(default) 70
//
// When the partial release (30 out of 100) comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag
// 2002 70 02
// 2001 30
// 1001 30 2001 1 30
// 1001 70 2002 0(default) 70
//
// Another partial release (50 out of 70) comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag
// 2002 50 1
// 2002 20 02
// 2001 30
// 1001 30 2001 1 30
// 1001 70 2002 0(default) 70
//
// When the return comes to clear the payment (30 out of 100) on 2001 comes
// Be Cleared Doc, Origin amt, Clear Doc, subitem, Clear Amt, Restriction Flag
// 3003 20
// 2002 50 1
// 2002 20 02
// 2001 30 3003 20
// 1001 30 2001 1 30
// 1001 70 2002 0(default) 70
// For interest document, the item view is I_CAInterestSupplement
// The relationship is Security Deposit ID, Payment Document and Payment Document Item
// Each payment document can pay for multiple security deposit requests for multiple security deposits
// Each interest document can calculated for multiple payment documents
// For example, payment document 400042342 is listed as below
// Document Item Triggering Document Amount
// 400042342 1 400042307 300
// 400042342 2 400042308 600
// 400042342 3 200000003777 1000
// 400042342 4 200000003778 200
// 400042342 5 200000003779 500
// Interest document 120000009132 is listed as below
// Document Item Calculated Document Item Document Amount Interest Amount
// 120000009132 1 400042342 3 1000 10
// 120000009132 2 400042342 4 200 2
// 120000009132 3 400042342 5 500 5
@AbapCatalog.sqlViewName: 'PCOLLRELDOC'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.viewType: #COMPOSITE
@VDM.private: true
define view P_CACollectRelateDocForRequest
as select distinct from I_CACashSecurityDepositRequest as RequestDoc
inner join I_CADocumentBPItemPhysical as RequestToPayReverse on RequestDoc.CADocumentNumber = RequestToPayReverse.CADocumentNumber
left outer join I_CADocumentBPItemPhysical as PayToReturn on RequestToPayReverse.CAClearingDocumentNumber = PayToReturn.CADocumentNumber
and RequestToPayReverse.CADocumentNumber = PayToReturn.CADocumentNumberOfOriginItem
// P_CAClearingDocument and PayToReturn.CADocumentNumberOfOriginItem <> ''
// and RequestToPayReverse.CAClearingDocumentNumber <> ''
left outer join I_CAInterestSupplement as InterestDoc on InterestDoc.CASecurityDeposit = RequestDoc.CASecurityDeposit
and InterestDoc.CADocumentNumber = PayToReturn.CADocumentNumber
and InterestDoc.CABPItemNumber = PayToReturn.CABPItemNumber
{
/* Request Document Key */
key RequestDoc.CASecurityDeposit,
key RequestDoc.CADocumentNumber,
key RequestToPayReverse.CASubItemNumber as CASubItemNumber,
/* Payment Document Key */
key case when RequestToPayReverse.CAItemIsWithdrawn = 'X' then ' '
when RequestToPayReverse.CAItemIsWithdrawn <> 'X' then RequestToPayReverse.CAClearingDocumentNumber end as CAPaymentDocument,
key PayToReturn.CABPItemNumber as CAPaymentBPItemNumber,
key PayToReturn.CASubItemNumber as CAPaymentDocumentSubItem,
/* Return Document Key */
key PayToReturn.CAClearingDocumentNumber as CAReturnDocumentNumber,
// return document do not have entry in DFKKOP, so sub-item number should be empty
/* Reverse Document Key */
key case when RequestToPayReverse.CAItemIsWithdrawn = 'X'
then RequestToPayReverse.CAClearingDocumentNumber
when RequestToPayReverse.CAItemIsWithdrawn <> 'X' then ' ' end as CAReversalDocumentNumber,
// reverse document do not have entry in DFKKOP, so sub-item number should be empty
/* Interest Document Key */
key InterestDoc.CAInterestDocument,
/* Authorization Group */
RequestDoc._SecurityDeposit.CAAuthorizationGroup,
/* Amounts */
@Semantics.amount.currencyCode: 'TransactionCurrency'
RequestToPayReverse.CAAmountInTransactionCurrency as CAAmountInTransactionCurrency,
@Semantics.amount.currencyCode: 'TransactionCurrency'
cast(case when RequestToPayReverse.CAItemIsWithdrawn = 'X'
then RequestToPayReverse.CAClearingAmountInClearingCrcy
when RequestToPayReverse.CAItemIsWithdrawn <> 'X' then 0 end as secdep_reversed_amt_kk) as CAReversedAmtInTransCurrency,
@Semantics.amount.currencyCode: 'TransactionCurrency'
RequestToPayReverse.CAAmountInTransactionCurrency as CAPaidAmountInTransCurrency,
@Semantics.amount.currencyCode: 'PaymentCurrency'
cast(case when RequestToPayReverse.CAClearingDocumentNumber <> ''
then PayToReturn.CAAmountInTransactionCurrency
when RequestToPayReverse.CAClearingDocumentNumber = '' then 0 end as betrw_kk ) as CAPaymentAmountInPaytCurrency,
@Semantics.amount.currencyCode: 'PaymentCurrency'
cast(case when PayToReturn.CASubItemNumber <> '' and PayToReturn.CAClearingRestrictionCode = ''
then PayToReturn.CAAmountInTransactionCurrency
else 0 end as rel_part_kk) as CAReleasedAmtInPaymentCurrency,
@Semantics.amount.currencyCode: 'RefundCurrency'
PayToReturn.CAClearingAmountInClearingCrcy as AmountInRefundCurrency,
@Semantics.currencyCode: true
RequestToPayReverse.TransactionCurrency,
@Semantics.currencyCode: true
cast(PayToReturn.TransactionCurrency as farp_fwaer) as PaymentCurrency,
@Semantics.currencyCode: true
cast(PayToReturn.CAClearingCurrency as secdep_return_waers_kk) as RefundCurrency
}