insertLetterRequest

Overview

Field	Value
Schema	WCIS_DBA
Package	pk80001_legalnet
Type	PROCEDURE
Database	Oracle
Calling libraries	SAIF.Application.Claim.Legal
Calling classes	clsRequestManager, clsScheduleManager (via LegalManagerBase.vb:385–410)

Purpose

Creates a LETTER_REQUEST row for a legal case and returns its newly generated sequence number. The procedure assigns a system-generated sequence number from letter_request_seq, populates the row with the provided letter type, legal case number, hold date, and audit user, and returns the generated request number for downstream batch-job submission (Appworx LGN0002_LEGALNET_LETTERS for RREQ type; case-eval flag update for DEFLT type).

Procedures / Methods

insertLetterRequest

Field	Value
Direction	— (stored procedure; no return type)
Result	Returns via IN OUT parameter requestNum
Called by	clsRequestManager.vb:261 (RREQ — recon request); clsScheduleManager.vb:209 (DEFLT — Defense Eval Form)

Description: Records a request for a legal letter (e.g. acknowledgement or DEF letter) and assigns a unique sequence number (letter_request_seq.NEXTVAL) to track it for batch printing. The generated request number is returned via the requestNum IN OUT parameter and is subsequently used as the batch-job input key for Appworx letter printing.

Parameters

Parameter	Direction	Type	Default	Description
letterTypeCode	IN	VARCHAR2	—	Letter type code written to LETTER_REQUEST.LETTER_TYPE_CODE. Values supplied by this library: RREQ (recon request, on new case save when SendReconRequest is set), DEFLT (Defense Evaluation Form, from clsScheduleManager.PrintDEF).
aLegalCaseNum	IN	NUMBER	—	Legal case sequence number; written to LETTER_REQUEST.legal_case_seq_num.
holdDate	IN	DATE	—	Hold/release date written to LETTER_REQUEST.hold_date. May be NULL for RREQ type (typed NULL DbType.Date bind when Date.MinValue is passed by .NET). For DEFLT, set by caller to the next Friday if today is not Friday, or next Friday if today is Friday (PR-ACL-006).
aClaimId	IN	NUMBER	—	Claim ID, bound by .NET from ClaimData.ClaimID. Accepted but never referenced in the procedure body — see PR-ACL-004.
createUser	IN	VARCHAR2	—	Audit user written to LETTER_REQUEST.create_user.
requestNum	IN OUT	NUMBER	—	Letter request number. The IN value is unconditionally overwritten by letter_request_seq.NEXTVAL; effectively an OUT-only parameter from the caller's perspective. Returned to the .NET caller via PortalRequest.DataRequest(0).ParmValue("requestNum") and used as a Long in downstream batch submission or case-eval flag updates.

Business Rules

Rules extracted from this procedure's logic:

Rule ID	Rule	Condition	Action
PR-ACL-001	Letter request numbers are server-assigned from an Oracle sequence	Always, on every invocation	SELECT letter_request_seq.NEXTVAL INTO requestNum FROM dual — any caller-supplied requestNum is discarded and replaced.
PR-ACL-002	Every letter request is persisted with a system creation timestamp	Always, on every invocation	INSERT INTO LETTER_REQUEST (...) VALUES (..., createUser, sysdate) — create_date is set from sysdate, never from a caller value.
PR-ACL-003	The generated request number is returned to the caller for downstream batch submission	After the INSERT completes	requestNum (IN OUT) carries the new sequence value back; LegalManagerBase.insertLetterRequest reads it via PortalRequest.DataRequest(0).ParmValue("requestNum") and returns it as Long.
PR-ACL-004	The aClaimId argument has no persisted effect (dead parameter)	Always	aClaimId is declared but never read in the body; it is not part of the INSERT column list. Callers pass ClaimData.ClaimID, but the value is discarded. Verified by inspection of the full procedure body.
PR-ACL-005	The local variable v_send is vestigial	Always	v_send VARCHAR2(3) is declared and assigned 'YES' but never subsequently read. No branch depends on it. Indicates removed conditional-send logic; no current behavioural effect.
PR-ACL-006	DEF letter requests are held until the next Friday	holddate starts at Today (caller-side rule, clsScheduleManager.vb:203–209)	If today is Friday, holddate = Today.AddDays(7) (next Friday); otherwise holddate = Today.NextDayOfWeek(Friday). The resulting date is passed as holdDate.
PR-ACL-007	Recon-packet requests carry no hold date	Letter type RREQ, new case with SendReconRequest set (caller-side rule, LegalManagerBase.vb:391–395 and clsRequestManager.vb:261)	Date.MinValue is passed, which LegalManagerBase converts to a typed NULL DbType.Date bind, persisting hold_date as NULL.
PR-ACL-008	The returned request number keys an asynchronous print job	Immediately after a successful RREQ insert (caller-side rule, clsRequestManager.vb:262–263)	clsRequestAppworxJob.SubmitBatchJob(DPI, "LGN0002_LEGALNET_LETTERS", "LegalNet", …, requestNum.ToString, …) — the Appworx batch job consumes the sequence value as its input.

These rules are documented in business-rules.md. Newly introduced rules must be added to business-rules.md as part of the same writing task.

Operations

Operations and processes implemented in this procedure:

