VIEW: APPS.HRFV_WORKFORCE_BUDGETS

Source

View Text - Preformatted

SELECT bgrt.NAME BUSINESS_GROUP_NAME, bgt.BUDGET_NAME BUDGET_NAME, bgt.PERIOD_SET_NAME BUDGET_CALENDAR_NAME, pc.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY, bgt.BUDGET_START_DATE BUDGET_START_DATE, bgt.BUDGET_END_DATE BUDGET_END_DATE, hr_bis.bis_decode_lookup('PQH_BUDGET_ENTITY' , bgt.budgeted_entity_cd) BUDGETED_ENTITY, bgt.budgeted_entity_cd BUDGETED_ENTITY_CD, hr_bis.bis_decode_lookup('PQH_BUDGET_STYLE' , bgt.budget_style_cd) BUDGET_STYLE, bgt.budget_style_cd BUDGET_STYLE_CD, os.NAME ORGANIZATION_HIERARCHY_NAME, ov.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION, HR_GENERAL.DECODE_ORGANIZATION(bgt.start_organization_id) STARTING_ORGANIZATION_NAME, pst.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE, pst.SYSTEM_TYPE_CD BUDGET_UNIT_CD, hr_bis.bis_decode_lookup('PQH_BGT_UOM_AGGREGATE' , bgt.budget_unit1_aggregate) BUDGET_UNIT_AGGREGATE_DESC, bgt.BUDGET_UNIT1_AGGREGATE BUDGET_UNIT_AGGREGATE, bvr.VERSION_NUMBER BUDGET_VERSION_NUMBER, bvr.DATE_FROM BUDGET_VERSION_START_DATE, bvr.DATE_TO BUDGET_VERSION_END_DATE, bdt.BUDGET_UNIT1_VALUE BUDGET_DETAIL_BUDGETED_VALUE, orgT.name ORGANIZATION_NAME, jbt.name JOB_NAME, pft.name POSITION_NAME, gdt.name GRADE_NAME, ptps.start_date||' - '||ptpe.end_date BUDGET_PERIOD_NAME, ptps.start_date BUDGET_PERIOD_START_DATE, ptpe.end_date BUDGET_PERIOD_END_DATE, bpr.BUDGET_UNIT1_VALUE BUDGET_PERIOD_BUDGETED_VALUE, hr_discoverer.get_actual_budget_values( pst.SYSTEM_TYPE_CD , bgt.business_group_id , orgT.organization_id , jbt.job_id , pft.position_id , gdt.grade_id , ptps.start_date , ptpe.end_date , bdt.BUDGET_UNIT1_VALUE) BUDGET_PERIOD_ACTUAL_VALUE, hr_disc_calculations.get_ff_actual_value_pqh( bgt.budget_id , bgt.business_group_id , gdt.grade_id , jbt.job_id , orgT.organization_id , bdt.position_id , bpr.budget_period_id , pst.system_type_cd) CALC_PERIOD_ACTUAL_VAULE, bgt.BUDGET_ID BUDGET_ID, bgt.BUDGET_UNIT1_ID BUDGET_UNIT_ID, bgt.BUSINESS_GROUP_ID BUSINESS_GROUP_ID, bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID, bdt.BUDGET_DETAIL_ID BUDGET_DETAIL_ID, pft.POSITION_ID POSITION_ID, bpr.BUDGET_PERIOD_ID BUDGET_PERIOD_ID, gdt.grade_id GRADE_ID, jbt.job_id JOB_ID, orgT.organization_id ORGANIZATION_ID FROM pqh_budgets bgt, per_shared_types_vl pst, hr_all_organization_units_tl bgrT, hr_all_organization_units_tl orgT, per_jobs_tl jbt, per_grades_tl gdt, pqh_budget_versions bvr, pqh_budget_details bdt, hr_all_positions_f_tl pft, pqh_budget_periods bpr, per_time_periods ptps, per_time_periods ptpe, per_organization_structures os, per_org_structure_versions ov, pay_calendars pc WHERE bgt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND bgt.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, bgt.business_group_id ) AND bgt.budget_unit1_id = pst.shared_type_id AND bgt.budget_id = bvr.budget_id AND bvr.budget_unit1_value is not null AND bvr.budget_version_id = bdt.budget_version_id AND bdt.budget_detail_id = bpr.budget_detail_id AND ptps.time_period_id = bpr.start_time_period_id AND ptpe.time_period_id = bpr.end_time_period_id AND bdt.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND bdt.job_id = jbt.job_id (+) AND jbt.language (+) = userenv('LANG') AND bdt.position_id = pft.position_id (+) AND pft.language (+) = userenv('LANG') AND bdt.grade_id = gdt.grade_id (+) AND gdt.language (+) = userenv('LANG') AND os.organization_structure_id (+) = ov.organization_structure_id AND ov.org_structure_version_id(+) = bgt.org_structure_version_id AND bgt.period_set_name = pc.period_set_name UNION ALL SELECT bgrT.name BUSINESS_GROUP_NAME, bgt.BUDGET_NAME BUDGET_NAME, bgt.PERIOD_SET_NAME BUDGET_CALENDAR_NAME, pc.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY, bgt.BUDGET_START_DATE BUDGET_START_DATE, bgt.BUDGET_END_DATE BUDGET_END_DATE, hr_bis.bis_decode_lookup('PQH_BUDGET_ENTITY' , bgt.budgeted_entity_cd) BUDGETED_ENTITY, bgt.budgeted_entity_cd BUDGETED_ENTITY_CD, hr_bis.bis_decode_lookup('PQH_BUDGET_STYLE' , bgt.budget_style_cd) BUDGET_STYLE, bgt.budget_style_cd BUDGET_STYLE_CD, os.NAME ORGANIZATION_HIERARCHY_NAME, ov.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION, HR_GENERAL.DECODE_ORGANIZATION(bgt.start_organization_id) STARTING_ORGANIZATION_NAME, pst.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE, pst.SYSTEM_TYPE_CD BUDGET_UNIT_CD, hr_bis.bis_decode_lookup('PQH_BGT_UOM_AGGREGATE' , bgt.budget_unit2_aggregate) BUDGET_UNIT_AGGREGATE_DESC, bgt.BUDGET_UNIT2_AGGREGATE BUDGET_UNIT_AGGREGATE, bvr.VERSION_NUMBER BUDGET_VERSION_NUMBER, bvr.DATE_FROM BUDGET_VERSION_START_DATE, bvr.DATE_TO BUDGET_VERSION_END_DATE, bdt.BUDGET_UNIT2_VALUE BUDGET_DETAIL_BUDGETED_VALUE, orgT.name ORGANIZATION_NAME, jbt.name JOB_NAME, pft.name POSITION_NAME, gdt.name GRADE_NAME, ptps.start_date||' - '||ptpe.end_date BUDGET_PERIOD_NAME, ptps.start_date BUDGET_PERIOD_START_DATE, ptpe.end_date BUDGET_PERIOD_END_DATE, bpr.BUDGET_UNIT2_VALUE BUDGET_PERIOD_BUDGETED_VALUE, hr_discoverer.get_actual_budget_values( pst.SYSTEM_TYPE_CD , bgt.business_group_id , orgT.organization_id , jbt.job_id , pft.position_id , gdt.grade_id , ptps.start_date , ptpe.end_date , bdt.BUDGET_UNIT2_VALUE) BUDGET_PERIOD_ACTUAL_VALUE, hr_disc_calculations.get_ff_actual_value_pqh( bgt.budget_id , bgt.business_group_id , gdt.grade_id , jbt.job_id , orgT.organization_id , bdt.position_id , bpr.budget_period_id , pst.system_type_cd) CALC_PERIOD_ACTUAL_VAULE, bgt.BUDGET_ID BUDGET_ID, bgt.BUDGET_UNIT2_ID BUDGET_UNIT_ID, bgt.BUSINESS_GROUP_ID BUSINESS_GROUP_ID, bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID, bdt.BUDGET_DETAIL_ID BUDGET_DETAIL_ID, pft.POSITION_ID POSITION_ID, bpr.BUDGET_PERIOD_ID BUDGET_PERIOD_ID, gdt.grade_id GRADE_ID, jbt.job_id JOB_ID, orgT.organization_id ORGANIZATION_ID FROM pqh_budgets_v bgt, per_shared_types_vl pst, hr_all_organization_units_tl bgrT, hr_all_organization_units_tl orgT, per_jobs_tl jbt, per_grades_tl gdt, pqh_budget_versions bvr, pqh_budget_details bdt, hr_all_positions_f_tl pft, pqh_budget_periods bpr, per_time_periods ptps, per_time_periods ptpe, per_organization_structures os, per_org_structure_versions ov, pay_calendars pc WHERE bgt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND bgt.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, bgt.business_group_id ) AND bgt.budget_unit2_id = pst.shared_type_id AND bgt.budget_id = bvr.budget_id AND bgt.BUDGET_UNIT2 IS NOT NULL AND bvr.budget_unit2_value is not null AND bvr.budget_version_id = bdt.budget_version_id AND bdt.budget_detail_id = bpr.budget_detail_id AND ptps.time_period_id = bpr.start_time_period_id AND ptpe.time_period_id = bpr.end_time_period_id AND bdt.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND bdt.job_id = jbt.job_id (+) AND jbt.language (+) = userenv('LANG') AND bdt.position_id = pft.position_id (+) AND pft.language (+) = userenv('LANG') AND bdt.grade_id = gdt.grade_id (+) AND gdt.language (+) = userenv('LANG') AND os.organization_structure_id (+) = ov.organization_structure_id AND ov.org_structure_version_id(+) = bgt.org_structure_version_id AND bgt.period_set_name = pc.period_set_name UNION ALL SELECT bgrT.name BUSINESS_GROUP_NAME, bgt.BUDGET_NAME BUDGET_NAME, bgt.PERIOD_SET_NAME BUDGET_CALENDAR_NAME, pc.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY, bgt.BUDGET_START_DATE BUDGET_START_DATE, bgt.BUDGET_END_DATE BUDGET_END_DATE, hr_bis.bis_decode_lookup('PQH_BUDGET_ENTITY' , bgt.budgeted_entity_cd) BUDGETED_ENTITY, bgt.budgeted_entity_cd BUDGETED_ENTITY_CD, hr_bis.bis_decode_lookup('PQH_BUDGET_STYLE' , bgt.budget_style_cd) BUDGET_STYLE, bgt.budget_style_cd BUDGET_STYLE_CD, os.NAME ORGANIZATION_HIERARCHY_NAME, ov.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION, HR_GENERAL.DECODE_ORGANIZATION(bgt.start_organization_id) STARTING_ORGANIZATION_NAME, pst.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE, pst.SYSTEM_TYPE_CD BUDGET_UNIT_CD, hr_bis.bis_decode_lookup('PQH_BGT_UOM_AGGREGATE' , bgt.budget_unit3_aggregate) BUDGET_UNIT_AGGREGATE_DESC, bgt.BUDGET_UNIT3_AGGREGATE BUDGET_UNIT_AGGREGATE, bvr.VERSION_NUMBER BUDGET_VERSION_NUMBER, bvr.DATE_FROM BUDGET_VERSION_START_DATE, bvr.DATE_TO BUDGET_VERSION_END_DATE, bdt.BUDGET_UNIT3_VALUE BUDGET_DETAIL_BUDGETED_VALUE, orgT.name ORGANIZATION_NAME, jbt.name JOB_NAME, pft.name POSITION_NAME, gdt.name GRADE_NAME, ptps.start_date||' - '||ptpe.end_date BUDGET_PERIOD_NAME, ptps.start_date BUDGET_PERIOD_START_DATE, ptpe.end_date BUDGET_PERIOD_END_DATE, bpr.BUDGET_UNIT3_VALUE BUDGET_PERIOD_BUDGETED_VALUE, hr_discoverer.get_actual_budget_values( pst.SYSTEM_TYPE_CD , bgt.business_group_id , orgT.organization_id , jbt.job_id , pft.position_id , gdt.grade_id , ptps.start_date , ptpe.end_date , bdt.BUDGET_UNIT3_VALUE) BUDGET_PERIOD_ACTUAL_VALUE, hr_disc_calculations.get_ff_actual_value_pqh( bgt.budget_id , bgt.business_group_id , gdt.grade_id , jbt.job_id , orgT.organization_id , bdt.position_id , bpr.budget_period_id , pst.system_type_cd) CALC_PERIOD_ACTUAL_VAULE, bgt.BUDGET_ID BUDGET_ID, bgt.BUDGET_UNIT3_ID BUDGET_UNIT_ID, bgt.BUSINESS_GROUP_ID BUSINESS_GROUP_ID, bvr.BUDGET_VERSION_ID BUDGET_VERSION_ID, bdt.BUDGET_DETAIL_ID BUDGET_DETAIL_ID, pft.POSITION_ID POSITION_ID, bpr.BUDGET_PERIOD_ID BUDGET_PERIOD_ID, gdt.grade_id GRADE_ID, jbt.job_id JOB_ID, orgT.organization_id ORGANIZATION_ID FROM pqh_budgets_v bgt, per_shared_types_vl pst, hr_all_organization_units_tl bgrT, hr_all_organization_units_tl orgT, per_jobs_tl jbt, per_grades_tl gdt, pqh_budget_versions bvr, pqh_budget_details bdt, hr_all_positions_f_tl pft, pqh_budget_periods bpr, per_time_periods ptps, per_time_periods ptpe, per_organization_structures os, per_org_structure_versions ov, pay_calendars pc WHERE bgt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND bgt.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,bgt.business_group_id ) AND bgt.budget_unit3_id = pst.shared_type_id AND bgt.budget_id = bvr.budget_id AND bgt.BUDGET_UNIT3 IS NOT NULL AND bvr.budget_unit3_value is not null AND bvr.budget_version_id = bdt.budget_version_id AND bdt.budget_detail_id = bpr.budget_detail_id AND ptps.time_period_id = bpr.start_time_period_id AND ptpe.time_period_id = bpr.end_time_period_id AND bdt.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND bdt.job_id = jbt.job_id (+) AND jbt.language (+) = userenv('LANG') AND bdt.position_id = pft.position_id (+) AND pft.language (+) = userenv('LANG') AND bdt.grade_id = gdt.grade_id (+) AND gdt.language (+) = userenv('LANG') AND os.organization_structure_id(+) = ov.organization_structure_id AND ov.org_structure_version_id(+) = bgt.org_structure_version_id AND bgt.period_set_name = pc.period_set_name WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, BGT.BUDGET_NAME BUDGET_NAME
, BGT.PERIOD_SET_NAME BUDGET_CALENDAR_NAME
, PC.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY
, BGT.BUDGET_START_DATE BUDGET_START_DATE
, BGT.BUDGET_END_DATE BUDGET_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_ENTITY'
, BGT.BUDGETED_ENTITY_CD) BUDGETED_ENTITY
, BGT.BUDGETED_ENTITY_CD BUDGETED_ENTITY_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_STYLE'
, BGT.BUDGET_STYLE_CD) BUDGET_STYLE
, BGT.BUDGET_STYLE_CD BUDGET_STYLE_CD
, OS.NAME ORGANIZATION_HIERARCHY_NAME
, OV.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION
, HR_GENERAL.DECODE_ORGANIZATION(BGT.START_ORGANIZATION_ID) STARTING_ORGANIZATION_NAME
, PST.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE
, PST.SYSTEM_TYPE_CD BUDGET_UNIT_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BGT_UOM_AGGREGATE'
, BGT.BUDGET_UNIT1_AGGREGATE) BUDGET_UNIT_AGGREGATE_DESC
, BGT.BUDGET_UNIT1_AGGREGATE BUDGET_UNIT_AGGREGATE
, BVR.VERSION_NUMBER BUDGET_VERSION_NUMBER
, BVR.DATE_FROM BUDGET_VERSION_START_DATE
, BVR.DATE_TO BUDGET_VERSION_END_DATE
, BDT.BUDGET_UNIT1_VALUE BUDGET_DETAIL_BUDGETED_VALUE
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, PTPS.START_DATE||' - '||PTPE.END_DATE BUDGET_PERIOD_NAME
, PTPS.START_DATE BUDGET_PERIOD_START_DATE
, PTPE.END_DATE BUDGET_PERIOD_END_DATE
, BPR.BUDGET_UNIT1_VALUE BUDGET_PERIOD_BUDGETED_VALUE
, HR_DISCOVERER.GET_ACTUAL_BUDGET_VALUES( PST.SYSTEM_TYPE_CD
, BGT.BUSINESS_GROUP_ID
, ORGT.ORGANIZATION_ID
, JBT.JOB_ID
, PFT.POSITION_ID
, GDT.GRADE_ID
, PTPS.START_DATE
, PTPE.END_DATE
, BDT.BUDGET_UNIT1_VALUE) BUDGET_PERIOD_ACTUAL_VALUE
, HR_DISC_CALCULATIONS.GET_FF_ACTUAL_VALUE_PQH( BGT.BUDGET_ID
, BGT.BUSINESS_GROUP_ID
, GDT.GRADE_ID
, JBT.JOB_ID
, ORGT.ORGANIZATION_ID
, BDT.POSITION_ID
, BPR.BUDGET_PERIOD_ID
, PST.SYSTEM_TYPE_CD) CALC_PERIOD_ACTUAL_VAULE
, BGT.BUDGET_ID BUDGET_ID
, BGT.BUDGET_UNIT1_ID BUDGET_UNIT_ID
, BGT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, BVR.BUDGET_VERSION_ID BUDGET_VERSION_ID
, BDT.BUDGET_DETAIL_ID BUDGET_DETAIL_ID
, PFT.POSITION_ID POSITION_ID
, BPR.BUDGET_PERIOD_ID BUDGET_PERIOD_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, ORGT.ORGANIZATION_ID ORGANIZATION_ID
FROM PQH_BUDGETS BGT
, PER_SHARED_TYPES_VL PST
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, PQH_BUDGET_VERSIONS BVR
, PQH_BUDGET_DETAILS BDT
, HR_ALL_POSITIONS_F_TL PFT
, PQH_BUDGET_PERIODS BPR
, PER_TIME_PERIODS PTPS
, PER_TIME_PERIODS PTPE
, PER_ORGANIZATION_STRUCTURES OS
, PER_ORG_STRUCTURE_VERSIONS OV
, PAY_CALENDARS PC
WHERE BGT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND BGT.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BGT.BUSINESS_GROUP_ID )
AND BGT.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID
AND BGT.BUDGET_ID = BVR.BUDGET_ID
AND BVR.BUDGET_UNIT1_VALUE IS NOT NULL
AND BVR.BUDGET_VERSION_ID = BDT.BUDGET_VERSION_ID
AND BDT.BUDGET_DETAIL_ID = BPR.BUDGET_DETAIL_ID
AND PTPS.TIME_PERIOD_ID = BPR.START_TIME_PERIOD_ID
AND PTPE.TIME_PERIOD_ID = BPR.END_TIME_PERIOD_ID
AND BDT.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND BDT.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND BDT.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND BDT.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND OS.ORGANIZATION_STRUCTURE_ID (+) = OV.ORGANIZATION_STRUCTURE_ID
AND OV.ORG_STRUCTURE_VERSION_ID(+) = BGT.ORG_STRUCTURE_VERSION_ID
AND BGT.PERIOD_SET_NAME = PC.PERIOD_SET_NAME UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, BGT.BUDGET_NAME BUDGET_NAME
, BGT.PERIOD_SET_NAME BUDGET_CALENDAR_NAME
, PC.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY
, BGT.BUDGET_START_DATE BUDGET_START_DATE
, BGT.BUDGET_END_DATE BUDGET_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_ENTITY'
, BGT.BUDGETED_ENTITY_CD) BUDGETED_ENTITY
, BGT.BUDGETED_ENTITY_CD BUDGETED_ENTITY_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_STYLE'
, BGT.BUDGET_STYLE_CD) BUDGET_STYLE
, BGT.BUDGET_STYLE_CD BUDGET_STYLE_CD
, OS.NAME ORGANIZATION_HIERARCHY_NAME
, OV.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION
, HR_GENERAL.DECODE_ORGANIZATION(BGT.START_ORGANIZATION_ID) STARTING_ORGANIZATION_NAME
, PST.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE
, PST.SYSTEM_TYPE_CD BUDGET_UNIT_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BGT_UOM_AGGREGATE'
, BGT.BUDGET_UNIT2_AGGREGATE) BUDGET_UNIT_AGGREGATE_DESC
, BGT.BUDGET_UNIT2_AGGREGATE BUDGET_UNIT_AGGREGATE
, BVR.VERSION_NUMBER BUDGET_VERSION_NUMBER
, BVR.DATE_FROM BUDGET_VERSION_START_DATE
, BVR.DATE_TO BUDGET_VERSION_END_DATE
, BDT.BUDGET_UNIT2_VALUE BUDGET_DETAIL_BUDGETED_VALUE
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, PTPS.START_DATE||' - '||PTPE.END_DATE BUDGET_PERIOD_NAME
, PTPS.START_DATE BUDGET_PERIOD_START_DATE
, PTPE.END_DATE BUDGET_PERIOD_END_DATE
, BPR.BUDGET_UNIT2_VALUE BUDGET_PERIOD_BUDGETED_VALUE
, HR_DISCOVERER.GET_ACTUAL_BUDGET_VALUES( PST.SYSTEM_TYPE_CD
, BGT.BUSINESS_GROUP_ID
, ORGT.ORGANIZATION_ID
, JBT.JOB_ID
, PFT.POSITION_ID
, GDT.GRADE_ID
, PTPS.START_DATE
, PTPE.END_DATE
, BDT.BUDGET_UNIT2_VALUE) BUDGET_PERIOD_ACTUAL_VALUE
, HR_DISC_CALCULATIONS.GET_FF_ACTUAL_VALUE_PQH( BGT.BUDGET_ID
, BGT.BUSINESS_GROUP_ID
, GDT.GRADE_ID
, JBT.JOB_ID
, ORGT.ORGANIZATION_ID
, BDT.POSITION_ID
, BPR.BUDGET_PERIOD_ID
, PST.SYSTEM_TYPE_CD) CALC_PERIOD_ACTUAL_VAULE
, BGT.BUDGET_ID BUDGET_ID
, BGT.BUDGET_UNIT2_ID BUDGET_UNIT_ID
, BGT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, BVR.BUDGET_VERSION_ID BUDGET_VERSION_ID
, BDT.BUDGET_DETAIL_ID BUDGET_DETAIL_ID
, PFT.POSITION_ID POSITION_ID
, BPR.BUDGET_PERIOD_ID BUDGET_PERIOD_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, ORGT.ORGANIZATION_ID ORGANIZATION_ID
FROM PQH_BUDGETS_V BGT
, PER_SHARED_TYPES_VL PST
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, PQH_BUDGET_VERSIONS BVR
, PQH_BUDGET_DETAILS BDT
, HR_ALL_POSITIONS_F_TL PFT
, PQH_BUDGET_PERIODS BPR
, PER_TIME_PERIODS PTPS
, PER_TIME_PERIODS PTPE
, PER_ORGANIZATION_STRUCTURES OS
, PER_ORG_STRUCTURE_VERSIONS OV
, PAY_CALENDARS PC
WHERE BGT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND BGT.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BGT.BUSINESS_GROUP_ID )
AND BGT.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID
AND BGT.BUDGET_ID = BVR.BUDGET_ID
AND BGT.BUDGET_UNIT2 IS NOT NULL
AND BVR.BUDGET_UNIT2_VALUE IS NOT NULL
AND BVR.BUDGET_VERSION_ID = BDT.BUDGET_VERSION_ID
AND BDT.BUDGET_DETAIL_ID = BPR.BUDGET_DETAIL_ID
AND PTPS.TIME_PERIOD_ID = BPR.START_TIME_PERIOD_ID
AND PTPE.TIME_PERIOD_ID = BPR.END_TIME_PERIOD_ID
AND BDT.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND BDT.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND BDT.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND BDT.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND OS.ORGANIZATION_STRUCTURE_ID (+) = OV.ORGANIZATION_STRUCTURE_ID
AND OV.ORG_STRUCTURE_VERSION_ID(+) = BGT.ORG_STRUCTURE_VERSION_ID
AND BGT.PERIOD_SET_NAME = PC.PERIOD_SET_NAME UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, BGT.BUDGET_NAME BUDGET_NAME
, BGT.PERIOD_SET_NAME BUDGET_CALENDAR_NAME
, PC.ACTUAL_PERIOD_TYPE BUDGET_CALENDAR_FREQUENCY
, BGT.BUDGET_START_DATE BUDGET_START_DATE
, BGT.BUDGET_END_DATE BUDGET_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_ENTITY'
, BGT.BUDGETED_ENTITY_CD) BUDGETED_ENTITY
, BGT.BUDGETED_ENTITY_CD BUDGETED_ENTITY_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BUDGET_STYLE'
, BGT.BUDGET_STYLE_CD) BUDGET_STYLE
, BGT.BUDGET_STYLE_CD BUDGET_STYLE_CD
, OS.NAME ORGANIZATION_HIERARCHY_NAME
, OV.VERSION_NUMBER ORGANIZATION_HIERARCHY_VERSION
, HR_GENERAL.DECODE_ORGANIZATION(BGT.START_ORGANIZATION_ID) STARTING_ORGANIZATION_NAME
, PST.SHARED_TYPE_NAME BUDGET_MEASUREMENT_TYPE
, PST.SYSTEM_TYPE_CD BUDGET_UNIT_CD
, HR_BIS.BIS_DECODE_LOOKUP('PQH_BGT_UOM_AGGREGATE'
, BGT.BUDGET_UNIT3_AGGREGATE) BUDGET_UNIT_AGGREGATE_DESC
, BGT.BUDGET_UNIT3_AGGREGATE BUDGET_UNIT_AGGREGATE
, BVR.VERSION_NUMBER BUDGET_VERSION_NUMBER
, BVR.DATE_FROM BUDGET_VERSION_START_DATE
, BVR.DATE_TO BUDGET_VERSION_END_DATE
, BDT.BUDGET_UNIT3_VALUE BUDGET_DETAIL_BUDGETED_VALUE
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, PTPS.START_DATE||' - '||PTPE.END_DATE BUDGET_PERIOD_NAME
, PTPS.START_DATE BUDGET_PERIOD_START_DATE
, PTPE.END_DATE BUDGET_PERIOD_END_DATE
, BPR.BUDGET_UNIT3_VALUE BUDGET_PERIOD_BUDGETED_VALUE
, HR_DISCOVERER.GET_ACTUAL_BUDGET_VALUES( PST.SYSTEM_TYPE_CD
, BGT.BUSINESS_GROUP_ID
, ORGT.ORGANIZATION_ID
, JBT.JOB_ID
, PFT.POSITION_ID
, GDT.GRADE_ID
, PTPS.START_DATE
, PTPE.END_DATE
, BDT.BUDGET_UNIT3_VALUE) BUDGET_PERIOD_ACTUAL_VALUE
, HR_DISC_CALCULATIONS.GET_FF_ACTUAL_VALUE_PQH( BGT.BUDGET_ID
, BGT.BUSINESS_GROUP_ID
, GDT.GRADE_ID
, JBT.JOB_ID
, ORGT.ORGANIZATION_ID
, BDT.POSITION_ID
, BPR.BUDGET_PERIOD_ID
, PST.SYSTEM_TYPE_CD) CALC_PERIOD_ACTUAL_VAULE
, BGT.BUDGET_ID BUDGET_ID
, BGT.BUDGET_UNIT3_ID BUDGET_UNIT_ID
, BGT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, BVR.BUDGET_VERSION_ID BUDGET_VERSION_ID
, BDT.BUDGET_DETAIL_ID BUDGET_DETAIL_ID
, PFT.POSITION_ID POSITION_ID
, BPR.BUDGET_PERIOD_ID BUDGET_PERIOD_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, ORGT.ORGANIZATION_ID ORGANIZATION_ID
FROM PQH_BUDGETS_V BGT
, PER_SHARED_TYPES_VL PST
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, PQH_BUDGET_VERSIONS BVR
, PQH_BUDGET_DETAILS BDT
, HR_ALL_POSITIONS_F_TL PFT
, PQH_BUDGET_PERIODS BPR
, PER_TIME_PERIODS PTPS
, PER_TIME_PERIODS PTPE
, PER_ORGANIZATION_STRUCTURES OS
, PER_ORG_STRUCTURE_VERSIONS OV
, PAY_CALENDARS PC
WHERE BGT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND BGT.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BGT.BUSINESS_GROUP_ID )
AND BGT.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID
AND BGT.BUDGET_ID = BVR.BUDGET_ID
AND BGT.BUDGET_UNIT3 IS NOT NULL
AND BVR.BUDGET_UNIT3_VALUE IS NOT NULL
AND BVR.BUDGET_VERSION_ID = BDT.BUDGET_VERSION_ID
AND BDT.BUDGET_DETAIL_ID = BPR.BUDGET_DETAIL_ID
AND PTPS.TIME_PERIOD_ID = BPR.START_TIME_PERIOD_ID
AND PTPE.TIME_PERIOD_ID = BPR.END_TIME_PERIOD_ID
AND BDT.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND BDT.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND BDT.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND BDT.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND OS.ORGANIZATION_STRUCTURE_ID(+) = OV.ORGANIZATION_STRUCTURE_ID
AND OV.ORG_STRUCTURE_VERSION_ID(+) = BGT.ORG_STRUCTURE_VERSION_ID
AND BGT.PERIOD_SET_NAME = PC.PERIOD_SET_NAME WITH READ ONLY