---
title: "PK80011_LEGALCOL_SOIL stored procedure documentation"
summary: Analysis of Oracle package/procedure including business rules and operations
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data.NHibernate
reference_type: package
source_schema: "WCIS_DBA"
source_name: "PK80011_LEGALCOL_SOIL"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name PK80011_LEGALCOL_SOIL --output analysis/oracle-source/pk80011-legalcol-soil.sql"
retrieval_date: "2026-09-24"
---


# PK80011_LEGALCOL_SOIL

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package (database object type: `PACKAGE`) |
| **Database** | Oracle (WCIS) |
| **Calling libraries** | `SAIF.Data.NHibernate` |
| **Calling classes** | CollectionsRepository |

## Purpose

This package implements: create_reference_records, create_SOIL_transactions (→ find_DOR_balance, insert_soil_tran, remove_soil_flag)

> **Scope:** `PK80011_LEGALCOL_SOIL` is a large package (5 members). Only the 5 member(s) actually called by SAIF.Data.NHibernate (`create_reference_records, create_SOIL_transactions, find_DOR_balance, remove_soil_flag, insert_soil_tran`) are reproduced below, extracted from the Legends CLI retrieval's per-member `codeItems`; the full package specification/body is not reproduced in this document. This is a deliberate scoping decision for readability, not a retrieval gap — the full source is available at the retrieval path in Sources.

## Procedures / Methods

### create_reference_records member

| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | SQL-002 (`CollectionsRepository.CreateSoilDebtorReferenceRecords`) |

#### Parameters for create_reference_records

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `create_date_in` | IN | DATE | — | Not individually described in the retrieved source* |
| `run_id` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |

### create_SOIL_transactions member

| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | SQL-003 (`CollectionsRepository.CreateSoilAdjustmentRecords`) |

#### Parameters for create_SOIL_transactions

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `create_date_in` | IN | DATE | — | Not individually described in the retrieved source* |
| `run_id` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |

### find_DOR_balance member

| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | SQL-003 (`CollectionsRepository.CreateSoilAdjustmentRecords`) |

#### Parameters for find_DOR_balance

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `debtor_ssn` | IN | VARCHAR2 | — | Not individually described in the retrieved source* |

### remove_soil_flag member

| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | SQL-003 (`CollectionsRepository.CreateSoilAdjustmentRecords`) |

#### Parameters for remove_soil_flag

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_debtor_seq` | IN | NUMBER | — | Not individually described in the retrieved source* |

### insert_soil_tran member

| Field | Value |
| --- | --- |
| **Returns** | cursor/result set |
| **Called by** | SQL-003 (`CollectionsRepository.CreateSoilAdjustmentRecords`) |

No parameters were extracted from the retrieved signature; refer to the Source Code section below*.

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-030](../business-rules.md#proc-rule-030-soil-eligibility) | SOIL eligibility | Balance ≥ 0 and INCLUDE_ON_SOIL_FLAG = 'Y' | INSERT SOIL_DEBTOR_REFERENCE from non-worker debtors (debtor_ssn without '-'), worker debtors (TEMP_DATA_PERSON_ADDRESS by run_id) and third-party debtors (TAX_ID) |
| [PROC-RULE-031](../business-rules.md#proc-rule-031-audit-user) | Audit user | Insert | update_user = 'LCB0006' |
| [PROC-RULE-032](../business-rules.md#proc-rule-032-balances-are-summed-per-ssn) | Balances are summed per SSN | Reference rows for the run date | Cursor ordered by SSN; accumulate balances |
| [PROC-RULE-033](../business-rules.md#proc-rule-033-no-prior-dor-balance-sentinel) | No prior DOR balance sentinel | No DEPT_REVENUE_SOIL_BALANCE row | Use -.33 |
| [PROC-RULE-034](../business-rules.md#proc-rule-034-transaction-code) | Transaction code | Prior balance and new sum | 'A' if prior -.33 or 0 and sum > 0; 'D' if sum = 0; else 'C' |
| [PROC-RULE-035](../business-rules.md#proc-rule-035-zero-balance-debtors-leave-soil) | Zero-balance debtors leave SOIL | Trans code 'D' | UPDATE LEGAL_COLLECTION_DEBTOR include_on_soil_flag = 'N' |
| [PROC-RULE-036](../business-rules.md#proc-rule-036-only-changes-are-sent) | Only changes are sent | sum ≠ DOR balance | INSERT DEPT_REVENUE_SOIL_ADJUSTMENT change_amt = sum − balance |
| [PROC-RULE-037](../business-rules.md#proc-rule-037-name-split) | Name split | first_name = 'REPLACE' | Split the name on a comma |
| [PROC-RULE-038](../business-rules.md#proc-rule-038-dropped-debtors-are-deleted-at-dor) | Dropped debtors are deleted at DOR | DOR balance > 0 (latest received) with no reference row for the run date | Insert 'D' adjustment; errors raise -20000 with the SSN |

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

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| Create SOIL reference records | Snapshot SOIL-eligible debtors for the run | SQL-002 (`CollectionsRepository.CreateSoilDebtorReferenceRecords`) |
| Create SOIL adjustment transactions | Adds/changes/deletes DOR intercept balances | SQL-003 (`CollectionsRepository.CreateSoilAdjustmentRecords`) |

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

## Source Code

<details>
<summary>create_reference_records (procedure)</summary>

```sql

