select
gl.name ledger,
hou.name operating_unit,
aps.vendor_name,
aia.invoice_num,
xxen_util.user_name(aia.created_by) created_by,
aia.invoice_currency_code,
aia.invoice_amount,
aia.total_tax_amount,
aila.line_number,
aida.distribution_line_number,
gcck.concatenated_segments,
aida.period_name,
aida.posted_flag,
aida.amount,
decode(aila.deferred_acctg_flag,'Y',xxen_util.meaning(aila.deferred_acctg_flag,'YES_NO',0)) deferred_option,
aila.def_acctg_start_date deferred_start_date,
aila.def_acctg_end_date deferred_end_date,
aila.deferred_months,
add_months(trunc(aila.def_acctg_start_date,'month'),rowgen.column_value-1) month,
round(aida.amount/aila.deferred_months,2) prorated_amount
from
gl_ledgers gl,
hr_operating_units hou,
ap_suppliers aps,
ap_invoices_all aia,
(select months_between(trunc(aila.def_acctg_end_date,'month'),trunc(aila.def_acctg_start_date,'month'))+1 deferred_months, aila.* from ap_invoice_lines_all aila) aila,
ap_invoice_distributions_all aida,
gl_code_combinations_kfv gcck,
table(xxen_util.rowgen(aila.deferred_months)) rowgen
where
1=1 and
gl.ledger_id=hou.set_of_books_id and
aila.deferred_acctg_flag='Y' and
hou.organization_id=aia.org_id and
aps.vendor_id=aia.vendor_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.dist_code_combination_id=gcck.code_combination_id(+)
order by
aia.invoice_id,
aila.line_number,
rowgen.column_value,
aida.distribution_line_number |
