Reports2017-11-18T12:27:27+00:00

AP Invoice Details and Aging

Description
Categories: Enginatics, Financials, Procurement
Application: Payables
Source: Invoice Aging Report and Invoice Detail Report

select
gsob.name ledger,
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,
aia.creation_date invoice_creation_date,
aia.invoice_date,
apsa.due_date,
ceil(sysdate-apsa.due_date) days_due,
apsa.payment_priority,
--(nvl(C_SUM_INV_DUE_AMT_3,0) * 100)/nvl(C_SUM_AMT_REMAINING,1) percent_open,
apsa.gross_amount,
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 current1,
case when ceil(sysdate-apsa.due_date) between 0 and 30 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_0_30,
case when ceil(sysdate-apsa.due_date) between 1 and 30 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_1_30,
case when ceil(sysdate-apsa.due_date) between 31 and 60 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_31_60,
case when ceil(sysdate-apsa.due_date) between 61 and 90 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_61_90,
case when ceil(sysdate-apsa.due_date) between 91 and 120 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_91_120,
case when ceil(sysdate-apsa.due_date) between 121 and 150 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_121_150,
case when ceil(sysdate-apsa.due_date) between 151 and 180 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end between_151_180,
case when ceil(sysdate-apsa.due_date) >=181 then nvl(apsa.amount_remaining,0)/nvl(aia.payment_cross_rate,1)*nvl(aia.exchange_rate,1) end greater_than_180,
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,
decode(asu.one_time_flag,'N','No','Y','Yes') 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,
decode(assa.purchasing_site_flag,'Y','Yes','N','No') purchasing_site,
decode(assa.rfq_only_site_flag,'Y','Yes','N','No') rfq_site,
decode(assa.pay_site_flag,'Y','Yes','N','No') pay_site,
decode(assa.tax_reporting_site_flag,'Y','Yes','N','No') tax_reporting_site,
decode(assa.pcard_site_flag,'Y','Yes','N','No') p_card_site,
decode(assa.attention_ar_flag,'Y','Yes','N','No') 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.gl_date invoice_gl_date,
aia.source invoice_source,
aia.invoice_type_lookup_code invoice_type,
aia.description invoice_description,
aia.invoice_currency_code,
aia.payment_currency_code,
aia.payment_cross_rate,
decode(aia.invoice_currency_code,gsob.currency_code,aia.invoice_amount,aia.base_amount) invoice_amount,
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,
apsa.payment_method_code,
decode(apsa.payment_status_flag,'Y','Yes','N','No') 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,
decode(aia.payment_status_flag,'Y','Yes','N','No') invoice_payment_status,
aia.base_amount invoice_base_amount,
aia.approved_amount,
decode(aia.exclusive_payment_flag,'Y','Yes','N','No') 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,
-----------
aila.line_number invoice_line,
aila.amount invoice_line_amount,
aia.invoice_amount,
aia.amount_paid,
--   decode (aia.po_number,'unmatched',null,'any multiple',null,aia.po_number ) po_number,
gsob.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,
aia.payment_method_lookup_code invoice_pay_method_lookup_code,
aia.payment_method_lookup_code,
&invoice_detail_columns
haou.name ou,
aia.invoice_id
from 
hr_all_organization_units haou,
hr_all_organization_units haou1,
hr_all_organization_units haou2,
hr_all_organization_units haou3,
ap_invoices_all aia,
ap_payment_schedules_all apsa,
gl_sets_of_books gsob,
ap_suppliers asu,
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
2=2 and
haou.organization_id(+)=aia.org_id and
haou1.organization_id(+)=aia.expenditure_organization_id and
haou2.organization_id(+)=aila.expenditure_organization_id and 
haou3.organization_id(+)=aida.expenditure_organization_id and 
aia.cancelled_date is null and
aia.payment_status_flag in ('N','P') 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.set_of_books_id=gsob.set_of_books_id
&p_order_by

Parameter Name SQL text Validation
Organization
haou.name=:org_name
LOV
Supplier
asu.vendor_name=:supplier_name
LOV
Ledger
gsob.name=:ledger_name
LOV
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,
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
Display Level
aida.invoice_line_number,
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(haou1.name,haou3.name,haou2.name) expenditure_organization,
aida.pa_addition_flag,
aida.project_accounting_context,
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,
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
Display Level
Y
Gl Account Segment2
gcc.segment2=:gl_account_segment2
Days Due
ceil(sysdate-apsa.due_date)>:days_due
Number
Display Level
Y
LOV
Inv. Date To
aia.invoice_date<:invoice_date_to+1
Date
Inv. Date From
aia.invoice_date>=:invoice_date_from
Date
Payment Status
aia.payment_status_flag=xxen_util.lookup_code(:payment_status,'INVOICE PAYMENT STATUS',200)
LOV

By continuing to use the site, you agree to the use of cookies. Accept