SQL-001: Cancellation status derivation¶

This database call derives the cancellation or collection-status text that clsStandardPolicyHeaderDataProvider appends to the rendered policy-header status. The .NET code invokes SP72268_GET_CANCEL_STATUS as a stored-procedure-style scalar request, but the analysis shows the Oracle object itself is a standalone function that returns VARCHAR2.

Classification¶

Field	Value
SQL ID	SQL-001
Operation type	Read
Execution method	Stored procedure-style scalar request to Oracle function
Database	Oracle
Schema	`WCIS_DBA`**
Command type	StoredProcedure
Business object	`N/A (Application layer)`
Application class	`clsStandardPolicyHeaderDataProvider`
Criteria property	`N/A (Application layer)`
Lifecycle method	`GetCancellationStatusDescription`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:in_policy_num`	Input	`NUMBER`	N/A	Policy number whose cancellation status is being derived.
`RetValue`	ReturnValue	`VARCHAR2`	100	Function return value bound through `ParameterDirection.ReturnValue`; the PL/SQL function returns up to 13 characters, but the .NET caller allocates 100.

Tables¶

Table	Access	Columns used
POLICY	SELECT	`POLICY_NUM`, `POLICY_STATUS_CODE`, `CURRENT_BALANCE`
POLICY_STATUS_CODE	JOIN	`CODE`, `POLICY_STATUS_GROUP_CODE`
POLICY_DEMAND	SELECT	`POLICY_NUM`, `COLLECTION_ACT_RESPONSE_CODE`
POLICY_PERIOD_PAYROLL_AUDIT	SELECT	`POLICY_NUM`, `AUDIT_STATUS_CODE`, `AUDIT_PERIOD_START_DATE`, `AUDIT_CODE`, `PAYROLL_SEQ_NUM`
AUDIT_CODE	JOIN	`CODE`, `AUDIT_TYPE_CODE`
CANCELLATION	SELECT	`POLICY_NUM`, `CANCELLATION_SEQ_NUM`, `CANCEL_DATE`
COLLECTION_LEGAL_ACTION	SELECT	`POLICY_NUM`, `COLLECTION_ACTION_CODE`, `CANCEL_DATE`

Sample SQL¶

:RetValue := SP72268_GET_CANCEL_STATUS(
  in_policy_num => :in_policy_num)

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Application\SAIF.Application.Policy\PolicyHeader\clsStandardPolicyHeaderDataProvider.vb