This sql query will help to get the payment status of the requisition , PO , Invoice and its payment status
SELECT DISTINCT
prh.segment1 req_number,
prh.authorization_status req_status
ph.segment1 po_number
ai.invoice_num,
DECODE(
ai.payment_status_flag,
‘Y’,
‘Fully Paid’,
‘N’,
‘Not paid’,
‘P’,
‘Not paid’,
ai.payment_status_flag
) payment_status_flag,
apc.check_number payment_number,
apc.check_date payment_date
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_req_distributions_all prd,
apps.po_distributions_all pd,
apps.po_line_locations_all pll,
apps.po_lines_all pl,
apps.po_headers_all ph,
apps.ap_invoices_all ai,
apps.ap_payment_schedules_all aps,
apps.ap_invoice_lines_all ail,
apps.ap_checks_all apc,
apps.ap_invoice_payments_all aip
WHERE
prh.requisition_header_id = prl.requisition_header_id
AND
prh.org_id = prl.org_id
AND
prl.requisition_line_id = prd.requisition_line_id
AND
prl.org_id = prd.org_id
AND
prd.distribution_id = pd.req_distribution_id (+)
AND
prd.org_id = pd.org_id (+)
AND
pd.line_location_id = pll.line_location_id (+)
AND
pd.org_id = pll.org_id (+)
AND
pll.po_line_id = pl.po_line_id (+)
AND
pll.org_id = pl.org_id (+)
AND
pl.po_header_id = ph.po_header_id (+)
AND
pl.org_id = ph.org_id (+)
AND
ph.po_header_id = ail.po_header_id (+)
AND
ail.invoice_id = ai.invoice_id (+)
AND
ai.invoice_id = aps.invoice_id (+)
AND
ail.invoice_id = aip.invoice_id (+)
AND
aip.check_id = apc.check_id (+)
–and prh.created_by in (
–select user_id from apps.fnd_user
–where employee_id in (
–select person_id from apps.per_all_people_f
–where email_address = ‘pedro.medeiros@test.com.br’
–)
–)
AND
trunc(prh.creation_date) >= ’01-JAN-2020′
