Apps2CloudFusion: 2026

BIP.Req.	15th July 2026	Goods Receipt Automation Report
	Purpose:
		Write SQL query is designed for BIP report "to identify Purchase Order (PO) lines of type "Goods" that have been ordered but have not yet been received" (that means they are missing a Goods Receipt).
	Why	In AP automation(through Email Bursting: GoodsAutoEmail Bursting Report), BIP report is typically used to drive a workflow or notification system. It identifies the exact requesters (and their email addresses) who need to go into the system and perform a "Receive" action so that Accounts Payable can complete the 3-way match and pay the supplier.

	Domain	Oracle Fusion Finanicals
	MODULE	Account Payable
		Columns/Attributes Required
		REQUESTER_ID
		REQUISITION_NUMBER
		PURCHASE_ORDER
		ITEM_NUMBER
		ITEM_DESCRIPTION
		QUANTITY_ORDERED
		NEED_BY_DATE
		DELIVER_TO_LOCATION
		SUPPLIER_NAME
		SUPPLIER_SITE
		REQUESTER
		EMAIL_ADDRESS
		PO Line Type
		TRANSACTION_TYPE

About the EXISTS Operator in Oracle SQL

*EXISTS*
	EXISTS	Both can be used in the WHERE clause
	NOT EXISTS	Both can be used in the WHERE clause
		Both operators are used to determine whether a record exists in the table.
		EXISTS Returns TRUE/FALSE
		If the Sub-query returns at least one row	TRUE
		If the Sub-query returns 0 rows	FALSE

		NOT EXISTS Returns TRUE/FALSE
		If the Sub-query returns 0 rows	TRUE
		If the Sub-query returns at least one row	FALSE

Note:

Ø Oracle recommends the EXISTS operator than IN operator because it gives good
performance than IN operator

Ø Oracle recommends the NOT EXISTS operator than NOT IN operator because it gives good
performance than NOT IN operator

Examples

Q: Display the departments which are not empty?

SELECT *
FROM DEPT D
WHERE EXISTS (SELECT * FROM EMP WHERE DEPTNO = D.DEPTNO);-- SBQ should return TRUE

Result:-

Since Sub-Query(SBQ) returns FALSE instead of TRUE, the last record will not be fetched.

Using IN Operator

SELECT *
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP);

Result:-

Q: Display departments that are empty?

SELECT *
FROM DEPT D
WHERE NOT EXISTS (SELECT * FROM EMP WHERE DEPTNO = D.DEPTNO); --SBQ returns 0 rows

Result:-

Q: Find all employees who belong to a valid department that exists in the system.

select *from emp e
where exists(select 1 from dept d where e.deptno=d.deptno);

Result:-

Q:Find all employees whose department number does not exist in the master department table.

select *from emp e
where not exists(select 1 from dept d where e.deptno=d.deptno);

Result: No Data

Q: Find all departments that currently have zero employees assigned to them.

select *from dept d
where not exists(select 1 from emp e where e.deptno=d.deptno);

DEPTNO	DNAME	LOC
40	OPERATIONS	BOSTON

Q: Explain the difference between IN and EXISTS Operators?

IN Operator	Vs	EXISTS Operator
Scans the inner subquery first, creates an in-memory list, and checks the outer table.		Scans the outer table first, and runs the subquery row-by-row until it finds a single match.
Requires a specific column (Ex: SELECT deptno).		Ignores the select list; typically uses a literal constant (Ex:SELECT 1).
Fails completely with NOT IN if the subquery returns even one NULL.		Works flawlessly with NOT EXISTS regardless of NULL values.
Small, static lists of literal values or small datasets.		Large datasets where the inner table contains many records.

Q: Explain the difference between EXISTS and NOT EXISTS?

EXISTS	Vs	NOT EXISTS
Semi-Join (Positive Check).		Anti-Join (Negative Check).
Subquery returns at least one row.		Subquery returns zero rows.
Stops scanning the instant it finds the first match (Early Exit).		Must scan until it finds a match, or scan the entire dataset to confirm zero matches exist.
Finding parents with children (Ex:-Departments with employees).		Finding orphans or missing links (Ex:-Departments with no employees).

Apps2CloudFusion