PA模块常用表

 

SELECT * FROM pa_projects_all; --项目

SELECT * FROM pa_project_types; --项目类型

SELECT * FROM pa_project_statuses; --项目状态

SELECT * FROM pa_project_options; --项目选项

SELECT * FROM pa_lookups l WHERE l.lookup_type = 'PA_OPTIONS' ;

SELECT * FROM pa_project_customers; --项目客户

SELECT h.name

  FROM pa_project_set_lines l, pa_project_sets_vl h

  WHERE l.project_set_id = h.project_set_id

   AND l.project_id = 155242 ; --项目集

 

--项目预算

SELECT t. budget_type, l.project_id, l.*, t.*

  FROM pa_budget_versions l, pa_budget_types t

  WHERE l.budget_type_code = t.budget_type_code

   AND l.budget_status_code = 'W' ; --活动

 

--预算状态

SELECT * FROM pa_lookups l WHERE l.lookup_type = 'BUDGET STATUS' ;

 

--项目角色（经理）

SELECT * FROM pa_project_role_types_vl;

 

SELECT l.resource_source_id, he.full_name

  FROM pa_project_parties l, pa_project_role_types_b t, hr_employees he

  WHERE l.project_role_id = t.project_role_id

   AND t.project_role_type = 'PROJECT MANAGER'

   AND l.resource_source_id = he.employee_id

   AND l.object_id = 155242 ;

 

SELECT * FROM pa_role_controls;

 

--人员默认账户

SELECT l.default_code_comb_id

-- INTO v_code_comb_id

  FROM per_all_assignments_f l

  WHERE l.person_id = 10995

   AND l.effective_start_date <= SYSDATE

   AND nvl (l.effective_end_date, SYSDATE + 1) >= SYSDATE ；

  

   ;

SELECT * FROM pa_expenditure_items_all  项目支出

select *from pa_cost_distribution_lines_all  支出分配行

SELECT l.project_id, h.project_set_id, h.name, l.project_set_id
  FROM pa_project_set_lines l, pa_project_sets_vl h
 WHERE l.project_set_id = h.project_set_id;   项目集

SELECT *FROM PA_PROJECT_TYPES_ALL 项目类别

SELECT l.task_id, l.task_number, l.task_name, l.*
  FROM pa_tasks l
 WHERE l.project_id = 157223; --项目任务

SELECT l.task_name,
       l.task_id,
       l.resource_class_code, --资源类别
       l.task_number,
       l.resource_alias,
       l.uom_code,
       l.uom_description,
       l.planned_cost, --计划成本
       l.*
  FROM pa_tasks_assigns_progress_v l
 WHERE l.project_id = 157223
   AND l.project_number = '1901140804'
   AND l.task_id = 246877; --计划资源

SELECT *FROM PA_RESOURCE_CLASSES_vl   资源类型

--取当前核准有用的收入预算版本
SELECT l.project_id,
       l.fin_plan_type_id,
       l.budget_version_id, --
       l.version_number, --版本号
       l.budget_status_code, --状态 B 核准， W 活动
       l.current_flag, --当前
       l.*
  FROM pa_budget_versions l
 WHERE l.project_id = 162229
   AND l.current_flag = 'Y' --当前的
   AND l.budget_status_code = 'B';

--取当前核准有用的收入预算金额
SELECT SUM(decode(bv.version_type,
                  'ORG_FORECAST',
                  nvl(revenue, 0),
                  nvl(bv.total_project_revenue, 0))) +
       SUM(nvl(bv.total_tp_revenue_in, 0)) +
       SUM(nvl(bv.total_borrowed_revenue, 0)) +
       SUM(nvl(bv.total_revenue_adj, 0)) -
       SUM(nvl(bv.total_tp_revenue_out, 0)) sss
  FROM pa_fin_plan_types_vl pt,
       pa_budget_versions   bv,
       pa_proj_fp_options   pfo
 WHERE pfo.project_id = bv.project_id(+)
   AND pfo.fin_plan_type_id = bv.fin_plan_type_id(+)
   AND pfo.fin_plan_type_id = pt.fin_plan_type_id
   AND pt.plan_class_code IN ('BUDGET', 'FORECAST')
   AND pfo.fin_plan_option_level_code = 'PLAN_TYPE'
   AND bv.ci_id IS NULL
   AND bv.current_flag(+) = 'Y'
   AND nvl(pt.use_for_workplan_flag, 'N') = 'N'
      
   AND bv.fin_plan_type_id = 10020 --FBS预算
   AND bv.project_id = 162229;

--计划类型
SELECT pt.fin_plan_type_id, pt.name, pt.description
  FROM pa_fin_plan_types_vl pt
 WHERE pt.plan_class_code IN ('BUDGET', 'FORECAST')
   AND nvl(pt.use_for_workplan_flag, 'N') = 'N'; --FBS预算   10020

本文来源 https://www.cnblogs.com/wanghang/p/6299229.html