EBS SQL : Projects & Sales Orders

If you ever are in a situation where you need to let the business know of scenarios where more than one Sales Orders are attached to the same Project, (or vice versa) – please use the below SQL/s.

Version 1 : All Projects that have more than one Sales Orders

SELECT (SELECT segment1 FROM pa_projects_all WHERE project_id = oel.project_id ) ProjNum
,count(DISTINCT header_id) Count_of_Distinct_SOs
,(SELECT name FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = oel.org_id ) BusinessUnit
FROM oe_order_lines_all oel
WHERE oel.project_id IS NOT NULL
AND oel.flow_status_code <> ‘CANCELLED’
AND oel.header_id IN (SELECT header_id FROM oe_order_headers_all WHERE flow_status_code <> ‘CANCELLED’ )
AND oel.project_id IN (SELECT project_id FROM pa_projects_all ppa ,pa_project_types_all ppt WHERE ppa.project_type = ppt.project_type
AND ppt.project_type_class_code = ‘CONTRACT’ AND ppa.PROJECT_STATUS_CODE = ‘1000’ )
GROUP BY oel.project_id,oel.org_id
HAVING count(DISTINCT oel.header_id) > 1
ORDER BY 2 DESC;

Version 2: All Sales Orders which are associated to more than one distinct Projects

SELECT (SELECT order_number FROM oe_order_headers_all WHERE header_id = oel.header_id ) SO_Number
,count(DISTINCT project_id) Count_of_Distinct_Projects
,(SELECT name FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = oel.org_id ) Business_Unit
FROM oe_order_lines_all oel
WHERE oel.project_id IS NOT NULL
AND oel.flow_status_code <> ‘CANCELLED’
AND oel.header_id IN (SELECT header_id FROM oe_order_headers_all WHERE flow_status_code <> ‘CANCELLED’ )
AND oel.project_id IN (SELECT project_id FROM pa_projects_all ppa ,pa_project_types_all ppt WHERE ppa.project_type = ppt.project_type
AND ppt.project_type_class_code = ‘CONTRACT’ AND ppa.PROJECT_STATUS_CODE = ‘1000’)
GROUP BY oel.header_id,oel.org_id
HAVING count(DISTINCT oel.project_id) > 1
ORDER BY 2 DESC;

Version 3 : a fancy version with LISTAGG function which comma separates multiple values into one cell (had to do some research, but got it working. YaY!)

SELECT ppa.segment1 “Project Number”

,ppa.name “Project Name”

,ppa.project_type “Project Type”

,pcv.customer_name “Customer Name”

,(SELECT project_status_name FROM pa_project_statuses WHERE project_status_code = ppa.project_status_code) “Project Status”

,LISTAGG(oeh.ORDER_NUMBER, ‘ ,’) WITHIN GROUP (ORDER BY oeh.ORDER_NUMBER) “Sales Order Numbers”

FROM pa_projects_all ppa

,oe_order_headers_all oeh

,pa_project_customers ppc

,apps.pa_customers_v pcv

WHERE 1 = 1

AND ppa.project_id = ppc.project_id

AND ppc.customer_id = pcv.customer_id

AND ppa.template_flag = ‘N’

AND oeh.header_id IN (SELECT DISTINCT header_id FROM oe_order_lines_all WHERE project_id = ppa.project_id)

GROUP BY ppa.segment1, ppa.name, ppa.project_type, pcv.customer_name, ppa.project_status_code, ppa.project_id;

PS: Subquery is just my personal choice & comfort area. You can denormalize as you please.