Avalara VAT LinesData

select
x.class,
decode(x.class,'Invoice',0,'Credit',2) "DocumentType",
null "SequencialNumberVK",
null "SequencialNumberAK",
x.trx_date "TransactionDate",
nvl(x.invoice_number,x.trx_number) "InvoiceNumber",
nvl(x.invoice_number,x.trx_number) "SupplierInvoiceNumber",
null "LastInvoiceNumber",
null "DocumentIndicator",
null "OwnReference",
x.credited_invoice "ReferenceInvoiceNumber",
null "ReferenceInvoiceDate",
null "ReferenceTaxableBasis",
null "ReferenceVAT",
null "AutoFatturaNumber",
x.trx_date "InvoiceDate",
x.currency "Currency",
null "Currency2",
null "Currency3",
null "DocumentCurrency",
null "ExchangeRateDate",
null "Discount",
null "DateOfSupply",
x.tax_rate "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,
x.account_number "CustomerID",
x.party_name "CustomerName",
null "CustomerPrivatePersonFirstName",
null "CustomerPrivatePersonLastName",
x.bill_to_address1 "CustomerStreet",
x.bill_to_address1 "CustomerHouseNumber",
x.bill_to_postal_code "CustomerZip",
x.bill_to_city "CustomerCity",
x.bill_to_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,
x.description "Description",
null "ExemptionReason",
null "ItemClassification",
abs(x.amount) "TaxableBasis",
abs(x.tax_amount) "ValueVAT",
null "SalesVATDueReverseCharge",
x.amount+nvl(x.tax_amount,0) "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",
x.quantity "Quantity",
x.uom_code "Unit",
null "ItemIdentifier",
x.bill_to_country "CountryDispatch",
x.bill_to_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",
x.uom_code "Quantity1",
x.uom_code "Unit1",
null "Quantity2",
null "Unit2",
x.amount+nvl(x.tax_amount,0) "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
(
select
haouv.name operating_unit,
acia.cons_billing_number invoice_number,
rcta.trx_number,
apsa.trx_date,
xxen_util.meaning(apsa.class,'INV/CM/ADJ',222)||case when apsa.class='CM' and obcl.bill_action='TR' then ' termination' end class,
apsa.class class_,
rctta.name type,
rcta.ct_reference reference,
rcta.purchase_order,
(
select
nvl2(acia0.cons_billing_number,acia0.cons_billing_number||' - ',null)||rcta0.trx_number credited_invoice
from
ra_customer_trx_all rcta0,
ar_cons_inv_trx_all acita0,
ar_cons_inv_all acia0
where
rcta.previous_customer_trx_id=rcta0.customer_trx_id and
rcta0.customer_trx_id=acita0.customer_trx_id(+) and
acita0.cons_inv_id=acia0.cons_inv_id(+)
) credited_invoice,
(select rctla0.line_number from ra_customer_trx_lines_all rctla0 where rctla.previous_customer_trx_line_id=rctla0.customer_trx_line_id) credited_invoice_line,
hca.account_number,
hp.party_name,
hcsua.location bill_to_location,
hz_format_pub.format_address(hps.location_id,null,null,' , ') bill_to_address,
hl.address1 bill_to_address1,
hl.address2 bill_to_address2,
hl.postal_code bill_to_postal_code,
hl.city bill_to_city,
hl.county bill_to_country,
hp.jgzz_fiscal_code taxpayer_id,
apsa.invoice_currency_code currency,
apsa.amount_due_original total_due_original,
apsa.amount_applied total_payment_applied,
apsa.amount_adjusted total_adjustment,
apsa.amount_credited total_credit,
apsa.amount_due_remaining total_due_remaining,
case when rctta.accounting_affect_flag='Y' and apsa.amount_in_dispute<>0 then apsa.amount_in_dispute end dispute_amount,
xxen_util.meaning(apsa.status,'PAYMENT_SCHEDULE_STATUS',222) status,
rtt.name payment_term,
decode(rcta.invoicing_rule_id,-3,'Arrears','Advance') invoicing_rule,
apsa.due_date,
case when apsa.class in ('INV','DM') and apsa.status='OP' then greatest(trunc(sysdate)-apsa.due_date,0) end overdue_days,
rcta.ship_date_actual ship_date,
arm.name receipt_method,
ifpct.payment_channel_name payment_method,
decode(ipiua.instrument_type,'BANKACCOUNT',ieba.masked_bank_account_num,'CREDITCARD',ic.masked_cc_number) instrument_number,
xxen_util.meaning(rcta.printing_option,'INVOICE_PRINT_OPTIONS',222) print_option,
rcta.printing_original_date first_printed_date,
rcta.customer_reference,
rcta.comments,
jrret.resource_name sales_rep,
----------line----------
decode(rctla.line_type,'FREIGHT',null,rctla.line_number) line,
nvl((select msibk.concatenated_segments from mtl_system_items_b_kfv msibk where rctla.inventory_item_id=msibk.inventory_item_id and nvl(rctla.warehouse_id,ospa.parameter_value)=msibk.organization_id),xxen_util.meaning(rctla.line_type,'STD_LINE_TYPE',222)) item,
rctla.description,
muomt.unit_of_measure_tl uom_code,
nvl(rctla.quantity_credited,rctla.quantity_invoiced) quantity,
rctla.unit_selling_price unit_price,
rctla.extended_amount amount,
(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) tax_rate,
(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') tax_amount,
nvl(rctla.interface_line_context,rbsa.name) category,
nvl(rctla.sales_order,rctla.interface_line_attribute1) sales_order,
rctla.sales_order_line,
rctla.sales_order_date,
(select rctlgda.code_combination_id from ra_cust_trx_line_gl_dist_all rctlgda where rctla.customer_trx_line_id=rctlgda.customer_trx_line_id and rctlgda.account_class='REV' and rctlgda.account_set_flag='N' and rctlgda.gl_date is not null and rctlgda.amount is not null and rctlgda.acctd_amount is not null and nvl(rctlgda.ccid_change_flag,'Y')='Y' and rownum=1) revenue_account_id,
xxen_util.user_name(rctla.created_by) created_by,
rctla.creation_date,
rctla.customer_trx_id,
rctla.customer_trx_line_id,
decode(rctla.customer_trx_line_id,min(rctla.customer_trx_line_id) keep (dense_rank first order by decode(rctla.line_type,'FREIGHT',null,rctla.line_number)) over (partition by apsa.payment_schedule_id),'Y') first_line,
----------OKS contracts----------
oklb1.line_number||nvl2(oklb2.line_number,'.',null)||oklb2.line_number contract_line,
okslb1.usage_type,
obsl.date_billed_from,
obsl.date_billed_to,
ccv.counter_reading end_read,
ccv.value_timestamp end_read_date,
obsld.actual,
decode(oklb2.lse_id,13,obsld.result) result,
nvl(oklb2.date_terminated,oklb.date_terminated) date_terminated,
msiv2.concatenated_segments||nvl2(msiv2.description,' - '||msiv2.description,null) covered_item,
cc.name installed_counter,
----------OKL contracts----------
nvl(round(months_between(obsl.date_billed_to+1,obsl.date_billed_from),2),ocasb.frequency) frequency,
ostb.code okl_stream_type,
nvl(decode(rcta.invoicing_rule_id,-3,obsl.date_billed_to+1,obsl.date_billed_from),decode(rctla.interface_line_context,'OKS CONTRACTS',decode(rcta.invoicing_rule_id,-3,to_date(rctla.interface_line_attribute5,'YYYY/MM/DD HH24:MI:SS')+1,to_date(rctla.interface_line_attribute4,'YYYY/MM/DD HH24:MI:SS')),ose.stream_element_date)) billing_due_date,
case when rctla.interface_line_context in ('OKL_CONTRACTS','OKL_INVESTOR') then rctla.interface_line_attribute1 end okl_contract_number,
nvl(
decode(oki2.jtot_object1_code,'OKX_CUSTPROD',oki2.object1_id1,'OKX_COUNTER',ccg.source_object_id),
(
select
oki3.object1_id1
from
okc_k_lines_b oklb2,
okc_k_lines_b oklb3,
okc_k_items oki3
where
case when oklb.lse_id in (49,53) then (select to_number(oki2.object1_id1) from okc_k_it