1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

AME Dynamic Script to Select Immediate Supervisor for Approval Group

Discussion in 'Oracle Apps Technical' started by tibrahim, Sep 27, 2013.

  1. tibrahim

    tibrahim Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Hello All,

    Please, I need an AME dynamic group approval script to enable me send approval request to the immediate supervisor of the main appraiser. I was using the static approval but it is sending approvals to all the employees in the supervisory hierarchy above the main appraiser despite selecting "Require approvals up to the first superior" under "Action" of "Supervisory Level" "Action Type".

    So instead of using Action Type of Supervisory Level, I will like to use Approval-group Chain of Authority where I would apply the script. Thank you.

    -Bayo
     
  2. Farhan Iqbal

    Farhan Iqbal Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    UAE
    ---------------------------------------------Use this function--------------------------------------------------
    create or replace function XX_get_supervisor_ame (p_transaction_id in number ) return varchar2 is

    v_emp_grade varchar2(10);

    v_submitter_id number;
    v_selected_id number;
    v_selected_supervisor number;

    v_emp_cat varchar2(10);

    v_return_val varchar2(30);


    /**** get the submitter_id*********************************/
    cursor get_submitter_id is
    select papf.person_id
    from per_all_people_f papf,hr_api_transactions trx
    where papf.person_id = trx.CREATOR_PERSON_ID
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and trx.transaction_id = p_transaction_id;

    /**** get the submitter_id*********************************/
    cursor get_selected_id is
    select papf.person_id
    from per_all_people_f papf,hr_api_transactions trx
    where papf.person_id = trx.SELECTED_PERSON_ID
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and trx.transaction_id = p_transaction_id;

    begin


    open get_submitter_id;
    fetch get_submitter_id into v_submitter_id;

    open get_selected_id;
    fetch get_selected_id into v_selected_id;

    select substr(pg.segment1,1,instr(pg.segment1,'B')-1) grade,substr(pg.segment1,instr(pg.segment1,'B'),length(pg.segment1))
    into v_emp_grade,v_emp_cat
    from per_all_people_f papf,per_all_assignments_f paaf,per_all_people_f sup,PAY_PEOPLE_GROUPS pg
    where papf.person_id = paaf.person_id
    and sup.PERSON_ID = paaf.SUPERVISOR_ID
    and pg.people_group_id = paaf.people_group_id
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and sysdate between sup.effective_start_date and sup.effective_end_date
    and sysdate between paaf.effective_start_date and paaf.effective_end_date
    and papf.person_id = v_selected_id
    and papf.business_group_id = 1703;

    select sup.person_id
    into v_selected_supervisor
    from per_all_people_f papf,per_all_assignments_f paaf,per_all_people_f sup
    where papf.person_id = paaf.person_id
    and sup.PERSON_ID = paaf.SUPERVISOR_ID
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and sysdate between sup.effective_start_date and sup.effective_end_date
    and sysdate between paaf.effective_start_date and paaf.effective_end_date
    and papf.PERSON_ID = ( select papf.person_id
    from per_all_people_f papf,hr_api_transactions trx
    where papf.person_id = trx.selected_person_id
    and sysdate between papf.effective_start_date and papf.effective_end_date
    and trx.transaction_id = p_transaction_id
    );

    if v_emp_grade is not null then

    if to_number (v_emp_grade) >= 8 and (v_submitter_id <> v_selected_id) then
    v_selected_supervisor := null;
    end if;

    end if;
    ------------------------------------------------------------------