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: 854

    select nls_language
    into   l_orig_language
    from   fnd_languages
    where  language_code = l_orig_language_code;

Line: 866

      select note_language_code
      into   l_new_language_code
      from   ap_expense_params_all
      where org_id = l_org_id;

Line: 880

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

Line: 889

    select nls_language
    into   l_new_language
    from   fnd_languages
    where  language_code = l_new_language_code;

Line: 1100

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: 1115

    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: 1122

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

Line: 1125

    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: 1139

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

Line: 1149

END UpdateExpenseStatus;

Line: 1164

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PENDING_HOLDS, null);

Line: 1199

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_PAYMENT_HELD, p_holding_report_header_id);

Line: 1233

    UpdateExpenseStatus(p_report_header_id, AP_WEB_RECEIPTS_WF.C_INVOICED, null);

Line: 1311

  select aerh.report_header_id,
         aerh.receipts_status,
	 aerh.image_receipts_status,
         aerh.source,
         aerh.expense_status_code,
         aerh.holding_report_header_id,
	 aerh.report_submitted_date,
	 aerh.org_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: 1470

  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.expense_status_code not in (AP_WEB_RECEIPTS_WF.C_INVOICED, AP_WEB_RECEIPTS_WF.C_PARPAID, AP_WEB_RECEIPTS_WF.C_PAID)
  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: 1535

  select aerh.report_header_id
  from   AP_EXPENSE_REPORT_HEADERS aerh
  where  (aerh.source <> 'NonValidatedWebExpense' or aerh.workflow_approved_flag is null)
  and    aerh.image_receipts_status is not null
  and    aerh.image_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.expense_status_code not in (AP_WEB_RECEIPTS_WF.C_INVOICED, AP_WEB_RECEIPTS_WF.C_PARPAID, AP_WEB_RECEIPTS_WF.C_PAID)
  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: 1640

  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: 1818

    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: 1871

    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.image_receipt_required_flag, 'N') = 'Y'
     and    rownum = 1
    )
    and    rownum = 1;

Line: 1978

  select aerh.report_header_id,
         aerh.bothpay_parent_id,
         aerh2.receipts_status,
	 aerh2.image_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: 2211

  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: 2241

  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);

Line: 2368

  SELECT rs.*
  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 = 'RECEIPT'
  AND   TRUNC(p_report_date) BETWEEN TRUNC(NVL(rsa.start_date,p_report_date)) AND TRUNC(NVL(rsa.end_date,p_report_date));