eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_FP_ADJUSTMENT_UTILS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 117


SELECT
B.VERSION_NUMBER,
B.VERSION_NAME,
B.PROJECT_ID,
DECODE(b.wp_version_flag,'Y',B.PROJECT_STRUCTURE_VERSION_ID,
       PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(b.project_id )),
B.FIN_PLAN_TYPE_ID,
B.VERSION_TYPE,
--B.RECORD_VERSION_NUMBER,
A1.NAME PLAN_TYPE_NAME,
B.WP_VERSION_FLAG,
C.FIN_PLAN_PREFERENCE_CODE PLAN_SETUP,
A.PLAN_CLASS_CODE,
C.MARGIN_DERIVED_FROM_CODE,
C.report_labor_hrs_from_code,
C.track_workplan_costs_flag
FROM
PA_FIN_PLAN_TYPES_B A,
PA_FIN_PLAN_TYPES_TL A1,
PA_BUDGET_VERSIONS B,
PA_PROJ_FP_OPTIONS C
WHERE
A.FIN_PLAN_TYPE_ID = B.FIN_PLAN_TYPE_ID
AND A.FIN_PLAN_TYPE_ID = A1.FIN_PLAN_TYPE_ID
AND B.PROJECT_ID = C.PROJECT_ID
AND B.FIN_PLAN_TYPE_ID = C.FIN_PLAN_TYPE_ID
AND B.BUDGET_VERSION_ID = C.FIN_PLAN_VERSION_ID
AND C.FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION'
AND B.BUDGET_VERSION_ID = p_budget_version_id
AND A1.Language = userenv('LANG');

Line: 149


SELECT project_currency_code, projfunc_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;

Line: 154


SELECT pe.name task_name, pe.element_number task_number
FROM pa_proj_elements pe
WHERE pe.proj_element_id = p_wbs_element_id;

Line: 159


SELECT pe.name task_name, pe.element_number task_number
FROM pa_proj_elements pe, Pa_resource_assignments ra
WHERE ra.task_id = pe.proj_element_id
and ra.resource_assignment_id = p_resource_assignment_id;

Line: 165


SELECT name.resource_name
FROM pa_rbs_elements element, pa_rbs_element_names_tl name
WHERE element.rbs_element_name_id = name.rbs_element_name_id
AND element.rbs_element_id = p_rbs_element_id
AND name.language=userenv('LANG');

Line: 172


SELECT object_type
FROM pa_proj_element_versions
WHERE proj_element_id = p_wbs_element_id
AND element_version_id = p_wbs_structure_version_id;

Line: 374


  ' SELECT BL.RESOURCE_ASSIGNMENT_ID, BL.TXN_CURRENCY_CODE, ' ||
  ' NVL(SUM(BL.RAW_COST),0) PFC_RAW_COST,' ||
  ' NVL(SUM(BL.BURDENED_COST),0) PFC_BURDENED_COST,' ||
  ' NVL(SUM(BL.PROJECT_RAW_COST),0) PC_RAW_COST, ' ||
  ' NVL(SUM(BL.PROJECT_BURDENED_COST),0) PC_BURDENED_COST, ' ;

Line: 452


    ' SELECT NVL(SUM(TOTAL_PLAN_RAW_COST),0) PFC_RAW_COST,' ||
    ' NVL(SUM(TOTAL_PLAN_BURDENED_COST),0) PFC_BURDENED_COST,' ||
    ' NVL(SUM(TOTAL_PROJECT_RAW_COST),0) PC_RAW_COST, ' ||
    ' NVL(SUM(TOTAL_PROJECT_BURDENED_COST),0) PC_BURDENED_COST, ' ;

Line: 492


    l_sql := 'SELECT ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0)),0) pfc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
    ' FROM ' ||
    ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
	' curr_record_type_id, plan_version_id ' ||
    ' FROM pji_fp_xbs_accum_f ' ||
    ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
    ' AND calendar_type = ''A'' ' ||
    ' AND prg_rollup_flag = ''N'' ' ||
	' AND project_id = :5 ' ||
    ' AND plan_version_id IN (:6, :7) ';

Line: 536


    l_sql := 'SELECT ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0),0)) pfc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
    ' FROM ' ||
    ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
	' curr_record_type_id, plan_version_id ' ||
    ' FROM pji_fp_xbs_accum_f ' ||
    ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
    ' AND calendar_type = ''A'' ' ||
    ' AND prg_rollup_flag = ''N'' ' ||
	' AND project_id = :5 ' ||
    ' AND plan_version_id IN (:6, :7) ';

Line: 582


    l_sql := 'SELECT ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, raw_cost, 0)),0) pfc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, brdn_cost, 0)),0) pfc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, raw_cost, 0)),0) pc_raw_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, brdn_cost, 0)),0) pc_burdened_cost, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 8)), 1, revenue, 0)),0) pc_revenue, ' ||
    ' NVL(SUM(DECODE(SIGN(bitand(curr_record_type_id, 4)), 1, revenue, 0)),0) pfc_revenue, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :1, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :2, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_equip_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :3, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, labor_hrs, 0), 0)),2),0) total_rev_labor_hours, ' ||
    ' NVL(ROUND(SUM(DECODE(plan_version_id, -1, 0, :4, DECODE(SIGN(bitand(curr_record_type_id,4)), 1, equipment_hours, 0), 0)),2),0) total_rev_equip_hours ' ||
    ' FROM ' ||
    ' ( SELECT raw_cost, brdn_cost, revenue, labor_hrs, equipment_hours, ' ||
	' curr_record_type_id, plan_version_id ' ||
    ' FROM pji_fp_xbs_accum_f ' ||
    ' WHERE bitand(curr_record_type_id, 12) > 0 ' ||
    ' AND calendar_type = ''A'' ' ||
    ' AND prg_rollup_flag = ''N'' ' ||
	' AND project_id = :5 ' ||
    ' AND plan_version_id IN (:6, :7) ';

