APPS.AP_WEB_RECEIPTS_WF SQL Statements

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

Line: 78

    select 'Y'
    into   l_is_notif_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_NOTIFY_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: 143

    select report_submitted_date
    into   l_report_submitted_date
    from   ap_expense_report_headers_all
    where  report_header_id = l_expense_report_id;

Line: 181

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

Line: 199

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

Line: 361

  l_debug_info := 'Update Receipts Status if not Received or Waived';

Line: 364

    update ap_expense_report_headers_all
    set    receipts_status = ''
    where  report_header_id = p_expense_report_id
    and    nvl(receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED)
    and    receipts_received_date is null;

Line: 370

    update ap_expense_report_headers_all
    set    image_receipts_status = ''
    where  report_header_id = p_expense_report_id
    and    nvl(image_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED)
    and    image_receipts_received_date is null;

Line: 384

    update ap_expense_report_headers_all
    set    expense_status_code = C_PENDING_HOLDS,
           holding_report_header_id = null,
           expense_last_status_date = sysdate
    where  holding_report_header_id = p_expense_report_id
    and    expense_status_code = C_PAYMENT_HELD;

Line: 450

		update ap_expense_report_headers_all
		set    receipts_status = ''
		where  report_header_id = l_report_header_id
		and    nvl(receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)
		and    receipts_received_date is null;

Line: 456

		update ap_expense_report_headers_all
		set    image_receipts_status = ''
		where  report_header_id = l_report_header_id
		and    nvl(image_receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)
		and    image_receipts_received_date is null;

Line: 464

	  update ap_expense_report_headers_all
	  set    expense_status_code = C_PENDING_HOLDS,
	  holding_report_header_id = null,
	  expense_last_status_date = sysdate
	  where  holding_report_header_id = l_report_header_id
	  and    expense_status_code = C_PAYMENT_HELD;

Line: 553

  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,
         missing_img_just
  into   l_created_by,
         l_employee_id,
         l_invoice_num,
         l_cost_center,
         l_total,
         l_purpose,
         l_report_submitted_date,
         l_missing_img_just
  from   ap_expense_report_headers_all
  where  report_header_id = l_expense_report_id;

Line: 961

      select item_key
      into   l_found_item_key
      from   wf_items
      where  item_type = p_item_type
      and    item_key like l_item_key
      and    end_date is null
      and    rownum = 1;

Line: 1030

      select item_key
      into   l_found_item_key
      from   wf_items
      where  item_type = p_item_type
      and    item_key like l_item_key
      and    end_date is null
      and    rownum = 1;

Line: 1085

    SELECT receipts_status, image_receipts_status, expense_status_code, workflow_approved_flag
        INTO l_receipts_status, l_image_receipts_status, l_expense_status_code, l_workflow_flag
	FROM ap_expense_report_headers_all WHERE report_header_id = l_expense_report_id;

Line: 1169

  select item_key
  from   wf_items
  where  item_type = p_item_type
  and    item_key like l_item_key
  and    end_date is null;

Line: 1323

    select receipts_status
    into   l_receipts_status
    from   ap_expense_report_headers_all
    where  report_header_id = p_report_header_id;

Line: 1401

    select receipts_status
    into   l_orig_receipts_status
    from   ap_expense_report_headers_all
    where  report_header_id = p_report_header_id
    for update of receipts_status nowait;

Line: 1408

    l_debug_info := 'Update current Receipt Status';

Line: 1410

    update ap_expense_report_headers_all
    set    receipts_status = p_receipts_status
    where  report_header_id = p_report_header_id;

Line: 1460

    l_debug_info := 'Update current Receipt Status';

Line: 1529

      l_debug_info := 'Update current Receipt Status to Overdue if In Transit';

Line: 1766

    select 'Y'
    into   l_is_shortpay
    from   ap_expense_report_headers_all aerh,
           wf_items wf
    where  aerh.report_header_id = l_report_header_id
    and    aerh.shortpay_parent_id is not null
    and    wf.item_type = C_APEXP
    and    wf.item_key = to_char(aerh.report_header_id)     -- Bug 6841589 (sodash) to solve the invalid number exception
    and    wf.end_date is null
    and    wf.root_activity = p_shortpay_type
    and    rownum = 1;

Line: 2074

    select nls_language
    into   l_orig_language
    from   fnd_languages
    where  language_code = l_orig_language_code;

Line: 2086

      select note_language_code
      into   l_new_language_code
      from   ap_expense_params_all
      where org_id = l_org_id;

Line: 2100

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

Line: 2109

    select nls_language
    into   l_new_language
    from   fnd_languages
    where  language_code = l_new_language_code;

Line: 2132

      select created_by
      into   l_created_by
      from   ap_expense_report_headers_all
      where  report_header_id = l_report_header_id;

Line: 2159

        select message_text
        into   l_note_prefix
        from   fnd_new_messages
        where  application_id = 200
        and    message_name = l_message_name
        and    language_code = l_new_language_code;

