SELECT 'FINANCE' detdepartment, 'FINANCE' detsub_dept, 'FINANCE' detuser_name,
ai.unique_remittance_identifier, ai.invoice_num, ai.invoice_date,
ai.description, ai.invoice_amount, av.vendor_name, av.vendor_type_disp,
ai.invoice_received_date, TRUNC (aipa.creation_date) last_forward_date,
TRUNC (xvd.creation_date + NVL (atl.due_days, 0)) due_date,
trunc(sysdate)-(TRUNC(xvd.CREATION_DATE)+NVL(atl.DUE_DAYS,0))due_days,
'Validated - Oracle Paid: Unclear' status, ai.invoice_id,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pha.segment1 || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_headers_all pha, ap_invoice_lines_all aila
WHERE pha.po_header_id = aila.po_header_id
AND aila.invoice_id = ai.invoice_id) po_number,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pha.type_lookup_code || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_headers_all pha, ap_invoice_lines_all aila
WHERE pha.po_header_id = aila.po_header_id
AND aila.invoice_id = ai.invoice_id) po_type,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pra.release_num || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_releases_all pra, ap_invoice_lines_all aila
WHERE pra.po_release_id = aila.po_release_id
AND aila.invoice_id = ai.invoice_id) release_number
FROM apps.ap_terms apt,
ap_invoices_all ai,
ap_terms_lines atl,
org_organization_definitions ood,
fnd_user fu,
xxindus_vhd_dynamic_h xvdh,
xxindus_vhd_dynamic xvd,
ap_lookup_codes alc,
ap_invoice_payments_all aipa,
ap_payment_history_all apha,
ap_vendors_v av
WHERE xvd.invoice_id = ai.invoice_id
AND apt.term_id = ai.terms_id
AND apt.term_id = atl.term_id
AND atl.sequence_num = 1
AND ai.terms_id = apt.term_id
AND ai.org_id = ood.organization_id
AND ai.created_by = fu.user_id
AND ai.invoice_id = xvdh.invoice_id
AND ai.invoice_id = xvd.invoice_id
AND alc.lookup_code = ai.invoice_type_lookup_code
AND ai.invoice_id = aipa.invoice_id
AND aipa.check_id = apha.check_id
AND aipa.invoice_payment_id = (SELECT MAX (invoice_payment_id)
FROM ap_invoice_payments_all a
WHERE a.invoice_id = ai.invoice_id)
AND apha.accounting_event_id = (SELECT MAX (accounting_event_id)
FROM ap_payment_history_all aph1
WHERE aph1.check_id = apha.check_id)
AND ai.vendor_id = av.vendor_id
AND apha.transaction_type IN ('PAYMENT CREATED', 'PAYMENT UNCLEARING')
AND ai.invoice_type_lookup_code = 'STANDARD'
AND alc.lookup_type = 'INVOICE TYPE'
AND alc.enabled_flag = 'Y'
AND ai.wfapproval_status IN ('WFAPPROVED', 'MANUALLY APPROVED')
AND xvdh.checked = 'Y'
AND xvd.status = 'Sent to Finance'
AND ai.cancelled_date IS NULL
--- AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE)
AND ai.invoice_amount =
NVL ((ai.amount_paid + NVL (ai.discount_amount_taken, 0)), 0)
-- AND ood.organization_name = :p_org_name
AND ood.organization_id = :p_org_id
AND ai.invoice_received_date
BETWEEN NVL (TRUNC (TO_DATE (:p_from_date, 'RRRR/MM/DD HH24:MI:SS')),
ai.invoice_received_date
)
AND NVL (TRUNC (TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')),
SYSDATE
)
AND (fu.user_name LIKE '%VHD%' OR fu.description = 'VHD')
after changes
SELECT 'FINANCE' detdepartment, 'FINANCE' detsub_dept, 'FINANCE' detuser_name,
ai.unique_remittance_identifier, ai.invoice_num, ai.invoice_date,
ai.description, ai.invoice_amount, av.vendor_name, av.vendor_type_disp,
ai.invoice_received_date, TRUNC (aipa.creation_date) last_forward_date,
TRUNC (xvd.creation_date + NVL (atl.due_days, 0)) due_date,
trunc(sysdate)-(TRUNC(xvd.CREATION_DATE)+NVL(atl.DUE_DAYS,0))due_days,
'Validated - Oracle Paid: Unclear' status, ai.invoice_id,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pha.segment1 || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_headers_all pha, ap_invoice_lines_all aila
WHERE pha.po_header_id = aila.po_header_id
AND aila.invoice_id = ai.invoice_id) po_number,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pha.type_lookup_code || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_headers_all pha, ap_invoice_lines_all aila
WHERE pha.po_header_id = aila.po_header_id
AND aila.invoice_id = ai.invoice_id) po_type,
(SELECT RTRIM
(XMLAGG (XMLELEMENT (e, pra.release_num || '|')).EXTRACT
('//text()'),
'|'
)
FROM po_releases_all pra, ap_invoice_lines_all aila
WHERE pra.po_release_id = aila.po_release_id
AND aila.invoice_id = ai.invoice_id) release_number
FROM apps.ap_terms apt,
ap_invoices_all ai,
ap_terms_lines atl,
org_organization_definitions ood,
fnd_user fu,
xxindus_vhd_dynamic_h xvdh,
xxindus_vhd_dynamic xvd,
ap_lookup_codes alc,
ap_invoice_payments_all aipa,
ap_payment_history_all apha,
ap_vendors_v av
WHERE xvd.invoice_id = ai.invoice_id
AND apt.term_id = ai.terms_id
AND apt.term_id = atl.term_id
AND atl.sequence_num = 1
AND ai.terms_id = apt.term_id
AND ai.org_id = ood.organization_id
AND ai.created_by = fu.user_id
AND ai.invoice_id = xvdh.invoice_id
AND ai.invoice_id = xvd.invoice_id
AND alc.lookup_code = ai.invoice_type_lookup_code
AND ai.invoice_id = aipa.invoice_id
AND aipa.check_id = apha.check_id
AND aipa.invoice_payment_id = (SELECT MAX (invoice_payment_id)
FROM ap_invoice_payments_all a
WHERE a.invoice_id = ai.invoice_id)
group by a.transaction_type)
AND apha.accounting_event_id = (SELECT MAX (accounting_event_id)
FROM ap_payment_history_all aph1
WHERE aph1.check_id = apha.check_id)
AND ai.vendor_id = av.vendor_id
AND apha.transaction_type IN ('PAYMENT CREATED', 'PAYMENT UNCLEARIN\G')
and not exists ( select 1
from apps.ap_payment_history_all apha
,apps.ap_invoice_payments_all aipa
,apps.ap_invoices_all ai1
where aipa.check_id = apha.check_id
and ai1.invoice_id = aipa.invoice_id
and ai1.INVOICE_ID=ai.invoice_id
and apha.transaction_type IN ('PAYMENT CLEARING')
and apha.CREATION_DATE = ( select max(apha.CREATION_DATE)
from apps.ap_payment_history_all apha
,apps.ap_invoice_payments_all aipa
,apps.ap_invoices_all ai2
where aipa.check_id = apha.check_id
and ai2.invoice_id = aipa.invoice_id
and ai2.INVOICE_ID=ai.INVOICE_ID
)
) ---- newly added
AND ai.invoice_type_lookup_code = 'STANDARD'
AND alc.lookup_type = 'INVOICE TYPE'
AND alc.enabled_flag = 'Y'
AND ai.wfapproval_status IN ('WFAPPROVED', 'MANUALLY APPROVED')
AND xvdh.checked = 'Y'
AND xvd.status = 'Sent to Finance'
AND ai.cancelled_date IS NULL
--- AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE)
AND ai.invoice_amount =
NVL ((ai.amount_paid + NVL (ai.discount_amount_taken, 0)), 0)
-- AND ood.organization_name = :p_org_name
AND ood.organization_id = :p_org_id
- AND AI.INVOICE_NUM='A302689/EXM/OSR/03-2012'
AND ai.invoice_received_date
BETWEEN NVL (TRUNC (TO_DATE (:p_from_date, 'RRRR/MM/DD HH24:MI:SS')),
ai.invoice_received_date
)
AND NVL (TRUNC (TO_DATE (:p_to_date, 'RRRR/MM/DD HH24:MI:SS')),
SYSDATE
)
AND (fu.user_name LIKE '%VHD%' OR fu.description = 'VHD')