Query To get Run Results information

SELECT INITCAP(ppf.last_name
  ||','
  ||ppf.first_name) name,
  ety.element_name,
  to_number(rrv.result_value) commission
FROM per_all_people_f ppf,
  apps.per_assignments_f paf,
  apps.pay_assignment_actions pas,
  apps.pay_payroll_actions ppa,
  apps.pay_run_results rr,
  apps.pay_run_result_values rrv,
  apps.pay_element_types_f ety,
  apps.pay_input_values_f I
WHERE ppf.person_id          = paf.person_id
AND paf.assignment_id        = pas.assignment_id
AND pas.assignment_action_id = rr.assignment_action_id
AND ppa.payroll_action_id    = pas.payroll_action_id
AND rr.element_type_id       = ety.element_type_id
AND i.element_type_id        = ety.element_type_id
AND rrv.run_result_id        = rr.run_result_id
AND rrv.input_value_id       = i.input_value_id
AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date
AND sysdate BETWEEN ety.effective_start_date AND ety.effective_end_date
AND sysdate BETWEEN I.effective_start_date AND I.effective_end_date
AND i.name           = 'Pay Value'
AND ety.element_name = 'Salary' ;

To Reset Oracle Apps User id and Password From Backend

DECLARE
  v_user_name    VARCHAR2(30):=upper('HCM'); --HCM is username of my oracle Apps.
  v_new_password VARCHAR2(30):='welcome1';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, newpassword => v_new_password );
  IF v_status =true THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;
/

Query to Find Oracle Alerts

The following query finds all enabled custom alerts. You can comment out the very last two lines (enabled_flag and created_by) to display all both enabled and disabled alerts.



SELECT created_by,
  enabled_flag,
  application_id,
  alert_id,
  alert_name,
  start_date_active,
  description,
  sql_statement_text
FROM apps.alr_alerts
WHERE 1          =1
AND created_by  <> 1   -- Shows only custom alerts
AND enabled_flag = 'Y';-- Shows only enabled alerts

To find Screen Entry Value from Element and Element Link Definition

SELECT peev.screen_entry_value
FROM pay_element_types_f pet,
pay_input_values_f piv,
--pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE 1 =1
and pet.element_type_id = piv.element_type_id
--AND piv.element_type_id = pel.element_type_id
-- AND pel.element_link_id = pee.element_link_id
and pee.element_entry_id=peev.element_entry_id
and pet.element_type_id = pee.element_type_id
and piv.input_value_id=peev.input_value_id
AND pee.assignment_id = p_assignment_id
AND piv.name = p_input_name
AND pet.element_name = p_element_name
AND p_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
and p_start_date between piv.effective_start_date and piv.effective_end_date
--AND p_start_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND p_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND p_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date;