Skip to content

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 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 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 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.

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.

Source Code

PL/SQL package specification 
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;
PL/SQL package/procedure body 
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;

Sources

  • Business\SAIF.Business\ImageServices.vb