Operation	Description	Trigger
Create LETTER_REQUEST row	Inserts a new row into the LETTER_REQUEST table with generated sequence ID, letter type, legal case number, optional hold date, and audit user.	Every successful call to insertLetterRequest.
Assign request number from sequence	Retrieves the next value from letter_request_seq and assigns it to the new LETTER_REQUEST row and the requestNum OUT parameter.	Every successful call; unconditional.
Return request number to caller	The generated requestNum is returned via the IN OUT parameter and read by the .NET wrapper for downstream use.	After INSERT completes and sequence value is bound to the OUT parameter.
Submit Appworx print job (RREQ only)	For letter type RREQ, the caller (clsRequestManager.vb:262–263) immediately submits an Appworx batch job LGN0002_LEGALNET_LETTERS keyed by the returned request number, which prints recon-packet letters and updates LEGAL_CASE.SendReconFlag = clsConstants.No.	After clsRequestManager.insertLetterRequest receives a successful RREQ request number.
Update case-eval flag (DEFLT only)	For letter type DEFLT, the caller (clsScheduleManager.vb:210–211 / clsScheduleManager.PrintDEF) sets CurrentCase.DefSentDate = Today and CurrentCase.SendDefFlag = clsConstants.Yes.	After clsScheduleManager.insertLetterRequest receives a successful DEFLT request number.

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL procedure definition

PROCEDURE insertLetterRequest(
         letterTypeCode  IN VARCHAR2,
         aLegalCaseNum   IN NUMBER,
         holdDate        IN DATE,
         aClaimId        IN NUMBER,
         createUser      IN VARCHAR2,
         requestNum      IN OUT NUMBER)
  AS
   v_send     VARCHAR2(3);
   BEGIN

   v_send := 'YES';

  SELECT letter_request_seq.NEXTVAL
    INTO requestNum
    FROM dual;

  INSERT INTO LETTER_REQUEST
   (letter_request_Seq_num,
    LETTER_TYPE_CODE,
    legal_case_seq_num,
    hold_date,
    create_user,
    create_date)
  VALUES(requestNum,
    letterTypeCode,
    aLegalCaseNum,
    holdDate,
    createUser,
    sysdate);

  END insertLetterRequest;

Calling Context (.NET)

LegalManagerBase.insertLetterRequest

Wrapper method in SAIF.Application.Claim.Legal\LegalManagerBase.vb:385–410:

Public Function insertLetterRequest(ByVal caseSeqNum As Decimal, ByVal letterType As String,
       ByVal holdDate As Date, ByVal ClaimId As Decimal, ByVal createUser As String) As Long
    Dim requestNum As Long
    Dim PortalRequest As IPortalRequest
    Dim dr As IDataRequest = Nothing

    With DPI
        PortalRequest = .DataAccessLayer.GetPortalRequest(.PortalID)
        dr = PortalRequest.NewRequest
        dr.RequestType = DataRequestType.Scalar
        dr.CommandType = CommandType.StoredProcedure
        dr.SQLStatement = "pk80001_LEGALNET.insertLetterRequest"
        dr.AddParm("letterTypeCode", letterType, 5)
        dr.AddParm("aLegalCaseNum", caseSeqNum)
        dr.AddParm("holdDate", IIf(holdDate = Date.MinValue, Nothing, holdDate), DbType.Date)
        dr.AddParm("createUser", createUser, 30)
        dr.AddParm("aClaimId", ClaimId)
        dr.AddParm("requestNum", requestNum, ParameterDirection.InputOutput)
        dr.Execute()
        requestNum = CLng(PortalRequest.DataRequest(0).ParmValue("requestNum"))
    End With

    Return requestNum
End Function

Call site 1: clsRequestManager.insertLetterRequest (RREQ)

Source: SAIF.Application.Claim.Legal\clsRequestManager.vb:261–263

Dim requestNum As Long = m_LegalManagerBase.insertLetterRequest(caseSeqNum, "RREQ", Date.MinValue, ClaimId, m_DPI.UserID)
clsRequestAppworxJob.SubmitBatchJob(DPI, "LGN0002_LEGALNET_LETTERS", "LegalNet", …, requestNum.ToString, …)

Letter type RREQ (recon request) is submitted for new case saves when SendReconRequest is True. Hold date is Date.MinValue, bound as typed NULL.

Call site 2: clsScheduleManager.PrintDEF (DEFLT)

Source: SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb:203–211

' DEF letter hold date: next Friday, or a week from Friday if today is Friday
Dim holdDate As Date = If(Today.DayOfWeek = DayOfWeek.Friday, Today.AddDays(7), Today.NextDayOfWeek(Friday))
Dim requestNum As Long = m_LegalManagerBase.insertLetterRequest(caseSeqNum, "DEFLT", holdDate, ClaimId, m_DPI.UserID)
CurrentCase.DefSentDate = Today
CurrentCase.SendDefFlag = clsConstants.Yes

Letter type DEFLT (Defense Evaluation Form) is submitted from the PrintDEF method. Hold date is computed to the next Friday and is not NULL.

Related Documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

Sources

• Oracle DBA_SOURCE: WCIS_DBA.pk80001_legalnet package body, insertLetterRequest procedure definition
• Legends CLI query executed 2026-08-31T21:20:00Z: legends oracle query --name pk80001_legalnet
• Calling code: SAIF.Application.Claim.Legal\LegalManagerBase.vb:385–410
• Call site 1: SAIF.Application.Claim.Legal\clsRequestManager.vb:261–263
• Call site 2: SAIF.Application.Claim.Legal\LegalNet\clsScheduleManager.vb:203–211