/* Formatted on 2009/02/18 15:41 (Formatter Plus v4.8.8) */
FUNCTION get_query
   RETURN VARCHAR2
IS
   v_qry     VARCHAR2 (6000) DEFAULT NULL;
   v_where   CHAR (1)        DEFAULT 'N';
   v_err     VARCHAR2 (2000);
   l_seq     VARCHAR2 (3);
BEGIN
   IF NVL (LTRIM (RTRIM (:search_blk.system_name)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(SYSTEM_NAME) LIKE UPPER('''
         || :search_blk.system_name
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.product_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(PRODUCT_ID) LIKE UPPER('''
         || :search_blk.product_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.product_type)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(PRODUCT_TYPE) LIKE UPPER('''
         || :search_blk.product_type
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_action_type)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' REVIEW_ACTION_TYPE='
         || :search_blk.review_action_type
         || ' AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.review_comment)), 'XXxx123') <> 'XXxx123'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEW_COMMENT) LIKE UPPER('''
         || :search_blk.review_comment
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(REVIEW_DATE)'
         || NVL (:search_blk.lst_review_date, '=')
         || ''''
         || :search_blk.review_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.reviewed_by)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEWED_BY) LIKE UPPER('''
         || :search_blk.reviewed_by
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.assigned_to)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ASSIGNED_TO) LIKE UPPER('''
         || :search_blk.assigned_to
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.account_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ACCOUNT_ID) LIKE UPPER('''
         || :search_blk.account_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF NVL (LTRIM (RTRIM (:search_blk.agreement_line_id)), 'N') <> 'N'
   THEN
      v_qry :=
            v_qry
         || ' UPPER(AGREEMENT_LINE_ID) LIKE UPPER('''
         || :search_blk.agreement_line_id
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.issue_type)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' UPPER(ISSUE_TYPE) LIKE UPPER('''
         || :search_blk.issue_type
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.last_found_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(LAST_FOUND_DATE)'
         || NVL (:search_blk.lst_last_found_date, '=')
         || ''''
         || :search_blk.last_found_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.review_status)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || ' UPPER(REVIEW_STATUS) LIKE UPPER('''
         || :search_blk.review_status
         || ''') AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.created_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(CREATED_DATE)'
         || NVL (:search_blk.lst_created_date, '=')
         || ''''
         || :search_blk.created_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   IF LTRIM (RTRIM (:search_blk.last_update_date)) IS NOT NULL
   THEN
      v_qry :=
            v_qry
         || 'TRUNC(LAST_UPDATE_DATE)'
         || NVL (:search_blk.lst_last_updated_date, '=')
         || ''''
         || :search_blk.last_update_date
         || ''' AND ';
      v_where := 'Y';
   END IF;

   RETURN SUBSTR (v_qry, 1, LENGTH (v_qry) - 4);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN SQLERRM || ': AT-LINE: ' || l_seq;
END get_query;