@AbapCatalog.sqlViewName: 'IPTSAFTSBADRHST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.serviceQuality: #D
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'SAF-T PT Self-Billing CustAddr History'
define view I_PT_SAFTSelfBillgCustAddrHist
as select from I_PT_SelfBillgCustAddrVers as Version
left outer join I_CustomerCompany as CustomerCompany on Version.PlantCustomer = CustomerCompany.Customer
and Version.CompanyCode = CustomerCompany.CompanyCode
left outer join I_GLAccountInCompanyCode as GLAccountInCompanyCode on CustomerCompany.ReconciliationAccount = GLAccountInCompanyCode.GLAccount
and CustomerCompany.CompanyCode = GLAccountInCompanyCode.CompanyCode
left outer join I_PT_SAFTCustAddrHist as History on Version.PlantCustomer = History.Customer
and Version.PortugueseCustomerVersion = History.PortugueseCustomerVersion
left outer join I_ContactPerson as ContactPerson on Version.PlantCustomer = ContactPerson.Customer
and Version.PlantCustomer is not initial
{
key Version.FiscalYear as FiscalYear,
key Version.SupplierInvoice as SupplierInvoice,
key Version.PlantCustomer as PlantCustomer,
Version.CompanyCode as CompanyCode,
Version.Supplier as Supplier,
cast(
case
when History.Customer is not initial
then concat(Version.PlantCustomer,concat('_',Version.PortugueseCustomerVersion))
when Version.PlantCustomer is not initial
then concat(Version.PlantCustomer,'_0000')
else 'Desconhecido'
end as sipt_kunnr_with_version) as PortugueseCustomerWithVersion,
cast(
case
when GLAccountInCompanyCode.AlternativeGLAccount is not initial
then GLAccountInCompanyCode.AlternativeGLAccount
when CustomerCompany.ReconciliationAccount is not initial
then CustomerCompany.ReconciliationAccount
else ''
end as akont) as ReconciliationAccount,
cast(
case
when History.Customer is not initial
then History.CustomerVATRegistration
else
case
when CustomerCompany._Customer.VATRegistration is not initial
then substring(CustomerCompany._Customer.VATRegistration,3,18)
when CustomerCompany._Customer.TaxNumber1 is not initial
then CustomerCompany._Customer.TaxNumber1
when CustomerCompany._Customer.IsOneTimeAccount is initial
then 'Desconhecido'
when Version._Customer.VATRegistration is not initial
then substring(Version._Customer.VATRegistration,3,18)
when Version._Customer.TaxNumber1 is not initial
then Version._Customer.TaxNumber1
when Version._Customer.IsOneTimeAccount is initial
then 'Desconhecido'
else 'Desconhecido'
end
end as stceg) as CustomerVATRegistration,
cast(
case
when History.Customer is not initial
then History.CompanyName
else
case
when CustomerCompany._Customer.OrganizationBPName1 is not initial
and CustomerCompany._Customer.OrganizationBPName2 is not initial
then concat_with_space(CustomerCompany._Customer.OrganizationBPName1,
CustomerCompany._Customer.OrganizationBPName2,1)
when CustomerCompany._Customer.OrganizationBPName1 is not initial
then CustomerCompany._Customer.OrganizationBPName1
when CustomerCompany._Customer.OrganizationBPName2 is not initial
then CustomerCompany._Customer.OrganizationBPName2
when Version._Customer.OrganizationBPName1 is not initial
and Version._Customer.OrganizationBPName2 is not initial
then concat_with_space(Version._Customer.OrganizationBPName1,
Version._Customer.OrganizationBPName2,1)
when Version._Customer.OrganizationBPName1 is not initial
then Version._Customer.OrganizationBPName1
when Version._Customer.OrganizationBPName2 is not initial
then Version._Customer.OrganizationBPName2
else 'Desconhecido'
end
end as bu_name1tx) as CompanyName,
case
when History.Customer is not initial
then min(History.PartnerFirstName)
else min(ContactPerson.FirstName)
end as PartnerFirstName,
case
when History.Customer is not initial
then min(History.PartnerLastName)
else min(ContactPerson.LastName)
end as PartnerLastName,
cast(
case
when History.Customer is not initial
then History.CustomerSupplierAddress
when CustomerCompany.Customer is not initial
then concat_with_space((concat_with_space(
concat_with_space(CustomerCompany._Customer._StandardAddress.StreetName,
CustomerCompany._Customer._StandardAddress.StreetPrefixName,1),
concat_with_space(CustomerCompany._Customer._StandardAddress.AdditionalStreetPrefixName,
CustomerCompany._Customer._StandardAddress.StreetSuffixName,1),1)
),CustomerCompany._Customer._StandardAddress.AdditionalStreetSuffixName,1)
when Version.PlantCustomer is not initial
then concat_with_space((concat_with_space(
concat_with_space(Version._Customer._StandardAddress.StreetName,
Version._Customer._StandardAddress.StreetPrefixName,1),
concat_with_space(Version._Customer._StandardAddress.AdditionalStreetPrefixName,
Version._Customer._StandardAddress.StreetSuffixName,1),1)
),Version._Customer._StandardAddress.AdditionalStreetSuffixName,1)
else 'Desconhecido'
end as fieu_address) as CustomerSupplierAddress,
cast(
case
when History.Customer is not initial
then History.CityName
when CustomerCompany.Customer is not initial
then CustomerCompany._Customer._StandardAddress.CityName
when History._Customer._StandardAddress.CityName is not initial
then History._Customer._StandardAddress.CityName
else 'Desconhecido'
end as ad_city1) as CityName,
cast(
case
when History.Customer is not initial
then History.PostalCode
else
case
when CustomerCompany._Customer._StandardAddress.PostalCode is not initial
then CustomerCompany._Customer._StandardAddress.PostalCode
when CustomerCompany._Customer._StandardAddress.POBox is not initial
then concat_with_space('Caixa Postal',CustomerCompany._Customer._StandardAddress.POBox,1)
when Version._Customer._StandardAddress.PostalCode is not initial
then Version._Customer._StandardAddress.PostalCode
when Version._Customer._StandardAddress.POBox is not initial
then concat_with_space('Caixa Postal',Version._Customer._StandardAddress.POBox,1)
else 'Desconhecido'
end
end as fieu_postal) as PostalCode,
cast(
case
when History.Customer is not initial
then History.Country
when CustomerCompany.Customer is not initial
then CustomerCompany._Customer._StandardAddress.Country
when Version._Customer._StandardAddress.Country is not initial
then Version._Customer._StandardAddress.Country
else 'PT'
end as land1) as Country,
cast(
case
when History.Customer is not initial
then History.PhoneNumber
when CustomerCompany.Customer is not initial
then CustomerCompany._Customer._StandardAddress.PhoneNumber
else Version._Customer._StandardAddress.PhoneNumber
end as ad_tlnmbr1) as PhoneNumber,
cast(
case
when History.Customer is not initial
then History.FaxNumber
when CustomerCompany.Customer is not initial
then CustomerCompany._Customer._StandardAddress.FaxNumber
else Version._Customer._StandardAddress.FaxNumber
end as ad_fxnmbr1) as FaxNumber,
cast(
case
when History.Customer is not initial
then History.EmailAddress
when CustomerCompany.Customer is not initial
then CustomerCompany._Customer._StandardAddress._DefaultEmailAddress.EmailAddress
else Version._Customer._StandardAddress._DefaultEmailAddress.EmailAddress
end as ad_smtpadr) as EmailAddress,
/* Associations */
//FOR DPP AUTHORIZATION CHECK
Version._Customer,
Version._Supplier
}
group by
Version.FiscalYear,
Version.SupplierInvoice,
Version.PlantCustomer,
Version.CompanyCode,
Version.Supplier,
History.Customer,
Version.PortugueseCustomerVersion,
GLAccountInCompanyCode.AlternativeGLAccount,
CustomerCompany.ReconciliationAccount,
History.CustomerVATRegistration,
CustomerCompany._Customer.VATRegistration,
CustomerCompany._Customer.TaxNumber1,
CustomerCompany._Customer.IsOneTimeAccount,
Version._Customer.VATRegistration,
Version._Customer.TaxNumber1,
Version._Customer.IsOneTimeAccount,
History.CompanyName,
CustomerCompany._Customer.OrganizationBPName1,
CustomerCompany._Customer.OrganizationBPName2,
Version._Customer.OrganizationBPName1,
Version._Customer.OrganizationBPName2,
History.CustomerSupplierAddress,
CustomerCompany.Customer,
CustomerCompany._Customer._StandardAddress.StreetName,
CustomerCompany._Customer._StandardAddress.StreetPrefixName,
CustomerCompany._Customer._StandardAddress.AdditionalStreetPrefixName,
CustomerCompany._Customer._StandardAddress.StreetSuffixName,
CustomerCompany._Customer._StandardAddress.AdditionalStreetSuffixName,
Version._Customer._StandardAddress.StreetName,
Version._Customer._StandardAddress.StreetPrefixName,
Version._Customer._StandardAddress.AdditionalStreetPrefixName,
Version._Customer._StandardAddress.StreetSuffixName,
Version._Customer._StandardAddress.AdditionalStreetSuffixName,
History.CityName,
CustomerCompany._Customer._StandardAddress.CityName,
History._Customer._StandardAddress.CityName,
History.PostalCode,
CustomerCompany._Customer._StandardAddress.PostalCode,
CustomerCompany._Customer._StandardAddress.POBox,
Version._Customer._StandardAddress.PostalCode,
Version._Customer._StandardAddress.POBox,
History.Country,
CustomerCompany._Customer._StandardAddress.Country,
Version._Customer._StandardAddress.Country,
History.PhoneNumber,
CustomerCompany._Customer._StandardAddress.PhoneNumber,
Version._Customer._StandardAddress.PhoneNumber,
History.FaxNumber,
CustomerCompany._Customer._StandardAddress.FaxNumber,
Version._Customer._StandardAddress.FaxNumber,
History.EmailAddress,
CustomerCompany._Customer._StandardAddress._DefaultEmailAddress.EmailAddress,
Version._Customer._StandardAddress._DefaultEmailAddress.EmailAddress
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_ADDRESSEMAILADDRESS",
"I_CONTACTPERSON",
"I_CUSTOMER",
"I_CUSTOMERCOMPANY",
"I_GLACCOUNTINCOMPANYCODE",
"I_PT_SAFTCUSTADDRHIST",
"I_PT_SELFBILLGCUSTADDRVERS"
],
"ASSOCIATED":
[
"I_CUSTOMER",
"I_SUPPLIER"
],
"BASE":
[
"I_PT_SELFBILLGCUSTADDRVERS"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/