Line: 697


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_xbs_denorm xbs, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and xbs.sub_emt_id = ra.task_id
and ra.budget_version_id = p_budget_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS');

Line: 709


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_xbs_denorm xbs, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and xbs.sub_emt_id = ra.task_id
and ra.budget_version_id = p_budget_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS')
and xbs.sub_level = xbs.sup_level;

Line: 721


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and ra.budget_version_id = p_budget_version_id;

Line: 728


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and ra.budget_version_id = p_budget_version_id
and ra.task_id = 0;

Line: 737


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and fp.fin_plan_version_id = ra.budget_version_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and ra.budget_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id;

Line: 750


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and fp.fin_plan_version_id = ra.budget_version_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and ra.budget_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and rbs.sub_level = rbs.sup_level;

Line: 765


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and xbs.sub_emt_id = ra.task_id
and xbs.sup_project_id = fp.project_id
and fp.fin_plan_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS');

Line: 783


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and xbs.sub_emt_id = ra.task_id
and xbs.sup_project_id = fp.project_id
and fp.fin_plan_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS')
and rbs.sub_level = rbs.sup_level;

Line: 802


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and xbs.sub_emt_id = ra.task_id
and xbs.sup_project_id = fp.project_id
and fp.fin_plan_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS')
and xbs.sub_level = xbs.sup_level;

Line: 821


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pji_xbs_denorm xbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and xbs.sub_emt_id = ra.task_id
and xbs.sup_project_id = fp.project_id
and fp.fin_plan_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and xbs.sup_emt_id = p_wbs_element_id
and xbs.struct_version_id = p_wbs_structure_version_id
and xbs.struct_type in ('WBS','XBS')
and xbs.sub_level = xbs.sup_level
and rbs.sub_level = rbs.sup_level;

Line: 840


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and fp.fin_plan_version_id = ra.budget_version_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and ra.budget_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and ra.task_id = 0;

Line: 854


Select distinct ra.resource_assignment_id, bl.TXN_CURRENCY_CODE
from pji_rbs_denorm rbs, pa_proj_fp_options fp, pa_resource_assignments ra, pa_budget_lines bl
where ra.resource_assignment_id = bl.resource_assignment_id
and fp.fin_plan_version_id = ra.budget_version_id
and rbs.struct_version_id = fp.rbs_version_id
and rbs.sub_id = ra.rbs_element_id
and ra.budget_version_id = p_budget_version_id
and rbs.sup_id = p_rbs_element_id
and rbs.struct_version_id = p_rbs_version_id
and rbs.sub_level = rbs.sup_level
and ra.task_id = 0;

Line: 867


SELECT object_type
FROM pa_proj_element_versions
WHERE proj_element_id = p_wbs_element_id
AND element_version_id = p_wbs_structure_version_id;

Line: 1604


       p_msg_name       => 'PA_NO_ACCESS_TO_UPDATE');

Line: 1618


           p_msg_name       => 'PA_NO_ACCESS_TO_UPDATE');

Line: 1665


             p_msg_name       => 'PA_UPDATE_PUB_VER_ERR');

Line: 1676


            p_msg_name       => 'PA_UPDATE_PUB_VER_ERR');

Line: 1733


       l_sql := ' select distinct resource_assignment_id, txn_currency_code ' ||
                ' from pa_budget_lines ' ||
                ' where resource_ASSIGNMENT_ID in (' || l_predicate1 || ')';

Line: 1822


        l_sql3 := ' select distinct pra.resource_assignment_id, bl.txn_currency_code ' ||
        ' from pa_resource_assignments pra, pa_budget_lines bl ' ||
        ' where pra.budget_version_id = :1 ' || /* to_char(l_target_budget_version_id) || */
        ' and   pra.resource_assignment_id = bl.resource_assignment_id ' ||
        ' and pra.parent_assignment_id in (' || l_predicate3 || ')';

Line: 1856


       UPDATE PA_BUDGET_VERSIONS SET version_name = p_new_version_name,
                                     description = p_new_version_desc
                               WHERE budget_version_id = l_target_budget_version_id;

Line: 1917


         l_sql2 := ' select distinct pra.resource_assignment_id, bl.txn_currency_code ' ||
         ' from pa_resource_assignments pra, pa_budget_lines bl ' ||
         ' where pra.budget_version_id = :1 ' || /* to_char(l_target_rev_version_id) || */
         ' and   pra.resource_assignment_id = bl.resource_assignment_id ' ||
       	 ' and pra.parent_assignment_id in (' || l_predicate2 || ')';

Line: 1952


       UPDATE PA_BUDGET_VERSIONS SET version_name = p_new_version_name,
                                     description = p_new_version_desc
                               WHERE budget_version_id = l_target_rev_version_id;