SQL used to find out which Purchase Order a requisition has been AutoCreated into

SELECT DISTINCT prha.creation_date

, prha.segment1 req_no
, prla.line_num req_line_number
, prla.item_description
, papf2.full_name req_prepaper
FROM po.poR_requisition_headers_all prha
, hr.per_all_people_f papf2
, po.poR_requisition_lines_all prla
, po.po_line_locations_all plla
, po.po_lines_all pla
, po.po_headers_all pha
WHERE prha.preparer_id = papf2.person_id
AND prha.requisition_header_id = prla.requisition_header_id
AND prla.line_location_id = plla.line_location_id(+)
AND plla.po_line_id = pla.po_line_id(+)
AND pla.po_header_id = pha.po_header_id(+)
AND prha.authorization_status = 'APPROVED'
AND prla.closed_code IS NULL
AND ( prla.cancel_flag = 'N'
OR prla.cancel_flag IS NULL)
ORDER BY 1 DESC;

select interface_source_code, last_update_date, last_updated_by, segment1

PO_NUM, creation_date, created_by, group_requisitions from PO_headers_all
--where interface_source_code is not null
order by creation_date desc

--select * FROM po_headers_all

--where purchase_date between to_date('2011-02-03','YYYY-MM-DD') AND
to_date( '2011-03-03','YYYY-MM-DD')

PO created daily:
select
segment1 PO_NUM,
creation_date,
created_by,
last_update_date,
last_updated_by
from PO_headers_all
where creation_date >= sysdate -5
order by creation_date desc

--select * FROM po_headers_all

--where purchase_date between to_date('2011-02-03','YYYY-MM-DD') AND
to_date( '2011-03-03','YYYY-MM-DD')

REQ created daily:

select
requisition_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
Document_status,
active_requisition_flag,
submission_date,
approved_date,
source_application_code,
lifecycle_status
from POR_REQUISITION_HEADERS_ALL
--where creation_date >= sysdate -15
order by creation_date desc

Invoices created daily:

