PAY_BALANCE_TRANSFER_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name

INSTANCE_NAME PERSON_ID PAYROLL_REL_ACTION_ID CALC_BREAKDOWN_ID TAX_UNIT_ID EFFECTIVE_DATE REASON LEGISLATIVE_DATA_GROUP_ID PAYROLL_ID PROCESS_INFO_GROUP_ID EXCLUDE_TARGET_PAYROLL MASS_ACTION_LINE_ID PROCESS_INFO_GROUP_NAME SOURCE_PR_ID SOURCE_ASG_ID SOURCE_TRM_ID TARGET_PR_ID TARGET_ASG_ID TARGET_TRM_ID SOURCE_ASG_SD SOURCE_PR_SD SOURCE_PR_ED TARGET_ASG_SD TARGET_PR_SD TARGET_PR_ED SOURCE_LAID SOURCE_LAID_V TARGET_LAID TARGET_COPY_LAID SOURCE_DEFBAL_ID TARGET_BAL_ID PRE_TARGET_ELEMENT_ID PRE_TARGET_ELEMENT_NAME SOURCE_BAL_NAME SOURCE_BALANCETYPE_ID SOURCE_DIM_NAME TARGET_DIM_SD TARGET_DIM_ED SOURCE_DIM_SD SOURCE_DIM_ED DIMENSION_LEVEL PERIOD_TYPE DIM_PERIOD_SOURCE_LAID CONTEXT_ID1 CONTEXT_NAME1 CONTEXT_VALUE1 CONTEXT_ID2 CONTEXT_NAME2 CONTEXT_VALUE2 CONTEXT_ID3 CONTEXT_NAME3 CONTEXT_VALUE3 CONTEXT_ID4 CONTEXT_NAME4 CONTEXT_VALUE4 CONTEXT_ID5 CONTEXT_NAME5 CONTEXT_VALUE5 CONTEXT_ID6 CONTEXT_NAME6 CONTEXT_VALUE6 CONTEXT_ID7 CONTEXT_NAME7 CONTEXT_VALUE7 CONTEXT_ID8 CONTEXT_NAME8 CONTEXT_VALUE8 CONTEXT_ID9 CONTEXT_NAME9 CONTEXT_VALUE9 CONTEXT_ID10 CONTEXT_NAME10 CONTEXT_VALUE10 CONTEXT_ID11 CONTEXT_NAME11 CONTEXT_VALUE11 CONTEXT_ID12 CONTEXT_NAME12 CONTEXT_VALUE12 CONTEXT_ISINPUTVALUE1 CONTEXT_ISINPUTVALUE2 CONTEXT_ISINPUTVALUE3 CONTEXT_ISINPUTVALUE4 CONTEXT_ISINPUTVALUE5 CONTEXT_ISINPUTVALUE6 CONTEXT_ISINPUTVALUE7 CONTEXT_ISINPUTVALUE8 CONTEXT_ISINPUTVALUE9 CONTEXT_ISINPUTVALUE10 CONTEXT_ISINPUTVALUE11 CONTEXT_ISINPUTVALUE12 TARGET_ELEMENT_NAME TARGET_ELEMENT_TYPE_ID REL_TARGET_ELEMENT_NAME REL_TARGET_ELEMENT_TYPE_ID INPUT_VALUE_NAME SOURCE_ACTION_TYPE SOURCE_REL_DEFBAL_ID INPUT_VALUE ORIG_BAL_VALUE REL_TARGET_INPUT_VALUE ASG_TARGET_INPUT_VALUE ALREADY_PROCESSED ENTRY_EXISTS NEW_CAL_BREAKDOWN

Query

SQL_Statement

