APPS.AP_WEB_HOLDS_WF SQL Statements

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

Line: 63

    select 'Y'
    into   l_is_holds_rule_setup
    from   AP_AUD_RULE_SETS rs,
           AP_AUD_RULE_ASSIGNMENTS_ALL rsa
    where  rsa.org_id = p_org_id
    and    rsa.rule_set_id = rs.rule_set_id
    and    rs.rule_set_type = C_HOLD_RULE
    and    TRUNC(p_report_submitted_date)
           BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
           AND     TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
    and    rownum = 1;

Line: 130

    select report_submitted_date
    into   l_report_submitted_date
    from   ap_expense_report_headers
    where  report_header_id = l_expense_report_id;

Line: 168

  select to_char(sysdate, 'DD-MON-RRRR HH:MI:SS')
  into   l_timestamp
  from   dual;

Line: 186

  select 'Y'
  into   l_event_key_exists
  from   wf_items
  where  item_type = C_APWHOLDS
  and    item_key = p_event_key
  and    rownum = 1;

Line: 378

  select created_by,
         employee_id,
         invoice_num,
         flex_concatenated,
         to_char(nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0),
                         FND_CURRENCY.Get_Format_Mask(default_currency_code,22))||' '||default_currency_code,
         description,
         report_submitted_date,
         holding_report_header_id
  into   l_created_by,
         l_employee_id,
         l_invoice_num,
         l_cost_center,
         l_total,
         l_purpose,
         l_report_submitted_date,
         l_holding_report_header_id
  from   ap_expense_report_headers
  where  report_header_id = l_expense_report_id;

Line: 403

      select invoice_num
      into   l_holding_invoice_num
      from   ap_expense_report_headers
      where  report_header_id = l_holding_report_header_id;

Line: 660

    select 'Y'
    into   l_any_holds_pending
    from   ap_expense_report_headers
    where  report_header_id <> holding_report_header_id
    and    holding_report_header_id = l_report_header_id
    and    rownum = 1;

Line: 698

    select rs.hold_code
    into   l_hold_code
    from   AP_AUD_RULE_SETS rs,
           AP_AUD_RULE_ASSIGNMENTS_ALL rsa
    where  rsa.org_id = p_org_id
    and    rsa.rule_set_id = rs.rule_set_id
    and    rs.rule_set_type = C_HOLD_RULE
    and    TRUNC(p_report_submitted_date)
           BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
           AND     TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
    and    rownum = 1;

Line: 852

    select nls_language
    into   l_orig_language
    from   fnd_languages
    where  language_code = l_orig_language_code;

Line: 861

      select note_language_code
      into   l_new_language_code
      from   ap_expense_params;

Line: 874

      select language_code
      into   l_new_language_code
      from   fnd_languages
      where  installed_flag in ('B');

Line: 883

    select nls_language
    into   l_new_language
    from   fnd_languages
    where  language_code = l_new_language_code;

Line: 1094

PROCEDURE UpdateExpenseStatus(
                                 p_report_header_id         IN NUMBER,
                                 p_expense_status_code      IN VARCHAR2,
                                 p_holding_report_header_id IN NUMBER) IS
------------------------------------------------------------------------
  l_debug_info                  VARCHAR2(200);

Line: 1109

    select expense_status_code
    into   l_expense_status_code
    from   ap_expense_report_headers
    where  report_header_id = p_report_header_id
    for update of expense_status_code nowait;

Line: 1116

    l_debug_info := 'Update current Expense Status to: '||p_expense_status_code||' for: '||p_report_header_id;

Line: 1119

    update ap_expense_report_headers
    set    expense_status_code = p_expense_status_code,
           holding_report_header_id = p_holding_report_header_id,
           expense_last_status_date = sysdate,
           request_id = fnd_global.conc_request_id
    where  report_header_id = p_report_header_id;

Line: 1133

                    FND_MESSAGE.SET_TOKEN( 'CALLING_SEQUENCE', 'UpdateExpenseStatus' );

Line: 1143

END UpdateExpenseStatus;

Line: 1158

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS, null);

Line: 1193

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, p_holding_report_header_id);

Line: 1227

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_INVOICED, null);

Line: 1301

  select aerh.report_header_id,
         aerh.receipts_status,
         aerh.source,
         aerh.expense_status_code,
         aerh.holding_report_header_id
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS_ALL rsa
  where  aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
  and    aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
  and    aerh.bothpay_parent_id is null
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_HOLD_RULE
  and    TRUNC(aerh.report_submitted_date)
         BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
         AND     TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
  and    rs.HOLD_CODE = C_HOLD_EACH_CODE;