Line: 2275

		SELECT trunc(receipts_received_date) INTO l_received_date
                FROM ap_expense_report_headers_all WHERE report_header_id = l_report_header_id;

Line: 2285

                SELECT trunc(image_receipts_received_date) INTO l_received_date
                FROM ap_expense_report_headers_all WHERE report_header_id = l_report_header_id;

Line: 2451

  UPDATE AP_EXPENSE_REPORT_HEADERS_ALL
	SET OVERDUE_REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
	WHERE REPORT_HEADER_ID = p_expense_report_id;

Line: 2509

  SELECT 'Y' INTO l_ret_value FROM wf_items
  WHERE item_key like to_char(p_report_header_id)||p_item_key
        AND end_date is null;

Line: 2594

select aerh.report_header_id,
         aerh.receipts_status,
	 aerh.image_receipts_status,
	 aerh.report_submitted_date,
	 rs.NOTIFY_RCT_OVERDUE_DAYS,
	 rs.NOTIFY_IMG_RCT_OVERDUE_DAYS
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS rsa
  where  (aerh.receipts_status in (C_REQUIRED, C_MISSING)
          OR decode(aerh.image_receipts_status, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.image_receipts_status) in (C_REQUIRED, C_MISSING))
  and    aerh.bothpay_parent_id is null
  and    aerh.shortpay_parent_id is null
  and    rsa.org_id = nvl(p_org_id, rsa.org_id)
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_NOTIFY_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
  (
	  ((aerh.RECEIPTS_STATUS = C_REQUIRED and sysdate - (aerh.report_submitted_date + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
	   and    not exists
	    (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT'
                          and (item_key like to_char(aerh.report_header_id)||':receipts.overdue.original%'
			       OR item_key like to_char(aerh.report_header_id)||':receipts.overdue.both%')
			  and end_date is null and rownum=1)
	  )
	  or
	  ((aerh.RECEIPTS_STATUS = C_MISSING and nvl(aerh.IMAGE_RECEIPTS_STATUS, C_NOT_REQUIRED) in (C_NOT_REQUIRED, C_MISSING, C_WAIVED, C_RECEIVED)
	    and sysdate - (aerh.report_submitted_date + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
	   and    not exists
	    (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT'
	                  and (item_key like to_char(aerh.report_header_id)||':receipts.missing.original%'
			      OR item_key like to_char(aerh.report_header_id)||':receipts.missing.both%')
			  and end_date is null and rownum=1)
	  )
	  or
	  ((decode(aerh.IMAGE_RECEIPTS_STATUS, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.IMAGE_RECEIPTS_STATUS) = C_REQUIRED
				  and sysdate - (aerh.report_submitted_date + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
            and (aerh.RECEIPTS_STATUS <> AP_WEB_RECEIPTS_WF.C_RECEIVED and (nvl(aerh.workflow_approved_flag, 'N') <> 'P' OR aerh.expense_status_code <> 'INVOICED'))
	    and    not exists
	    (select 1
		    from wf_items
		    where decode(aerh.IMAGE_RECEIPTS_STATUS, 'PENDING_IMAGE_SUBMISSION', C_REQUIRED, aerh.IMAGE_RECEIPTS_STATUS) = C_REQUIRED
		    and item_type = 'APWRECPT'
		    and (item_key like to_char(aerh.report_header_id)||':receipts.overdue.image%'
			 OR item_key like to_char(aerh.report_header_id)||':receipts.overdue.both%')
		    and end_date is null
		    and rownum=1)
	  )
	  or
	  ((aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and nvl(aerh.RECEIPTS_STATUS, C_NOT_REQUIRED) in (C_NOT_REQUIRED, C_MISSING, C_WAIVED, C_RECEIVED)
	    and sysdate - (aerh.report_submitted_date + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
	    and (aerh.RECEIPTS_STATUS <> AP_WEB_RECEIPTS_WF.C_RECEIVED and (nvl(aerh.workflow_approved_flag, 'N') <> 'P' OR aerh.expense_status_code <> 'INVOICED'))
	    and    not exists
	    (select 1
		   from wf_items
		   where aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT'
		   and (item_key like to_char(aerh.report_header_id)||':receipts.missing.image%'
			OR item_key like to_char(aerh.report_header_id)||':receipts.missing.both%')
		   and end_date is null
		   and rownum=1)
	  )
  );

Line: 2661

  select aerh.report_header_id,
         aerh.receipts_status,
	 'ORIGINAL'
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS rsa
  where  aerh.receipts_status in (C_REQUIRED, C_MISSING)
  and    aerh.bothpay_parent_id is null
  and    rsa.org_id = nvl(p_org_id, rsa.org_id)
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_NOTIFY_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
  (
  (aerh.RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
  or
  (aerh.RECEIPTS_STATUS = C_MISSING and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
  )
  and    not exists
  (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
  and    not exists
  (select 1 from wf_items where aerh.RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.missing%'  and end_date is null and rownum=1)

  UNION

  select aerh.report_header_id,
	 aerh.image_receipts_status,
	 'IMAGE'
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS rsa
  where  aerh.image_receipts_status in (C_REQUIRED, C_MISSING)
  and    aerh.bothpay_parent_id is null
  and    rsa.org_id = nvl(p_org_id, rsa.org_id)
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = C_NOTIFY_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
  (
  (aerh.IMAGE_RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
  or
  (aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_IMG_RCT_OVERDUE_DAYS) > 0)
  )
  and    not exists
  (select 1 from wf_items where aerh.IMAGE_RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
  and    not exists
  (select 1 from wf_items where aerh.IMAGE_RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.missing%'  and end_date is null and rownum=1);

Line: 2723

  select aerh.employee_id,
         hr.business_group_id,
         max(rs.audit_term_duration_days)
  from   AP_EXPENSE_REPORT_HEADERS aerh,
         hr_organization_units hr,
         AP_AUD_RULE_SETS rs,
         AP_AUD_RULE_ASSIGNMENTS rsa
  where  aerh.org_id = nvl(p_org_id, aerh.org_id)
  and    aerh.bothpay_parent_id is null
  and    aerh.report_submitted_date is not null
  and    hr.organization_id = aerh.org_id
  and    rsa.org_id = aerh.org_id
  and    rsa.rule_set_id = rs.rule_set_id
  and    rs.rule_set_type = 'AUDIT_LIST'
  and    rs.receipt_delay_rule_flag = 'Y'
  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    aerh.receipts_status in ('REQUIRED', 'MISSING', 'OVERDUE', 'IN_TRANSIT', 'RESOLUTN')
  and    trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.receipt_delay_days) > 0
  group by employee_id, business_group_id;

Line: 2748

 SELECT DISTINCT supervisor_id FROM
	 (SELECT emp.supervisor_id
	 FROM  per_employees_x emp
	 WHERE  emp.employee_id in (select distinct employee_id
					from ap_expense_report_headers_all
					where overdue_request_id = FND_GLOBAL.CONC_REQUEST_ID)
	 AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
	   UNION ALL
	 SELECT emp.supervisor_id
	 FROM  per_cont_workers_current_x emp
	 WHERE  emp.person_id in (select distinct employee_id
					from ap_expense_report_headers_all
					where overdue_request_id = FND_GLOBAL.CONC_REQUEST_ID)
	 );

Line: 2789

      l_debug_info := 'Update current Receipt Status';

Line: 2972

  select 'Y'
  into   l_event_key_exists
  from   wf_items
  where  item_type = C_APWRECPT
  and    item_key like p_report_header_id||'%'
  and    rownum = 1;

Line: 3029

  select nvl(receipts_status,'N'), nvl(image_receipts_status,'N') into l_receipts_status, l_image_receipts_status
  from ap_expense_report_headers_all where report_header_id = l_expense_report_id;

Line: 3104

    select image_receipts_status
    into   l_receipts_status
    from   ap_expense_report_headers_all
    where  report_header_id = p_report_header_id;

Line: 3137

    select image_receipts_status
    into   l_image_receipts_status
    from   ap_expense_report_headers_all
    where  report_header_id = p_report_header_id
    for update of receipts_status nowait;

Line: 3144

    l_debug_info := 'Update current Receipt Status';

Line: 3146

    update ap_expense_report_headers_all
    set    image_receipts_status = p_receipts_status
    where  report_header_id = p_report_header_id;

Line: 3260

    l_debug_info := 'Update current Receipt Status';

Line: 3363

      l_debug_info := 'Update current Receipt Status to Overdue if In Transit';

Line: 3422

PROCEDURE UpdateOriginalInTransit(
                                 p_item_type    IN VARCHAR2,
                                 p_item_key     IN VARCHAR2,
                                 p_actid        IN NUMBER,
                                 p_funmode      IN VARCHAR2,
                                 p_result       OUT NOCOPY VARCHAR2) IS
------------------------------------------------------------------------
l_org_id	        	number;

Line: 3452

END UpdateOriginalInTransit;

Line: 3517

           select nvl(workflow_approved_flag, 1) into l_wf_appr_flag
           from ap_expense_report_headers_all
           where report_header_id = l_expense_report_id;

Line: 3524

	    update ap_expense_report_headers_all set
	    expense_status_code = 'RETURNED',
            report_submitted_date = null,last_update_date = sysdate
            where report_header_id = l_expense_report_id;

Line: 3610

        SELECT nvl(receipts_status, 'NOT_REQUIRED'), nvl(image_receipts_status, 'NOT_REQUIRED')
        INTO l_receipts_status, l_image_receipts_status
        FROM ap_expense_report_headers_all
        WHERE report_header_id = l_expense_report_id;

Line: 3668

	SELECT receipts_status, image_receipts_status INTO l_receipts_status, l_image_receipts_status
	FROM ap_expense_report_headers_all WHERE report_header_id = l_expense_report_id;

Line: 3831

    SELECT nvl(workflow_approved_flag, 1) INTO l_wf_status_code
    FROM ap_expense_report_headers_all
    WHERE report_header_id = l_report_header_id;