PROCEDURE create_reference_records
          (create_date_in IN DATE, run_id IN VARCHAR2)
IS BEGIN

INSERT INTO soil_debtor_reference
	(
		create_date,
		debtor_ssn,
		legal_coll_debtor_seq_num,
		legal_collection_seq_num,
		balance_amt,
		update_user,
		update_date,
		update_num
	)
SELECT create_date_in
      ,REPLACE(d.debtor_ssn, '-')
      ,d.LEGAL_COLL_DEBTOR_SEQ_NUM
      ,d.LEGAL_COLLECTION_SEQ_NUM
      ,l.current_balance_amt
      ,'LCB0006'
      ,SYSDATE
      ,0
  FROM legal_collection_debtor d
     , legal_collection l
 WHERE d.LEGAL_COLLECTION_SEQ_NUM = l.LEGAL_COLLECTION_SEQ_NUM
   AND l.current_balance_amt >= 0
   AND d.INCLUDE_ON_SOIL_FLAG = 'Y'
   and D.THIRD_PARTY_SEQ_NUM is null
   and D.WORKER_ID is null

UNION

SELECT create_date_in
      ,REPLACE(cp.primary_tax_id, '-')
      ,d.LEGAL_COLL_DEBTOR_SEQ_NUM
      ,d.LEGAL_COLLECTION_SEQ_NUM
      ,l.current_balance_amt
      ,'LCB0006'
      ,SYSDATE
      ,0
  FROM legal_collection_debtor d
     , legal_collection l
     , clm_dba.temp_data_person_address cp
 WHERE d.LEGAL_COLLECTION_SEQ_NUM = l.LEGAL_COLLECTION_SEQ_NUM
   AND d.worker_id = cp.person_id
   AND cp.run_id = run_id
   AND l.current_balance_amt >= 0
   AND d.INCLUDE_ON_SOIL_FLAG = 'Y'
   and D.THIRD_PARTY_SEQ_NUM is null
   and D.WORKER_ID is not null

UNION

SELECT create_date_in
      ,REPLACE(P.TAX_ID, '-')
      ,d.LEGAL_COLL_DEBTOR_SEQ_NUM
      ,d.LEGAL_COLLECTION_SEQ_NUM
      ,l.current_balance_amt
      ,'LCB0006'
      ,SYSDATE
      ,0
  FROM legal_collection_debtor d
     , legal_collection l
     , thrdparty tp
     , person p
 WHERE d.LEGAL_COLLECTION_SEQ_NUM = l.LEGAL_COLLECTION_SEQ_NUM
   AND l.current_balance_amt >= 0
   AND d.INCLUDE_ON_SOIL_FLAG = 'Y'
   and D.THIRD_PARTY_SEQ_NUM = TP.THRDPARTY_SEQ_NUM
   and TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM;

