---
title: "pk73101_image stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-24"
source_schema: "`WCIS_DBA`"
source_name: "pk73101_image"
source_type: "`Package`"
oracle_status: "found"
library: SAIF.Business
reference_type: package
---

# pk73101_image

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | `Package` |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business` |
| **Calling classes** | `ImageServices` |

## Purpose

Imaging helper package used to track report status and summarize who was alerted for an image.

## Procedures / Methods

### track_in_report

| Field | Value |
| --- | --- |
| **Returns** | The function overload returns the remote `@POLDB` tracking result as `VARCHAR2`; the procedure overload executes the same remote call without returning a cursor or scalar result. |
| **Called by** | `ImageServices.TrackInReport` |

**Description:** Calls the remote policy-database tracking routine and exposes both a direct function result and an nHibernate-friendly procedure wrapper.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpolicynum` | IN | NUMBER | — | Policy number forwarded to the report-tracking package. |
| `inauditperiodstartdate` | IN | DATE | — | Audit-period start date forwarded to the report-tracking package. |

### who_was_alerted

| Field | Value |
| --- | --- |
| **Returns** | A `VARCHAR2` message listing alert recipients or the explicit not-found message when no tasks exist. |
| **Called by** | `ImageServices.WhoWasAlerted` |

**Description:** Reads image-task rows and assembles the user-facing alert summary string.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inimageseqnum` | IN | NUMBER | — | Image sequence number whose alert history should be summarized. |

## Business Rules

Rules extracted from this procedure's verified logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-001](../business-rules.md#proc-rule-001-alert-recipients-are-concatenated-only-while-the-output-buffer-remains-under-4000-characters) | Alert recipients are concatenated only while the output buffer remains under 4000 characters. | `LENGTH(vreturn) < 4000` during task iteration. | Append `task_user` to the return string, prefixing later values with `, `. |
| [PROC-RULE-002](../business-rules.md#proc-rule-002-missing-alert-tasks-produce-an-explicit-not-found-message) | Missing alert tasks produce an explicit not-found message. | `dummy = -1` after the task loop. | Return `No alerts found for the selected image.` |
| [PROC-RULE-003](../business-rules.md#proc-rule-003-the-result-message-uses-singular-or-plural-grammar-based-on-alert-count) | The result message uses singular or plural grammar based on alert count. | `vcounter = 1` versus more than one task. | Append `has been alerted...` for one user or `have been alerted...` for multiple users. |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| OP-001 | Calls `WCIS_DBA.pk73101_image.track_in_report@POLDB` and returns the remote function result unchanged. | `ImageServices.TrackInReport` invokes the package function. |
| OP-002 | Selects `task_user` values for the image and orders them by alert date. | `ImageServices.WhoWasAlerted` invokes the function. |
| OP-003 | Iterates the alert-task cursor and assembles the response string returned to the caller. | After `get_tasks` opens. |

> These operations are also listed in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
FUNCTION track_in_report (
      inpolicynum              IN   NUMBER,
      inauditperiodstartdate   IN   DATE
   )
      RETURN VARCHAR2;

PROCEDURE track_in_report (
      inpolicynum              IN   NUMBER,
      inauditperiodstartdate   IN   DATE
   );

FUNCTION who_was_alerted (inimageseqnum IN NUMBER)
      RETURN VARCHAR2;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
FUNCTION track_in_report (
      inpolicynum              IN   NUMBER,
      inauditperiodstartdate   IN   DATE
   )
      RETURN VARCHAR2
   IS
   BEGIN
     Return wcis_dba.pk73101_image.track_in_report@poldb(inpolicynum, inauditperiodstartdate);
   END;

PROCEDURE track_in_report (
      inpolicynum              IN   NUMBER,
      inauditperiodstartdate   IN   DATE
   ) AS
      v_isArrears          VARCHAR2(1);
   BEGIN
     v_isArrears := wcis_dba.pk73101_image.track_in_report@poldb(inpolicynum, inauditperiodstartdate);
   END;

FUNCTION who_was_alerted (inimageseqnum IN NUMBER)
      RETURN VARCHAR2
   IS
      vreturn    VARCHAR2 (4000) := ' ';
      dummy      NUMBER          := -1;
      vcounter   NUMBER          := 0;

      CURSOR get_tasks (inimageseqnum NUMBER)
      IS
         SELECT   task_user alerttext
             /*                || ' - '
                             || TO_CHAR (task_date, 'mm/dd/yyyy')
                             || ' - '
                             || task_text alerttext */
         FROM     task
            WHERE image_seq_num = inimageseqnum
         ORDER BY task_date ASC;
   BEGIN
      FOR rec IN get_tasks (inimageseqnum)
      LOOP
         dummy := LENGTH (vreturn);
         vcounter := vcounter + 1;

         IF LENGTH (vreturn) < 4000
         THEN
            IF vreturn = ' '
            THEN
               vreturn := vreturn || rec.alerttext;
            ELSE
               vreturn := vreturn || ', ' || rec.alerttext;
            END IF;
         END IF;
      END LOOP;

      IF dummy = -1
      THEN
         vreturn := 'No alerts found for the selected image.';
      ELSE
         IF vcounter = 1
         THEN
            vreturn := vreturn || ' has been alerted of the selected image. ';
         ELSE
            vreturn :=
                      vreturn || ' have been alerted of the selected image. ';
         END IF;
      END IF;

      RETURN vreturn;
   END;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- `Business\SAIF.Business\ImageServices.vb`