SQL/Tables

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 […]

SQL to fetch Budget Numbers (very useful in saving time) SELECT ppa.segment1 “Proj Num” ,ppa.name “Proj Name” ,ppa.description “Proj Desc” ,ppa.project_type “Proj Type” ,pt.task_number “Task Num” ,( SELECT name FROM pa_proj_elements WHERE proj_element_id = task_id AND object_type = ‘PA_TASKS’ ) “Task Name” ,pt.description “Task Desc” ,pt.long_task_name ,( SELECT sum(bl.burdened_cost) FROM pa_budget_lines bl ,pa_resource_assignments ra ,pa_budget_versions

When working with large implementations, the below sql/s come in handy to view information quickly Query to get Org Hierarchy Information :- select (select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.PARENT_ORGANIZATION_ID) Parent, (select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.CHILD_ORGANIZATION_ID) Child, (select name from HR_ALL_ORGANIZATION_UNITS where ORGANIZATION_ID = pahd.ORG_ID) OU , pd.* from apps.pa_org_hierarchy_denorm pahd