@AbapCatalog.sqlViewName: 'ISAFTPTGLOTS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'SAFT PT GLEntries OTS'
define view I_PT_SAFTGenLdgrOneTmeSupplier
as select from I_SAFTGenLedgerOneTimeSupplier as a
inner join I_PT_SAFTGenericSettings as b on b.CompanyCode = a.CompanyCode
and b.FiscalYearFrom <= a.FiscalYear
and b.FiscalYearTo >= a.FiscalYear
and b.SourceLedger = a.Ledger
inner join P_PT_SAFTIsAlternateAccount as c on c.CompanyCode = a.CompanyCode
inner join I_GLAccountInCompanyCode as d on d.GLAccount = a.ReconciliationAccount
and d.CompanyCode = a.CompanyCode
left outer join P_PT_SAFTSUPMAX as CM on CM.CompanyCode = a.CompanyCode
and CM.Supplier = a.Supplier
and CM.VATRegistration = a.VATRegistration
{
@ObjectModel.foreignKey.association: '_CompanyCode'
key a.CompanyCode as CompanyCode,
@ObjectModel.foreignKey.association: '_FiscalYear'
key a.FiscalYear as FiscalYear,
@ObjectModel.foreignKey.association: '_JournalEntry'
key a.AccountingDocument as AccountingDocument,
@ObjectModel.foreignKey.association: '_Ledger'
key a.Ledger,
a.PostingDate as DocumentDate,
case when a.VATRegistration is initial or a.VATRegistration is null
then
concat(a.Supplier, concat('/', '999999990'))
else
concat(a.Supplier, concat('/', a.VATRegistration))
end as Supplier,
case
when c.IsAltAcct is not initial
then d.AlternativeGLAccount
when c.IsAltAcct is initial
then a.ReconciliationAccount
else 'Desconhecido'
end as ReconciliationAccount,
case when a.VATRegistration is initial or a.VATRegistration is null
then
'999999990'
else
a.VATRegistration
end as VATRegistration,
case when a.VATRegistration is not initial and a.VATRegistration is not null and
a.CompanyName is not initial and a.CompanyName is not null
then a.CompanyName
when a.VATRegistration is not initial and a.VATRegistration is not null and
CM.CompanyName is not initial and CM.CompanyName is not null
then CM.CompanyName
else 'Consumidor Final'
end as CompanyName,
case when a.VATRegistration is not initial and a.VATRegistration is not null and
a.StreetAddressName is not initial and a.StreetAddressName is not null
then cast(concat_with_space(concat_with_space((concat_with_space(
concat_with_space(a.StreetAddressName, a.StreetPrefixName,1),
concat_with_space(a.AdditionalStreetPrefixName, a.StreetSuffixName,1),1)
), a.AdditionalStreetSuffixName,1), a.HouseNumber,1) as fieu_address)
when a.VATRegistration is not initial and a.VATRegistration is not null and
CM.StreetAddressName is not initial and CM.StreetAddressName is not null
then cast(concat_with_space(concat_with_space((concat_with_space(
concat_with_space(CM.StreetAddressName,CM.StreetPrefixName,1),
concat_with_space(CM.AdditionalStreetPrefixName,CM.StreetSuffixName,1),1)
),CM.AdditionalStreetSuffixName,1),CM.HouseNumber,1) as fieu_address)
else 'Desconhecido'
end as StreetName,
case when a.VATRegistration is not initial and a.VATRegistration is not null and
a.CityName is not initial and a.CityName is not null
then a.CityName
when a.VATRegistration is not initial and a.VATRegistration is not null and
CM.CityName is not initial and CM.CityName is not null
then CM.CityName
else 'Desconhecido'
end as CityName,
case when a.VATRegistration is not initial and a.VATRegistration is not null and
a.PostalCode is not initial and a.PostalCode is not null
then a.PostalCode
when a.VATRegistration is not initial and a.VATRegistration is not null and
CM.PostalCode is not initial and CM.PostalCode is not null
then CM.PostalCode
else 'Desconhecido'
end as PostalCode,
case when a.Country is not initial or a.Country is not null
then a.Country
when CM.Country is not initial or CM.Country is not null
then CM.Country
else 'Desconhecido'
end as Country,
//optional fields
case when a.VATRegistration is not null and a.VATRegistration is not initial and
a._Address.PhoneNumber is not null and a._Address.PhoneNumber is not initial
then a._Address.PhoneNumber
when a.VATRegistration is not null and a.VATRegistration is not initial and
CM.PhoneNumber is not null and CM.PhoneNumber is not initial
then CM.PhoneNumber
end as PhoneNumber,
case when a.VATRegistration is not null and a.VATRegistration is not initial and
a._Address.FaxNumber is not null and a._Address.FaxNumber is not initial
then a._Address.FaxNumber
when a.VATRegistration is not null and a.VATRegistration is not initial and
CM.FaxNumber is not null and CM.FaxNumber is not initial
then CM.FaxNumber
end as FaxNumber,
case when a.VATRegistration is not null and a.VATRegistration is not initial and
a.EmailAddress is not null and a.EmailAddress is not initial
then a.EmailAddress
when a.VATRegistration is not null and a.VATRegistration is not initial and
CM.EmailAddress is not null and CM.EmailAddress is not initial
then CM.EmailAddress
end as EmailAddress,
case when a.VATRegistration is not null and a.VATRegistration is not initial and
a.SearchURLAddress is not null and a.SearchURLAddress is not initial
then cast(lower(a.SearchURLAddress) as ad_uri2)
when a.VATRegistration is not null and a.VATRegistration is not initial and
CM.WebsiteURL is not null and CM.WebsiteURL is not initial
then CM.WebsiteURL
end as WebsiteURL,
_CompanyCode,
_JournalEntry,
_Ledger,
_FiscalYear
} where a.AccountingDocumentCategory = ''
or a.AccountingDocumentCategory = 'J'
or a.AccountingDocumentCategory = 'U'
or a.AccountingDocumentCategory = 'L'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_GLACCOUNTINCOMPANYCODE",
"I_PT_SAFTGENERICSETTINGS",
"I_SAFTGENLEDGERONETIMESUPPLIER",
"P_PT_SAFTISALTERNATEACCOUNT",
"P_PT_SAFTSUPMAX"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_FISCALYEARFORCOMPANYCODE",
"I_JOURNALENTRY",
"I_LEDGER"
],
"BASE":
[
"I_SAFTGENLEDGERONETIMESUPPLIER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/