RK: Avalara Report

➜ Back to Library
Description
Categories: Draft
In development
Run RK: Avalara Report and other Oracle EBS reports with Blitz Report™ on our demo environment
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(+)
Download
Related Blitz Reports
AR Transactions and Lines AR European Sales Listing ONT Order/Invoice Summary CAC Intercompany Relationship Setup XXSJ Report VAT Register JE Portuguese Receivables Taxpayer ID Exception Report- Not Supported: Reserved For Future Use JA Third Party Detail Trial Balance with Contra Accounts AR Customers and Sites CAC Internal Order Shipment Margin JE Spanish B2G Electronic Invoices XML File Output
Blitz Report™

Blitz Report™ provides multiple benefits: