AP Recon Report
SELECT lb.company_code co,
lb.location_code ,
lb.ledger_name
ledger_name,
lb.org_id org_id,
lb.organization_name organization_name,
lb.glbl_account gac,
lb.lcl_account lac,
lb.vendor_name
vendor_name,
lb.invoice_number
invoice_num,
lb.invoice_type
invoice_type,
lb.invoice_date
inv_date,
lb.due_date
due_date,
( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) )
days_due,
CASE
WHEN ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) <= 0 THEN
'DUE_0'
WHEN ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) > 0
AND ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) <= 30
THEN
'DUE_1_30'
WHEN ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) > 30
AND ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) <= 60
THEN
'DUE_31_60'
WHEN ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) > 60
AND ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) <= 90
THEN
'DUE_61_90'
WHEN ( Trunc(Nvl(:p_sysdate, SYSDATE)) - Trunc(lb.due_date) ) > 90 THEN
'DUE_OVR_90'
ELSE NULL
END
aging_bucket,
lb.invoice_description
invoice_desc,
lb.document_category
document_category,
lb.document_sequence
document_seq_value,
lb.internal_seq_num,
lb.payment_terms
payment_terms,
lb.inv_curr_cd
inv_cy_cd,
lb.gl_curr_cd
ledger_cy_cd,
NULL
description,
Nvl(lb.inv_cr_entered, 0) - Nvl(lb.inv_dr_entered, 0)
invoice_amt,
Nvl(lb.inv_cr_func, 0) - Nvl(lb.inv_dr_func, 0)
inv_amt_func,
(
(
nvl(tbpmt.pmt_cr_entered, 0) - nvl(tbpmt.pmt_dr_entered, 0)
)
) rem_amount,
( Nvl(tbpmt.pmt_cr_func, 0) - Nvl(tbpmt.pmt_dr_func, 0) )
rem_amt_func,
CASE
WHEN lb.inv_curr_cd = 'USD' THEN
( Nvl(tbpmt.pmt_cr_func, 0) - Nvl(tbpmt.pmt_dr_func, 0) )
ELSE
Round(( (nvl(tbpmt.pmt_cr_entered, 0) - nvl(tbpmt.pmt_dr_entered, 0))*
eop.conversion_rate ), 2)
END
rem_amt_usd_eop,
eop.conversion_rate eop_con_rate,
lb.po_num,
lb.po_line,
lb.po_line_desc,
lb.po_gac,
lb.po_dept,
lb.po_proj,
LB.PERIOD_NAME,
To_char(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM')
timestamp
FROM (
SELECT inv.org_id org_id,
hou.name organization_name,
xte.source_id_int_1 source_entity_id,
gll.name ledger_name,
lbt.ledger_id ledger_id,
cde.segment1 company_code,
cde.segment2 location_code,
cde.segment3 glbl_account,
cde.segment4 lcl_account,
inv.invoice_id invoice_id,
lbt.party_id vendor_id,
XTE.PERIOD_NAME,
vnd.vendor_name vendor_name,
inv.invoice_num invoice_number,
inv.invoice_date invoice_date,
ddt.due_date due_date,
inv.invoice_currency_code inv_curr_cd,
gll.currency_code gl_curr_cd,
inv.description invoice_description,
inv.invoice_type_lookup_code invoice_type,
inv.doc_category_code document_category,
inv.doc_sequence_value document_sequence,
inv.tax_invoice_internal_seq internal_seq_num,
trm.name payment_terms,
To_char(:p_sysdate, 'mm/dd/yyyy') closing_date,
SUM(nvl(lbt.entered_dr,0)) inv_dr_entered,
--replaced by SUM(lbt.entered_rounded_dr)
SUM(nvl(lbt.entered_cr,0)) inv_cr_entered,
--replaced by SUM(lbt.entered_rounded_cr)
SUM(nvl(lbt.accounted_dr,0)) inv_dr_func,
--replaced by SUM(lbt.acctd_rounded_dr)
SUM(nvl(lbt.accounted_cr,0)) inv_cr_func,
--replaced by SUM(lbt.acctd_rounded_cr)
po.po_number po_num,
po.po_line,
po.po_line_description po_line_desc,
po.po_global_account po_gac,
po.po_department po_dept,
po.po_project po_proj,
NULL abc
FROM ap_trial_balances lbt,--replaced with xla_trial_balances
ap_invoices_all inv,
gl_code_combinations cde,
xla_ae_headers xah,
xla_transaction_entities xte,
poz_suppliers_v vnd,
ap_terms_tl trm,
gl_ledgers gll,
hr_all_organization_units hou,
(SELECT invoice_id,
org_id,
Max(due_date) due_date
FROM ap_payment_schedules_all
GROUP BY invoice_id,
org_id) ddt,
(SELECT aid.org_id org_id,
aid.set_of_books_id ledger_id,
aid.invoice_id invoice_id,
pol.line_num po_line,
pol.item_description po_line_description,
cde.segment3 po_global_account,
cde.segment5 po_department,
cde.segment8 po_project,
phd.segment1 po_number
FROM ap_invoice_distributions_all aid,
po_distributions_all pod,
po_headers_all phd,
po_lines_all pol,
gl_code_combinations cde
WHERE pod.po_distribution_id = aid.po_distribution_id
AND phd.po_header_id = pod.po_header_id
AND pol.po_line_id = pod.po_line_id
AND cde.code_combination_id = pod.code_combination_id
AND aid.invoice_distribution_id =
(SELECT DISTINCT Max(invoice_distribution_id)
over()
FROM ap_invoice_distributions_all mxd
WHERE mxd.amount <> 0
AND Nvl(mxd.reversal_flag, 'N') = 'N'
AND mxd.line_type_lookup_code = 'ITEM'
AND mxd.po_distribution_id IS NOT NULL
AND mxd.invoice_id = aid.invoice_id
AND mxd.org_id = aid.org_id))po
WHERE cde.code_combination_id = lbt.code_combination_id
AND lbt.ae_header_id = xah.ae_header_id
AND xte.entity_code(+) = 'AP_INVOICES' --added outer join
AND xte.application_id (+) = 200 --added outer join
AND xah.application_id (+) = 200
AND xte.entity_id (+) = xah.entity_id --added outer join
AND inv.vendor_id = vnd.vendor_id --changed join lbt.party_id
AND gll.ledger_id = lbt.ledger_id
AND Nvl(xte.source_id_int_1, -99) = inv.invoice_id(+)
--added outer join
AND trm.term_id (+) = inv.terms_id
AND trm.LANGUAGE (+) = 'US' --added outer join
AND ddt.invoice_id (+) = inv.invoice_id --added outer join
AND ddt.org_id (+) = inv.org_id --added outer join
AND inv.org_id = po.org_id (+)
AND lbt.ledger_id = po.ledger_id (+)
AND inv.invoice_id = po.invoice_id (+)
AND inv.org_id = hou.organization_id(+)
AND lbt.accounting_date <= :p_sysdate
AND inv.invoice_date > To_date('12/31/2011', 'mm/dd/yyyy')
AND ( CASE
WHEN cde.segment1 IN ( :p_OU ) THEN 1
WHEN ( Coalesce(NULL, :p_OU) IS NULL ) THEN 1
END = 1 )
--and (cde.segment1 = NVL(:p_OU,cde.segment1))
AND ( CASE
WHEN gll.name IN ( :p_LEDGER_NAME ) THEN 1
WHEN ( Coalesce(NULL, :p_LEDGER_NAME) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN hou.name IN ( :p_ORG_ID ) THEN 1
WHEN ( Coalesce(NULL, :p_ORG_ID) IS NULL ) THEN 1
END = 1 )
GROUP BY inv.org_id,
hou.name,
gll.name,
lbt.ledger_id,
cde.segment1,
cde.segment2,
cde.segment3,
cde.segment4,
inv.invoice_id,
lbt.party_id,
XTE.PERIOD_NAME,
vnd.vendor_name,
inv.invoice_num,
inv.invoice_date,
ddt.due_date,
inv.invoice_currency_code,
gll.currency_code,
inv.description,
inv.invoice_type_lookup_code,
inv.doc_category_code,
inv.doc_sequence_value,
inv.tax_invoice_internal_seq,
trm.name,
xte.source_id_int_1,
po.po_number,
po.po_line,
po.po_line_description,
po.po_global_account,
po.po_department,
po.po_project)lb,
(SELECT lbt.ledger_id ledger_id,
lbt.invoice_id applied_to_entity_id,
SUM(nvl(lbt.entered_dr,0)) pmt_dr_entered,
SUM(nvl(lbt.entered_cr,0)) pmt_cr_entered,
SUM(nvl(lbt.accounted_dr,0)) pmt_dr_func,
SUM(nvl(lbt.accounted_cr,0)) pmt_cr_func
FROM ap_trial_balances lbt,--replaced with xla_trial_balances
gl_ledgers gll
WHERE 1 = 1
AND trunc(lbt.accounting_date) <= :p_sysdate
AND gll.ledger_id = lbt.ledger_id
AND ( gll.name = Nvl(:p_LEDGER_NAME, gll.name) )
GROUP BY lbt.ledger_id,lbt.invoice_id
)tbpmt,
(SELECT Al1.Period_Mon_Yy period,
Al1.From_Curr,
Al1.To_Curr,
SUM(Decode(Al1.Conversion_Type,
'Corporate',
Round(Al1.Conversion_Rate, 10),
NULL)) Corporate,
SUM(Decode(Al1.Conversion_Type,
'AVG',
Round(Al1.Conversion_Rate, 10),
NULL)) AVG,
SUM(Decode(Al1.Conversion_Type,
'EOP',
Round(Al1.Conversion_Rate, 10),
NULL)) conversion_rate,
to_char(sysdate,'MM/DD/YYYY HH12:MM:SS AM') timestamp
FROM (SELECT Glp.Period_Name Period_Mon_Yy,
Rat.From_Currency From_Curr,
Rat.To_Currency To_Curr,
Decode(Rat.Conversion_Type,
'Corporate',
'Corporate',
--'1001',
'300000339115913',
'EOP',
--'1002',
'300000339115912',
--300000339115913--EOM
'AVG',
Rat.Conversion_Type) Conversion_Type,
Rat.Conversion_Type Conv_Type_Orig,
Rat.Conversion_Rate Conversion_Rate
FROM Gl_Periods Glp, Gl_Daily_Rates Rat
WHERE Rat.Conversion_Date BETWEEN Glp.Start_Date AND Glp.End_Date
AND Glp.Period_Set_Name = '4-4-5'
AND Glp.Period_Type like 'MONTH8731158251'
and Rat.Conversion_Type='300000339115913'
AND To_Char(Rat.Conversion_Date, 'DD') = '15'
-- AND Glp.Period_Name = NVL(:P_PERIOD_NAME,Glp.Period_Name)
-- AND Rat.From_Currency = NVL(:P_FROM_CURRENCY,Rat.From_Currency)
-- AND Rat.To_Currency = NVL(:P_TO_CURRENCY,Rat.To_Currency)
--AND :P_REPORT_TYPE = 'MONTHLY_RATES'
) Al1
GROUP BY Al1.Period_Mon_Yy, Al1.From_Curr, Al1.To_Curr
ORDER BY Al1.From_Curr,
Al1.To_Curr
)
eop
WHERE 1 = 1
AND tbpmt.applied_to_entity_id (+) = lb.source_entity_id --commented due to no equivalent column
AND tbpmt.ledger_id (+) = lb.ledger_id
-- AND tbpmt.definition_code (+) = lb.definition_code --commented due to no equivalent column
AND ( ( Nvl(lb.inv_cr_entered, 0) - Nvl(lb.inv_dr_entered, 0) ) - (
Nvl(tbpmt.pmt_dr_entered, 0) - Nvl(tbpmt.pmt_cr_entered, 0) ) )
<> 0
AND eop.from_curr (+) = lb.inv_curr_cd
-- AND eop.TO_curr (+) = lb.gl_curr_cd
AND eop.PERIOD (+) = lb.PERIOD_NAME
AND eop.to_curr (+) = 'USD'
No comments:
Post a Comment