AP Invoices and Lines

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Ledger, Operating Unit, Supplier, Invoice Num, Invoice Status, Invoice Creation Date, Invoice Date, Gl Date, Due Date, Days Due ...
Detail Invoice Aging report with line item details and amounts
select
gl.name ledger,
haouv.name operating_unit,
aps.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,
aia.gl_date,
apsa.due_date,
ceil(sysdate-apsa.due_date) days_due,
xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
aia.source invoice_source,
aia.description invoice_description,
decode(apsa.payment_num,1,aia.invoice_amount) invoice_amount,
decode(apsa.payment_num,1,aia.total_tax_amount) tax_amount,
decode(apsa.payment_num,1,aia.amount_applicable_to_discount) amount_applicable_to_discount,
decode(apsa.payment_num,1,aia.discount_amount_taken) discount_amount_taken,
decode(apsa.payment_num,1,aia.approved_amount) approved_amount,
aia.invoice_currency_code,
decode(apsa.payment_num,1,aia.amount_paid) amount_paid,
decode(apsa.payment_num,1,aia.pay_curr_invoice_amount) pay_curr_invoice_amount,
aia.payment_currency_code,
decode(apsa.payment_num,1,decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount)) invoice_amount_base,
decode(apsa.payment_num,1,aia.total_tax_amount)*aia.exchange_rate tax_amount_base,
decode(apsa.payment_num,1,aia.amount_applicable_to_discount)*aia.exchange_rate amt_applicable_to_disc_base,
decode(apsa.payment_num,1,aia.discount_amount_taken)/aia.payment_cross_rate*aia.exchange_rate discount_amount_taken_base,
decode(apsa.payment_num,1,aia.approved_amount)*aia.exchange_rate approved_amount_base,
decode(apsa.payment_num,1,aia.amount_paid)/aia.payment_cross_rate*aia.exchange_rate amount_paid_base,
gl.currency_code base_currency,
aia.doc_sequence_value,
apsa.payment_priority,
--(nvl(C_SUM_INV_DUE_AMT_3,0) * 100)/nvl(C_SUM_AMT_REMAINING,1) percent_open,
apsa.payment_num,
apsa.gross_amount,
case when ceil(sysdate-apsa.due_date)=0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end current1,
case when ceil(sysdate-apsa.due_date) between 0 and 30 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_0_30,
case when ceil(sysdate-apsa.due_date) between 1 and 30 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_1_30,
case when ceil(sysdate-apsa.due_date) between 31 and 60 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_31_60,
case when ceil(sysdate-apsa.due_date) between 61 and 90 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_61_90,
case when ceil(sysdate-apsa.due_date) between 91 and 120 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_91_120,
case when ceil(sysdate-apsa.due_date) between 121 and 150 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_121_150,
case when ceil(sysdate-apsa.due_date) between 151 and 180 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end between_151_180,
case when ceil(sysdate-apsa.due_date) >=181 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end greater_than_180,
aps.segment1 supplier_number,
aps.num_1099 taxpayer_id,
aps.vat_registration_num tax_registration_number,
aps.end_date_active inactive_on,
aps.customer_num,
xxen_util.meaning(aps.one_time_flag,'YES_NO',0) one_time,
aps.credit_status_lookup_code,
aps.credit_limit,
aps.withholding_status_lookup_code,
aps.withholding_start_date,
aps.vat_code,
assa.vendor_site_id,
assa.vendor_site_code,
assa.vendor_site_code supplier_site,
assa.vendor_site_code_alt,
xxen_util.meaning(assa.purchasing_site_flag,'YES_NO',0) purchasing_site,
xxen_util.meaning(assa.rfq_only_site_flag,'YES_NO',0) rfq_site,
xxen_util.meaning(assa.pay_site_flag,'YES_NO',0) pay_site,
xxen_util.meaning(assa.tax_reporting_site_flag,'YES_NO',0) tax_reporting_site,
xxen_util.meaning(assa.pcard_site_flag,'YES_NO',0) p_card_site,
xxen_util.meaning(assa.attention_ar_flag,'YES_NO',0) attention_ar,
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.payment_cross_rate,
aia.payment_cross_rate_date,
apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) amount_remaining,
apsa.discount_date,
apsa.future_pay_due_date,
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,
cbv.bank_name,
ieba.iban,
at.name invoice_terms,
aia.terms_date,
aia.cancelled_date invoice_cancelled_date,
decode(apsa.payment_num,1,aia.cancelled_amount) invoice_cancelled_amount,
xxen_util.user_name(aia.cancelled_by) invoice_cancelled_by,
decode(apsa.payment_num,1,aia.temp_cancelled_amount) invoice_temp_cancelled_amount,
aia.auto_tax_calc_flag,
--   decode (aia.po_number,'unmatched',null,'any multiple',null,aia.po_number ) po_number,
case when ceil(sysdate-apsa.due_date)=0 then apsa.amount_remaining/aia.payment_cross_rate*nvl(aia.exchange_rate,1) end current_bucket,
&invoice_detail_columns
aia.pay_group_lookup_code invoice_pay_group,
xxen_util.concatenated_segments(aia.accts_pay_code_combination_id) accts_pay_account,
xxen_util.segments_description(aia.accts_pay_code_combination_id) accts_pay_account_descripton,
xxen_util.meaning(aia.exclusive_payment_flag,'YES_NO',0) invoice_exclusive_payment,
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,
iby_ext_bank_accounts ieba,
ce_banks_v cbv,
ap_suppliers aps,
ap_supplier_sites_all assa,
(select aila.* from ap_invoice_lines_all aila where '&enable_aila'='Y') aila,
(select aida.* from ap_invoice_distributions_all aida where '&enable_aida'='Y') aida,
ap_recurring_payments_all arpa,
ap_terms at,
pa_projects_all ppa,
pa_tasks pt
where
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
apsa.external_bank_account_id=ieba.ext_bank_account_id(+) and
ieba.bank_id=cbv.bank_party_id(+) and
aia.vendor_id=aps.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(+)
order by
haouv.name,
aps.vendor_name,
aia.invoice_date,
aia.gl_date,
aia.invoice_num,
apsa.payment_num
&p_order_by
Parameter Name SQL text Validation
Ledger 
gl.name=:ledger
 LOV
Operating Unit 
haouv.name=:operating_unit
 LOV
Invoice Number 
aia.invoice_num=:invoice_number
 LOV
Invoice Type 
aia.invoice_type_lookup_code=xxen_util.lookup_code(:invoice_type,'INVOICE TYPE',200)
 LOV
Supplier 
aps.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
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') and
apsa.amount_remaining<>0
 LOV Oracle
Exclude Cancelled 
aia.cancelled_date is null
 LOV Oracle
Display Level 
Y
Display Level 
aila.line_number,
xxen_util.meaning(aila.line_type_lookup_code,'INVOICE LINE TYPE',200) line_type,
xxen_util.meaning(aila.line_source,'LINE SOURCE',200) line_source,
replace(aila.description,'~','-') line_description,
aila.amount line_amount,
aila.base_amount line_base_amount,
Display Level 
aila.line_number,
xxen_util.meaning(aila.line_type_lookup_code,'INVOICE LINE TYPE',200) line_type,
xxen_util.meaning(aila.line_source,'LINE SOURCE',200) line_source,
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,
xxen_util.concatenated_segments(aida.dist_code_combination_id) expense_account,
xxen_util.meaning(aida.line_type_lookup_code,'INVOICE DISTRIBUTION TYPE',200) distribution_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,
Download
 
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: