APPS.AP_WEB_AUDIT_QUEUE_UTILS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 74

  SELECT count(1) INTO l_report_weight FROM ap_expense_report_lines_all
  WHERE report_header_id = p_report_header_id
    AND (itemization_parent_id is null OR itemization_parent_id <> -1);

Line: 122

      SELECT auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;

Line: 127

    SELECT count(1) INTO l_scratch
    FROM fnd_user u,
         ap_expense_report_headers_all exp
    WHERE u.user_id <> -1
    AND   u.user_id = p_auditor_id
-- making sure auditor does not audit expense report owned by her OR filed by her
    AND   exp.report_header_id = p_report_header_id
    AND  (nvl(u.employee_id , -1) = nvl(exp.employee_id , -2) OR nvl(u.user_id , -1) = nvl(exp.created_by , -2));

Line: 144

      INSERT INTO ap_aud_queues(expense_report_id,
				auditor_id,
  				report_weight,
  				creation_date,
  				created_by,
  				last_update_login,
  				last_update_date,
  				last_updated_by)
      VALUES (			p_report_header_id,
				p_auditor_id,
  				report_weight(p_report_header_id),
  				sysdate,
  				FND_GLOBAL.USER_ID,
  				null,
  				sysdate,
				FND_GLOBAL.USER_ID);

Line: 203

    SELECT h.last_audited_by INTO l_assignee
    FROM AP_EXPENSE_REPORT_HEADERS_ALL h
    WHERE h.report_header_id = p_report_header_id;

Line: 280

  CURSOR c1 IS SELECT expense_report_id
    FROM ap_aud_queues
    WHERE auditor_id = p_auditor_id order by last_update_date desc;

Line: 332

    SELECT s.current_total_workload,
           w.workload_percent
    FROM
           ap_aud_auditors a,
           ap_aud_queue_summaries_v s,
           ap_aud_workloads w
    WHERE  a.auditor_id = p_auditor_id
    AND    a.auditor_id = s.auditor_id(+)
    AND    a.auditor_id = w.auditor_id
    AND    w.start_date <= sysdate
    AND    sysdate < nvl(w.end_date,sysdate+1);

Line: 372

  SELECT ORG_ID INTO l_org_id FROM ap_expense_report_headers_all
  WHERE report_header_id = p_report_header_id;

Line: 407

    SELECT a.auditor_id
    FROM ap_aud_auditors a,
         per_organization_list per,
         hr_organization_information oi,
         fnd_user u,
         ap_expense_report_headers_all exp
    WHERE a.security_profile_id = per.security_profile_id
    AND   a.auditor_id <> -1
    AND   per.organization_id = oi.organization_id
    AND   oi.org_information_context = 'CLASS'
    AND   oi.org_information1 = 'OPERATING_UNIT'
    AND   oi.organization_id = p_org_id
-- making sure auditor does not audit expense report owned by her
    AND   exp.report_header_id = p_report_header_id
    AND   a.auditor_id = u.user_id
    AND  nvl(u.employee_id , -1) <> nvl(exp.employee_id , -2)
-- making sure auditor does not audit expense report filed by her
    AND  nvl(u.user_id , -1) <> nvl(exp.created_by , -2)
UNION
SELECT a.auditor_id
    FROM ap_aud_auditors a,
         per_security_profiles sec,
         fnd_user u,
         ap_expense_report_headers_all exp
    WHERE a.security_profile_id = sec.security_profile_id
    AND sec.view_all_organizations_flag = 'Y'
    AND   a.auditor_id <> -1
-- making sure auditor does not audit expense report owned by her
    AND   exp.report_header_id = p_report_header_id
    AND   a.auditor_id = u.user_id
    AND  nvl(u.employee_id , -1) <> nvl(exp.employee_id , -2)
-- making sure auditor does not audit expense report filed by her
    AND  nvl(u.user_id , -1) <> nvl(exp.created_by , -2);

Line: 470

    SELECT expense_report_id, auditor_id
    FROM ap_aud_queues WHERE
    expense_report_id NOT IN
    ( SELECT q.expense_report_id
      FROM ap_aud_queues q,
           ap_expense_report_headers_all e,
           ap_aud_auditors a,
           per_organization_list per,
           hr_organization_information oi
      WHERE q.auditor_id = a.auditor_id
      AND   q.expense_report_id = e.report_header_id
      AND   a.security_profile_id = per.security_profile_id
      AND   per.organization_id = oi.organization_id
      AND   oi.org_information_context = 'CLASS'
      AND   oi.org_information1 = 'OPERATING_UNIT'
      AND   e.org_id = oi.organization_id);

Line: 532

    SELECT h.last_audited_by INTO l_parent_auditor_id
    FROM AP_EXPENSE_REPORT_HEADERS_ALL h
    WHERE h.report_header_id = p_report_header_id;

Line: 570

         	  SELECT count(1) INTO l_num FROM ap_aud_queues WHERE expense_report_id = p_report_header_id AND auditor_id = l_current_auditor_id;

Line: 572

              SELECT report_weight INTO l_num FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;

Line: 617

  SELECT shortpay_parent_id
  INTO   l_shortpay_id
  FROM   ap_expense_report_headers_all
  WHERE  report_header_id = p_report_id;

Line: 650

  SELECT last_audited_by
  INTO   l_last_audited_by
  FROM   ap_expense_report_headers_all
  WHERE  report_header_id = p_report_id;

Line: 738

    UPDATE ap_aud_queues SET 	auditor_id = p_next_auditor_id,
   				last_update_login = null,
    				last_update_date = sysdate,
    				last_updated_by = FND_GLOBAL.USER_ID
    WHERE auditor_id = p_auditor_id AND expense_report_id = p_expense_report_id;

Line: 822

  DELETE FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;

Line: 853

  SELECT auditor_id INTO p_auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;

Line: 928

  CURSOR c1 IS SELECT auditor_id, security_profile_id
    FROM ap_aud_auditors
    WHERE auditor_id <> -1 AND security_profile_id IS NOT NULL;

Line: 937

    SELECT count(1) INTO l_num FROM
    (SELECT
    		u.user_id,
    		FND_PROFILE.VALUE_SPECIFIC('XLA_MO_SECURITY_PROFILE_LEVEL', u.user_id, r.responsibility_id, 200/*SQLAP*/) security_profile_id
    	FROM
    		FND_USER u,
    		FND_USER_RESP_GROUPS g,
    		FND_RESPONSIBILITY r,
    		FND_FORM_FUNCTIONS f
    	WHERE
		u.user_id = l_auditor_id
    		AND u.user_id = g.user_id
    		AND g.responsibility_id = r.responsibility_id
    		AND AP_WEB_AUDIT_QUEUE_UTILS.IS_FUNCTION_ON_MENU(r.menu_id, f.function_id, 'Y') = 'Y'
    		AND f.function_name = 'OIE_AUD_AUDIT') sp
    WHERE sp.security_profile_id = l_security_profile_id;

Line: 955

      UPDATE ap_aud_auditors SET security_profile_id = null WHERE auditor_id = l_auditor_id;