@AbapCatalog.sqlViewName: 'PGMGBRRBB'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AbapCatalog.buffering.status: #NOT_ALLOWED
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@VDM.viewType: #COMPOSITE
@VDM.private:true
@ObjectModel: {
usageType: {
dataClass: #MIXED,
serviceQuality: #P,
sizeCategory: #XL
}
}
//@EndUserText.label: 'GM Billing Items RRB Billed'
define view P_GteeMBillgItmsRRBBilled
as select from I_GLAccountLineItem as AcItem
// inner join I_BillingDocumentBasic as SDBillDocHead on SDBillDocHead.BillingDocument = AcItem.ReferenceDocument
// and SDBillDocHead.CompanyCode = AcItem.CompanyCode
// and SDBillDocHead.FiscalYear = AcItem.FiscalYear
// inner join I_BillingDocumentItemBasic as SDBillDocItem on SDBillDocItem.BillingDocument = SDBillDocHead.BillingDocument
// and SDBillDocItem.BillingDocumentItem = AcItem.ReferenceDocumentItem
// // Skip rejected/returned items
// and SDBillDocItem.ReturnItemProcessingType = ''
// // Only include items to be posted or completed
// and( SDBillDocItem.OverallBillingStatus = 'A'
// or SDBillDocItem.OverallBillingStatus = 'B' )
inner join P_GteeMBillingElmntEntrFlwDoc as SDBillDocItem on SDBillDocItem.BillingDocument = AcItem.ReferenceDocument
and SDBillDocItem.CompanyCode = AcItem.CompanyCode
and SDBillDocItem.FiscalYear = AcItem.FiscalYear
and SDBillDocItem.BillingDocumentItem = AcItem.ReferenceDocumentItem
inner join I_SDDocumentMultiLevelProcFlow as SDDocFlow on SDDocFlow.SubsequentDocument = SDBillDocItem.BillingDocument
and SDDocFlow.SubsequentDocumentItem = SDBillDocItem.BillingDocumentItem
inner join I_GteeMBillingElementEntryFlw as GBEEF on GBEEF.BillingDocument = SDDocFlow.PrecedingDocument
and GBEEF.BillingDocumentItem = SDDocFlow.PrecedingDocumentItem
inner join I_GteeMBillingElementEntry as GBEE on GBEE.GteeMBillgElmntEntrItmUUID = GBEEF.GteeMBillgElmntEntrItmUUID
inner join I_GLAccountLineItem as BillableDoc on BillableDoc.Ledger = GBEE.Ledger
and BillableDoc.CompanyCode = GBEE.CompanyCode
and BillableDoc.FiscalYear = GBEE.FiscalYear
and BillableDoc.AccountingDocument = GBEE.AccountingDocument
and BillableDoc.LedgerGLLineItem = GBEE.LedgerGLLineItem
and BillableDoc.SourceLedger = GBEE.Ledger
{
// Document and item key
key AcItem.Ledger,
key AcItem.CompanyCode,
key AcItem.FiscalYear,
key AcItem.AccountingDocument,
key AcItem.LedgerGLLineItem,
key GBEE.GteeMBillgElmntEntrItmUUID,
GBEE.GteeMBillingPlanItemUUID,
// Document references
BillableDoc.ReferenceDocumentType,
BillableDoc.ReferenceDocument,
BillableDoc.ReferenceDocumentItem,
// Grant Account assignments
AcItem.GrantID,
AcItem.SponsoredProgram,
BillableDoc.SponsoredClass,
// Fund Account Assignments
AcItem.FinancialManagementArea,
AcItem.Fund,
// More Attributes
AcItem.CreationDate,
AcItem.PubSecBudgetCnsmpnType,
AcItem.PubSecBudgetCnsmpnDate,
// Currencies and Amounts
GBEEF.DocumentCurrency,
cast( '0' as gmbee_billablerevnindoccrcy ) as BillableRevenueAmtInDocCrcy,
cast(
case when GBEEF.BilledRevenueAmtInDocCrcy is not initial
then 0 - GBEEF.BilledRevenueAmtInDocCrcy
else 0 - GBEEF.BillgInProcAmtInDocCrcy
end as gmbee_notbilledamtdoccrcy
) as NotBilledAmtInDocCrcy,
// because of DocFlow issues above cast replaces: cast( 0 - GBEEF.BilledRevenueAmtInDocCrcy as gmbee_notbilledamtdoccrcy ) as NotBilledAmtInDocCrcy,
cast(
case when GBEEF.BilledRevenueAmtInDocCrcy is not initial
then GBEEF.BilledRevenueAmtInDocCrcy
else GBEEF.BillgInProcAmtInDocCrcy
end as gmbe_billedrevnindoccrcy
) as BilledRevenueAmtInDocCrcy,
// because of DocFlow issues above cast replaces: cast( GBEEF.BilledRevenueAmtInDocCrcy as gmbe_billedrevnindoccrcy ) as BilledRevenueAmtInDocCrcy,
cast( 0 - AcItem.AmountInCompanyCodeCurrency as gmbe_notpaidrevnamtindoccrcy ) as NotPaidRevenueAmtInDocCrcy,
cast( '0' as gmbe_paidrevnindoccrcy ) as PaidRevenueAmtInDocCrcy,
// Associations
AcItem._Grant,
AcItem._SponsoredProgram
}
where
AcItem.Ledger = '0L'
and AcItem.SourceLedger = '0L'
and AcItem.GrantID is not initial
and AcItem.ReferenceDocumentType = 'VBRK'
and AcItem._Grant.GrantIsNotRelevantGrant = ' '
and(
AcItem._Grant.GranteeMgmtBillingRule = '0002'
or AcItem._Grant.GranteeMgmtBillingRule = '0003'
)
union all select from I_GLAccountLineItem as AcItem
// inner join I_BillingDocumentBasic as SDBillDocHead on SDBillDocHead.BillingDocument = AcItem.ReferenceDocument
// and SDBillDocHead.CompanyCode = AcItem.CompanyCode
// and SDBillDocHead.FiscalYear = AcItem.FiscalYear
// inner join I_BillingDocumentItemBasic as SDBillDocItem on SDBillDocItem.BillingDocument = SDBillDocHead.BillingDocument
// and SDBillDocItem.BillingDocumentItem = AcItem.ReferenceDocumentItem
inner join P_GteeMBillingElmntEntrFlwDoc as SDBillDocItem on SDBillDocItem.BillingDocument = AcItem.ReferenceDocument
and SDBillDocItem.CompanyCode = AcItem.CompanyCode
and SDBillDocItem.FiscalYear = AcItem.FiscalYear
and SDBillDocItem.BillingDocumentItem = AcItem.ReferenceDocumentItem
inner join I_SDDocumentMultiLevelProcFlow as SDDocFlow on SDDocFlow.SubsequentDocument = SDBillDocItem.BillingDocument
and SDDocFlow.SubsequentDocumentItem = SDBillDocItem.BillingDocumentItem
inner join I_GteeMBillingElementEntryFlw as GBEEF on GBEEF.BillingDocument = SDDocFlow.PrecedingDocument
and GBEEF.BillingDocumentItem = SDDocFlow.PrecedingDocumentItem
inner join I_GteeMBillingElementEntry as GBEE on GBEE.GteeMBillgElmntEntrItmUUID = GBEEF.GteeMBillgElmntEntrItmUUID
inner join I_GteeMBillingPlanItem as GBPI on GBPI.GteeMBillingPlanItemUUID = GBEE.GteeMBillingPlanItemUUID
{
// Document and item key
key AcItem.Ledger,
key AcItem.CompanyCode,
key AcItem.FiscalYear,
key AcItem.AccountingDocument,
key AcItem.LedgerGLLineItem,
key GBEE.GteeMBillgElmntEntrItmUUID,
GBEE.GteeMBillingPlanItemUUID,
// Document references
cast( '' as fis_awtyp ) as ReferenceDocumentType,
cast( '' as awref ) as ReferenceDocument,
cast( '000000' as fis_awitem ) as ReferenceDocumentItem,
// Grant Account assignments
AcItem.GrantID,
AcItem.SponsoredProgram,
GBEE.SponsoredClass,
// Fund Account Assignments
AcItem.FinancialManagementArea,
AcItem.Fund,
// More Attributes
AcItem.CreationDate,
AcItem.PubSecBudgetCnsmpnType,
AcItem.PubSecBudgetCnsmpnDate,
// Currencies and Amounts
GBEEF.DocumentCurrency,
cast( '0' as gmbee_billablerevnindoccrcy ) as BillableRevenueAmtInDocCrcy,
cast(
case when GBEEF.BilledRevenueAmtInDocCrcy is not initial
then 0 - GBEEF.BilledRevenueAmtInDocCrcy
else 0 - GBEEF.BillgInProcAmtInDocCrcy
end as gmbee_notbilledamtdoccrcy
) as NotBilledAmtInDocCrcy,
// because of DocFlow issues above cast replaces: cast( 0 - GBEEF.BilledRevenueAmtInDocCrcy as gmbee_notbilledamtdoccrcy ) as NotBilledAmtInDocCrcy,
cast(
case when GBEEF.BilledRevenueAmtInDocCrcy is not initial
then GBEEF.BilledRevenueAmtInDocCrcy
else GBEEF.BillgInProcAmtInDocCrcy
end as gmbe_billedrevnindoccrcy
) as BilledRevenueAmtInDocCrcy,
// because of DocFlow issues above cast replaces: cast( GBEEF.BilledRevenueAmtInDocCrcy as gmbe_billedrevnindoccrcy ) as BilledRevenueAmtInDocCrcy,
cast( 0 - AcItem.AmountInCompanyCodeCurrency as gmbe_notpaidrevnamtindoccrcy ) as NotPaidRevenueAmtInDocCrcy,
cast( '0' as gmbe_paidrevnindoccrcy ) as PaidRevenueAmtInDocCrcy,
// Associations
AcItem._Grant,
AcItem._SponsoredProgram
}
where
AcItem.Ledger = '0L'
and AcItem.SourceLedger = '0L'
and AcItem.GrantID is not initial
and AcItem.ReferenceDocumentType = 'VBRK'
and AcItem._Grant.GrantIsNotRelevantGrant = ' '
and AcItem._Grant.GranteeMgmtBillingRule = '0004'