RK: Avalara Report

Description
Categories: Draft
Columns: Type, Documentype, Sequencialnumbervk, Sequencialnumberak, Transactiondate, Invoicenumber, Supplierinvoicenumber, Lastinvoicenumber, Documentindicator, Ownreference ...
In development
select
rctta.type,
decode(rctta.type,'INV',0,'CM',2) DocumenType,
null "SequencialNumberVK",
null "SequencialNumberAK",
rcta.trx_date TransactionDate,
rcta.trx_number InvoiceNumber,
rcta.trx_number SupplierInvoiceNumber,
null "LastInvoiceNumber",
null "DocumentIndicator",
null "OwnReference",
(select rcta0.trx_number from ra_customer_trx_all rcta0 where rcta.previous_customer_trx_id=rcta0.customer_trx_id) "ReferenceInvoiceNumber",
(select rcta0.trx_date from ra_customer_trx_all rcta0 where rcta.previous_customer_trx_id=rcta0.customer_trx_id) "ReferenceInvoiceDate",
null "ReferenceTaxableBasis",
null "ReferenceVAT",
null "AutoFatturaNumber",
apsa.trx_date InvoiceDate,
apsa.invoice_currency_code Currency,
null "Currency2",
null "Currency3",
null "DocumentCurrency",
null "ExchangeRateDate",
null "Discount",
null "DateOfSupply",
(select rctla2.tax_rate from ra_customer_trx_lines_all rctla2 where rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id and rctla2.line_type='TAX' and rownum=1) VATCode,
null "VATCodeScheme",
null "VATCodeDescription",
null "CreditNoteOriginalDocument",
null "CreditNoteReason",
null "CreditNotePeriod",
null "VATDate",
null "InvoiceReceiptDate",
null "IncomingPostingDate",
null "OutsourcingPartyType",
null "OutsourcingPartyName",
null "OutsourcingPartyAddress",
null "OutsourcingPartyVRN",
null "SupplierID",
null "SupplierName",
null "SupplierPrivatePersonFirstName",
null "SupplierPrivatePersonLastName",
null "SupplierStreet",
null "SupplierHouseNumber",
null "SupplierZip",
null "SupplierCity",
null "SupplierCountry",
null "SupplierTelephone",
null "SupplierFax",
null "SupplierVATNumberUsed",
null "SupplierCountryVATNumberUsed",
null "SupplierVatNumberType",
null "SupplierDeductionType",
null "SupplierSpecialType",
null "SupplierFiscalNumber",
null "SupplierFiscalNumberIssuedBy",
null supplierfiscalrepresentativena,
null supplierfiscalrepresentativep1,
null supplierfiscalrepresentativep2,
null supplierfiscalrepresentativest,
null supplierfiscalrepresentativeho,
null supplierfiscalrepresentativezi,
null supplierfiscalrepresentativeci,
null supplierfiscalrepresentativeco,
null supplierfiscalrepresentativete,
null supplierfiscalrepresentativefa,
null supplierfiscalrepresentativeem,
null supplierfiscalrepresentativeva,
null supplierfiscalrepresentativeta,
null supplierfixedestablishmentstre,
null supplierfixedestablishmenthous,
null "SupplierFixedEstablishmentZip",
null "SupplierFixedEstablishmentCity",
null supplierfixedestablishmentcoun,
null supplierfixedestablishmenttele,
null "SupplierFixedEstablishmentFax",
null supplierfixedestablishmentemai,
null supplieridentifierpassportnumb,
null "SupplierIdentifierIdCardNumber",
null supplieridentifierresidencycer,
hca.account_number,
hp.party_name,
null "CustomerPrivatePersonFirstName",
null "CustomerPrivatePersonLastName",
hl.address1 "CustomerStreet",
hl.address2 "CustomerHouseNumber",
hl.postal_code "CustomerZip",
hl.city "CustomerCity",
hl.country "CustomerCountry",
null "CustomerTelephone",
null "CustomerFax",
null "CustomerVATNumberUsed",
null "CustomerCountryVATNumberUsed",
null "CustomerVatNumberType",
null "CustomerDeductionType",
null "CustomerSpecialType",
null "CustomerFiscalNumber",
null "CustomerFiscalNumberIssuedBy",
null customerfiscalrepresentativena,
null customerfiscalrepresentativep1,
null customerfiscalrepresentativep2,
null customerfiscalrepresentativest,
null customerfiscalrepresentativeho,
null customerfiscalrepresentativezi,
null customerfiscalrepresentativeci,
null customerfiscalrepresentativeco,
null customerfiscalrepresentativete,
null customerfiscalrepresentativefa,
null customerfiscalrepresentativeem,
null customerfiscalrepresentativeva,
null customerfiscalrepresentativeta,
null customerfixedestablishmentstre,
null customerfixedestablishmenthous,
null "CustomerFixedEstablishmentZip",
null "CustomerFixedEstablishmentCity",
null customerfixedestablishmentcoun,
null customerfixedestablishmenttele,
null "CustomerFixedEstablishmentFax",
null customerfixedestablishmentemai,
null customeridentifierpassportnumb,
null "CustomerIdentifierIdCardNumber",
null customeridentifierresidencycer,
rctla.description "Description",
null "ExemptionReason",
null "ItemClassification",
abs(rctla.extended_amount) "TaxableBasis",
abs((select sum(rctla2.extended_amount) from ra_customer_trx_lines_all rctla2 where rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id and rctla2.line_type='TAX')) "ValueVAT",
null "SalesVATDueReverseCharge",
abs(rctla.extended_amount)+abs((select sum(rctla2.extended_amount) from ra_customer_trx_lines_all rctla2 where rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id and rctla2.line_type='TAX')) "TotalValueLine",
null "AmountVATDeducted",
null "AmountVATReverseCharged",
null "TaxableBasisCurrency2",
null "ValueVATCurrency2",
null salesvatduereversechargecurre2,
null "TotalValueLineCurrency2",
null "AmountVATDeductedCurrency2",
null amountvatreversechargedcurren2,
null "TaxableBasisCurrency3",
null "ValueVATCurrency3",
null salesvatduereversechargecurre3,
null "TotalValueLineCurrency3",
null "AmountVATDeductedCurrency3",
null amountvatreversechargedcurren3,
null "OutOfVAT",
nvl(rctla.quantity_credited,rctla.quantity_invoiced) Quantity,
rctla.uom_code Unit,
null "ItemIdentifier",
hl.country "CountryDispatch",
hl.country "CountryArrival",
null "ShipToCity",
null "ShipToZIP",
null "ShipToStreet",
null "ShipToStreetNumber",
null "ShipFromCity",
null "ShipFromZIP",
null "ShipFromStreet",
null "ShipFromStreetNumber",
null "CountryOperation",
null "Installation",
null "Transporter",
null "CountryEUImportation",
null "EUImporter",
null "DeliveryConditions",
null "PlaceOfDelivery",
null "Triangulation",
null "AdditionalDocumentReference",
null "ReportingType",
null "TransactionType",
null "AdditionalTransactionType",
null "IntrastatCode",
null "AdditionalIntrastatCode",
null "ExtrastatCode",
null "AdditionalDescription",
nvl(rctla.quantity_credited,rctla.quantity_invoiced) Quantity1,
rctla.uom_code Unit1,
null "Quantity2",
null "Unit2",
abs(rctla.extended_amount)+abs((select sum(rctla2.extended_amount) from ra_customer_trx_lines_all rctla2 where rctla.customer_trx_line_id=rctla2.link_to_cust_trx_line_id and rctla2.line_type='TAX')) "CommercialValue",
null "StatisticalValue",
null "CommercialValueCurrency2",
null "StatisticalValueCurrency2",
null "CommercialValueCurrency3",
null "StatisticalValueCurrency3",
null "ModeOfTransport",
null "ItemType",
null "RegionDispatch",
null "HarbourDispatch",
null "RegionArrival",
null "HarbourArrival",
null "CountryOrigin",
null "ServiceCode",
null "NationalityTransportVehicle",
null "AccountNumber",
null "CashRegisterNumber",
null "AccountNumberTaxableBasis",
null "AccountNumberVAT",
null "AccountNumberDeductibleVAT",
null "AccountNumberNonDeductibleVAT",
null "AccountNumberReversedVAT",
null "RefundNatureOfItem",
null "RefundDescription",
null "RefundDescriptionLanguage",
null "ImportDocumentNumber",
null "ImportReferenceInformation",
null "ScannedDocumentFileName",
null "ScannedDocumentFileDescription",
null "ClearingDate",
null "ClearingDocumentNumber",
null "ClearingDocumentAmount",
null "ClearingDocumentCurrency",
null clearingdocumentaccountinforma,
null "ClearingDocumentPayementMethod",
null "NationalityTransportMeans",
null "CountryCustomsDeclaration",
null "InternalModeOfTransport",
null "EUCountryDispatch",
null "EUCountryArrival",
null "Container",
null "EORINRPSI",
null "EORINRPSIAgent",
null "CustomsProcedureCode",
null "PreferentialTreatment",
null "StatisticalProcedure"
from
ra_cust_trx_types_all rctta,
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
ra_customer_trx_lines_all rctla,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
where
1=1 and
rctta.type in ('CM','INV') and
rctta.org_id=rcta.org_id and 
rctta.cust_trx_type_id=rcta.cust_trx_type_id and
rcta.org_id=apsa.org_id and
rcta.customer_trx_id=apsa.customer_trx_id and
apsa.customer_trx_id=rctla.customer_trx_id and
rctla.line_type<>'TAX' and
apsa.customer_id=hca.cust_account_id(+) and
hca.party_id=hp.party_id(+) and
apsa.customer_site_use_id=hcsua.site_use_id(+) and
hcsua.cust_acct_site_id=hcasa.cust_acct_site_id(+) and
hcasa.party_site_id=hps.party_site_id(+) and
hps.location_id=hl.location_id(+)