RK: AP Invoices and Lines

Description
Categories: Draft
Columns: Documenttype, Sequencialnumbervk, Sequencialnumberak, Transactiondate, Invoicenumber, Supplierinvoicenumber, Lastinvoicenumber, Documentindicator, Ownreference, Referenceinvoicenumber ...
Application: Payables
Source: Invoice Aging Report and Invoice Detail Report
Check for Avalara
select
decode(xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200),'Standard',1,'Credit Memo',3) "DocumentType",
null "SequencialNumberVK",
null "SequencialNumberAK",
aia.invoice_date "TransactionDate",
aia.invoice_num "InvoiceNumber",
aia.invoice_num "SupplierInvoiceNumber",
null "LastInvoiceNumber",
null "DocumentIndicator",
null "OwnReference",
null "ReferenceInvoiceNumber",
null "ReferenceInvoiceDate",
null "ReferenceTaxableBasis",
null "ReferenceVAT",
null "AutoFatturaNumber",
aia.invoice_date "InvoiceDate",
aia.invoice_currency_code "Currency",
aia.payment_currency_code "Currency2",
null "Currency3",
null "DocumentCurrency",
null "ExchangeRateDate",
null "Discount",
null "DateOfSupply",
null "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",
asu.segment1 "SupplierID",
asu.vendor_name "SupplierName",
asu.first_name "SupplierPrivatePersonFirstName",
asu.last_name "SupplierPrivatePersonLastName",
assa.address_line1 "SupplierStreet",
assa.address_line2 "SupplierHouseNumber",
assa.zip "SupplierZip",
assa.city "SupplierCity",
assa.country "SupplierCountry",
assa.area_code||assa.phone "SupplierTelephone",
assa.fax_area_code||assa.fax "SupplierFax",
asu.vat_registration_num "SupplierVATNumberUsed",
null "SupplierCountryVATNumberUsed",
assa.vat_code "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,
assa.address_line1 supplierfixedestablishmentstre,
assa.address_line2 supplierfixedestablishmenthous,
assa.zip "SupplierFixedEstablishmentZip",
assa.city "SupplierFixedEstablishmentCity",
assa.country supplierfixedestablishmentcoun,
assa.phone supplierfixedestablishmenttele,
assa.fax "SupplierFixedEstablishmentFax",
assa.email_address supplierfixedestablishmentemai,
null supplieridentifierpassportnumb,
null "SupplierIdentifierIdCardNumber",
null supplieridentifierresidencycer,
null "CustomerID",
null "CustomerName", 
null "CustomerPrivatePersonFirstName",
null "CustomerPrivatePersonLastName",
null "CustomerStreet",
null "CustomerHouseNumber",
null "CustomerZip",
null "CustomerCity",
null "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,
aia.description "Description",
null "ExemptionReason",
null "ItemClassification",
nvl(aia.invoice_amount,aia.base_amount) "TaxableBasis",
aia.tax_amount "ValueVAT",
null "SalesVATDueReverseCharge",
aia.invoice_amount+aia.tax_amount "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",
aila.quantity_invoiced "Quantity",
aila.unit_meas_lookup_code "Unit",
aila.inventory_item_id "ItemIdentifier",
assa.country "CountryDispatch",
assa.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",
null "Quantity1",
null "Unit1",
null "Quantity2",
null "Unit2",
aia.invoice_amount+aia.tax_amount "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"
/*----
decode(xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200),'Standard',1,'Credit Memo',3) "DocumentType",
asu.vendor_name supplier,
aia.invoice_num,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
xxen_util.client_time(aia.creation_date) invoice_creation_date,
aia.invoice_date,
apsa.due_date,
ceil(sysdate-apsa.due_date) days_due,
apsa.gross_amount,
asu.segment1 supplier_number,
asu.num_1099 taxpayer_id,
asu.vat_registration_num tax_registration_number,
asu.end_date_active inactive_on,
asu.customer_num,
xxen_util.meaning(asu.one_time_flag,'YES_NO',0) one_time,
asu.credit_status_lookup_code,
asu.credit_limit,
asu.withholding_status_lookup_code,
asu.withholding_start_date,
asu.vat_code,
assa.vendor_site_id,
assa.vendor_site_code,
assa.vendor_site_code supplier_site,
assa.vendor_site_code_alt,
assa.address_line1,
assa.address_line2,
assa.address_line3,
assa.address_line4,
assa.city,
assa.state,
assa.zip,
assa.county,
assa.province,
assa.country,
assa.area_code,
assa.phone,
assa.fax_area_code,
assa.fax,
assa.supplier_notif_method,
assa.email_address,
assa.remittance_email,
aia.gl_date invoice_gl_date,
aia.source invoice_source,
xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
aia.description invoice_description,
aia.invoice_currency_code,
aia.payment_currency_code,
aia.payment_cross_rate,
decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount) invoice_amount_base_currency,
aia.amount_paid invoice_amount_paid,
nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) amount_remaining,
apsa.discount_date,
apsa.future_pay_due_date,
apsa.gross_amount,
apsa.hold_flag,
nvl(xxen_util.meaning(apsa.payment_method_code,'PAYMENT METHOD',200),apsa.payment_method_code) payment_method,
xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) invoice_payment_status,
xxen_util.meaning(apsa.payment_status_flag,'INVOICE PAYMENT STATUS',200) schedule_payment_status,
apsa.second_discount_date,
apsa.third_discount_date,
apsa.discount_amount_available,
apsa.second_disc_amt_available,
apsa.third_disc_amt_available,
apsa.discount_amount_remaining,
apsa.inv_curr_gross_amount,
nvl(aia.amount_paid,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*aia.exchange_rate amount_paid_base,
nvl(aia.amount_applicable_to_discount,0)*aia.exchange_rate amt_applicable_to_disc_base,
nvl(aia.discount_amount_taken,0)/decode(nvl(aia.payment_cross_rate,1),0,1,aia.payment_cross_rate)*aia.exchange_rate discount_amount_taken_base,
nvl(aia.approved_amount,0)*aia.exchange_rate manual_approval_amount_base,
nvl(aia.payment_amount_total,0)*aia.exchange_rate payment_amount_total_base,
nvl(aia.tax_amount,0)*aia.exchange_rate tax_amount_base,
aia.discount_amount_taken,
aia.amount_applicable_to_discount,
aia.tax_amount,
aia.pay_curr_invoice_amount,
aia.payment_cross_rate_date,
at.name invoice_terms,
aia.terms_date,
aia.pay_group_lookup_code invoice_pay_group,
aia.accts_pay_code_combination_id,
xxen_util.concatenated_segments(aia.accts_pay_code_combination_id) account,
xxen_util.segments_description(aia.accts_pay_code_combination_id) account_descripton,
aia.base_amount invoice_base_amount,
aia.approved_amount,
xxen_util.meaning(aia.exclusive_payment_flag,'YES_NO',0) invoice_exclusive_payment,
aia.cancelled_date invoice_cancelled_date,
aia.cancelled_amount invoice_cancelled_amount,
xxen_util.user_name(aia.cancelled_by) invoice_cancelled_by,
aia.temp_cancelled_amount invoice_temp_cancelled_amount,
aia.auto_tax_calc_flag,
aia.invoice_amount,
aia.amount_paid,
--   decode (aia.po_number,'unmatched',null,'any multiple',null,aia.po_number ) po_number,
gl.currency_code,
apsa.payment_num,
case when ceil(sysdate-apsa.due_date)=0 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end current_bucket,
aila.line_number,
aila.line_type_lookup_code line_type,
replace(aila.description,'~','-') line_description,
aila.amount line_amount,
aila.tax_classification_code,
aila.product_fisc_classification,
aila.user_defined_fisc_class,
aila.base_amount line_base_amount,
arpa.recurring_pay_num,
arpa.rec_pay_period_type,
arpa.num_of_periods,
arpa.description recurring_pmt_description,
aia.invoice_id*/
from
gl_ledgers gl,
hr_all_organization_units_vl haouv,
hr_all_organization_units_vl haouv1,
hr_all_organization_units_vl haouv2,
hr_all_organization_units_vl haouv3,
ap_invoices_all aia,
ap_payment_schedules_all apsa,
ap_suppliers asu,
ap_supplier_sites_all assa,
(select aila.* from ap_invoice_lines_all aila where 'Y'='Y') aila,
(select aida.* from ap_invoice_distributions_all aida where ''='Y') aida,
ap_recurring_payments_all arpa,
ap_terms at,
pa_projects_all ppa,
pa_tasks pt
where
aia.payment_status_flag in ('N','P') and
aia.cancelled_date is null and
1=1 and
aia.set_of_books_id=gl.ledger_id and
aia.org_id=haouv.organization_id(+) and
aia.expenditure_organization_id=haouv1.organization_id(+) and
aila.expenditure_organization_id=haouv2.organization_id(+) and
aida.expenditure_organization_id=haouv3.organization_id(+) and
aia.invoice_id=apsa.invoice_id and
nvl(apsa.amount_remaining,0)*nvl(aia.exchange_rate,1)!=0 and
aia.vendor_id=asu.vendor_id and
aia.vendor_site_id=assa.vendor_site_id and
aia.invoice_id=aila.invoice_id(+) and
aila.invoice_id=aida.invoice_id(+)and
aila.line_number=aida.invoice_line_number(+) and
aida.project_id=ppa.project_id(+)and
aida.task_id=pt.task_id(+)and
aia.recurring_payment_id=arpa.recurring_payment_id(+) and
aia.terms_id=at.term_id(+) and
aia.invoice_type_lookup_code in ('STANDARD','CREDIT')
Parameter Name SQL text Validation
Organization
haouv.name=:org_name
LOV
Supplier
asu.vendor_name=:supplier_name
LOV
Ledger
gl.name=:ledger
LOV
Inv. Date From
aia.invoice_date>=:invoice_date_from
Date
Inv. Date To
aia.invoice_date<:invoice_date_to+1
Date
Display Level
Y
LOV
Days Due
ceil(sysdate-apsa.due_date)>:days_due
Number
Gl Account
gcc.segment2=:gl_account_segment
Payment Status
aia.payment_status_flag=xxen_util.lookup_code(:payment_status,'INVOICE PAYMENT STATUS',200)
LOV
Open only
aia.payment_status_flag in ('N','P')
LOV Oracle
Exclude Cancelled
aia.cancelled_date is null
LOV Oracle
Display Level
Y
Display Level
aila.line_number,
aila.line_type_lookup_code line_type,
replace(aila.description,'~','-') line_description,
aila.amount line_amount,
aila.base_amount line_base_amount,
Display Level
aila.line_number,
aila.line_type_lookup_code line_type,
replace(aila.description,'~','-') line_description,
aila.amount line_amount,
aila.base_amount line_base_amount,
aida.distribution_line_number,
aida.parent_reversal_id,
aida.dist_match_type,
aida.accounting_date dist_accounting_date,
aida.assets_addition_flag dist_assets_addition,
aida.distribution_line_number dist_line_number,
aida.dist_code_combination_id,
xxen_util.concatenated_segments(aida.dist_code_combination_id) expense_account,
aida.line_type_lookup_code dist_line_type,
aida.period_name,
aida.creation_date dist_creation_date,
aida.last_update_date dist_last_update_date,
aida.amount dist_amount,
nvl(aida.amount,aida.base_amount) original_amount,
aida.base_invoice_price_variance dist_base_inv_price_variance,
replace(aida.description,'~','-') dist_description,
aida.invoice_price_variance dist_invoice_price_variance,
aida.match_status_flag dist_match_status,
aida.po_distribution_id,
aida.quantity_invoiced,
aida.unit_price,
aida.price_var_code_combination_id,
xxen_util.concatenated_segments(aida.price_var_code_combination_id) price_variance_account,
aida.expenditure_item_date pa_expenditure_item_date,
aida.expenditure_type pa_expenditure_type,
aida.expenditure_organization_id,
coalesce(haouv1.name,haouv3.name,haouv2.name) expenditure_organization,
aida.pa_addition_flag,
aida.project_accounting_context,
ppa.project_id,
pt.task_id,
ppa.segment1 project_number,
ppa.name project_name,
ppa.description project_description,
ppa.project_type,
ppa.project_status_code,
ppa.start_date project_start_date,
ppa.completion_date project_completion_date,
pt.task_number,
pt.task_name,
pt.description task_description,
pt.service_type_code,
pt.start_date task_start_date,
pt.completion_date task_completion_date,