select
invoice_num,
to_char(CREATION_Date, 'MM/DD/YYYY') "creation_Date",
created_by,
source,
invoice_type_lookup_code,
last_update_date,
last_updated_by,
invoice_amount,
invoice_date,
approval_status
from ap_invoices_all
--where creation_date >= sysdate -15
--where source not in ('Manual Invoice
Entry','CLAIMS','EMP_EXPENSE_REPORT','CC_EXPENSE_REPORT','REFUND
CLAIMS','IMAGE','INVOICE GATEWAY','RECURRING INVOICE','DYNAMICS SL')
order by creation_date desc

Manual Invoice Entry

CLAIMS
EMP_EXPENSE_REPORT
CC_EXPENSE_REPORT
REFUND CLAIMS
IMAGE
INVOICE GATEWAY
RECURRING INVOICE
DYNAMICS SL

workflow query- pick process name:

select count(*)
to_char(CREATedDate, 'MM/DD/YYYY') "creation_Date",
COMPOSITECREATEDTIME,
UPDATEDBYDISPLAYNAME,
UPDATEDDATE,
TITLE,
PROCESSNAME,
COMPONENTNAME,
ACQUIREDBY,
ASSIGNEESDISPLAYNAME,
CREATOR,
EXPIRATIONDATE,
APPROVERS,
ASSIGNEDDATE,
ELAPSEDTIME,
ENDDATE,
FROMUSERDISPLAYNAME,
ORIGINALASSIGNEEUSER,
OUTCOME,
STAGE,
ASSIGNMENTCONTEXT
from fa_fusion_soainfra.wftask
where createddate >= sysdate -1
order by createddate desc

/*
where processname not in
('ApprovePurchasingDocument','InvoiceApprovalProcess','ApproveReq','HoldsApprovalPr
ocess','AssetTransactionsApprovalProcess','ExpenseApprovalProcess','ProjectStatusAp
proval','JournalApprovalProcess','RoleProvisionNotificationFlow','ReportNotificatio
nBpelFlow')
*/

POVSS0000008

OBN id pwd:
innovageobn
Nimshi2024$

FIND VALIDATED INVOICES r12 query:

SELECT
V.VENDOR_ID,
s.segment1 VENDOR_NUM,
--S.VENDOR_NAME,
SS.VENDOR_SITE_CODE SITE_NAME,
v.INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
DECODE (ap_invoices_utility_pkg.get_approval_status(v.invoice_id, v.invoice_amount,
v.payment_status_flag, v.invoice_type_lookup_code)
,'FULL', 'Fully Applied'
,'UNAPPROVED', 'Unvalidated'
,'NEEDS REAPPROVAL', 'Needs Revalidation'
,'APPROVED', 'Validated'
,'NEVER APPROVED', 'Never Validated'
,'CANCELLED', 'Cancelled'
,'UNPAID', 'Unpaid'
,'AVAILABLE', 'Available'
)
invoice_check_status,
invoice_date,
gl_date,
payment_status_flag
from
ap_invoices_all v,
poz_suppliers s,
poz_supplier_sites_all_m ss,
ap_lookup_codes alc
where 1=1
and v.vendor_id = ss.vendor_id
and v.vendor_id = s.vendor_id
and v.vendor_site_id = ss.vendor_site_id
------------------------------------------------------------------------
In BI run the below query:
Select * from ap_invoices_all

1.The Approval_status field refers to invoice validation status.

APPROVED - Validated
NEVER APPROVED - Not Validated
NEEDS REAPPROVAL - Needs revalidation
incomplete -
2.For invoice approval status check for the field WFAPPROVAL_STATUS
INITIATED
REJECTED
WORKFLOW APPROVED

select * from ap_invoices_all

where creation_date >= sysdate -15
and approval_status =
'APPROVED' --Validated
--NEVER APPROVED - Not Validated
--NEEDS REAPPROVAL - Needs revalidation
order by creation_date desc

PO MATCH INVOICES:
select distinct
aps.vendor_name,
poh.segment1,
--pol.LINE_NUM,
--pol.ITEM_DESCRIPTION,
--pol.QUANTITY,
--(pol.QUANTITY * pol.unit_price) PO_amount,
--pll.QUANTITY_BILLED,
aia.invoice_date,
aia.invoice_num,
--aia.INVOICE_AMOUNT,
--aca.CHECK_NUMBER,
--aca.check_date,
--aca.AMOUNT
from
ap_invoices_all aia,
ap_invoice_lines_all ail,
POZ_suppliers_V aps,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
ap_invoice_distributions_all aid,
ap_checks_all aca,
ap_invoice_payments_all aip
where aia.invoice_id = ail.invoice_id
and aia.vendor_id = aps.vendor_id
and aia.INVOICE_TYPE_LOOKUP_CODE IN( 'STANDARD','CREDIT')
and ail.PO_HEADER_ID = poh.po_header_id
--and ail.po_line_id = pol.po_line_id
and poh.po_header_id = pol.po_header_id
and poh.vendor_id = aps.vendor_id
and poh.po_header_id = pll.po_header_id
and pol.po_line_id = pll.po_line_id
and poh.po_header_id = pod.po_header_id
and poh.vendor_id = aps.vendor_id
and aid.po_distribution_id = pod.PO_DISTRIBUTION_ID
and aca.check_id = aip.check_id
and aip.invoice_id = aia.invoice_id
--and aia.invoice_date between :from_date and :to_date
--and poh.approved_date between :from_date and :to_date
order by aia.invoice_num,poh.segment1,pol.LINE_NUM
-----------------------------------------------------------------------------------
------------------------------------
PO invoices processed daily:

SELECT DISTINCT
aia.invoice_num,
poh.segment1,
aia.creation_date
FROM
ap_invoices_all aia
JOIN
ap_invoice_lines_all ail ON aia.invoice_id = ail.invoice_id
JOIN
po_headers_all poh ON ail.PO_HEADER_ID = poh.po_header_id
WHERE
aia.approval_status = 'APPROVED'
AND aia.creation_date >= SYSDATE - 90
-----------------------------------------------------------------------------------
-------------------------------

NON-PO invoices:

SELECT aia.invoice_num, aia.invoice_date, aia.invoice_amount,aia.creation_date,

aia.source
FROM ap_invoices_all aia
WHERE NOT EXISTS (
SELECT 1
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND (aila.po_header_id IS NOT NULL OR aila.po_line_id IS NOT NULL)
)
and aia.approval_status = 'APPROVED'
AND aia.creation_date >= SYSDATE - 20
order by aia.creation_date desc
-----------------------------------------------------------------------------------
------------------------------
All Payments Completed:

select
--IBY_PAY_SERVICE_REQUESTS
PAY_ADMIN_ASSIGNED_REF_CODE,
PAYMENT_INSTRUCTION_ID,
INTERNAL_BANK_ACCOUNT_ID,
PAYMENT_INSTRUCTION_STATUS,
PAYMENTS_COMPLETE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
from IBY_PAY_INSTRUCTIONS_ALL
WHERE PAYMENT_INSTRUCTION_STATUS != 'TERMINATED'
AND creation_date >= SYSDATE - 30
--------------------------------------------------------------
select *
from IBY_PAY_SERVICE_REQUESTS
/*
PAY_ADMIN_ASSIGNED_REF_CODE,
PAYMENT_INSTRUCTION_ID,
INTERNAL_BANK_ACCOUNT_ID,
PAYMENT_INSTRUCTION_STATUS,
PAYMENTS_COMPLETE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
*/
--from IBY_PAY_INSTRUCTIONS_ALL
--WHERE PAYMENT_INSTRUCTION_STATUS != 'TERMINATED'
--AND creation_date >= SYSDATE - 30
-------------------------------------------------------------------
business units table

select
NAME, organization_id
from HR_ORGANIZATION_UNITS_F_TL
where name like '%CLAIM%'
-------------------------------------------------------------------------------

finscm --> j16Xw1nT2@O4

IT_NONPROD_SCM --> DevSetCalculate96!

dev11-BIPC006180

document number = BIPC010167 , dev11-BIPC006179

sold-to LE =
change order number
document header id = 300001497163395
is document in draft status = Y
language (internal) = US
PDF Mode = Y

Brinker International Payroll Company LP

https://fa-etjg-saasfaprod1.fa.ocs.oraclecloud.com/fscmRestApi/resources/
11.13.18.05/purchaseOrders/300001497163395/action/communicate

POSOL0000072
TOTAL LONGTERM CARE SOLUTIONS, LLC

AMOUNT
CREATION_DATE
ORG_ID
inv_org_id

org_id
payments_complete_flag
payment_status =
REMOVED
FORMATTED
REMOVED_REQUEST_TERMINATED
REJECTED
REMOVED_INSTRUCTION_TERMINATED
VOID
PENDING_ACK
ACKNOWLEDGED
ISSUED

PAYMENTS_COMPLETE_FLAG =Y, STATUS FORMATTED, VOID, PENDING_ACK, ISSUED,

ACKNOWLEDGED
select * FROM
iby_payments_all
WHERE PAYMENTS_COMPLETE_FLAG = 'Y'

invoice# invoice_num
amount invoice_amount`
supplier name, number
po#
validation status

/*
select
--aps.vendor_name,
--poh.segment1,
--aia.invoice_date,
aia.invoice_num,
aia.INVOICE_AMOUNT
from
ap_invoices_all aia,
POZ_suppliers_V aps,
po_headers_all poh

where
aia.vendor_id = aps.vendor_id
and aia.INVOICE_TYPE_LOOKUP_CODE IN( 'STANDARD','CREDIT')
and poh.vendor_id = aps.vendor_id
and poh.vendor_id = aps.vendor_id
and poh.segment1 IS NULL or IS NOT NULL
*/

SELECT
inv.INVOICE_NUM, -- Invoice number
inv.INVOICE_AMOUNT, -- Invoice amount
supp.SUPPLIER_NAME, -- Supplier name
supp.SUPPLIER_NUM, -- Supplier number
po.PO_NUMBER, -- Purchase order number
inv.VALIDATION_STATUS -- Invoice validation status
FROM
AP_INVOICES_ALL inv
JOIN
AP_SUPPLIERS supp ON inv.SUPPLIER_ID = supp.SUPPLIER_ID
LEFT JOIN
PO_HEADERS_ALL po_header ON inv.PO_HEADER_ID = po_header.PO_HEADER_ID
LEFT JOIN
PO_LINES_ALL po_line ON inv.PO_LINE_ID = po_line.PO_LINE_ID
WHERE
inv.VALIDATION_STATUS IS NOT NULL -- Filter for valid invoices
ORDER BY
inv.INVOICE_NUM;
------------------------------------------------------------------------------
select COUNT(*)
from EXM_EXPENSES
WHERE CARD_ID IS NOT NULL

----------------------------------------------------------------
FIND REQUISITION BU:
select RHA.REQUISITION_NUMBER, BU.NAME from
POR_REQUISITION_HEADERS_ALL RHA,
HR_ORGANIZATION_UNITS_F_TL BU
where RHA.REQ_BU_ID = BU.ORGANIZATION_ID
AND RHA.REQ_BU_ID = '300000004336278'
--AND requisition_number = 'REQ0000007'

select invoice_num, invoice_amount, last_update_date, creation_date,

approval_status
from ap_invoices_all
where
--aia.org_id = bu.organization_id
--and bu.name like '%CLAIM%'
approval_status <> 'APPROVED'
AND CREATION_DATE <> LAST_UPDATE_DATE
AND CREATION_DATE >= SYSDATE-4
order by creation_date asc

--where creation_date >= sysdate -1