@AbapCatalog.sqlViewName: 'IIDVATINCUBE'
@AbapCatalog.preserveKey: true
@AbapCatalog.compiler.compareFilter: true
@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: false }
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #CHECK
@Metadata.allowExtensions:true
@ObjectModel: { usageType.serviceQuality: #D,
usageType.sizeCategory: #XXL,
usageType.dataClass: #MIXED }
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@Analytics.internalName:#LOCAL
@EndUserText.label: 'Indonesia VAT Return In - Cube'
define view I_ID_StRpVATReturnInCube
with parameters
@EndUserText.label: 'Company Code'
P_CompanyCode : fis_bukrs,
P_StatryRptgEntity : srf_reporting_entity,
P_StatryRptCategory : srf_rep_cat_id,
P_StatryRptRunID : srf_report_run_id,
P_ReportingCountry : land1,
P_TaxBoxConfiguration : figen_tdt_taxbox_config,
P_FiscalYear : gjahr
as select from I_AccountingDocument as AccountingDocument
inner join I_AcctDocCustomer as _AccDocCust on _AccDocCust.CompanyCode = :P_CompanyCode
and _AccDocCust.FiscalYear = :P_FiscalYear
and AccountingDocument.AccountingDocument = _AccDocCust.AccountingDocument
and (_AccDocCust.Customer != '' or _AccDocCust.Supplier != '')
left outer to many join I_ID_StRpTaxReturnItemGroup (P_CompanyCode: :P_CompanyCode, P_StatryRptgEntity: :P_StatryRptgEntity, P_StatryRptCategory: :P_StatryRptCategory, P_StatryRptRunID: :P_StatryRptRunID, P_ReportingCountry: :P_ReportingCountry, P_TaxBoxConfiguration: :P_TaxBoxConfiguration ,P_FiscalYear : :P_FiscalYear ) as _TaxItem
on _TaxItem.CompanyCode = :P_CompanyCode
and _TaxItem.FiscalYear = :P_FiscalYear
and AccountingDocument.AccountingDocument = _TaxItem.AccountingDocument
association [0..1] to I_Supplier as _Supplier on $projection.Supplier = _Supplier.Supplier
association [0..1] to I_Customer as _Customer on $projection.Customer = _Customer.Customer
{
key AccountingDocument.CompanyCode,
key AccountingDocument.FiscalYear,
key AccountingDocument.AccountingDocument,
key _TaxItem.TaxBoxStrucValidityStartDate as TaxBoxStrucValidityStartDate,
key _TaxItem.UnifiedTaxBoxStructureType,
_TaxItem.TaxCode,
_TaxItem._StRpBPTaxItem.TaxRate,
TaxRateValidityStartDate,
TaxBoxStrucValidityEndDate,
TransactionCode,
AccountingDocument.AccountingDocumentType,
_AccDocCust.BusinessPlace as BusinessPlace,
AccountingDocument.AccountingDocumentHeaderText as AccountingDocumentHeaderText,
AccountingDocument.Reference1InDocumentHeader as Reference1InDocumentHeader,
AccountingDocument.Reference2InDocumentHeader as Reference2InDocumentHeader,
AccountingDocument.OriginalReferenceDocument as OriginalReferenceDocument,
substring(AccountingDocument.DocumentReferenceID, 4, 16) as DocumentReferenceID,
AccountingDocument.FiscalPeriod as FiscalPeriod,
AccountingDocument.DocumentDate as BillingDate,
AccountingDocument.DocumentDate as DocumentDate,
AccountingDocument.PostingDate,
cast(case dats_is_valid(AccountingDocument.TaxReportingDate)
when 1 then
AccountingDocument.TaxReportingDate
else
AccountingDocument.PostingDate
end as glo_reporting_date ) as ReportingDate,
@Semantics.currencyCode: true
AccountingDocument.Currency,
@Semantics.currencyCode: true
AccountingDocument.CompanyCodeCurrency,
substring(OriginalReferenceDocument, 1, 10) as InvoiceDocumentNumber,
case when _AccDocCust.Customer != ''
then _AccDocCust.Customer
else _AccDocCust.Supplier
end as CustomerSupplierAccount,
_AccDocCust.Supplier,
_AccDocCust.Customer,
case when _AccDocCust.Customer != ''
then _Customer.VATRegistration
else _Supplier.VATRegistration
end as VATRegistration,
//For FM-Header Row
case when _TaxItem._OneTimeAccountBP.TaxID1!=''
then replace(replace( _TaxItem._OneTimeAccountBP.TaxID1, '.', '' ),'-','')
else case when _AccDocCust.Customer != ''
then replace(replace(_Customer.TaxNumber1, '.', '' ),'-','')
else replace(replace(_Supplier.TaxNumber1, '.', '' ),'-','')
end
end as TaxNumber1,
case when BusinessPartnerName!= ''
then replace( BusinessPartnerName,',','')
else case when _AccDocCust.Customer != ''
then replace(_Customer.CustomerName, ',', '')
else replace(_Supplier.SupplierName, ',', '')
end
end as SupplierName,
coalesce(concat(concat(concat(concat(_OneTimeAccountBP.StreetAddressName, ','), concat(_OneTimeAccountBP.CityName, ',')), concat(_OneTimeAccountBP.Country, ',')), _OneTimeAccountBP.PostalCode),
//Street 2 , Street 3 + Street + House number + Street 4 , Street 5 + Different City, District + City, Post Code + Country
coalesce(concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat(
concat(_Customer._AddressDefaultRepresentation.StreetPrefixName1, ','), _Customer._AddressDefaultRepresentation.StreetPrefixName2),
concat_with_space(_Customer._AddressDefaultRepresentation.StreetName, _Customer._AddressDefaultRepresentation.HouseNumber, 1), 1),
concat(concat(_Customer._AddressDefaultRepresentation.StreetSuffixName1, ','), _Customer._AddressDefaultRepresentation.StreetSuffixName2), 1),
concat(concat(_Customer._AddressDefaultRepresentation.VillageName, ','), _Customer._AddressDefaultRepresentation.DistrictName), 1),
concat(concat(_Customer._AddressDefaultRepresentation.CityName, ','), _Customer._AddressDefaultRepresentation.PostalCode), 1),
_Customer._AddressDefaultRepresentation.Country, 1),
concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat_with_space(concat(
concat(_Supplier._AddressDefaultRepresentation.StreetPrefixName1, ','), _Supplier._AddressDefaultRepresentation.StreetPrefixName2),
concat_with_space(_Supplier._AddressDefaultRepresentation.StreetName, _Supplier._AddressDefaultRepresentation.HouseNumber, 1), 1),
concat(concat(_Supplier._AddressDefaultRepresentation.StreetSuffixName1, ','), _Supplier._AddressDefaultRepresentation.StreetSuffixName2), 1),
concat(concat(_Supplier._AddressDefaultRepresentation.VillageName, ','), _Supplier._AddressDefaultRepresentation.DistrictName), 1),
concat(concat(_Supplier._AddressDefaultRepresentation.CityName, ','), _Supplier._AddressDefaultRepresentation.PostalCode), 1),
_Supplier._AddressDefaultRepresentation.Country, 1)
)) as AddressLine1,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when _AccDocCust.IsNegativePosting = 'X' then
-(_TaxItem.TaxBaseAmountInCoCodeCrcy)
else
_TaxItem.TaxBaseAmountInCoCodeCrcy
end as TaxBaseAmountInCoCodeCrcy,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when _AccDocCust.IsNegativePosting = 'X' then
-(_TaxItem.TotalTaxAmount)
else
_TaxItem.TotalTaxAmount
end as TaxAmount,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
case when _AccDocCust.IsNegativePosting = 'X' then
-(_TaxItem.TaxAmountInCoCodeCrcy)
else
_TaxItem.TaxAmountInCoCodeCrcy
end as LuxuryTaxAmt,
cast(case _TaxItem._StRpBPTaxItem.TaxIsNotDeductible
when 'X' then '0'
else '1'
end as stazf_007b ) as TaxIsNotDeductible
}
where
not(
AccountingDocument.IsReversal = 'X'
or AccountingDocument.IsReversed = 'X'
or AccountingDocument.ReferenceDocumentType = 'VBRK'
or AccountingDocument.ReferenceDocumentType = 'MKPF'
)
and AccountingDocument.CompanyCode = :P_CompanyCode
and AccountingDocument.FiscalYear = :P_FiscalYear
//Input tax
and _TaxItem.TaxType = 'V'
and (_Customer.Country = 'ID' or _Supplier.Country = 'ID')
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_ACCTDOCCUSTOMER",
"I_CUSTOMER",
"I_ID_STRPTAXRETURNITEMGROUP",
"I_ONETIMEACCOUNTBP",
"I_ORGANIZATIONADDRESS",
"I_STRPTAXITEM",
"I_SUPPLIER"
],
"ASSOCIATED":
[
"I_CUSTOMER",
"I_SUPPLIER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/