@AbapCatalog.sqlViewName: 'IPTSAFTGENLCUST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@EndUserText.label: 'SAF-T PT Gen Ledger OneTimeCustomer'
define view I_PT_SAFTGenLedgerOneTimeCust
as select distinct from I_SAFTGenLedgerOneTimeCustomer 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 = b.CompanyCode
inner join I_GLAccountInCompanyCode as f on f.GLAccount = a.ReconciliationAccount
and f.CompanyCode = a.CompanyCode
left outer join I_PT_AcctgDocDgtlSgntr as Sig on Sig.AccountingDocument = a.AccountingDocument
and Sig.CompanyCode = a.CompanyCode
and Sig.FiscalYear = a.FiscalYear
left outer join P_PT_SAFTCUSTMAX as CM on CM.CompanyCode = a.CompanyCode
and CM.Customer = a.Customer
and CM.CustomerVATRegistration = a.CustomerVATRegistration
{
@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 as Ledger,
//Associations
a._CompanyCode,
a._FiscalYear,
a._JournalEntry,
a._Ledger,
//NEW APPROACH - Cover accounting customers that comes from signed or not signed documents
case when Sig.PortugueseInvoiceDate is not initial
then Sig.PortugueseInvoiceDate
else a.PostingDate
end as DocumentDate,
case when a.CustomerVATRegistration is initial or a.CustomerVATRegistration is null
then
concat(a.Customer, concat('/', '999999990'))
else
concat(a.Customer, concat('/', a.CustomerVATRegistration))
end as PortugueseCustomerWithVersion,
a.Customer,
case when a.CustomerVATRegistration is initial or a.CustomerVATRegistration is null
then
'999999990'
else
a.CustomerVATRegistration
end as CustomerVATRegistration,
case when c.IsAltAcct is not initial
then f.AlternativeGLAccount
else a.ReconciliationAccount
end as ReconciliationAccount,
cast(
case when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
a.CompanyName is not initial and a.CompanyName is not null
then a.CompanyName
when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
CM.CompanyName is not initial and CM.CompanyName is not null
then CM.CompanyName
else 'Consumidor Final'
end as bu_name1tx) as CompanyName,
case when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
a.StreetName is not initial and a.StreetName is not null
then cast(concat_with_space(concat_with_space((concat_with_space(
concat_with_space(a.StreetName, a.StreetPrefixName,1),
concat_with_space(a.AdditionalStreetPrefixName, a.StreetSuffixName,1),1)
), a.AdditionalStreetSuffixName,1), a.HouseNumber,1) as fieu_address)
when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
CM.StreetName is not initial and CM.StreetName is not null
then cast(concat_with_space(concat_with_space((concat_with_space(
concat_with_space(CM.StreetName,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.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
a.CityName is not initial and a.CityName is not null
then a.CityName
when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration 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.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
a.PostalCode is not initial and a.PostalCode is not null
then a.PostalCode
when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration 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.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
a.Country is not initial and a.Country is not null
then a.Country
when a.CustomerVATRegistration is not initial and a.CustomerVATRegistration is not null and
CM.Country is not initial and CM.Country is not null
then CM.Country
else 'Desconhecido'
end as Country,
case when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
a.PhoneNumber is not null and a.PhoneNumber is not initial
then a.PhoneNumber
when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
CM.PhoneNumber is not null and CM.PhoneNumber is not initial
then CM.PhoneNumber
end as PhoneNumber,
case when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
a.FaxNumber is not null and a.FaxNumber is not initial
then a.FaxNumber
when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
CM.FaxNumber is not null and CM.FaxNumber is not initial
then CM.FaxNumber
end as FaxNumber,
case when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
a.EmailAddress is not null and a.EmailAddress is not initial
then a.EmailAddress
when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
CM.EmailAddress is not null and CM.EmailAddress is not initial
then CM.EmailAddress
end as EmailAddress,
case when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
a.WebsiteURL is not null and a.WebsiteURL is not initial
then cast(lower(a.WebsiteURL) as ad_uri2)
when a.CustomerVATRegistration is not null and a.CustomerVATRegistration is not initial and
CM.WebsiteURL is not null and CM.WebsiteURL is not initial
then CM.WebsiteURL
end as WebsiteURL,
//Associations
a._Customer
} where a.AccountingDocumentCategory = ''
or a.AccountingDocumentCategory = 'J'
or a.AccountingDocumentCategory = 'U'
or a.AccountingDocumentCategory = 'L'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_GLACCOUNTINCOMPANYCODE",
"I_PT_ACCTGDOCDGTLSGNTR",
"I_PT_SAFTGENERICSETTINGS",
"I_SAFTGENLEDGERONETIMECUSTOMER",
"P_PT_SAFTCUSTMAX",
"P_PT_SAFTISALTERNATEACCOUNT"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_CUSTOMER",
"I_FISCALYEARFORCOMPANYCODE",
"I_JOURNALENTRY",
"I_LEDGER"
],
"BASE":
[
"I_SAFTGENLEDGERONETIMECUSTOMER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/