@AbapCatalog.sqlViewName: 'IPTSAFTGLCUST'
@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 Customer'
define view I_PT_SAFTGenLedgerCustomer
as select distinct from I_SAFTGenLedgerCustomer 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 s on s.AccountingDocument = a.AccountingDocument
and s.CompanyCode = a.CompanyCode
and s.FiscalYear = a.FiscalYear
left outer join I_PT_SAFTAcctgDocCustAddrHist as h on h.AccountingDocument = a.AccountingDocument
and h.CompanyCode = a.CompanyCode
and h.FiscalYear = a.FiscalYear
and h.Customer = a.Customer
association [0..1] to I_SAFTCustTaxRegnNmbr as _CustLand
on a.Customer = _CustLand.CustomerNum
{
@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,
//Associations
a._CompanyCode,
a._FiscalYear,
a._JournalEntry,
a._Ledger,
//NEW APPROACH - Cover accounting customers that comes from signed or not signed documents
case when s.PortugueseInvoiceDate is not initial
then s.PortugueseInvoiceDate
else a.PostingDate
end as DocumentDate,
//NEW APPROACH - Cover accounting customers that comes from signed or not signed documents
cast ( case when h.PortugueseCustomerWithVersion is not initial
then case when a.DelivOfGoodsDestCountry is not initial
then case when a.DelivOfGoodsDestCountry != a.Country
then concat(h.PortugueseCustomerWithVersion,concat('/',a.DelivOfGoodsDestCountry))
else h.PortugueseCustomerWithVersion
end
else h.PortugueseCustomerWithVersion
end
when a.Customer is not initial
then case when a.DelivOfGoodsDestCountry is not initial
then case when a.DelivOfGoodsDestCountry != a.Country
then concat(a.Customer,concat('/',a.DelivOfGoodsDestCountry))
else a.Customer
end
else a.Customer
end
end as char0032 ) as PortugueseCustomerWithVersion,
// case when a.VATRegistration is not initial and a.VATRegistration is not null
// then substring(a.VATRegistration,3,18)
// else case when h.CustomerVATRegistration is initial or h.CustomerVATRegistration is null
// then 'Desconhecido'
// else h.CustomerVATRegistration
// end end as CustomerVATRegistration,
case when h.CustomerVATRegistration is not initial and h.CustomerVATRegistration is not null
then h.CustomerVATRegistration
else case when a.VATRegistration is not initial
then substring(a.VATRegistration,3,18)
else case when a.TaxNumber1 is not initial
then a.TaxNumber1
else 'Desconhecido'
end end
end as CustomerVATRegistration,
case when c.IsAltAcct is not initial
then f.AlternativeGLAccount
else a.ReconciliationAccount
end as ReconciliationAccount,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then cast(case
when a.OrganizationBPName1 is not initial and a.OrganizationBPName2 is not initial
then concat_with_space(a.OrganizationBPName1,a.OrganizationBPName2,1)
when a.OrganizationBPName1 is not initial
then a.OrganizationBPName1
when a.OrganizationBPName2 is not initial
then a.OrganizationBPName2
end as bu_name1tx)
else h.CompanyName
end as CompanyName,
//NEW APPROACH - Cover accounting customers that comes from signed or not signed documents
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then case when a.StreetName is not initial
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)
else 'Desconhecido'
end
when s.PortugueseInvoiceNumber is not initial
then case when h.CustomerSupplierAddress is not initial
then h.CustomerSupplierAddress
else 'Desconhecido'
end
end as StreetName,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then case when a.CityName is not initial
then a.CityName
else 'Desconhecido'
end
when s.PortugueseInvoiceNumber is not initial
then case when h.CityName is not initial
then h.CityName
else 'Desconhecido'
end
end as CityName,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then case when a.PostalCode is not initial
then a.PostalCode
else 'Desconhecido'
end
when s.PortugueseInvoiceNumber is not initial
then case when h.PostalCode is not initial
then h.PostalCode
else 'Desconhecido'
end
end as PostalCode,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then case when a.Country is not initial
then a.Country
else 'Desconhecido'
end
when s.PortugueseInvoiceNumber is not initial
then case when h.Country is not initial
then h.Country
else 'Desconhecido'
end
end as Country,
a.Customer,
case when h.ContactPersonName is initial or h.ContactPersonName is null
then coalesce ( cast( concat_with_space(a.PartnerFirstName,a.PartnerLastName,1)
as saft_pt_contact_name) , '' )
else coalesce( h.ContactPersonName , '' )
end as CompanyContactPersonFullName,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then coalesce ( a._StandardAddress.PhoneNumber , '' )
else coalesce ( h.PhoneNumber, '' )
end as PhoneNumber,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then coalesce ( a._StandardAddress.FaxNumber , '')
else coalesce ( h.FaxNumber , '' )
end as FaxNumber,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then coalesce ( a._StandardAddress._DefaultEmailAddress.EmailAddress , '' )
else coalesce( h.EmailAddress , '' )
end as EmailAddress,
case when s.PortugueseInvoiceNumber is initial or s.PortugueseInvoiceNumber is null
then coalesce ( cast(lower(a._StandardAddress._DefaultURLAddress.SearchURLAddress) as ad_uri2) , '' )
else coalesce ( h.SearchURLAddress , '' )
end as WebsiteURL
} where ( a.Country = a.DelivOfGoodsDestCountry or a.DelivOfGoodsDestCountry is initial ) and
( a.AccountingDocumentCategory = ''
or a.AccountingDocumentCategory = 'J'
or a.AccountingDocumentCategory = 'U'
or a.AccountingDocumentCategory = 'L' )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_ADDRESSEMAILADDRESS",
"I_ADDRESSHOMEPAGEURL",
"I_GLACCOUNTINCOMPANYCODE",
"I_PT_ACCTGDOCDGTLSGNTR",
"I_PT_SAFTACCTGDOCCUSTADDRHIST",
"I_PT_SAFTGENERICSETTINGS",
"I_SAFTGENLEDGERCUSTOMER",
"P_PT_SAFTISALTERNATEACCOUNT"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_FISCALYEARFORCOMPANYCODE",
"I_JOURNALENTRY",
"I_LEDGER",
"I_SAFTCUSTTAXREGNNMBR"
],
"BASE":
[
"I_SAFTGENLEDGERCUSTOMER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/