END create_reference_records;
```

</details>

<details>
<summary>create_SOIL_transactions (procedure)</summary>

```sql

PROCEDURE create_SOIL_transactions
          (create_date_in IN DATE, run_id IN VARCHAR2)
AS
v_refText              VARCHAR2(30);
v_lName                VARCHAR2(20);
v_fname                VARCHAR2(25);
v_Initial              VARCHAR2(1);
v_suffix               VARCHAR2(5);
v_sum                  NUMBER := 0;
v_last_ssn             VARCHAR2(11);
v_last_seq			   NUMBER := 0;
v_balance              NUMBER := 0;
v_trancode             VARCHAR2(1);
v_count                NUMBER := 0;
v_add                  NUMBER := 0;
v_change               NUMBER := 0;
v_delete               NUMBER := 0;

  CURSOR get_debtor_cursor IS
  --Affiliate
  SELECT r.legal_coll_debtor_seq_num,
       r.debtor_ssn,
       r.balance_amt,
	   l.policy_num,
	   l.saif_claim_num,
	   f.AFFILIATE_LAST_NAME AS last_name,
	   f.AFFILIATE_FIRST_NAME AS first_name,
	   f.affiliate_initial AS minitial,
	   f.affiliate_suffix AS suffix
  FROM soil_debtor_reference r, legal_collection l
    JOIN legal_collection_debtor d ON l.LEGAL_COLLECTION_SEQ_NUM = d.LEGAL_COLLECTION_SEQ_NUM
	JOIN affiliate f ON trim(d.AFFILIATE_ID) = f.AFFILIATE_SSN AND d.BUSINESS_AFFILIATE_FLAG = 'Y'
 WHERE TRUNC(r.create_date) = TRUNC(create_date_in)
   AND r.LEGAL_COLL_debtor_SEQ_NUM = d.LEGAL_COLL_debtor_SEQ_NUM
 UNION
 --Get worker name from claim
  SELECT r.legal_coll_debtor_seq_num,
       r.debtor_ssn,
       r.balance_amt,
	   l.policy_num,
	   l.saif_claim_num,
	   wkr.primary_last_name,
	   wkr.primary_first_name,
	   SUBSTR(wkr.primary_middle_name, 1,1) AS minitial,
	   wkr.primary_name_extension_code AS suffix
  FROM soil_debtor_reference r, legal_collection l
    JOIN legal_collection_debtor d ON l.LEGAL_COLLECTION_SEQ_NUM = d.LEGAL_COLLECTION_SEQ_NUM
	JOIN CLM_DBA.temp_data_person_address wkr ON d.worker_id = wkr.person_id AND d.worker_id IS NOT NULL
	     AND NVL(d.loc_seq_num,0) = 0
         AND wkr.run_id = run_id
 WHERE TRUNC(r.create_date) = TRUNC(create_date_in)
   AND r.LEGAL_COLL_debtor_SEQ_NUM = d.LEGAL_COLL_debtor_SEQ_NUM
 UNION
       --Use name on person table for thirdparty
  SELECT r.legal_coll_debtor_seq_num,
       r.debtor_ssn,
       r.balance_amt,
       l.policy_num,
       l.saif_claim_num,
       p.LAST_NAME,
       p.FIRST_NAME,
       P.MIDDLE_INITIAL  AS minitial,
       ''
  FROM soil_debtor_reference r, legal_collection l
    JOIN legal_collection_debtor d ON l.LEGAL_COLLECTION_SEQ_NUM = d.LEGAL_COLLECTION_SEQ_NUM
    JOIN thrdparty tp on D.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM
    JOIN person p on TP.PERSON_SEQ_NUM = p.person_seq_Num
 WHERE TRUNC(r.create_date) = TRUNC(create_date_in)
   AND r.LEGAL_COLL_debtor_SEQ_NUM = d.LEGAL_COLL_debtor_SEQ_NUM
 UNION
 --Use name on legal_coll_debtor table
  SELECT r.legal_coll_debtor_seq_num,
       r.debtor_ssn,
       r.balance_amt,
	   l.policy_num,
	   l.saif_claim_num,
	   d.last_name,
	   d. first_name,
	   d.middle_initial  AS minitial,
	   d.suffix
  FROM soil_debtor_reference r, legal_collection l
    JOIN legal_collection_debtor d ON l.LEGAL_COLLECTION_SEQ_NUM = d.LEGAL_COLLECTION_SEQ_NUM
	         AND d.BUSINESS_AFFILIATE_FLAG = 'N'
			 AND d.worker_id IS NULL
 WHERE TRUNC(r.create_date) = TRUNC(create_date_in)
   AND r.LEGAL_COLL_debtor_SEQ_NUM = d.LEGAL_COLL_debtor_SEQ_NUM
   AND D.THIRD_PARTY_SEQ_NUM is null
   ORDER BY 2;

 debtor_rec    get_debtor_cursor%ROWTYPE;

 CURSOR get_delete_cursor IS
 SELECT ssn, reference_text, balance_amt, debtor_name
   FROM dept_revenue_soil_balance b1
  WHERE b1.BALANCE_AMT > 0
    AND TRUNC(b1.received_by_saif_date) = (SELECT MAX(TRUNC(b2.received_by_saif_date))
     FROM dept_revenue_soil_balance b2)
    AND NOT EXISTS
 (SELECT 1 FROM soil_debtor_reference b3
  WHERE b1.ssn = b3.debtor_ssn
         AND  TRUNC(b3.create_date) = TRUNC(create_date_in));