select rec.instance_name, rec.person_id, rec.payroll_rel_action_id, rec.CALC_BREAKDOWN_ID, rec.TAX_UNIT_ID, rec.effective_date, rec.REASON, rec.legislative_data_group_id, rec.payroll_id, rec.PROCESS_INFO_GROUP_ID, rec.EXCLUDE_TARGET_PAYROLL, rec.mass_action_line_id, rec.PROCESS_INFO_GROUP_NAME, rec.SOURCE_PR_ID, rec.SOURCE_ASG_ID, rec.SOURCE_TRM_ID, rec.TARGET_PR_ID, rec.TARGET_ASG_ID, rec.TARGET_TRM_ID, rec.SOURCE_ASG_SD, rec.SOURCE_PR_SD, rec.SOURCE_PR_ED, rec.TARGET_ASG_SD, rec.TARGET_PR_SD, rec.TARGET_PR_ED, rec.SOURCE_LAID, rec.SOURCE_LAID_V, rec.TARGET_LAID, rec.TARGET_COPY_LAID, rec.SOURCE_DEFBAL_ID, rec.TARGET_BAL_ID, rec.pre_target_element_id, NVL2(rec.pre_target_element_id, (select max(element_name) from pay_element_types_vl where element_type_id =rec.pre_target_element_id), null) pre_target_element_name, rec.SOURCE_BAL_NAME, rec.SOURCE_BALANCETYPE_ID, rec.SOURCE_DIM_NAME, rec.TARGET_DIM_SD, rec.TARGET_DIM_ED, rec.SOURCE_DIM_SD, rec.SOURCE_DIM_ED, rec.dimension_level, rec.period_type, rec.DIM_PERIOD_SOURCE_LAID, rec.context_id1, rec.context_name1, rec.context_value1, rec.context_id2, rec.context_name2, rec.context_value2, rec.context_id3, rec.context_name3, rec.context_value3, rec.context_id4, rec.context_name4, rec.context_value4, rec.context_id5, rec.context_name5, rec.context_value5, rec.context_id6, rec.context_name6, rec.context_value6, rec.context_id7, rec.context_name7, rec.context_value7, rec.context_id8, rec.context_name8, rec.context_value8, rec.context_id9, rec.context_name9, rec.context_value9, rec.context_id10, rec.context_name10, rec.context_value10, rec.context_id11, rec.context_name11, rec.context_value11, rec.context_id12, rec.context_name12, rec.context_value12, rec.context_isInputValue1, rec.context_isInputValue2, rec.context_isInputValue3, rec.context_isInputValue4, rec.context_isInputValue5, rec.context_isInputValue6, rec.context_isInputValue7, rec.context_isInputValue8, rec.context_isInputValue9, rec.context_isInputValue10, rec.context_isInputValue11, rec.context_isInputValue12, rec.target_element_name, rec.target_element_type_id, rec.rel_target_element_name, rec.rel_target_element_type_id, pbt.base_balance_name input_value_name, rec.source_action_type, rec.source_rel_defbal_id, (CASE WHEN rec.SOURCE_LAID is null THEN 0 ELSE PAY_BAL_COPY_PKG.get_balance_value(rec.SOURCE_DEFBAL_ID, rec.SOURCE_BALANCETYPE_ID, decode(rec.period_type,'YEAR',rec.DIM_PERIOD_SOURCE_LAID,'QUARTER',rec.DIM_PERIOD_SOURCE_LAID, rec.SOURCE_LAID_V), null, null, 'N', rec.context_value1, rec.context_id1, rec.context_isInputValue1, rec.context_value2, rec.context_id2, rec.context_isInputValue2, rec.context_value3, rec.context_id3, rec.context_isInputValue3, rec.context_value4, rec.context_id4, rec.context_isInputValue4, rec.context_value5, rec.context_id5, rec.context_isInputValue5, rec.context_value6, rec.context_id6, rec.context_isInputValue6) END ) input_value, NVL2(rec.TARGET_LAID, PAY_BAL_COPY_PKG.get_balance_value(rec.SOURCE_DEFBAL_ID, rec.SOURCE_BALANCETYPE_ID, rec.TARGET_LAID, null, null, 'Y', rec.context_value1, rec.context_id1, rec.context_isInputValue1, rec.context_value2, rec.context_id2, rec.context_isInputValue2, rec.context_value3, rec.context_id3, rec.context_isInputValue3, rec.context_value4, rec.context_id4, rec.context_isInputValue4, rec.context_value5, rec.context_id5, rec.context_isInputValue5, rec.context_value6, rec.context_id6, rec.context_isInputValue6), 0) orig_bal_value, (CASE WHEN rec.dimension_level = 'ASG' AND rec.source_rel_defbal_id is not null AND rec.TARGET_LAID is not null THEN PAY_BAL_COPY_PKG.get_balance_value(rec.source_rel_defbal_id, rec.SOURCE_BALANCETYPE_ID, rec.TARGET_LAID, null, null, 'Y', rec.context_value1, rec.context_id1, rec.context_isInputValue1, rec.context_value2, rec.context_id2, rec.context_isInputValue2, rec.context_value3, rec.context_id3, rec.context_isInputValue3, rec.context_value4, rec.context_id4, rec.context_isInputValue4, rec.context_value5, rec.context_id5, rec.context_isInputValue5, rec.context_value6, rec.context_id6, rec.context_isInputValue6) ELSE 0 END ) rel_target_input_value, (CASE WHEN rec.dimension_level = 'ASG' AND rec.source_rel_defbal_id is not null AND rec.TARGET_LAID is not null THEN PAY_BAL_COPY_PKG.get_balance_value(rec.SOURCE_DEFBAL_ID, rec.SOURCE_BALANCETYPE_ID, rec.TARGET_LAID, null, rec.TARGET_ASG_ID, 'N', rec.context_value1, rec.context_id1, rec.context_isInputValue1, rec.context_value2, rec.context_id2, rec.context_isInputValue2, rec.context_value3, rec.context_id3, rec.context_isInputValue3, rec.context_value4, rec.context_id4, rec.context_isInputValue4, rec.context_value5, rec.context_id5, rec.context_isInputValue5, rec.context_value6, rec.context_id6, rec.context_isInputValue6) ELSE 0 END ) asg_target_input_value, (CASE WHEN rec.EXCLUDE_TARGET_PAYROLL IS NOT NULL AND rec.EXCLUDE_TARGET_PAYROLL = 'Y' THEN (select max('Y') from pay_object_group_types where exists (select null from pay_payroll_actions ppa, pay_payroll_rel_actions pra, pay_rel_groups_dn asg where ppa.payroll_action_id = pra.payroll_action_id and pra.payroll_relationship_id = rec.target_pr_id and ppa.action_type in ('I','R','Q','V') and pra.source_action_id is null and pra.payroll_rel_action_id != rec.payroll_rel_action_id and asg.relationship_group_id = rec.target_asg_id and ((rec.dimension_level = 'REL' and rec.source_pr_id != rec.target_pr_id and ppa.creation_Date > asg.creation_Date) or (rec.dimension_level = 'ASG' and rec.source_pr_id != rec.target_pr_id and ppa.creation_Date > asg.creation_Date) or (rec.dimension_level = 'ASG' and rec.source_pr_id = rec.target_pr_id and ppa.creation_Date > asg.creation_Date)))) ELSE 'N' END ) already_processed, (select max('Y') from pay_object_group_types where exists (select null from pay_element_entries_f pee, pay_element_entry_values_f peev, pay_entry_usages peu, pay_element_types_vl pet, pay_input_values_vl piv where pee.person_id = rec.person_id and pee.element_entry_id = peu.element_entry_id and rec.effective_Date between pee.effective_start_date and pee.effective_end_Date and peev.element_entry_id = pee.element_entry_id and rec.effective_Date between peev.effective_start_date and peev.effective_end_Date and peu.payroll_relationship_id = rec.target_PR_ID and rec.pre_target_element_id is not null and rec.pre_target_element_id=pee.element_type_id and (peu.payroll_assignment_id = rec.target_asg_id or peu.payroll_assignment_id is null) and pee.element_type_id = pet.element_type_id and pet.element_type_id = piv.element_type_id and peev.input_value_id = piv.input_value_id and (piv.context_id is null OR ((rec.context_id1 = piv.context_id and peev.screen_entry_value = rec.context_value1) or (rec.context_id2 = piv.context_id and peev.screen_entry_value = rec.context_value2) or (rec.context_id3 = piv.context_id and peev.screen_entry_value = rec.context_value3) or (rec.context_id4 = piv.context_id and peev.screen_entry_value = rec.context_value4) or (rec.context_id5 = piv.context_id and peev.screen_entry_value = rec.context_value5) or (rec.context_id6 = piv.context_id and peev.screen_entry_value = rec.context_value6) or (rec.context_id7 = piv.context_id and peev.screen_entry_value = rec.context_value7) or (rec.context_id8 = piv.context_id and peev.screen_entry_value = rec.context_value8) or (rec.context_id9 = piv.context_id and peev.screen_entry_value = rec.context_value9) or (rec.context_id10 = piv.context_id and peev.screen_entry_value = rec.context_value10) or (rec.context_id11 = piv.context_id and peev.screen_entry_value = rec.context_value11) or (rec.context_id12 = piv.context_id and peev.screen_entry_value = rec.context_value12) )))) entry_exists, PAY_BAL_COPY_PKG.GET_CALC_BREAKDOWN_ID(rec.target_pr_id, rec.TARGET_TRM_ID, (select max(legislation_code) from per_legislative_data_groups_vl where legislative_data_group_id = rec.legislative_data_group_id) ) new_cal_breakdown from ( select distinct rec.instance_name, rec.person_id, rec.payroll_rel_action_id, pcv.CALC_BREAKDOWN_ID CALC_BREAKDOWN_ID, pcv.TAX_UNIT_ID, rec.effective_date, rec.REASON, rec.legislative_data_group_id, REC.payroll_id, rec.PROCESS_INFO_GROUP_ID, rec.EXCLUDE_TARGET_PAYROLL, rec.mass_action_line_id, rec.PROCESS_INFO_GROUP_NAME, rec.SOURCE_PR_ID, rec.SOURCE_ASG_ID, rec.SOURCE_TRM_ID, rec.TARGET_PR_ID, rec.TARGET_ASG_ID, rec.TARGET_TRM_ID, rec.TARGET_ASG_SD, rec.TARGET_PR_SD, rec.TARGET_PR_ED, rec.SOURCE_ASG_SD, rec.SOURCE_PR_SD, rec.SOURCE_PR_ED, rec.TARGET_LAID, rec.TARGET_COPY_LAID, rec.SOURCE_LAID, rec.source_effective_date, rec.SOURCE_LAID_V, rec.SOURCE_DEFBAL_ID, rec.TARGET_BAL_ID, rec.pre_target_element_id, rec.SOURCE_BAL_NAME, rec.SOURCE_BALANCETYPE_ID, rec.SOURCE_DIM_NAME, rec.TARGET_DIM_SD, rec.TARGET_DIM_ED, rec.SOURCE_DIM_SD, rec.SOURCE_DIM_ED, rec.dimension_level, rec.period_type, NVL((select max(payroll_rel_action_id) from pay_payroll_rel_actions where source_action_id = rec.DIM_PERIOD_SOURCE_LAID), rec.DIM_PERIOD_SOURCE_LAID) DIM_PERIOD_SOURCE_LAID, rec.context_id1, rec.context_name1, rec.target_element_name, rec.target_element_type_id, rec.rel_target_element_name, rec.rel_target_element_type_id, (select action_type from pay_payroll_actions where payroll_action_id = (select max(ppa.payroll_action_id) from pay_payroll_rel_actions pra, pay_payroll_actions ppa where pra.payroll_relationship_id= rec.SOURCE_PR_ID and ppa.payroll_action_id = pra.payroll_action_id and pra.action_status = 'C' and ppa.action_type in ('I','B','R','Q','V') and pra.source_action_id is null)) source_action_type, (select max(defined_balance_id) from pay_defined_balances where balance_dimension_id in ( select balance_dimension_id from pay_balance_dimensions where base_db_item_suffix in ( select replace(base_db_item_suffix, 'ASG', 'REL') from pay_balance_dimensions where balance_dimension_id in (select max(balance_dimension_id) from pay_defined_balances where defined_balance_id = rec.SOURCE_DEFBAL_ID))) and balance_type_id in (select max(balance_type_id) from pay_defined_balances where defined_balance_id = rec.SOURCE_DEFBAL_ID)) source_rel_defbal_id, decode(rec.context_name1,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id1 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value1, rec.context_id2, rec.context_name2, decode(rec.context_name2,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id2 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value2, rec.context_id3, rec.context_name3, decode(rec.context_name3,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id3 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value3, rec.context_id4, rec.context_name4, decode(rec.context_name4,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id4 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value4, rec.context_id5, rec.context_name5, decode(rec.context_name5,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id5 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value5, rec.context_id6, rec.context_name6, decode(rec.context_name6,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id6 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value6, rec.context_id7, rec.context_name7, decode(rec.context_name7,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id7 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value7, rec.context_id8, rec.context_name8, decode(rec.context_name8,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id8 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value8, rec.context_id9, rec.context_name9, decode(rec.context_name9,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id9 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value9, rec.context_id10, rec.context_name10, decode(rec.context_name10,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id10 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value10, rec.context_id11, rec.context_name11, decode(rec.context_name11,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id11 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value11, rec.context_id12, rec.context_name12, decode(rec.context_name12,'DUMMY','DUMMY','CALC_BREAKDOWN_ID',pcv.CALC_BREAKDOWN_ID,'PAYROLL_REL_ACTION_ID',rec.SOURCE_LAID,'PAYROLL_TERM_ID',rec.SOURCE_TRM_ID,'PAYROLL_ASSIGNMENT_ID',rec.SOURCE_ASG_ID,'TAX_UNIT_ID',pcv.TAX_UNIT_ID,'LEGAL_EMPLOYER_ID',pcv.LEGAL_EMPLOYER_ID,'ELEMENT_ENTRY_ID',pcv.ELEMENT_ENTRY_ID,'PAYROLL_ID',pcv.payroll_id,'BALANCE_DATE',pcv.BALANCE_DATE,'AREA1',pcv.AREA1,'AREA2',pcv.AREA2,'AREA3',pcv.AREA3,'AREA4',pcv.AREA4,'THIRD_PARTY_PAYEE_ID',pcv.THIRD_PARTY_PAYEE_ID,'TIME_DEFINITION_ID',pcv.TIME_DEFINITION_ID,'PERSON_ID',pcv.PERSON_ID,CASE rec.context_id12 WHEN pcv.context_id1 THEN pcv.context_value1 WHEN pcv.context_id2 THEN pcv.context_value2 WHEN pcv.context_id3 THEN pcv.context_value3 WHEN pcv.context_id4 THEN pcv.context_value4 WHEN pcv.context_id5 THEN pcv.context_value5 WHEN pcv.context_id6 THEN pcv.context_value6 WHEN pcv.context_id7 THEN pcv.context_value7 WHEN pcv.context_id8 THEN pcv.context_value8 WHEN pcv.context_id9 THEN pcv.context_value9 WHEN pcv.context_id10 THEN pcv.context_value10 WHEN pcv.context_id11 THEN pcv.context_value11 WHEN pcv.context_id12 THEN pcv.context_value12 END ) context_value12, (select max('Y') from pay_input_values_f where context_id = rec.context_id1 and element_type_id = rec.target_element_type_id) context_isInputValue1, (select max('Y') from pay_input_values_f where context_id = rec.context_id2 and element_type_id = rec.target_element_type_id) context_isInputValue2, (select max('Y') from pay_input_values_f where context_id = rec.context_id3 and element_type_id = rec.target_element_type_id) context_isInputValue3, (select max('Y') from pay_input_values_f where context_id = rec.context_id4 and element_type_id = rec.target_element_type_id) context_isInputValue4, (select max('Y') from pay_input_values_f where context_id = rec.context_id5 and element_type_id = rec.target_element_type_id) context_isInputValue5, (select max('Y') from pay_input_values_f where context_id = rec.context_id6 and element_type_id = rec.target_element_type_id) context_isInputValue6, (select max('Y') from pay_input_values_f where context_id = rec.context_id7 and element_type_id = rec.target_element_type_id) context_isInputValue7, (select max('Y') from pay_input_values_f where context_id = rec.context_id8 and element_type_id = rec.target_element_type_id) context_isInputValue8, (select max('Y') from pay_input_values_f where context_id = rec.context_id9 and element_type_id = rec.target_element_type_id) context_isInputValue9, (select max('Y') from pay_input_values_f where context_id = rec.context_id10 and element_type_id = rec.target_element_type_id) context_isInputValue10, (select max('Y') from pay_input_values_f where context_id = rec.context_id11 and element_type_id = rec.target_element_type_id) context_isInputValue11, (select max('Y') from pay_input_values_f where context_id = rec.context_id12 and element_type_id = rec.target_element_type_id) context_isInputValue12 from (select rec.instance_name, rec.person_id, rec.payroll_rel_action_id, rec.effective_date, rec.REASON, rec.legislative_data_group_id, REC.payroll_id, rec.PROCESS_INFO_GROUP_ID, rec.EXCLUDE_TARGET_PAYROLL, rec.mass_action_line_id, rec.base_object_group_name PROCESS_INFO_GROUP_NAME, rec.SOURCE_PR_ID, rec.SOURCE_ASG_ID, rec.SOURCE_TRM_ID, rec.TARGET_PR_ID, rec.TARGET_ASG_ID, rec.TARGET_TRM_ID, rec.TARGET_ASG_SD, rec.TARGET_PR_SD, rec.TARGET_PR_ED, rec.SOURCE_ASG_SD, rec.SOURCE_PR_SD, rec.SOURCE_PR_ED, rec.TARGET_LAID, (select max(effective_date) from pay_payroll_actions where payroll_action_id in (select payroll_action_id from pay_payroll_rel_actions where payroll_rel_action_id=rec.SOURCE_LAID)) source_effective_date, rec.TARGET_COPY_LAID, rec.SOURCE_LAID, rec.SOURCE_LAID_V, rec.SOURCE_DEFBAL_ID, rec.TARGET_BAL_ID, rec.pre_target_element_id, rec.SOURCE_BAL_NAME, rec.SOURCE_BALANCETYPE_ID, rec.SOURCE_DIM_NAME, rec.TARGET_DIM_SD, rec.TARGET_DIM_ED, rec.SOURCE_DIM_SD, rec.SOURCE_DIM_ED, rec.dimension_level, rec.period_type, (select max(pra.payroll_rel_action_id) from pay_payroll_rel_actions pra, pay_payroll_actions ppa where pra.payroll_relationship_id= rec.SOURCE_PR_ID and ppa.payroll_action_id = pra.payroll_action_id and pra.payroll_rel_action_id != rec.payroll_rel_action_id and pra.action_status = 'C' and ppa.effective_Date between rec.SOURCE_DIM_SD and rec.SOURCE_DIM_ED and ppa.action_type in ('I','B','R','Q','V') and pra.source_action_id is null) DIM_PERIOD_SOURCE_LAID, rec.target_element_name, rec.target_element_type_id, rec.rel_target_element_name, rec.rel_target_element_type_id, max(decode(pbc.sequence_no,1,pbc.context_id,null)) context_id1, max(decode(pbc.sequence_no,1,fc.context_name,null)) context_name1, max(decode(pbc.sequence_no,2,pbc.context_id,null)) context_id2, max(decode(pbc.sequence_no,2,fc.context_name,null)) context_name2, max(decode(pbc.sequence_no,3,pbc.context_id,null)) context_id3, max(decode(pbc.sequence_no,3,fc.context_name,null)) context_name3, max(decode(pbc.sequence_no,4,pbc.context_id,null)) context_id4, max(decode(pbc.sequence_no,4,fc.context_name,null)) context_name4, max(decode(pbc.sequence_no,5,pbc.context_id,null)) context_id5, max(decode(pbc.sequence_no,5,fc.context_name,null)) context_name5, max(decode(pbc.sequence_no,6,pbc.context_id,null)) context_id6, max(decode(pbc.sequence_no,6,fc.context_name,null)) context_name6, max(decode(pbc.sequence_no,7,pbc.context_id,null)) context_id7, max(decode(pbc.sequence_no,7,fc.context_name,null)) context_name7, max(decode(pbc.sequence_no,8,pbc.context_id,null)) context_id8, max(decode(pbc.sequence_no,8,fc.context_name,null)) context_name8, max(decode(pbc.sequence_no,9,pbc.context_id,null)) context_id9, max(decode(pbc.sequence_no,9,fc.context_name,null)) context_name9, max(decode(pbc.sequence_no,10,pbc.context_id,null)) context_id10, max(decode(pbc.sequence_no,10,fc.context_name,null)) context_name10, max(decode(pbc.sequence_no,11,pbc.context_id,null)) context_id11, max(decode(pbc.sequence_no,11,fc.context_name,null)) context_name11, max(decode(pbc.sequence_no,12,pbc.context_id,null)) context_id12, max(decode(pbc.sequence_no,12,fc.context_name,null)) context_name12, max(decode(pbc.sequence_no,13,pbc.context_id,null)) context_id13, max(decode(pbc.sequence_no,13,fc.context_name,null)) context_name13, max(decode(pbc.sequence_no,14,pbc.context_id,null)) context_id14, max(decode(pbc.sequence_no,14,fc.context_name,null)) context_name14, max(decode(pbc.sequence_no,15,pbc.context_id,null)) context_id15, max(decode(pbc.sequence_no,15,fc.context_name,null)) context_name15, max(decode(pbc.sequence_no,16,pbc.context_id,null)) context_id16, max(decode(pbc.sequence_no,16,fc.context_name,null)) context_name16, max(decode(pbc.sequence_no,17,pbc.context_id,null)) context_id17, max(decode(pbc.sequence_no,17,fc.context_name,null)) context_name17, max(decode(pbc.sequence_no,18,pbc.context_id,null)) context_id18, max(decode(pbc.sequence_no,18,fc.context_name,null)) context_name18, max(decode(pbc.sequence_no,19,pbc.context_id,null)) context_id19, max(decode(pbc.sequence_no,19,fc.context_name,null)) context_name19, max(decode(pbc.sequence_no,20,pbc.context_id,null)) context_id20, max(decode(pbc.sequence_no,20,fc.context_name,null)) context_name20, max(decode(pbc.sequence_no,21,pbc.context_id,null)) context_id21, max(decode(pbc.sequence_no,21,fc.context_name,null)) context_name21, max(decode(pbc.sequence_no,22,pbc.context_id,null)) context_id22, max(decode(pbc.sequence_no,22,fc.context_name,null)) context_name22, max(decode(pbc.sequence_no,23,pbc.context_id,null)) context_id23, max(decode(pbc.sequence_no,23,fc.context_name,null)) context_name23, max(decode(pbc.sequence_no,24,pbc.context_id,null)) context_id24, max(decode(pbc.sequence_no,24,fc.context_name,null)) context_name24, max(decode(pbc.sequence_no,25,pbc.context_id,null)) context_id25, max(decode(pbc.sequence_no,25,fc.context_name,null)) context_name25, max(decode(pbc.sequence_no,26,pbc.context_id,null)) context_id26, max(decode(pbc.sequence_no,26,fc.context_name,null)) context_name26, max(decode(pbc.sequence_no,27,pbc.context_id,null)) context_id27, max(decode(pbc.sequence_no,27,fc.context_name,null)) context_name27, max(decode(pbc.sequence_no,28,pbc.context_id,null)) context_id28, max(decode(pbc.sequence_no,28,fc.context_name,null)) context_name28, max(decode(pbc.sequence_no,29,pbc.context_id,null)) context_id29, max(decode(pbc.sequence_no,29,fc.context_name,null)) context_name29, max(decode(pbc.sequence_no,30,pbc.context_id,null)) context_id30, max(decode(pbc.sequence_no,30,fc.context_name,null)) context_name30 from (select rec.instance_name, rec.person_id, rec.payroll_rel_action_id, rec.effective_date, rec.REASON, rec.legislative_data_group_id, REC.payroll_id, rec.PROCESS_INFO_GROUP_ID, rec.EXCLUDE_TARGET_PAYROLL, rec.mass_action_line_id, rec.SOURCE_PR_ID, rec.SOURCE_ASG_ID, rec.SOURCE_TRM_ID, rec.TARGET_PR_ID, rec.TARGET_ASG_ID, rec.TARGET_TRM_ID, rec.TARGET_ASG_SD, rec.TARGET_PR_SD, rec.TARGET_PR_ED, rec.SOURCE_ASG_SD, rec.SOURCE_PR_SD, rec.SOURCE_PR_ED, NVL((select max(payroll_rel_action_id) from pay_payroll_rel_actions where source_action_id = rec.TARGET_LAID), rec.TARGET_LAID) TARGET_LAID, 0 TARGET_COPY_LAID, NVL((select max(payroll_rel_action_id) from pay_payroll_rel_actions where source_action_id = rec.SOURCE_LAID), rec.SOURCE_LAID) SOURCE_LAID, NVL((select max(payroll_rel_action_id) from pay_payroll_rel_actions where source_action_id = rec.SOURCE_LAID_V), rec.SOURCE_LAID_V) SOURCE_LAID_V, pdb.defined_balance_id SOURCE_DEFBAL_ID, (select max(pbt.balance_type_id) from pay_balance_types_vl pbt, per_legislative_data_groups ldg where (ldg.legislative_data_group_id = pbt.legislative_data_group_id or ldg.legislation_code = pbt.legislation_code) and nvl(ldg.legislative_data_group_id, rec.legislative_data_group_id) = rec.legislative_data_group_id and ldg.legislation_code || '-' || pbt.base_balance_name = pogs.obj_information2) TARGET_BAL_ID, (select max(pet.element_type_id) from pay_element_types_vl pet, per_legislative_data_groups ldg where (ldg.legislative_data_group_id = pet.legislative_data_group_id or ldg.legislation_code = pet.legislation_code) and exists (select element_type_id from pay_element_links_f el where el.element_type_id = pet.element_type_id) and nvl(ldg.legislative_data_group_id, rec.legislative_data_group_id) = rec.legislative_data_group_id and ldg.legislation_code || '-' || pet.element_name = pogs.obj_information3) pre_target_element_id, pbt.balance_name SOURCE_BAL_NAME, pbt.balance_type_id SOURCE_BALANCETYPE_ID, pdu.dimension_name SOURCE_DIM_NAME, pdu.balance_dimension_id SOURCE_DIM_ID, pey.base_element_name target_element_name, pey.element_type_id target_element_type_id, rel_pey.base_element_name rel_target_element_name, rel_pey.element_type_id rel_target_element_type_id, pay_bal_copy_pkg.get_dimension_period_date(pbd.balance_dimension_id,rec.effective_date,rec.legislative_data_group_id,rec.TARGET_PSU_ID,rec.payroll_id,1) TARGET_DIM_SD, pay_bal_copy_pkg.get_dimension_period_date(pbd.balance_dimension_id,rec.effective_date,rec.legislative_data_group_id,rec.TARGET_PSU_ID,rec.payroll_id,0) TARGET_DIM_ED, pay_bal_copy_pkg.get_dimension_period_date(pbd.balance_dimension_id,rec.effective_date,rec.legislative_data_group_id,rec.SOURCE_PSU_ID,ppa.payroll_id,1) SOURCE_DIM_SD, pay_bal_copy_pkg.get_dimension_period_date(pbd.balance_dimension_id,rec.effective_date,rec.legislative_data_group_id,rec.SOURCE_PSU_ID,ppa.payroll_id,0) SOURCE_DIM_ED, pbd.dimension_level, pbd.period_type, pog.base_object_group_name from (select fpara.instance_name, fpara.payroll_rel_action_id, fpara.effective_date, fpara.REASON, fpara.legislative_data_group_id, fpara.payroll_id, fpara.PROCESS_INFORMATION_GROUP PROCESS_INFO_GROUP_ID, fpara.EXCLUDE_TARGET_PAYROLL EXCLUDE_TARGET_PAYROLL, fpara.mass_action_line_id mass_action_line_id, asg_source.payroll_relationship_id SOURCE_PR_ID, asg_source.relationship_group_id SOURCE_ASG_ID, asg_source.PARENT_rel_group_id SOURCE_TRM_ID, prl_source.payroll_stat_unit_id SOURCE_PSU_ID, asg_target.payroll_relationship_id TARGET_PR_ID, asg_target.relationship_group_id TARGET_ASG_ID, asg_target.PARENT_rel_group_id TARGET_TRM_ID, prl_target.payroll_stat_unit_id TARGET_PSU_ID, asg_target.start_Date TARGET_ASG_SD, prl_target.start_date TARGET_PR_SD, prl_target.end_date TARGET_PR_ED, asg_source.start_Date SOURCE_ASG_SD, prl_source.start_date SOURCE_PR_SD, prl_source.end_date SOURCE_PR_ED, (select max(pra.payroll_rel_action_id) from pay_payroll_rel_actions pra, pay_payroll_actions ppa where pra.payroll_relationship_id= asg_target.payroll_relationship_id and pra.payroll_rel_action_id != fpara.payroll_rel_action_id and ppa.payroll_action_id = pra.payroll_action_id and pra.action_status = 'C' and ppa.action_type in ('I','B','R','Q') and pra.source_action_id is null) TARGET_LAID, 0 TARGET_COPY_LAID, (select max(pra.payroll_rel_action_id) from pay_payroll_rel_actions pra, pay_payroll_actions ppa where pra.payroll_relationship_id= asg_source.payroll_relationship_id and ppa.payroll_action_id = pra.payroll_action_id and pra.payroll_rel_action_id != fpara.payroll_rel_action_id and pra.action_status = 'C' and ppa.action_type in ('I','B','R','Q') and pra.source_action_id is null) SOURCE_LAID, (select max(pra.payroll_rel_action_id) from pay_payroll_rel_actions pra, pay_payroll_actions ppa where pra.payroll_relationship_id= asg_source.payroll_relationship_id and ppa.payroll_action_id = pra.payroll_action_id and pra.payroll_rel_action_id != fpara.payroll_rel_action_id and pra.action_status = 'C' and ppa.action_type in ('I','B','R','Q','V') and pra.source_action_id is null) SOURCE_LAID_V, prl_target.person_id from (select fpara.instance_name, fpara.payroll_relationship_id, fpara.payroll_rel_action_id, fpara.effective_date,fpara.REASON,fpara.legislative_data_group_id, to_number(nvl(pmal.PVAL003,fpara.SOURCE_ASSIGNMENT)) SOURCE_ASSIGNMENT, to_number(nvl(pmal.PVAL062,fpara.TARGET_ASSIGNMENT)) TARGET_ASSIGNMENT, pmal.mass_action_line_id mass_action_line_id, PROCESS_INFORMATION_GROUP, EXCLUDE_TARGET_PAYROLL, payroll_id from (select pfi.instance_name, pra.payroll_relationship_id, pra.payroll_rel_action_id, ppa.effective_date, pfi.legislative_data_group_id,ppa.payroll_id, max(to_number(decode(pfp.base_flow_parameter_name,'SOURCE_ASSIGNMENT',pfpv.flow_param_value,null))) SOURCE_ASSIGNMENT, max(to_number(decode(pfp.base_flow_parameter_name,'TARGET_ASSIGNMENT',pfpv.flow_param_value,null))) TARGET_ASSIGNMENT, max(decode(pfp.base_flow_parameter_name,'REASON',pfpv.flow_param_value,null)) REASON, max(to_number(decode(pfp.base_flow_parameter_name,'MASS_TRANSFER_PROCESS',pfpv.flow_param_value,null))) MASS_TRANSFER_PROCESS, max(to_number(decode(pfp.base_flow_parameter_name,'PROCESS_INFORMATION_GROUP',pfpv.flow_param_value,null))) PROCESS_INFORMATION_GROUP, max(decode(pfp.base_flow_parameter_name,'EXCLUDE_TARGET_ASSIGNMENT_WITH_PAYROLL_RESULT',pfpv.flow_param_value,null)) EXCLUDE_TARGET_PAYROLL from pay_payroll_Actions ppa, pay_requests prq, pay_flow_instances pfi, pay_payroll_rel_actions pra, pay_flow_param_values pfpv, pay_flow_parameters pfp where ppa.action_type='B' and ppa.pay_request_id = prq.pay_request_id and prq.flow_instance_id = pfi.flow_instance_id and ppa.payroll_action_id = pra.payroll_action_id and pfpv.flow_instance_id = pfi.flow_instance_id and pfpv.base_flow_parameter_id = pfp.flow_parameter_id and pfp.base_flow_parameter_name in ('MASS_TRANSFER_PROCESS','TARGET_ASSIGNMENT','SOURCE_ASSIGNMENT','PROCESS_INFORMATION_GROUP', 'REASON', 'EXCLUDE_TARGET_ASSIGNMENT_WITH_PAYROLL_RESULT') group by pfi.instance_name, ppa.effective_date , pra.payroll_relationship_id, pra.payroll_rel_action_id , pfi.legislative_data_group_id,ppa.payroll_id ) fpara, per_mass_action_header pmah, per_mass_action_lines pmal where fpara.MASS_TRANSFER_PROCESS = pmah.mass_action_header_id (+) and pmah.mass_action_header_id = pmal.mass_action_header_id (+) ) fpara, pay_rel_groups_dn asg_source, pay_rel_groups_dn asg_target, pay_pay_relationships_dn prl_target, pay_pay_relationships_dn prl_source where fpara.SOURCE_ASSIGNMENT = asg_source.assignment_id and fpara.TARGET_ASSIGNMENT = asg_target.assignment_id and prl_target.payroll_relationship_id = asg_target.payroll_relationship_id and prl_source.payroll_relationship_id = asg_source.payroll_relationship_id and fpara.payroll_relationship_id = asg_target.payroll_relationship_id ) rec, pay_object_group_store pogs, pay_object_groups pog, pay_defined_balances pdb, pay_balance_types_vl pbt, pay_dimension_usages_vl pdu, per_legislative_data_groups ldg, pay_balance_dimensions pbd, pay_payroll_rel_actions pra, pay_payroll_actions ppa, pay_element_types_f pey, pay_element_types_f rel_pey where pogs.object_group_id = pog.object_group_id and pog.object_group_id = rec.PROCESS_INFO_GROUP_ID and pogs.OBJ_INFORMATION1 = substr(ldg.legislation_code||'-'||pbt.base_balance_name||'-'||pbd.base_db_item_suffix, 1, 150) and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_dimension_id = pdu.balance_dimension_id and pbd.balance_dimension_id = pdu.balance_dimension_id and ldg.legislative_data_group_id = rec.legislative_data_group_id and ldg.legislation_code = pdu.legislation_code and nvl(pogs.legislative_data_group_id, rec.legislative_data_group_id) = rec.legislative_data_group_id and pra.payroll_rel_action_id = rec.SOURCE_LAID and pra.payroll_action_id = ppa.payroll_action_id and nvl(pey.legislative_data_group_id, rec.legislative_data_group_id) = rec.legislative_data_group_id and nvl(rel_pey.legislative_data_group_id, rec.legislative_data_group_id) = rec.legislative_data_group_id and pey.base_element_name = 'USER_BAL_ADJ_'|| pbd.dimension_level ||'_ID'||rec.PROCESS_INFO_GROUP_ID and rel_pey.base_element_name = 'USER_BAL_ADJ_REL_ID'||rec.PROCESS_INFO_GROUP_ID ) rec, pay_dim_context_usages pbc, ff_contexts fc where pbc.balance_dimension_id = rec.SOURCE_DIM_ID and fc.context_id = pbc.context_id group by rec.instance_name, rec.person_id, rec.payroll_rel_action_id, rec.effective_date, rec.REASON, rec.legislative_data_group_id, REC.PAYROLL_ID, rec.PROCESS_INFO_GROUP_ID, rec.EXCLUDE_TARGET_PAYROLL, rec.mass_action_line_id, rec.base_object_group_name, rec.SOURCE_PR_ID, rec.SOURCE_ASG_ID, rec.SOURCE_TRM_ID, rec.TARGET_PR_ID, rec.TARGET_ASG_ID, rec.TARGET_TRM_ID, rec.TARGET_ASG_SD, rec.TARGET_PR_SD, rec.TARGET_PR_ED, rec.SOURCE_ASG_SD, rec.SOURCE_PR_SD, rec.SOURCE_PR_ED, rec.TARGET_LAID, rec.TARGET_COPY_LAID, rec.SOURCE_LAID, rec.SOURCE_LAID_V, rec.SOURCE_DEFBAL_ID, rec.TARGET_BAL_ID, rec.pre_target_element_id, rec.SOURCE_BAL_NAME, rec.SOURCE_BALANCETYPE_ID, rec.SOURCE_DIM_NAME, rec.TARGET_DIM_SD, rec.TARGET_DIM_ED, rec.SOURCE_DIM_SD, rec.SOURCE_DIM_ED, rec.dimension_level, rec.period_type, rec.target_element_name, rec.target_element_type_id, rec.rel_target_element_name, rec.rel_target_element_type_id ) rec, table(pay_balance_utility.get_action_contexts(rec.SOURCE_PR_ID, least((select max(ptp.start_date) from pay_payroll_actions ppa, pay_payroll_rel_actions ppra, pay_time_periods ptp where ppa.payroll_action_id = ppra.payroll_action_id and ppra.payroll_rel_action_id = rec.SOURCE_LAID and ppa.earn_time_period_id = ptp.time_period_id), rec.source_effective_date), greatest((select max(ptp.end_date) from pay_payroll_actions ppa, pay_payroll_rel_actions ppra, pay_time_periods ptp where ppa.payroll_action_id = ppra.payroll_action_id and ppra.payroll_rel_action_id = rec.SOURCE_LAID and ppa.earn_time_period_id = ptp.time_period_id), rec.source_effective_date), rec.legislative_data_group_id,null, pay_number_tab_t(rec.context_id1,rec.context_id2,rec.context_id3,rec.context_id4,rec.context_id5,rec.context_id6,rec.context_id7,rec.context_id8,rec.context_id9,rec.context_id10, rec.context_id11,rec.context_id12,rec.context_id13,rec.context_id14,rec.context_id15,rec.context_id16,rec.context_id17,rec.context_id18,rec.context_id19,rec.context_id20, rec.context_id21,rec.context_id22,rec.context_id23,rec.context_id24,rec.context_id25,rec.context_id26,rec.context_id27,rec.context_id28,rec.context_id29,(select context_id from ff_contexts where context_name = 'PAYROLL_RELATIONSHIP_ID')), null,null)) pcv where rec.source_pr_id = pcv.payroll_relationship_id (+) ) rec, pay_balance_types pbt where pbt.balance_type_id = rec.TARGET_BAL_ID