APPS.FND_OAM_WS_UTIL SQL Statements

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

Line: 4

/* Purge Function to delete all Data */
procedure delete_by_date_cp(   errbuf out NOCOPY varchar2,
                               retcode out NOCOPY varchar2,
                               start_date in  varchar2,
                               end_date in  varchar2 ) is
        v_strt_date DATE;

Line: 11

        deleted_requests_count NUMBER;

Line: 12

        deleted_response_count NUMBER;

Line: 13

        deleted_method_count NUMBER;

Line: 14

	deleted_att_count NUMBER;

Line: 36

  /* Purge Function to delete all requests Data */
  deleted_requests_count := delete_requests_by_date_range(v_strt_date,v_end_date);

Line: 38

  /* Purge Function to delete all response Data */
  deleted_response_count := delete_responses_by_date_range(v_strt_date,v_end_date);

Line: 40

  /* Purge Function to delete all method Data */
  deleted_method_count := delete_method_by_date_range(v_strt_date,v_end_date);

Line: 42

  /* Purge Function to delete all attachment Data */
  deleted_att_count := delete_att_by_date_range(v_strt_date,v_end_date);

Line: 44

  /* Purge Function to delete all attachment body Data */
  deleted_att_count := delete_body_by_date_range(v_strt_date,v_end_date);

Line: 48

end delete_by_date_cp;

Line: 51

/* Purge Function to delete all requests Data */
FUNCTION delete_requests_by_date_range(
         x_start_date IN DATE,
         x_end_date IN DATE) return NUMBER is
  rowcount number := 0;

Line: 62

    DELETE
      FROM  OAM_PAT_WS_REQUEST
      WHERE message_id IN
	(SELECT WR.message_id
	FROM
	OAM_PAT_WS_REQUEST    WR
	WHERE
	(x_start_date IS NOT NULL and x_end_date IS NOT NULL)
	AND  nvl(x_start_date, WR.request_timestamp)<= WR.request_timestamp
	AND  nvl(x_end_date, WR.request_timestamp)>= WR.request_timestamp
	)
	AND rownum <= 1000;

Line: 90

  fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from OAM_PAT_WS_REQUEST ');

Line: 96

/* Purge Function to delete all response Data */
FUNCTION delete_responses_by_date_range(
         x_start_date IN DATE,
         x_end_date IN DATE) return NUMBER is
  rowcount number := 0;

Line: 109

    DELETE
      FROM  OAM_PAT_WS_RESPONSE
      WHERE message_id IN
	(SELECT WR.message_id
	FROM
	OAM_PAT_WS_RESPONSE    WR
	WHERE
	(x_start_date IS NOT NULL and x_end_date IS NOT NULL)
	AND  nvl(x_start_date, WR.response_timestamp)<= WR.response_timestamp
	AND  nvl(x_end_date, WR.response_timestamp)>= WR.response_timestamp
	)
	AND rownum <= 1000;

Line: 139

  fnd_file.put_line(fnd_file.output,'Deleted '|| rowcount ||' rows from FND_OAM_WS_RESPONSE ');

Line: 143

/* Purge Function to delete all method Data */
FUNCTION delete_method_by_date_range(
         x_start_date IN DATE,
         x_end_date IN DATE) return NUMBER is
  rowcount number := 0;

Line: 153

	DELETE
	FROM OAM_PAT_WS_RESPONSE_METHOD
	WHERE
	message_id NOT  IN
	(SELECT message_id
	FROM
	OAM_PAT_WS_RESPONSE)
	AND rownum <= 1000;

Line: 179

			'Deleted '|| rowcount ||' rows from OAM_PAT_WS_RESPONSE_METHOD ');

Line: 183

/* Purge Function to delete all attachment Data */
FUNCTION delete_att_by_date_range(
         x_start_date IN DATE,
         x_end_date IN DATE) return NUMBER is
  rowcount number := 0;

Line: 193

	DELETE
	FROM OAM_PAT_WS_ATTACHMENT WA
	WHERE
	((WA.BELONGS_TO='REQUEST' AND  message_id NOT  IN
	(SELECT message_id
	FROM
	OAM_PAT_WS_REQUEST))
	OR
	(WA.BELONGS_TO='RESPONSE' AND  message_id NOT  IN
	(SELECT message_id
	FROM
	OAM_PAT_WS_RESPONSE)))
	AND rownum <= 1000;

Line: 224

			'Deleted '|| rowcount ||' rows from OAM_PAT_WS_ATTACHMENT ');

Line: 229

/* Purge Function to delete all attachment body Data */
FUNCTION delete_body_by_date_range(
         x_start_date IN DATE,
         x_end_date IN DATE) return NUMBER is
  rowcount number := 0;

Line: 239

	DELETE
	FROM OAM_PAT_WS_BODY_PIECE WP
	WHERE
	((WP.BELONGS_TO='REQUEST' AND  message_id NOT  IN
	(SELECT message_id
	FROM
	OAM_PAT_WS_REQUEST))
	OR
	(WP.BELONGS_TO='RESPONSE' AND  message_id NOT  IN
	(SELECT message_id
	FROM
	OAM_PAT_WS_RESPONSE)))
	AND rownum <= 1000;

Line: 270

			'Deleted '|| rowcount ||' rows from OAM_PAT_WS_BODY_PIECE ');