Introduction:
This blog has the script to get the Non po invoice details in Oracle Cloud application for Audit purpose.
Cause of the issue:
Auditor wanted to see the Non-PO invoice details.
How do we solve:
Below script will identity Non-po invoices.
SELECT
nvl((
SELECT
psv.segment1
FROM
poz_suppliers_v psv
WHERE
1 = 1
AND psv.vendor_id = pssa.vendor_id
AND aia.vendor_id = psv.vendor_id
),
(
SELECT
party_number
FROM
hz_parties
WHERE
party_id = aia.party_id
)) “VENDOR_NUMBER”,
nvl((
SELECT
psv.vendor_name
FROM
poz_suppliers_v psv
WHERE
1 = 1
AND psv.vendor_id = pssa.vendor_id
AND aia.vendor_id = psv.vendor_id
),
(
SELECT
party_name
FROM
hz_parties
WHERE
party_id = aia.party_id
)) vendor_name,
(
SELECT
ppa.segment1
FROM
pjf_projects_all_b ppa,
po_distributions_all pda
WHERE
pda.pjc_project_id = ppa.project_id
AND aida.po_distribution_id = pda.po_distribution_id
) project_number,
(
SELECT
ptv.task_name
FROM
pjf_tasks_v ptv,
po_distributions_all pda
WHERE
ptv.task_id = pda.pjc_task_id
AND aida.po_distribution_id = pda.po_distribution_id
) task_name,
aia.invoice_num,
to_char(aia.invoice_date, ‘DD-MON-YY’, ‘NLS_DATE_LANGUAGE=ENGLISH’) invoice_date,
to_char(aipa.accounting_date, ‘DD-MON-YY’, ‘NLS_DATE_LANGUAGE=ENGLISH’) accounting_date,
aila.line_type_lookup_code,
aia.invoice_amount,
aia.payment_method_code,
aia.source,
— PSV.attribute3 PO_VENDOR_ONLY,
(
SELECT
ppnf.full_name
FROM
ap_inv_aprvl_hist_all t,
per_email_addresses pea,
per_person_names_f ppnf
WHERE
t.last_update_date = (
SELECT
MAX(x.last_update_date)
FROM
ap_inv_aprvl_hist_all x
WHERE
x.invoice_id = t.invoice_id
AND x.response = ‘ORA_ASSIGNED TO’
)
AND ppnf.name_type = ‘GLOBAL’
AND trunc(sysdate) BETWEEN trunc(ppnf.effective_start_date) AND trunc(ppnf.effective_end_date)
AND t.invoice_id = aia.invoice_id
AND t.approver_id = ( substr(pea.email_address, 1, instr(pea.email_address, ‘.com’, – 1) + 3) )
AND ppnf.person_id = pea.person_id
AND ROWNUM = 1
) approver_name,
(
SELECT
psv.attribute3
FROM
poz_suppliers_v psv
WHERE
1 = 1
AND psv.vendor_id = pssa.vendor_id
AND aia.vendor_id = psv.vendor_id
) po_vendor_only,
gcc.segment1
|| ‘.’
|| gcc.segment2
|| ‘.’
|| gcc.segment3
|| ‘.’
|| gcc.segment4
|| ‘.’
|| gcc.segment5
|| ‘.’
|| gcc.segment6
|| ‘.’
|| gcc.segment7
|| ‘.’
|| gcc.segment8
|| ‘.’
|| gcc.segment9 inv_gl_account,
gcc.segment5 natural_account,
(
SELECT DISTINCT
name
FROM
hr_operating_units
WHERE
organization_id = aia.org_id
) bu_name
FROM –POZ_SUPPLIERS_V PSV,
poz_supplier_sites_all_m pssa,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_invoice_payments_all aipa,
gl_code_combinations gcc
WHERE
1 = 1
AND aia.vendor_site_id = pssa.vendor_site_id (+)
AND aia.invoice_id = aila.invoice_id
AND aida.invoice_id = aia.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aia.invoice_id = aipa.invoice_id (+)
AND aia.org_id = aipa.org_id (+)
AND aila.line_number = aida.invoice_line_number
AND gcc.code_combination_id = aida.dist_code_combination_id
AND aia.org_id IN ( :bu_id )
AND aia.invoice_date BETWEEN :p_date_from AND :po_date_to
AND NOT EXISTS (
SELECT
1
FROM
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda
WHERE
pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pda.po_header_id
AND pla.po_line_id = pda.po_line_id
AND aida.po_distribution_id = pda.po_distribution_id
)