delete_rec get_delete_cursor%ROWTYPE;
```

</details>

<details>
<summary>find_DOR_balance (procedure)</summary>

```sql

 PROCEDURE find_DOR_balance(debtor_ssn IN VARCHAR2)
       IS

BEGIN
BEGIN
  SELECT balance_amt INTO v_balance
        FROM dept_revenue_soil_balance
       WHERE ssn = debtor_ssn
         AND received_by_SAIF_date = (SELECT MAX(received_by_SAIF_date) FROM dept_revenue_soil_balance);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
       v_balance := -.33;
   END;

END find_DOR_balance;
```

</details>

<details>
<summary>remove_soil_flag (procedure)</summary>

```sql

PROCEDURE remove_soil_flag(in_debtor_seq IN NUMBER)
    IS
	BEGIN
	BEGIN
	    UPDATE legal_collection_debtor
		         SET include_on_soil_flag = 'N',
				           update_user = 'LCB0006',
						   update_date = SYSDATE,
						   update_num = update_num + 1
				 WHERE legal_coll_debtor_seq_num = in_debtor_seq;
	END;
	END remove_soil_flag;
```

</details>

<details>
<summary>insert_soil_tran (procedure)</summary>

```sql

 PROCEDURE insert_soil_tran
 IS
 BEGIN
     -- Update to re-add users if they have a previous record in DORS with a zero balance
     -- and they still have an outstanding balance in the debtor_reference table
     IF (v_balance = -.33 OR v_balance = 0) AND v_sum > 0 THEN
			      v_trancode := 'A';
				  v_add := v_add + 1;
				  v_balance := 0;
	 ELSE
	      IF v_sum = 0 THEN
				     v_trancode := 'D';
					 v_delete := v_delete + 1;
					 remove_soil_flag(v_last_seq);
		  ELSE
	 	         v_trancode := 'C';
				 v_change := v_change + 1;
		  END IF;
```

</details>

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

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.PK80011_LEGALCOL_SOIL`
- Legends CLI query executed 2026-09-24: `legends oracle query --name PK80011_LEGALCOL_SOIL --output analysis/oracle-source/pk80011-legalcol-soil.sql`
- Calling code: `Data/SAIF.Data.NHibernate/Repositories/CollectionsRepository.cs`