Line: 1419

  select aerh.report_header_id
  from   AP_EXPENSE_REPORT_HEADERS aerh
  where  (aerh.source <> 'NonValidatedWebExpense' or aerh.workflow_approved_flag is null)
  and    aerh.receipts_status is not null
  and    aerh.receipts_status in (AP_WEB_RECEIPTS_WF.C_REQUIRED, AP_WEB_RECEIPTS_WF.C_MISSING, AP_WEB_RECEIPTS_WF.C_OVERDUE, AP_WEB_RECEIPTS_WF.C_IN_TRANSIT, AP_WEB_RECEIPTS_WF.C_RESOLUTN)
  and    aerh.bothpay_parent_id is null
  and    trunc(sysdate) - (trunc(aerh.report_submitted_date) + p_hold_rct_overdue_days) > 0
  and    aerh.employee_id = p_employee_id
  order  by aerh.report_submitted_date asc;

Line: 1519

  select aerh.report_header_id,
         aerh.receipts_status,
         aerh.source,
         aerh.expense_status_code,
         aerh.holding_report_header_id,
         aerh.employee_id,
         rs.hold_rct_overdue_days
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS_ALL rsa
  where  aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
  and    aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
  and    aerh.bothpay_parent_id is null
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_HOLD_RULE
  and    TRUNC(aerh.report_submitted_date)
         BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
         AND     TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
  and    rs.HOLD_CODE = C_HOLD_ALL_CODE;

Line: 1663

    select 'Y'
    into   l_is_cc_receipts_required
    from   AP_EXPENSE_REPORT_HEADERS aerh
    where  aerh.report_header_id = p_report_header_id
    and
    exists
    (select 1
     from   ap_expense_report_lines aerl
     where  aerl.report_header_id = aerh.report_header_id
     and    aerl.credit_card_trx_id is not null
     and    nvl(aerl.receipt_required_flag, 'N') = 'Y'
     and    rownum = 1
    )
    and    rownum = 1;

Line: 1769

  select aerh.report_header_id,
         aerh.bothpay_parent_id,
         aerh2.receipts_status,
         aerh2.expense_status_code,
         aerh.source,
         aerh.expense_status_code,
         aerh.holding_report_header_id,
         rs.hold_rct_overdue_bp_cc_code
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_EXPENSE_REPORT_HEADERS aerh2,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS_ALL rsa
  where  aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
  and    aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
  and    aerh.bothpay_parent_id is not null
  and    aerh2.report_header_id = aerh.bothpay_parent_id
  and    rsa.org_id = aerh2.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_HOLD_RULE
  and    TRUNC(aerh2.report_submitted_date)
         BETWEEN TRUNC(NVL(rsa.START_DATE, aerh2.report_submitted_date))
         AND     TRUNC(NVL(rsa.END_DATE, aerh2.report_submitted_date))
  and    rs.HOLD_CODE in (C_HOLD_EACH_CODE, C_HOLD_ALL_CODE);

Line: 1973

  select aerh.report_header_id,
         aerh.source,
         aerh.expense_status_code
  from   AP_EXPENSE_REPORT_HEADERS aerh
  where  aerh.source in (AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE, AP_WEB_RECEIPTS_WF.C_BOTHPAY)
  and    aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
  and
  not exists
  (select 1
   from  AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS_ALL rsa
   where rsa.org_id = aerh.org_id
   and   rsa.rule_set_id = rs.rule_set_id
   and   rs.rule_set_type = C_HOLD_RULE
   and   TRUNC(aerh.report_submitted_date)
         BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
         AND     TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
   and   rownum = 1
  );

Line: 2003

  select aerh.report_header_id,
         aerh.expense_status_code
  from   AP_EXPENSE_REPORT_HEADERS aerh
  where  aerh.source = AP_WEB_RECEIPTS_WF.C_SELF_SERVICE_SOURCE
  and    aerh.expense_status_code in (AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS)
  and    aerh.bothpay_parent_id is null
  and
  not exists
  (select 1
   from   ap_expense_report_lines aerl
   where  aerl.report_header_id = aerh.report_header_id
   and    aerl.credit_card_trx_id IS NULL
   and    rownum = 1)
  and
  exists
  (select 1
   from   ap_expense_report_headers aerh2
   where  aerh2.bothpay_parent_id = aerh.report_header_id
   and    aerh2.expense_status_code = AP_WEB_RECEIPTS_WF.C_INVOICED
   and    rownum = 1);