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

AP Invoice Details and Aging - draft

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

with 
Ap_inv_lines as (select * from ap_invoice_lines_all where 0=0 ) ,
Ap_inv_distr as (select * from ap_invoice_distributions_all where 1=1 )
select
gsob.NAME ledger_name,
aia.org_id,
haou.name ou,
asu.vendor_id,
asu.vendor_name supplier,
asu.segment1 supplier_number,
asu.vendor_name_alt alternate_name,
asu.num_1099 taxpayer_id, 
asu.type_1099,
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.invoice_id,
aia.invoice_num,
aia.invoice_date,
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.due_date,
ceil(sysdate-apsa.due_date) days_due,
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,
apsa.future_pay_due_date,
apsa.gross_amount,
apsa.hold_flag,
apsa.payment_method_code,
apsa.payment_priority,
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,
gcc.concatenated_segments invoice_payables_account,
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,
fu.user_name invoice_cancelled_by,
aia.temp_cancelled_amount invoice_temp_cancelled_amount,
aia.auto_tax_calc_flag,
aia.creation_date invoice_creation_date,
/*decode(ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),
  'APPROVED','Validated',
  'NEVER APPROVED','Never Validated',
  'NEEDS REAPPROVAL','Needs Revalidation') invoice_status,*/
--   decode (aia.po_number,'UNMATCHED',NULL,'ANY MULTIPLE',NULL,aia.po_number ) po_number,
gsob.currency_code,
gcc.code_combination_id code_combination_id,
gcc.concatenated_segments gl_account_string,
gcc.segment2 gl_segment2,
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,
aia.batch_id,
aia.set_of_books_id,
aia.recurring_payment_id,
arpa.rec_pay_period_type,
aia.exchange_rate_type   &invoice_detail_columns
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,
ap_system_parameters_all aspa,
gl_sets_of_books gsob,
ap_suppliers asu,
ap_supplier_sites_all assa,
ap_inv_distr aida,
ap_inv_lines aila, 
(select * from ap_recurring_payments_all where rec_pay_period_type in(select period_type from ap_other_period_types where module(+)='RECURRING PAYMENT'))arpa,
ap_terms at,
pa_projects_all ppa,
pa_tasks pt, 
fnd_currencies fc,
fnd_currencies fc1,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
fnd_user fu
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(aia.org_id,-1)=nvl(aspa.org_id,-1) 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
aida.dist_code_combination_id=gcc2.code_combination_id(+) and
aida.price_var_code_combination_id=gcc3.code_combination_id(+) and
aia.cancelled_by=fu.user_id(+)and
aia.recurring_payment_id=arpa.recurring_payment_id(+) and 
aia.terms_id=at.term_id(+) and
gsob.currency_code=fc.currency_code and
aia.accts_pay_code_combination_id=gcc.code_combination_id and
aia.set_of_books_id=gsob.set_of_books_id and 
aspa.base_currency_code=fc1.currency_code  
 
&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
:display_level in('All','Lines')
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_distribution_id,
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,
gcc2.concatenated_segments 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,
gcc3.concatenated_segments 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
Gl Account Segment2
gcc.segment2=:gl_account_segment2
Days Due
ceil(sysdate-apsa.due_date)>:days_due
Number
Display Level
:display_level='All'
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=decode(:payment_status,'Partially Paid','P','Not Paid','N','Paid','Y')
LOV

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