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	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	Audit user	Insert	update_user = 'LCB0006'
PROC-RULE-032	Balances are summed per SSN	Reference rows for the run date	Cursor ordered by SSN; accumulate balances
PROC-RULE-033	No prior DOR balance sentinel	No DEPT_REVENUE_SOIL_BALANCE row	Use -.33
PROC-RULE-034	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	Zero-balance debtors leave SOIL	Trans code 'D'	UPDATE LEGAL_COLLECTION_DEBTOR include_on_soil_flag = 'N'
PROC-RULE-036	Only changes are sent	sum ≠ DOR balance	INSERT DEPT_REVENUE_SOIL_ADJUSTMENT change_amt = sum − balance
PROC-RULE-037	Name split	first_name = 'REPLACE'	Split the name on a comma
PROC-RULE-038	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.

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.

Source Code¶

create_reference_records (procedure)

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;

create_SOIL_transactions (procedure)

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;

find_DOR_balance (procedure)

 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;

remove_soil_flag (procedure)

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;

insert_soil_tran (procedure)

 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;

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