RK: AP Invoices and Lines

Description
Categories: Draft
Application: Payables
Source: Invoice Aging Report and Invoice Detail Report
Check for Avalara
Run RK: AP Invoices and Lines and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Supplier
asu.vendor_name=:supplier_name
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
Char
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
Organization
haouv.name=:org_name
LOV
Ledger
gl.name=:ledger
LOV
Download
Blitz Report™

Blitz Report™ provides multiple benefits: