P_RU_VATReturnGeneralSection3

@VDM.private: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.sqlViewName: 'PRUVATRETGS3'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_RU_VATReturnGeneralSection3
  with parameters
    P_FiscalYearVariant : fis_periv,
    P_Date              : datum

  as select from            P_RU_VATReturnGeneralSection2(
                            P_Date  : $parameters.P_Date)  as VatReturnGS

    inner join              I_JournalEntry                 as SourceDocHeader  on  SourceDocHeader.CompanyCode        = VatReturnGS.CompanyCode
                                                                               and SourceDocHeader.AccountingDocument = VatReturnGS.SourceDocument
                                                                               and SourceDocHeader.FiscalYear         = VatReturnGS.SourceDocumentFiscalYear

  //left outer to one join  I_RU_CurrencyConversion        as CurrencyConversion on CurrencyConversion.Currency = VatReturnGS.Currency


    left outer to one join  I_RU_VATDocumentExtraInfo      as VatExtraInfo     on  VatExtraInfo.CompanyCode        = VatReturnGS.CompanyCode
                                                                               and VatExtraInfo.AccountingDocument = VatReturnGS.SourceDocument
                                                                               and VatExtraInfo.FiscalYear         = VatReturnGS.SourceDocumentFiscalYear

  //for section 5

    left outer to one join  I_FiscalYearPeriod             as FiscalYearPeriod on  FiscalYearPeriod.FiscalYearVariant     = $parameters.P_FiscalYearVariant
                                                                               and FiscalYearPeriod.FiscalPeriodStartDate <= VatReturnGS.DocumentDate
                                                                               and FiscalYearPeriod.FiscalPeriodEndDate   >= VatReturnGS.DocumentDate
                                                                               and VatReturnGS.RU_VATReturnSectionType    =  '5'

  //VAT Return operation for sections 2,4,5,6,7

    left outer to many join P_RU_CFinVATCorrectionDocument as SourceDocIsCorr  on  SourceDocIsCorr.CompanyCode        = VatReturnGS.CompanyCode
                                                                               and SourceDocIsCorr.AccountingDocument = VatReturnGS.SourceDocument
                                                                               and SourceDocIsCorr.FiscalYear         = VatReturnGS.SourceDocumentFiscalYear

    left outer to one join  P_RU_TaxItemIsNegativePosting  as TaxItem          on  TaxItem.CompanyCode        = VatReturnGS.CompanyCode
                                                                               and TaxItem.AccountingDocument = VatReturnGS.AccountingDocument
                                                                               and TaxItem.FiscalYear         = VatReturnGS.FiscalYear
                                                                               and TaxItem.TaxItem            = VatReturnGS.TaxItem

    left outer to many join P_RU_VATReportingCustSubs      as VatSubsCust      on  VatSubsCust.RU_VATSubScenarioVersion     =  VatReturnGS.RU_VATSubScenarioVersion
                                                                               and VatSubsCust.RU_VATSubScenario            =  VatReturnGS.RU_VATSubScenario
                                                                               and VatSubsCust.TaxCode                      =  VatReturnGS.TaxCode
                                                                               and VatSubsCust.TaxItemClassification        =  VatReturnGS.TransactionTypeDetermination
                                                                               and VatSubsCust.VATReturnOperation           <> ''

                                                                               and (
                                                                                  VatSubsCust.DebitCreditCode               is null
                                                                                  or VatReturnGS.IsReversal                 =  ''
                                                                                  and TaxItem.IsNegativePosting             is null
                                                                                  and VatReturnGS.DebitCreditCode           =  VatSubsCust.DebitCreditCode
                                                                                  or(
                                                                                    VatReturnGS.IsReversal                  =  'X'
                                                                                    or TaxItem.IsNegativePosting            is not null
                                                                                  )
                                                                                  and VatReturnGS.DebitCreditCode           <> VatSubsCust.DebitCreditCode
                                                                                )
                                                                               and (
                                                                                  VatSubsCust.AccountingDocumentType        is null
                                                                                  or VatReturnGS.AccountingDocumentType     =  VatSubsCust.AccountingDocumentType
                                                                                )
                                                                               and (
                                                                                  VatSubsCust.RU_SourceDocumentType         is null
                                                                                  or SourceDocHeader.AccountingDocumentType =  VatSubsCust.RU_SourceDocumentType
                                                                                )
                                                                               and (
                                                                                  VatSubsCust.IsCorrectionInvoice           is null
                                                                                  or VatSubsCust.IsCorrectionInvoice        =  'C'
                                                                                  and SourceDocIsCorr.CompanyCode           is not null
                                                                                  or VatSubsCust.IsCorrectionInvoice        =  'N'
                                                                                  and SourceDocIsCorr.CompanyCode           is null
                                                                                )
{
  VatReturnGS.CompanyCode,
  VatReturnGS.AccountingDocument,
  VatReturnGS.FiscalYear,
  VatReturnGS.TaxItem,
  VatReturnGS.PostingDate,
  VatReturnGS.TaxReportingDate,
  VatReturnGS.DocumentDate,
  VatReturnGS.IsReversal,
  VatReturnGS.IsReversed,
  cast(coalesce(VatExtraInfo.ExternalInvoice, '')       as firu_inv_extnum  preserving type) as ExternalInvoice,
  cast(coalesce(VatExtraInfo.InvoiceDate, '00010101')   as firu_inv_extdate preserving type) as InvoiceDate,
  VatReturnGS.CompanyCodeCurrency,
  VatReturnGS.Currency,
  //CurrencyConversion.CurrencyISOCode,

  VatReturnGS.BusinessTransactionType,
  VatReturnGS.AccountingDocumentType,

  VatReturnGS.TaxCode,
  VatReturnGS.DebitCreditCode,
  VatReturnGS.Sign,
  VatReturnGS.TransactionTypeDetermination,
  VatReturnGS.TaxBaseAmountInCoCodeCrcy,
  VatReturnGS.TaxAmountInCoCodeCrcy,

  // Section 2

  VatReturnGS.Supplier,
  VatReturnGS.BusinessPartner,
  VatReturnGS.SupplierName,
  VatReturnGS.BusinessPartnerIsOrganization,
  VatReturnGS.BusinessPartnerIsNaturalPerson,
  VatReturnGS.RU_PartnerOrganizationINN,
  VatReturnGS.RU_PartnerOrganizationKPP,
  VatReturnGS.RU_PartnerPersonINN,
  VatReturnGS.FirstName,
  VatReturnGS.LastName,
  VatReturnGS.MiddleName,
  VatReturnGS.AuthorizationGroup,
  VatReturnGS.RU_TerritoryClassfctnOKTMO,

  // Sections 2,4,5,6,7

  coalesce(VatSubsCust.VATReturnOperation, '')                                               as VATReturnOperation,

  // Section 5

  coalesce(FiscalYearPeriod.FiscalYear, '0000')                                              as ExportInvcConfirmedFiscalYear,
  case FiscalYearPeriod.FiscalPeriod
    when '001' then '21'
    when '002' then '22'
    when '003' then '23'
    when '004' then '24'
    else ''
  end                                                                                        as FiscalPeriod,

  VatReturnGS.RU_VATReturnVersion,
  VatReturnGS.RU_VATReturnSectionType,
  VatReturnGS.RU_VATReturnSection,
  VatReturnGS.RU_VATReturnLine,
  VatReturnGS.VATReturnItemDescription,
  VatReturnGS.RU_VATSubScenarioVersion,
  VatReturnGS.RU_VATSubScenario,
  VatReturnGS.RU_VATScenario,

  VatReturnGS.SourceDocument,
  VatReturnGS.SourceDocumentFiscalYear,
  VatReturnGS.RevisionNumber,
  VatReturnGS.StatryRptgEntity,
  VatReturnGS.StatryRptCategory,
  VatReturnGS.StatryRptRunID
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FISCALYEARPERIOD",
"I_JOURNALENTRY",
"I_RU_VATDOCUMENTEXTRAINFO",
"P_RU_CFINVATCORRECTIONDOCUMENT",
"P_RU_TAXITEMISNEGATIVEPOSTING",
"P_RU_VATREPORTINGCUSTSUBS",
"P_RU_VATRETURNGENERALSECTION2"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/