This sql query will be useful to extract the approval limits that are changed for any employee’s job/assignments supervisor change
SQL Query
SELECT
pbg.name business_group_name,
papf.employee_number,
papf.person_id,
cur.effective_start_date new_assignment_effective_date,
papf.attribute30 oracle_old_employee_number,
hr_person_type_usage_info.get_user_person_type(
p_effective_date => papf.effective_start_date,
p_person_id => papf.person_id
) person_type,
papf.first_name,
papf.middle_names,
papf.last_name,
papf.full_name,
papf.email_address,
pre.effective_start_date old_asg_effective_start_date,
cur_pj.name new_job,
pre_pj.name old_job,
appr_assign1.control_function_name new_control_function_name,
appr_assign2.control_function_name old_control_function_name,
appr_assign1.control_group_name new_control_group_name,
appr_assign2.control_group_name old_control_group_name,
appr_assign1.amount_limit new_amount_limit,
appr_assign2.amount_limit old_amount_limit,
cur_pj.approval_authority new_approval_authority,
pre_pj.approval_authority old_approval_authority,
(
SELECT
gl.name
FROM
apps.gl_ledgers gl
WHERE
gl.ledger_id = cur.set_of_books_id
) new_ledgers_name,
(
SELECT
concatenated_segments
FROM
apps.gl_code_combinations_kfv
WHERE
code_combination_id = cur.default_code_comb_id
) new_default_account,
supvr.full_name new_supervisor_full_name,
supvr_pre.full_name old_supervisor_full_name,
appr_assign3.control_function_name new_supr_approval_func_name,
(
SELECT
pcf.control_function_name
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
pre.effective_end_date BETWEEN nvl(
ppca.start_date,
pre.effective_end_date
) AND nvl(
ppca.end_date,
pre.effective_end_date
)
AND
hou.business_group_id = supvr_assign_pre.business_group_id
AND
ppca.job_id = supvr_assign_pre.job_id
) old_supr_approval_func_name,
appr_assign3.control_group_name new_supr_approval_group_name,
(
SELECT
pcga.control_group_name
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
pre.effective_end_date BETWEEN nvl(
ppca.start_date,
pre.effective_end_date
) AND nvl(
ppca.end_date,
pre.effective_end_date
)
AND
hou.business_group_id = supvr_assign_pre.business_group_id
AND
ppca.job_id = supvr_assign_pre.job_id
) old_supr_approval_group_name,
appr_assign3.amount_limit new_supr_amount_limit,
(
SELECT
pcr.amount_limit
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
pre.effective_end_date BETWEEN nvl(
ppca.start_date,
pre.effective_end_date
) AND nvl(
ppca.end_date,
pre.effective_end_date
)
AND
hou.business_group_id = supvr_assign_pre.business_group_id
AND
ppca.job_id = supvr_assign_pre.job_id
) old_supr_amount_limit,
supvr_assign_pj.approval_authority new_supvr_approval_authority,
supvr_assign_pre_pj.approval_authority old_supvr_approval_authority,
supvr_assign_pj.name new_supvr_job,
supvr_assign_pre_pj.name old_supvr_job,
supvr.email_address new_supervisor_email_address,
cur.creation_date new_asg_creation_date,
cur.last_update_date new_asg_last_update_date,
(
SELECT
fu.user_name
FROM
apps.fnd_user fu
WHERE
fu.user_id = cur.created_by
) new_asg_created_by,
(
SELECT
fu.user_name
FROM
apps.fnd_user fu
WHERE
fu.user_id = cur.last_updated_by
) new_asg_last_updated_by,
fu.user_name employee_user_name,
fu.start_date employee_user_start_date,
fu.end_date employee_user_end_date,
supvsr_fu.user_name new_supervisor_user_name,
supvsr_fu.start_date new_supervisor_user_start_date,
supvsr_fu.end_date new_supervisor_user_end_date
FROM
apps.per_all_assignments_f cur,
apps.per_all_assignments_f pre,
apps.per_all_people_f papf,
apps.per_all_people_f supvr,
apps.per_all_people_f supvr_pre,
apps.per_all_assignments_f supvr_assign,
apps.per_all_assignments_f supvr_assign_pre,
(
SELECT
hou.business_group_id,
hou.name operating_unit,
ppca.job_id,
pcf.control_function_name,
pcga.control_group_name,
TO_CHAR(ppca.start_date,’DD-MON-YY’) start_date,
TO_CHAR(ppca.end_date,’DD-MON-YY’) end_date,
pcr.amount_limit
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
nvl(ppca.end_date,SYSDATE) >= SYSDATE
) appr_assign1,
(
SELECT
hou.business_group_id,
hou.name operating_unit,
ppca.job_id,
pcf.control_function_name,
pcga.control_group_name,
TO_CHAR(ppca.start_date,’DD-MON-YY’) start_date,
TO_CHAR(ppca.end_date,’DD-MON-YY’) end_date,
pcr.amount_limit
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
nvl(ppca.end_date,SYSDATE) >= SYSDATE
) appr_assign2,
(
SELECT
hou.business_group_id,
hou.name operating_unit,
ppca.job_id,
pcf.control_function_name,
pcga.control_group_name,
TO_CHAR(ppca.start_date,’DD-MON-YY’) start_date,
TO_CHAR(ppca.end_date,’DD-MON-YY’) end_date,
pcr.amount_limit
FROM
apps.po_position_controls_all ppca,
apps.po_control_functions pcf,
apps.hr_operating_units hou,
apps.po_control_groups_all pcga,
apps.po_control_rules pcr
WHERE
ppca.control_function_id = pcf.control_function_id
AND
ppca.org_id = hou.organization_id
AND
pcga.control_group_id = ppca.control_group_id
AND
pcga.control_group_id = pcr.control_group_id
AND
pcr.control_group_id = ppca.control_group_id
AND
pcr.object_code = ‘DOCUMENT_TOTAL’
AND
pcf.control_function_name = ‘Approve Purchase Requisitions’
AND
nvl(ppca.end_date,SYSDATE) >= SYSDATE
) appr_assign3,
apps.per_jobs cur_pj,
apps.per_jobs pre_pj,
apps.per_business_groups pbg,
apps.fnd_user fu,
apps.fnd_user supvsr_fu,
apps.per_jobs supvr_assign_pj,
apps.per_jobs supvr_assign_pre_pj
WHERE
1 = 1
AND
cur.effective_start_date = (
SELECT
MAX(effective_start_date) latest_eff_st_date
FROM
apps.per_all_assignments_f latest_assign
WHERE
latest_assign.person_id = cur.person_id
AND
latest_assign.assignment_id = cur.assignment_id
AND
latest_assign.assignment_type = ‘E’
AND
latest_assign.primary_flag = ‘Y’
AND
trunc(last_update_date) >= trunc(SYSDATE – 30)
)
AND
cur.person_id = pre.person_id
AND
cur.assignment_id = pre.assignment_id
AND
cur.assignment_type = ‘E’
AND
cur.primary_flag = ‘Y’
AND
pre.assignment_type = ‘E’
AND
pre.primary_flag = ‘Y’
AND
trunc(pre.effective_end_date) = trunc(cur.effective_start_date – 1)
–and papf.employee_number = ‘134580’
AND
cur.person_id = papf.person_id
AND
cur.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND
cur.job_id = cur_pj.job_id
AND
cur.effective_start_date BETWEEN cur_pj.date_from AND nvl(
cur_pj.date_to,
cur.effective_start_date
)
AND
pre.job_id = pre_pj.job_id
AND
pre.effective_start_date BETWEEN pre_pj.date_from AND nvl(
pre_pj.date_to,
cur.effective_start_date
)
AND (
cur.job_id <> pre.job_id
OR
cur.supervisor_id <> pre.supervisor_id
) AND
cur.job_id = appr_assign1.job_id (+)
AND
cur.business_group_id = appr_assign1.business_group_id (+)
AND
pre.job_id = appr_assign2.job_id (+)
AND
pre.business_group_id = appr_assign2.business_group_id (+)
AND
papf.business_group_id = pbg.business_group_id
AND
papf.person_id = fu.employee_id (+)
AND
cur.supervisor_id = supvsr_fu.employee_id (+)
AND
cur.supervisor_id = supvr.person_id
AND
cur.effective_start_date BETWEEN supvr.effective_start_date AND supvr.effective_end_date
AND
pre.supervisor_id = supvr_pre.person_id
AND
pre.effective_start_date BETWEEN supvr_pre.effective_start_date AND supvr_pre.effective_end_date
AND
supvr_assign.job_id = appr_assign3.job_id (+)
AND
supvr_assign.business_group_id = appr_assign3.business_group_id (+)
AND
cur.supervisor_id = supvr_assign.person_id
AND
supvr_assign.assignment_type = ‘E’
AND
supvr_assign.primary_flag = ‘Y’
AND
cur.effective_start_date BETWEEN supvr_assign.effective_start_date AND supvr_assign.effective_end_date
AND
pre.supervisor_id = supvr_assign_pre.person_id
AND
supvr_assign_pre.assignment_type = ‘E’
AND
supvr_assign_pre.primary_flag = ‘Y’
AND
pre.effective_end_date BETWEEN supvr_assign_pre.effective_start_date AND supvr_assign_pre.effective_end_date
AND
supvr_assign.job_id = supvr_assign_pj.job_id
AND
supvr_assign.effective_start_date BETWEEN supvr_assign_pj.date_from AND nvl(
supvr_assign_pj.date_to,
supvr_assign.effective_start_date
)
AND
supvr_assign_pre.job_id = supvr_assign_pre_pj.job_id
AND
supvr_assign_pre.effective_start_date BETWEEN supvr_assign_pre_pj.date_from AND nvl(
supvr_assign_pre_pj.date_to,
supvr_assign_pre.effective_start_date
)
