--- title: "PK80010_LEGAL_COLLECTIONS 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: "PK80010_LEGAL_COLLECTIONS" source_type: "PACKAGE" retrieval_status: "found" retrieval_command: "legends oracle query --name PK80010_LEGAL_COLLECTIONS --output analysis/oracle-source/pk80010-legal-collections.sql" retrieval_date: "2026-09-24" --- # PK80010_LEGAL_COLLECTIONS ## 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: get_cases_by_reference, get_category_totals, getDebtorSSN, getDebtorName, getPrimaryDebtorName, get_fraud_other_address, get_policy_address, get_thirdparty_address, search_collections, applyPayments (→ applyIt, legalcol_payment_ref_insert), interest_calc_select (→ get_interest_start_date), getPolicyPayments, getThirdPartyPayments, getDebtorsWithoutPayments, getDebtorsWithExpiredLocs, getBalanceBroughtToMinAmount, get_last_statement_balance > **Scope:** `PK80010_LEGAL_COLLECTIONS` is a large package (25 members). Only the 20 member(s) actually called by SAIF.Data.NHibernate (`get_cases_by_reference, get_category_totals, get_fraud_other_address, get_policy_address, get_thirdparty_address, applyPayments, applyIt, search_collections, interest_calc_select, getPrimaryDebtorName, getDebtorName, getDebtorSSN, getPolicyPayments, getThirdPartyPayments, getDebtorsWithoutPayments, getDebtorsWithExpiredLocs, getBalanceBroughtToMinAmount, legalcol_payment_ref_insert, get_last_statement_balance, get_interest_start_date`) 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 ### get_cases_by_reference member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-004 (`CollectionsRepository.GetCasesByReferenceNumber`) | #### Parameters for get_cases_by_reference | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `reference_id_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | ### get_category_totals member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-005 (`CollectionsRepository.GetCategoryTotals`) | #### Parameters for get_category_totals | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `legal_collection_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### get_fraud_other_address member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-009, SQL-010, SQL-011 (`CollectionsRepository.GetFraudOtherAddress`, `CollectionsRepository.GetPolicyAddress`, `CollectionsRepository.GetThirdPartyAddress`) | #### Parameters for get_fraud_other_address | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `loc_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### get_policy_address member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-009, SQL-010, SQL-011 (`CollectionsRepository.GetFraudOtherAddress`, `CollectionsRepository.GetPolicyAddress`, `CollectionsRepository.GetThirdPartyAddress`) | #### Parameters for get_policy_address | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `debtor_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### get_thirdparty_address member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-009, SQL-010, SQL-011 (`CollectionsRepository.GetFraudOtherAddress`, `CollectionsRepository.GetPolicyAddress`, `CollectionsRepository.GetThirdPartyAddress`) | #### Parameters for get_thirdparty_address | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `third_party_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### applyPayments member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-052, SQL-053 (`Collections.hbm.xml (no caller).(none — ApplyPayments uses inline SQL)`, `CollectionsRepository.ApplyPayments`) | #### Parameters for applyPayments | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `inSeqNum` | IN | NUMBER | — | Not individually described in the retrieved source* | | `pmt_date` | IN | DATE | — | Not individually described in the retrieved source* | ### applyIt member | Field | Value | | --- | --- | | **Returns** | scalar/function value* | | **Called by** | SQL-052, SQL-053 (`Collections.hbm.xml (no caller).(none — ApplyPayments uses inline SQL)`, `CollectionsRepository.ApplyPayments`) | #### Parameters for applyIt | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_seq_num` | IN | NUMBER | — | Not individually described in the retrieved source* | | `in_payment_tran_seq_num` | IN | NUMBER | — | Not individually described in the retrieved source* | | `in_amt` | IN | NUMBER | — | Not individually described in the retrieved source* | | `in_date` | IN | DATE | — | Not individually described in the retrieved source* | | `in_update_user` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | ### search_collections member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-012 (`CollectionsRepository.SearchCollections`) | #### Parameters for search_collections | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `last_name_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `first_name_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `ssn_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `policy_name_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `show_closed_cases_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `run_id_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | ### interest_calc_select member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-013 (`CollectionsRepository.GetInterest`) | #### Parameters for interest_calc_select | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `legal_collection_seq_num_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | | `run_date_in` | IN | DATE | — | Not individually described in the retrieved source* | ### getPrimaryDebtorName member | Field | Value | | --- | --- | | **Returns** | scalar/function value* | | **Called by** | SQL-006, SQL-007, SQL-008 (`CollectionsRepository.GetDebtorName`, `CollectionsRepository.GetDebtorSsn`, `CollectionsRepository.GetPrimaryDebtorName`) | #### Parameters for getPrimaryDebtorName | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `legal_collection_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### getDebtorName member | Field | Value | | --- | --- | | **Returns** | scalar/function value* | | **Called by** | SQL-006, SQL-007, SQL-008 (`CollectionsRepository.GetDebtorName`, `CollectionsRepository.GetDebtorSsn`, `CollectionsRepository.GetPrimaryDebtorName`) | #### Parameters for getDebtorName | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `debtor_seq_num_in` | IN | NUMBER | — | Not individually described in the retrieved source* | ### getDebtorSSN member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-006, SQL-007, SQL-008 (`CollectionsRepository.GetDebtorName`, `CollectionsRepository.GetDebtorSsn`, `CollectionsRepository.GetPrimaryDebtorName`) | #### Parameters for getDebtorSSN | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `debtor_seq_num_in` | IN | VARCHAR2 | — | Not individually described in the retrieved source* | ### getPolicyPayments member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for getPolicyPayments | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `selectDate` | IN | Date | — | Not individually described in the retrieved source* | ### getThirdPartyPayments member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for getThirdPartyPayments | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `selectDate` | IN | Date | — | Not individually described in the retrieved source* | ### getDebtorsWithoutPayments member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for getDebtorsWithoutPayments | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `selectDate` | IN | Date | — | Not individually described in the retrieved source* | ### getDebtorsWithExpiredLocs member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for getDebtorsWithExpiredLocs | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `selectDate` | IN | Date | — | Not individually described in the retrieved source* | ### getBalanceBroughtToMinAmount member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for getBalanceBroughtToMinAmount | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `recordset` | OUT | ref_cursor | — | Not individually described in the retrieved source* | | `selectDate` | IN | Date | — | Not individually described in the retrieved source* | ### legalcol_payment_ref_insert member | Field | Value | | --- | --- | | **Returns** | cursor/result set | | **Called by** | SQL-052, SQL-053 (`Collections.hbm.xml (no caller).(none — ApplyPayments uses inline SQL)`, `CollectionsRepository.ApplyPayments`) | #### Parameters for legalcol_payment_ref_insert | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `payment_tran_seq_num_in` | IN | legal_collection_payment_ref.payment_tran_seq_num%TYPE | — | Not individually described in the retrieved source* | | `legal_coll_tran_seq_num_in` | IN | legal_collection_payment_ref.legal_coll_tran_seq_num%TYPE | — | Not individually described in the retrieved source* | | `legal_collection_seq_num_in` | IN | legal_collection_payment_ref.legal_collection_seq_num%TYPE | — | Not individually described in the retrieved source* | | `applied_amt_in` | IN | legal_collection_payment_ref.applied_amt%TYPE | — | Not individually described in the retrieved source* | | `applied_date_in` | IN | legal_collection_payment_ref.applied_date%TYPE | — | Not individually described in the retrieved source* | | `gl_report_date_in` | IN | legal_collection_payment_ref.gl_report_date%TYPE | — | Not individually described in the retrieved source* | | `create_user_in` | IN | legal_collection_payment_ref.create_user%TYPE | — | Not individually described in the retrieved source* | | `create_date_in` | IN | legal_collection_payment_ref.create_date%TYPE | — | Not individually described in the retrieved source* | ### get_last_statement_balance member | Field | Value | | --- | --- | | **Returns** | scalar/function value* | | **Called by** | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | #### Parameters for get_last_statement_balance | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `last_statement_date_in` | IN | legal_collection_debtor.last_statement_date%TYPE | — | Not individually described in the retrieved source* | | `debtor_seq_num_in` | IN | legal_collection_debtor.legal_coll_debtor_seq_num%TYPE | — | Not individually described in the retrieved source* | ### get_interest_start_date member | Field | Value | | --- | --- | | **Returns** | scalar/function value* | | **Called by** | SQL-013 (`CollectionsRepository.GetInterest`) | #### Parameters for get_interest_start_date | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `legal_coll_seq_num_in` | IN | legal_collection_note.legal_collection_seq_num%TYPE | — | Not individually described in the retrieved source* | ## Business Rules Rules extracted from this procedure's logic: | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | [PROC-RULE-001](../business-rules.md#proc-rule-001-numeric-references-match-case-id%2C-policy-number-or-claim-number) | Numeric references match case id, policy number or claim number | reference_id_in is numeric and ≠ 0 | Match LEGAL_COLLECTION seq_num, policy_num or saif_claim_num | | [PROC-RULE-002](../business-rules.md#proc-rule-002-non-numeric-references) | Non-numeric references (OD claims) match the claim number only; the case description depends on the case code | Non-numeric reference; code WRKR/FOTH/THRD/CTHRD vs POL/FPOL/CPOL | Match saif_claim_num; describe as claim number, or as policy number + name | | [PROC-RULE-003](../business-rules.md#proc-rule-003-payments-and-credit-adjustments-are-shown-negative) | Payments and credit adjustments are shown negative | Tran code PMT or CADJ | Negate the amount; RPMT is shown as Payments | | [PROC-RULE-004](../business-rules.md#proc-rule-004-fixed-display-order-of-categories) | Fixed display order of categories | Category code | BBAL 1, COST2 2, PJFEE 2 ("Pre-Judgment Fees"), INT2 3, COST3 4, INT 5, COST 6, CADJ 7, DADJ 8, other 10 | | [PROC-RULE-005](../business-rules.md#proc-rule-005-the-subtotal-excludes-payments) | The subtotal excludes payments | Display row 9 | Sum all categories except PMT/RPMT (CADJ negated); Current Balance is row 11 | | [PROC-RULE-006](../business-rules.md#proc-rule-006-judgment-description-source) | Judgment description source | Judgment code NA | Use code_descr; otherwise JUDGMENT_DESCR | | [PROC-RULE-007](../business-rules.md#proc-rule-007-foreign-address-flag) | Foreign address flag | LOC_ADDR3 populated* | ForeignAddressFlag derived from LOC_ADDR3 on LOCATION_VIEW | | [PROC-RULE-008](../business-rules.md#proc-rule-008-debtor-location-precedence) | Debtor location precedence | Debtor has loc_seq_num | Use the debtor location, else the policy's active 'POL' location (rownum = 1) | | [PROC-RULE-009](../business-rules.md#proc-rule-009-third-party-contact-sources) | Third-party contact sources | Person or firm third party | UNION ALL person address and company address; phone type 'P', fax type 'F' | | [PROC-RULE-010](../business-rules.md#proc-rule-010-closed-cases-are-hidden-unless-requested) | Closed cases are hidden unless requested | show_closed_cases_in = 'N' | Add CURRENT_BALANCE_AMT > 0 to every search branch | | [PROC-RULE-011](../business-rules.md#proc-rule-011-search-branch-selection) | Search branch selection | policy_name_in supplied vs not | Policy/company name search (POLICY_SEARCH types LEGAL/TRD with DBA labelling, third-party companies); else worker name via CLM_DBA.TEMP_DATA_PERSON_ADDRESS (run_id), worker SSN, affiliate, third-party person, fraud-other | | [PROC-RULE-012](../business-rules.md#proc-rule-012-debtor-name-resolution-order) | Debtor name resolution order | First match (rownum ≤ 1) | Affiliate → policy name → third-party person → third-party company → fraud-other (FOTH, suffix included) | | [PROC-RULE-013](../business-rules.md#proc-rule-013-debtor-ssn-source) | Debtor SSN source (PII) | Debtor type | Affiliate match, third-party person TAX_ID, or FOTH debtor SSN | | [PROC-RULE-014](../business-rules.md#proc-rule-014-'all'-selects-every-case) | 'ALL' selects every case | legal_collection_seq_num_in = 'ALL' | Convert to '%' LIKE pattern | | [PROC-RULE-015](../business-rules.md#proc-rule-015-monthly-interest-accrues-only-after-a-full-prior-month) | Monthly interest accrues only after a full prior month | Interest option MTH | ROUND(outstanding × rate / 12 × (1 if days since interest start ≥ days in prior month else 0), 2) | | [PROC-RULE-016](../business-rules.md#proc-rule-016-daily-interest) | Daily interest | Interest option DAY | outstanding × rate × days / 365.25, rounded; only INTEREST_FLAG = 'Y', outstanding > 0, rate > 0; only interest > 0 returned | | [PROC-RULE-017](../business-rules.md#proc-rule-017-interest-start-date-and-statement-balance) | Interest start date and statement balance | Case transactions | Start = TRUNC(MAX(date)) where REFERENCE_TEXT = 'LCB00003', else MIN(date), else SYSDATE; statement balance = sum to date with PMT/CADJ negated, NO_DATA_FOUND → 0 | | [PROC-RULE-018](../business-rules.md#proc-rule-018-unposted-payments-for-open-cases) | Unposted payments for open cases | Case balance > 0 | Policy financial trans with trans_amt < 0 on/after date and no matching LC transaction; third-party distributions created after date | | [PROC-RULE-019](../business-rules.md#proc-rule-019-statement-and-follow-up-selection) | Statement and follow-up selection | Balance and hold flags | No DPMT after date with HOLD_STATEMENT_FLAG = 'N'; expired policy location updated after date; balance < 100 whose latest transaction is DPMT after date | | [PROC-RULE-025](../business-rules.md#proc-rule-025-only-unapplied-payments-are-applied) | Only unapplied payments are applied | Payment tran PAY_DOWN_ORDER = 0 and outstanding > 0 (inSeqNum = 0 → all cases) | Process in date order | | [PROC-RULE-026](../business-rules.md#proc-rule-026-pay-down-order) | Pay-down order | Cost transactions PAY_DOWN_ORDER > 0 | Apply to costs ordered by pay_down_order, date; reduce outstanding, increment update_num | | [PROC-RULE-027](../business-rules.md#proc-rule-027-every-application-is-recorded) | Every application is recorded | Amount applied to a cost | INSERT LEGAL_COLLECTION_PAYMENT_REF | | [PROC-RULE-028](../business-rules.md#proc-rule-028-the-payment-keeps-the-unapplied-remainder) | The payment keeps the unapplied remainder; the payment date is ignored | After applyIt | UPDATE payment outstanding = remainder; pmt_date unused (date filter commented out) | > These rules are documented in [business-rules.md](../business-rules.md). ## Operations Operations and processes implemented in this procedure: | Operation | Description | Trigger | | --- | --- | --- | | Look up collection cases by reference | Returns case headers for a case, policy or claim reference | SQL-004 (`CollectionsRepository.GetCasesByReferenceNumber`) | | Summarise case category totals | Category totals, subtotal and current balance for a case | SQL-005 (`CollectionsRepository.GetCategoryTotals`) | | Resolve debtor name / SSN | Resolve display name or SSN across debtor types | SQL-006, SQL-007, SQL-008 (`CollectionsRepository.GetDebtorName`, `CollectionsRepository.GetDebtorSsn`, `CollectionsRepository.GetPrimaryDebtorName`) | | Get debtor addresses | Contact info for fraud-other, policy and third-party debtors | SQL-009, SQL-010, SQL-011 (`CollectionsRepository.GetFraudOtherAddress`, `CollectionsRepository.GetPolicyAddress`, `CollectionsRepository.GetThirdPartyAddress`) | | Search collections | Multi-branch debtor/case search | SQL-012 (`CollectionsRepository.SearchCollections`) | | Apply payments to costs | Allocates unapplied payments across cost transactions by pay-down order | SQL-052, SQL-053 (`Collections.hbm.xml (no caller).(none — ApplyPayments uses inline SQL)`, `CollectionsRepository.ApplyPayments`) | | Calculate interest | Monthly or daily interest per interest-bearing transaction | SQL-013 (`CollectionsRepository.GetInterest`) | | Select payments and debtors for statements | Batch selections for payment posting and statements | SQL-014, SQL-015, SQL-016, SQL-017, SQL-018, SQL-021 (`CollectionsRepository.BalanceBroughtToMinAmountTransactions`, `CollectionsRepository.DebtorLastStatementBalance`, `CollectionsRepository.DebtorsWithExpiredLocs`, `CollectionsRepository.DebtorsWithoutPayments`, `CollectionsRepository.GetMissingPolicyPayments`, `CollectionsRepository.GetThirdPartyPayments`) | > These operations are also listed in [operations-processes.md](../operations-processes.md). ## Source Code
get_cases_by_reference (procedure) ```sql PROCEDURE get_cases_by_reference (recordset OUT ref_cursor, reference_id_in IN VARCHAR2) IS BEGIN -- Check for a numeric reference_id_in value. IF LENGTH(TRIM(TRANSLATE(reference_id_in, '0123456789',' '))) is null THEN -- This query executes for all but OD claims based cases. OPEN recordset FOR SELECT l.legal_collection_seq_num ID ,l.received_date ReceivedDate ,l.current_balance_amt CurrentBalanceAmount ,legal_collection_code CollectionCode ,LC.CODE_DESCR CollectionCodeDescription ,'' DebtorName ,l.legal_collection_seq_num LegalCollectionSeqNum ,l.saif_claim_num ClaimNumber ,CASE WHEN l.legal_collection_code in ('WRKR', 'FOTH', 'THRD', 'CTHRD') THEN (SELECT distinct COL.SAIF_CLAIM_NUM CaseDescription FROM legal_collection COL WHERE COL.LEGAL_COLLECTION_SEQ_NUM = l.legal_collection_seq_num) WHEN l.legal_collection_code in ('POL', 'FPOL', 'CPOL') THEN (SELECT COL.POLICY_NUM || ' - ' || trim(pol.POLICY_NAME) FROM legal_collection COL ,policy pol WHERE COL.POLICY_NUM = pol.POLICY_NUM AND COL.LEGAL_COLLECTION_SEQ_NUM = l.legal_collection_seq_num) END CaseDescription, CASE WHEN NVL(l.policy_num, 0) <> 0 THEN to_char(l.policy_num) Else l.SAIF_CLAIM_NUM End ReferenceNumber FROM legal_collection l, legal_collection_code lc WHERE l.legal_collection_seq_num in ( SELECT l.legal_collection_seq_num FROM legal_collection l WHERE (L.LEGAL_COLLECTION_SEQ_NUM = reference_id_in OR l.policy_num = reference_id_in OR l.saif_claim_num = reference_id_in) AND reference_id_in <> 0) AND L.LEGAL_COLLECTION_CODE = LC.CODE ORDER BY 1 DESC; ELSE -- Allows for OD claims OPEN recordset FOR SELECT l.legal_collection_seq_num ID ,l.received_date ReceivedDate ,l.current_balance_amt CurrentBalanceAmount ,legal_collection_code CollectionCode ,LC.CODE_DESCR CollectionCodeDescription ,'' DebtorName ,CASE WHEN l.legal_collection_code in ('WRKR', 'FOTH', 'THRD', 'CTHRD') THEN (SELECT distinct COL.SAIF_CLAIM_NUM CaseDescription FROM legal_collection COL WHERE COL.LEGAL_COLLECTION_SEQ_NUM = l.legal_collection_seq_num) WHEN l.legal_collection_code in ('POL', 'FPOL', 'CPOL') THEN (SELECT COL.POLICY_NUM || ' - ' || trim(pol.POLICY_NAME) FROM legal_collection COL ,policy pol WHERE COL.POLICY_NUM = pol.POLICY_NUM AND COL.LEGAL_COLLECTION_SEQ_NUM = l.legal_collection_seq_num) END CaseDescription, CASE WHEN NVL(l.policy_num, 0) <> 0 THEN to_char(l.policy_num) Else l.SAIF_CLAIM_NUM End ReferenceNumber FROM legal_collection l, legal_collection_code lc WHERE l.legal_collection_seq_num in ( SELECT l.legal_collection_seq_num FROM legal_collection l WHERE l.saif_claim_num = reference_id_in) AND L.LEGAL_COLLECTION_CODE = LC.CODE ORDER BY 1 DESC; END IF; END get_cases_by_reference; /******************************************** ** UPDATE PROCEDURE FOR BALANCE ON LEGAL_COLLECTION" ** DATE: 16-JUN-05 09:39:01 *********************************************/ ```
get_category_totals (procedure) ```sql PROCEDURE get_category_totals (recordset OUT ref_cursor, legal_collection_seq_num_in IN NUMBER) AS BEGIN OPEN recordset FOR -- Reverse Payments are summarized as Payments and will reduce the Payments category amount. -- The DISPLAY_SEQ_NUM column is used to produce the required category display order. SELECT legal_collection_seq_num id, category_descr description, SUM (category_amt) amount, SUM (category_outstanding_amt) outstandingamount, MIN (display_seq_num) displayorder FROM ( SELECT lc.legal_collection_seq_num, DECODE ( tc.LC_TRAN_CATEGORY_CODE, 'RPMT', 'Payments', 'PJFEE', 'Pre-Judgment Fees', DECODE (MIN (lc.JUDGMENT_CODE), 'NA', c.code_descr, MIN (c.JUDGMENT_DESCR))) category_descr, DECODE (tc.LC_TRAN_CATEGORY_CODE, 'PMT', SUM (t.transaction_amt) * -1, 'CADJ', SUM (t.transaction_amt) * -1, SUM (t.transaction_amt)) category_amt, SUM (t.outstanding_amt) category_outstanding_amt, DECODE (tc.LC_TRAN_CATEGORY_CODE, 'BBAL', 1, 'COST', 6, 'COST2', 2, 'COST3', 4, 'INT', 5, 'INT2', 3, 'CADJ', 7, 'DADJ', 8, 10) display_seq_num FROM legal_collection_transaction t, legal_collection lc, lc_tran_category_code c, legal_collection_tran_code tc WHERE t.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in AND t.LEGAL_COLLECTION_SEQ_NUM = lc.LEGAL_COLLECTION_SEQ_NUM AND t.LEGAL_COLLECTION_TRAN_CODE = tc.code AND tc.LC_TRAN_CATEGORY_CODE = c.CODE AND t.LEGAL_COLLECTION_TRAN_CODE <> 'PJFEE' GROUP BY lc.legal_collection_seq_num, c.code_descr, tc.LC_TRAN_CATEGORY_CODE UNION -- PJFEE will always display as 'Pre-Judgment Fees'. SELECT legal_collection_seq_num, 'Pre-Judgment Fees' category_descr, SUM (t.transaction_amt) category_amt, SUM (t.outstanding_amt) category_outstanding_amt, 2 display_seq_num FROM legal_collection_transaction t WHERE t.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in AND t.LEGAL_COLLECTION_TRAN_CODE = 'PJFEE' GROUP BY legal_collection_seq_num, 2) WHERE Category_amt IS NOT NULL GROUP BY legal_collection_seq_num, category_descr UNION -- Payments and Reverse payments are not included in the Subtotal. Credit Adjustments reduce the Subtotal. SELECT legal_collection_seq_num, category_descr, category_amt, category_outstanding_amt, display_seq_num FROM ( SELECT lc.legal_collection_seq_num, 'Subtotal' category_descr, SUM ( DECODE (c.code, 'CADJ', t.transaction_amt * -1, t.transaction_amt)) category_amt, SUM (t.outstanding_amt) category_outstanding_amt, 9 display_seq_num FROM legal_collection_transaction t, legal_collection lc, lc_tran_category_code c, legal_collection_tran_code tc WHERE t.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in AND t.LEGAL_COLLECTION_SEQ_NUM = lc.LEGAL_COLLECTION_SEQ_NUM AND t.LEGAL_COLLECTION_TRAN_CODE = tc.code AND tc.LC_TRAN_CATEGORY_CODE = c.CODE AND tc.LC_TRAN_CATEGORY_CODE NOT IN ('PMT', 'RPMT') GROUP BY lc.legal_collection_seq_num) WHERE Category_amt IS NOT NULL UNION SELECT legal_collection_seq_num, 'Current Balance', current_balance_amt, current_balance_amt category_outstanding_amt, 11 display_seq_num FROM legal_collection WHERE legal_collection_seq_num = legal_collection_seq_num_in ORDER BY 5; END get_category_totals; ```
get_fraud_other_address (procedure) ```sql PROCEDURE get_fraud_other_address (recordset OUT ref_cursor, loc_seq_num_in IN NUMBER) AS BEGIN OPEN recordset FOR SELECT l.LOC_ADDR1 ADDRESSLINE1, l.LOC_ADDR2 ADDRESSLINE2, l.LOC_ADDR3 ADDRESSLINE3, l.LOCATION_NAME ADDRESSLINE4, l.LOC_CITY City, l.LOC_STATE StateCode, l.LOC_ZIP ZipCode, l.LOC_ZIP4 ZipCodeExtension, DECODE(l.LOC_ADDR3, '', 'N', 'Y') ForeignAddressFlag, l.LOCATION_CODE AddressCode, l.LOC_PHONE ||l.LOC_PHONE_EXT Phone, l.LOC_FAX Fax FROM LOCATION_VIEW l WHERE L.LOC_SEQ_NUM = loc_seq_num_in; END get_fraud_other_address; ```
get_policy_address (procedure) ```sql PROCEDURE get_policy_address (recordset OUT ref_cursor, debtor_seq_num_in IN NUMBER) AS BEGIN OPEN recordset FOR SELECT l.LOC_ADDR1 ADDRESSLINE1, l.LOC_ADDR2 ADDRESSLINE2, l.LOC_ADDR3 ADDRESSLINE3, l.LOCATION_NAME ADDRESSLINE4, l.LOC_CITY City, l.LOC_STATE StateCode, l.LOC_ZIP ZipCode, l.LOC_ZIP4 ZipCodeExtension, DECODE(l.LOC_ADDR3, '', 'N', 'Y') ForeignAddressFlag, l.LOCATION_CODE AddressCode, l.LOC_PHONE ||l.LOC_PHONE_EXT Phone, l.LOC_FAX Fax FROM LOCATION l WHERE loc_seq_num in ( select loc_seq_num from ( select loc_seq_num from legal_collection lc, legal_collection_debtor d where LC.LEGAL_COLLECTION_SEQ_NUM = D.LEGAL_COLLECTION_SEQ_NUM and D.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in and loc_seq_num is not null Union all select l.loc_seq_num from legal_collection lc, legal_collection_debtor d, LOCATION l where LC.LEGAL_COLLECTION_SEQ_NUM = D.LEGAL_COLLECTION_SEQ_NUM AND LC.POLICY_NUM = L.POLICY_NUM AND D.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in AND l.loc_exp_date IS NULL AND l.location_code = 'POL' ) where rownum = 1 ); END get_policy_address; ```
get_thirdparty_address (procedure) ```sql PROCEDURE get_thirdparty_address (recordset OUT ref_cursor, third_party_seq_num_in IN NUMBER) AS BEGIN OPEN recordset FOR SELECT A.ADDRESS_LINE1 AddressLine1, A.ADDRESS_LINE2 AddressLine2, A.ADDRESS_LINE3 AddressLine3, A.ADDRESS_LINE4 AddressLine4, A.CITY_NAME City, A.STATE StateCode, A.ZIP_CODE ZipCode, A.ZIP_CODE_EXT ZipCodeExtension, A.FOREIGN_ADDRESS_FLAG ForeignAddressFlag, A.ADDRESS_CODE AddressCode, (select phone_num from phone p, person_phone pp where PP.PERSON_SEQ_NUM = TP.PERSON_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_CODE = 'P') Phone, (select phone_num from phone p, person_phone pp where PP.PERSON_SEQ_NUM = TP.PERSON_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_CODE = 'F') Fax FROM thrdparty tp, person_address pa, address a WHERE TP.PERSON_SEQ_NUM = PA.PERSON_SEQ_NUM AND PA.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM AND TP.THRDPARTY_SEQ_NUM = third_party_seq_num_in UNION ALL SELECT A.ADDRESS_LINE1 AddressLine1, A.ADDRESS_LINE2 AddressLine2, A.ADDRESS_LINE3 AddressLine3, A.ADDRESS_LINE4 AddressLine4, A.CITY_NAME City, A.STATE StateCode, A.ZIP_CODE ZipCode, A.ZIP_CODE_EXT ZipCodeExtension, A.FOREIGN_ADDRESS_FLAG ForeignAddressFlag, A.ADDRESS_CODE AddressCode, (select phone_num from phone p, person_phone pp where PP.PERSON_SEQ_NUM = TP.PERSON_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_CODE = 'P') Phone, (select phone_num from phone p, person_phone pp where PP.PERSON_SEQ_NUM = TP.PERSON_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_SEQ_NUM = PP.PHONE_SEQ_NUM and P.PHONE_CODE = 'F') Fax FROM thrdparty tp, thrdparty_firm tf, company_address ca, address a WHERE TP.THRDPARTY_FIRM_SEQ_NUM = TF.THRDPARTY_FIRM_SEQ_NUM AND TF.COMPANY_SEQ_NUM = CA.COMPANY_SEQ_NUM AND ca.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM AND TP.THRDPARTY_SEQ_NUM = third_party_seq_num_in; END get_thirdparty_address; ```
applyPayments (procedure) ```sql PROCEDURE applyPayments(inSeqNum IN NUMBER, pmt_date IN DATE) IS v_complete VARCHAR2(1); v_amt_left NUMBER; payment_cursor legal_collection_trans_cursor; payment_rec payment_cursor%ROWTYPE; BEGIN OPEN payment_cursor FOR SELECT t.legal_coll_tran_seq_num, t.legal_collection_seq_num, t.legal_collection_tran_code, t.transaction_date, t.transaction_amt, t.outstanding_amt, t.interest_flag, t.update_date, t.update_user, t.update_num, t.approved_by_user, t.reference_text, c.LC_TRAN_CATEGORY_CODE, T.SYSTEM_GENERATED_FLAG FROM legal_collection_transaction t, legal_collection_tran_code c, lc_tran_category_code g WHERE (legal_collection_seq_num = inSeqNum OR inSeqNum = 0) AND outstanding_amt > 0 AND t.LEGAL_COLLECTION_TRAN_CODE = c.CODE AND c.LC_TRAN_CATEGORY_CODE = g.CODE AND g.PAY_DOWN_ORDER = 0 ORDER BY t.TRANSACTION_DATE; LOOP FETCH payment_cursor INTO payment_rec; EXIT WHEN payment_cursor%NOTFOUND; v_amt_left := applyIt(payment_rec.legal_collection_seq_num, payment_rec.legal_coll_tran_seq_num, payment_rec.outstanding_amt, payment_rec.transaction_date, payment_rec.update_user); UPDATE legal_collection_transaction SET outstanding_amt = v_amt_left, update_date = SYSDATE, update_num = payment_rec.update_num + 1 WHERE legal_coll_tran_seq_num = payment_rec.legal_coll_tran_seq_num; END LOOP; CLOSE payment_cursor; END applyPayments; ```
applyIt (function) ```sql FUNCTION applyIt(in_seq_num IN NUMBER, in_payment_tran_seq_num IN NUMBER, in_amt IN NUMBER, in_date IN DATE, in_update_user IN VARCHAR2) RETURN NUMBER AS v_amount NUMBER := in_amt; v_out NUMBER := 0; v_applied NUMBER := 0; cost_cursor legal_collection_trans_cursor; cost_rec cost_cursor%ROWTYPE; BEGIN OPEN cost_cursor FOR SELECT t.legal_coll_tran_seq_num, t.legal_collection_seq_num, t.legal_collection_tran_code, t.transaction_date, t.transaction_amt, t.outstanding_amt, t.interest_flag, t.update_date, t.update_user, t.update_num, t.approved_by_user, t.reference_text, c.LC_TRAN_CATEGORY_CODE, T.SYSTEM_GENERATED_FLAG FROM legal_collection_transaction t, legal_collection_tran_code c, lc_tran_category_code g WHERE (legal_collection_seq_num = in_seq_num) AND outstanding_amt > 0 AND t.LEGAL_COLLECTION_TRAN_CODE = c.CODE AND c.LC_TRAN_CATEGORY_CODE = g.CODE AND g.PAY_DOWN_ORDER > 0 --AND t.transaction_date <= in_date ORDER BY g.pay_down_order, t.transaction_date; LOOP FETCH cost_cursor INTO cost_rec; EXIT WHEN cost_cursor%NOTFOUND OR v_amount = 0; IF cost_rec.outstanding_amt > v_amount THEN v_out := cost_rec.outstanding_amt - v_amount; v_applied := v_amount; v_amount := 0; ELSE v_out := 0; v_amount := v_amount - cost_rec.outstanding_amt; v_applied := cost_rec.outstanding_amt; END IF; UPDATE legal_collection_transaction SET outstanding_amt = v_out, update_date = SYSDATE, update_num = cost_rec.update_num + 1 WHERE legal_coll_tran_seq_num = cost_rec.legal_coll_tran_seq_num; legalcol_payment_ref_insert(in_payment_tran_seq_num, cost_rec.legal_coll_tran_seq_num, in_seq_num, v_applied, SYSDATE, NULL, in_update_user, SYSDATE); END LOOP; CLOSE cost_cursor; RETURN v_amount; END applyIt; ```
search_collections (procedure) ```sql PROCEDURE search_collections (recordset OUT ref_cursor, last_name_in IN VARCHAR2, first_name_in IN VARCHAR2, ssn_in IN VARCHAR2, policy_name_in IN VARCHAR2, show_closed_cases_in IN VARCHAR2, run_id_in IN VARCHAR2) IS BEGIN IF show_closed_cases_in = 'N' THEN IF length(policy_name_in) > 0 THEN OPEN recordset FOR SELECT DISTINCT /*+FIRST_ROWS */ hdr.reference_text REFERENCETEXT , hdr.debtor_name DEBTORNAME , DECODE(hdr.reference_type, 'POL', DECODE(hdr.policy_name, '', (SELECT /*+FIRST_ROWS */ psh.PS_NAME||DECODE(psh.PS_NAME, polh.POLICY_NAME, '', ' (DBA)') POLICY_NAME FROM policy_search psh ,POLICY polh WHERE psh.PS_POLICY_NUM = polh.POLICY_NUM AND psh.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') AND ps_policy_num = reference_text AND ROWNUM <= 1), hdr.policy_name), ' ') POLICYNAME , hdr.current_balance_amt CURRENTBALANCEAMOUNT , hdr.credit_rep_user CREDITREPUSER , hdr.received_date RECEIVEDDATE , hdr.legal_collection_code COLLECTIONCODE , (SELECT p.market_type_code FROM policy p, MARKET_TYPE_CODE m WHERE p.policy_num = DECODE(instr(translate(reference_text,'-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'), 0, reference_text, 0) AND p.market_type_code = m.code) MARKETTYPE , hdr.legal_collection_seq_num LEGALCOLLECTIONSEQNUM FROM ( -- Search by Policy Name SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,'' DEBTOR_NAME, ps.PS_NAME||DECODE(ps.PS_NAME, pol.POLICY_NAME, '', ' (DBA)') POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,policy_search ps ,POLICY pol WHERE COL.POLICY_NUM = ps.PS_POLICY_NUM AND ps.PS_POLICY_NUM = pol.POLICY_NUM AND COL.CURRENT_BALANCE_AMT > 0 AND ps.PS_NAME LIKE policy_name_in AND ps.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') UNION ALL -- Get ThirdParty Company debtor_name SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,C.COMPANY_NAME DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,thrdparty tp ,thrdparty_firm tf ,company c WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.THRDPARTY_FIRM_SEQ_NUM = tf.THRDPARTY_FIRM_SEQ_NUM AND tf.COMPANY_SEQ_NUM = C.COMPANY_SEQ_NUM AND COL.CURRENT_BALANCE_AMT > 0 AND UPPER(c.COMPANY_NAME) LIKE UPPER(policy_name_in) ) hdr ORDER BY 1,2,3,4,5,6,7; ELSE OPEN recordset FOR SELECT DISTINCT /*+FIRST_ROWS */ hdr.reference_text REFERENCETEXT , hdr.debtor_name DEBTORNAME , DECODE(hdr.reference_type, 'POL', DECODE(hdr.policy_name, '', (SELECT /*+FIRST_ROWS */ psh.PS_NAME||DECODE(psh.PS_NAME, polh.POLICY_NAME, '', ' (DBA)') POLICY_NAME FROM policy_search psh ,POLICY polh WHERE psh.PS_POLICY_NUM = polh.POLICY_NUM AND psh.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') AND ps_policy_num = reference_text AND ROWNUM <= 1), hdr.policy_name), ' ') POLICYNAME , hdr.current_balance_amt CURRENTBALANCEAMOUNT , hdr.credit_rep_user CREDITREPUSER , hdr.received_date RECEIVEDDATE , hdr.legal_collection_code COLLECTIONCODE , (SELECT p.market_type_code FROM policy p, MARKET_TYPE_CODE m WHERE p.policy_num = DECODE(instr(translate(reference_text,'-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'), 0, reference_text, 0) AND p.market_type_code = m.code) MARKETTYPE , hdr.legal_collection_seq_num LEGALCOLLECTIONSEQNUM FROM ( -- Search by Worker Name as Debtor SELECT debt.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,hist.PRIMARY_LAST_NAME || ', ' || hist.PRIMARY_FIRST_NAME || ' ' || hist.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection col, legal_collection_debtor debt, CLM_DBA.temp_data_person_address hist WHERE col.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.WORKER_ID = hist.PERSON_ID AND debt.worker_id is not null AND (hist.PRIMARY_LAST_NAME LIKE last_name_in AND hist.PRIMARY_FIRST_NAME LIKE first_name_in) AND col.CURRENT_BALANCE_AMT > 0 AND col.saif_claim_num = hist.clm_num AND hist.run_id = run_id_in UNION ALL -- Search by Worker Name on Claim (when debtors don't exist yet) SELECT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,hist.PRIMARY_LAST_NAME || ', ' || hist.PRIMARY_FIRST_NAME || ' ' || hist.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,COL.RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection COL, CLM_DBA.temp_data_person_address hist WHERE ((hist.PRIMARY_LAST_NAME like last_name_in AND hist.PRIMARY_FIRST_NAME like first_name_in) OR hist.PRIMARY_TAX_ID LIKE ssn_in) AND col.CURRENT_BALANCE_AMT > 0 AND hist.run_id = run_id_in UNION ALL -- Search by Worker SSN SELECT col.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,pers.PRIMARY_LAST_NAME || ', ' || pers.PRIMARY_FIRST_NAME || ' ' || pers.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection col, legal_collection_debtor debt, CLM_DBA.temp_data_person_address pers WHERE debt.WORKER_ID = pers.PERSON_ID AND col.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND col.CURRENT_BALANCE_AMT > 0 AND pers.PRIMARY_TAX_ID LIKE ssn_in AND pers.run_id = run_id_in UNION ALL -- Search by Affiliate Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,aff.AFFILIATE_LAST_NAME || ', ' || aff.AFFILIATE_FIRST_NAME || ' ' || aff.AFFILIATE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,affiliate aff WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.AFFILIATE_ID = aff.AFFILIATE_SSN AND COL.CURRENT_BALANCE_AMT > 0 AND ((UPPER(aff.AFFILIATE_LAST_NAME) LIKE last_name_in AND UPPER(aff.AFFILIATE_FIRST_NAME) LIKE first_name_in) OR debt.DEBTOR_SSN LIKE ssn_in) UNION ALL -- Search by Third Party Debtor Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,p.LAST_NAME||', '||p.FIRST_NAME||' '||p.MIDDLE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,thrdparty tp ,person p WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM AND COL.CURRENT_BALANCE_AMT > 0 AND p.LAST_NAME IS NOT NULL AND ((UPPER(p.LAST_NAME) LIKE last_name_in AND UPPER(p.FIRST_NAME) LIKE first_name_in) OR p.TAX_ID LIKE ssn_in) UNION ALL -- Search by Fraud Other Debtor Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,debt.LAST_NAME||', '||debt.FIRST_NAME||' '||debt.MIDDLE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND COL.CURRENT_BALANCE_AMT > 0 AND debt.LAST_NAME IS NOT NULL AND ((UPPER(debt.LAST_NAME) LIKE last_name_in AND UPPER(debt.FIRST_NAME) LIKE first_name_in) OR debt.DEBTOR_SSN LIKE ssn_in) ) hdr ORDER BY 1,2,3,4,5,6,7; END IF; ELSE IF length(policy_name_in) > 0 THEN OPEN recordset FOR SELECT DISTINCT /*+FIRST_ROWS */ hdr.reference_text REFERENCETEXT , hdr.debtor_name DEBTORNAME , DECODE(hdr.reference_type, 'POL', DECODE(hdr.policy_name, '', (SELECT /*+FIRST_ROWS */ psh.PS_NAME||DECODE(psh.PS_NAME, polh.POLICY_NAME, '', ' (DBA)') POLICY_NAME FROM policy_search psh ,POLICY polh WHERE psh.PS_POLICY_NUM = polh.POLICY_NUM AND psh.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') AND ps_policy_num = reference_text AND ROWNUM <= 1), hdr.policy_name), ' ') POLICYNAME , hdr.current_balance_amt CURRENTBALANCEAMOUNT , hdr.credit_rep_user CREDITREPUSER , hdr.received_date RECEIVEDDATE , hdr.legal_collection_code COLLECTIONCODE , (SELECT p.market_type_code FROM policy p, MARKET_TYPE_CODE m WHERE p.policy_num = DECODE(instr(translate(reference_text,'-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'), 0, reference_text, 0) AND p.market_type_code = m.code) MARKETTYPE , hdr.legal_collection_seq_num LEGALCOLLECTIONSEQNUM FROM ( -- Search by Policy Name SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,'' DEBTOR_NAME, ps.PS_NAME||DECODE(ps.PS_NAME, pol.POLICY_NAME, '', ' (DBA)') POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,policy_search ps ,POLICY pol WHERE COL.POLICY_NUM = ps.PS_POLICY_NUM AND ps.PS_POLICY_NUM = pol.POLICY_NUM AND ps.PS_NAME LIKE policy_name_in AND ps.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') UNION ALL -- Get ThirdParty Company debtor_name SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,C.COMPANY_NAME DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,thrdparty tp ,thrdparty_firm tf ,company c WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.THRDPARTY_FIRM_SEQ_NUM = tf.THRDPARTY_FIRM_SEQ_NUM AND tf.COMPANY_SEQ_NUM = C.COMPANY_SEQ_NUM AND UPPER(c.COMPANY_NAME) LIKE UPPER(policy_name_in) ) hdr ORDER BY 1,2,3,4,5,6,7; ELSE OPEN recordset FOR SELECT DISTINCT /*+FIRST_ROWS */ hdr.reference_text REFERENCETEXT , hdr.debtor_name DEBTORNAME , DECODE(hdr.reference_type, 'POL', DECODE(hdr.policy_name, '', (SELECT /*+FIRST_ROWS */ psh.PS_NAME||DECODE(psh.PS_NAME, polh.POLICY_NAME, '', ' (DBA)') POLICY_NAME FROM policy_search psh ,POLICY polh WHERE psh.PS_POLICY_NUM = polh.POLICY_NUM AND psh.POLICY_SEARCH_TYPE_CODE IN ('LEGAL', 'TRD') AND ps_policy_num = reference_text AND ROWNUM <= 1), hdr.policy_name), ' ') POLICYNAME , hdr.current_balance_amt CURRENTBALANCEAMOUNT , hdr.credit_rep_user CREDITREPUSER , hdr.received_date RECEIVEDDATE , hdr.legal_collection_code COLLECTIONCODE , (SELECT p.market_type_code FROM policy p, MARKET_TYPE_CODE m WHERE p.policy_num = DECODE(instr(translate(reference_text,'-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X'), 0, reference_text, 0) AND p.market_type_code = m.code) MARKETTYPE , hdr.legal_collection_seq_num LEGALCOLLECTIONSEQNUM FROM ( -- Search by Worker Name SELECT debt.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,hist.PRIMARY_LAST_NAME || ', ' || hist.PRIMARY_FIRST_NAME || ' ' || hist.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection col, legal_collection_debtor debt, CLM_DBA.temp_data_person_address hist WHERE col.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.WORKER_ID = hist.PERSON_ID AND debt.worker_id is not null AND (hist.PRIMARY_LAST_NAME LIKE last_name_in AND hist.PRIMARY_FIRST_NAME LIKE first_name_in) AND hist.run_id = run_id_in AND col.saif_claim_num = hist.clm_num UNION ALL -- Search by Worker Name on Claim (when debtors don't exist yet) SELECT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,hist.PRIMARY_LAST_NAME || ', ' || hist.PRIMARY_FIRST_NAME || ' ' || hist.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,COL.RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection COL, CLM_DBA.temp_data_person_address hist WHERE ((hist.PRIMARY_LAST_NAME like last_name_in AND hist.PRIMARY_FIRST_NAME like first_name_in) OR hist.PRIMARY_TAX_ID LIKE ssn_in) AND hist.run_id = run_id_in UNION ALL -- Search by Worker SSN SELECT col.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,pers.PRIMARY_LAST_NAME || ', ' || pers.PRIMARY_FIRST_NAME || ' ' || pers.PRIMARY_MIDDLE_NAME DEBTOR_NAME , '' POLICY_NAME, CURRENT_BALANCE_AMT ,CREDIT_REP_USER ,RECEIVED_DATE ,LEGAL_COLLECTION_CODE FROM legal_collection col, legal_collection_debtor debt, CLM_DBA.temp_data_person_address pers WHERE debt.WORKER_ID = pers.PERSON_ID AND col.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND pers.PRIMARY_TAX_ID LIKE ssn_in AND pers.run_id = run_id_in UNION ALL -- Search by Affiliate Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,aff.AFFILIATE_LAST_NAME || ', ' || aff.AFFILIATE_FIRST_NAME || ' ' || aff.AFFILIATE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,affiliate aff WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.AFFILIATE_ID = aff.AFFILIATE_SSN AND ((UPPER(aff.AFFILIATE_LAST_NAME) LIKE last_name_in AND UPPER(aff.AFFILIATE_FIRST_NAME) LIKE first_name_in) OR debt.DEBTOR_SSN LIKE ssn_in) UNION ALL -- Search by Third Party Debtor Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,p.LAST_NAME||', '||p.FIRST_NAME||' '||p.MIDDLE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt ,thrdparty tp ,person p WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM AND p.LAST_NAME IS NOT NULL AND ((UPPER(p.LAST_NAME) LIKE last_name_in AND UPPER(p.FIRST_NAME) LIKE first_name_in) OR p.TAX_ID LIKE ssn_in) UNION ALL -- Search by Fraud Other Debtor Name or SSN SELECT DISTINCT COL.LEGAL_COLLECTION_SEQ_NUM ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', TO_CHAR(COL.POLICY_NUM), 'FPOL', TO_CHAR(COL.POLICY_NUM), 'CPOL', TO_CHAR(COL.POLICY_NUM), 'WRKR', COL.SAIF_CLAIM_NUM, 'THRD', COL.SAIF_CLAIM_NUM, 'CTHRD', COL.SAIF_CLAIM_NUM, 'FOTH', COL.SAIF_CLAIM_NUM) Reference_Text ,DECODE(COL.LEGAL_COLLECTION_CODE, 'POL', 'POL', 'FPOL', 'POL', 'CPOL', 'POL', 'WRKR', 'CLM', 'PROV', 'TAXID', 'THRD', 'CLM', 'CTHRD', 'CLM', 'FOTH', 'CLM') Reference_Type ,debt.LAST_NAME||', '||debt.FIRST_NAME||' '||debt.MIDDLE_INITIAL DEBTOR_NAME, '' POLICY_NAME, COL.CURRENT_BALANCE_AMT, COL.CREDIT_REP_USER, COL.RECEIVED_DATE ,COL.LEGAL_COLLECTION_CODE FROM legal_collection COL ,legal_collection_debtor debt WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.LAST_NAME IS NOT NULL AND ((UPPER(debt.LAST_NAME) LIKE last_name_in AND UPPER(debt.FIRST_NAME) LIKE first_name_in) OR debt.DEBTOR_SSN LIKE ssn_in) ) hdr ORDER BY 1,2,3,4,5,6,7; END IF; END IF; END search_collections; ```
interest_calc_select (procedure) ```sql PROCEDURE interest_calc_select (recordset OUT ref_cursor, legal_collection_seq_num_in IN VARCHAR2, run_date_in IN DATE ) AS v_case_num VARCHAR2(15); v_interest_calc_months INTEGER; BEGIN IF legal_collection_seq_num_in = 'ALL' THEN v_case_num := '%'; ELSE v_case_num := legal_collection_seq_num_in; END IF; SELECT to_number(to_char(add_months(last_day(sysdate),-1),'dd')) INTO v_interest_calc_months FROM DUAL; --The following sql has inline calls to get_interest_start_date. If running SQL from toad and pointing --to WCISQA or WCISPROD, you will need to change the function to point to the same environment. OPEN recordset FOR SELECT REFERENCE_TEXT ReferenceText , COL.CURRENT_BALANCE_AMT OutstandingBalance , Amount_Charged_Interest AmountChargedInterest , Amount_Of_Interest InterestAmount , COL.LEGAL_COLLECTION_SEQ_NUM CaseId , INTEREST_RATE InterestRate , CALC_OPTION CalcOption , COL.UPDATE_NUM updateNumber FROM legal_collection COL, ( SELECT lc.LEGAL_COLLECTION_SEQ_NUM , DECODE(lc.LEGAL_COLLECTION_CODE, 'WRKR', MIN(lc.SAIF_CLAIM_NUM), 'POL', MIN(lc.POLICY_NUM), 'FPOL', MIN(lc.POLICY_NUM), 'CPOL', MIN(lc.POLICY_NUM), 'THRD', MIN(lc.SAIF_CLAIM_NUM), 'CTHRD', MIN(lc.SAIF_CLAIM_NUM), 'FOTH', MIN(lc.SAIF_CLAIM_NUM)) Reference_Text , MIN(NVL(lc.INTEREST_ANNUAL_RATE,0)) INTEREST_RATE , MIN(NVL(lc.INTEREST_CALC_OPTION_CODE,0)) CALC_OPTION , SUM(NVL(lct.OUTSTANDING_AMT,0)) Amount_Charged_Interest , NVL(ROUND(((SUM(lct.OUTSTANDING_AMT) * MIN(lc.INTEREST_ANNUAL_RATE)) / 12) * decode(trunc(max((run_date_in - get_interest_start_date(lc.LEGAL_COLLECTION_SEQ_NUM)) + 1) / v_interest_calc_months), 0, 0, 1) ,2),0) Amount_Of_Interest FROM legal_collection lc, legal_collection_transaction lct WHERE lc.LEGAL_COLLECTION_SEQ_NUM = lct.LEGAL_COLLECTION_SEQ_NUM AND lc.INTEREST_ANNUAL_RATE > 0 AND lct.OUTSTANDING_AMT > 0 AND lct.INTEREST_FLAG = 'Y' AND lc.INTEREST_CALC_OPTION_CODE = 'MTH' AND trunc(run_date_in - get_interest_start_date(lc.LEGAL_COLLECTION_SEQ_NUM) + 1) > 0 AND lc.LEGAL_COLLECTION_SEQ_NUM LIKE v_case_num GROUP BY lc.LEGAL_COLLECTION_SEQ_NUM, lc.LEGAL_COLLECTION_CODE UNION ALL SELECT lc.LEGAL_COLLECTION_SEQ_NUM , DECODE(lc.LEGAL_COLLECTION_CODE, 'WRKR', MIN(lc.SAIF_CLAIM_NUM), 'POL', MIN(lc.POLICY_NUM), 'FPOL', MIN(lc.POLICY_NUM), 'CPOL', MIN(lc.POLICY_NUM), 'THRD', MIN(lc.SAIF_CLAIM_NUM), 'CTHRD', MIN(lc.SAIF_CLAIM_NUM), 'FOTH', MIN(lc.SAIF_CLAIM_NUM)) Reference_Text , MIN(NVL(lc.INTEREST_ANNUAL_RATE,0)) INTEREST_RATE , MIN(NVL(lc.INTEREST_CALC_OPTION_CODE,0)) CALC_OPTION , SUM(NVL(lct.OUTSTANDING_AMT,0)) Amount_Charged_Interest , NVL(ROUND(((SUM(NVL(lct.OUTSTANDING_AMT,0)) * MIN(NVL(lc.INTEREST_ANNUAL_RATE,0))) * ROUND(ROUND(max(run_date_in - get_interest_start_date(lc.LEGAL_COLLECTION_SEQ_NUM)),0)/((3*365+366)/4),4)),2),0) Amount_Of_Interest FROM legal_collection lc, legal_collection_transaction lct WHERE lc.LEGAL_COLLECTION_SEQ_NUM = lct.LEGAL_COLLECTION_SEQ_NUM AND lc.INTEREST_ANNUAL_RATE > 0 AND lct.OUTSTANDING_AMT > 0 AND lct.INTEREST_FLAG = 'Y' AND lc.INTEREST_CALC_OPTION_CODE = 'DAY' AND lc.LEGAL_COLLECTION_SEQ_NUM LIKE v_case_num GROUP BY lc.LEGAL_COLLECTION_SEQ_NUM, lc.LEGAL_COLLECTION_CODE ) details WHERE COL.LEGAL_COLLECTION_SEQ_NUM = Details.LEGAL_COLLECTION_SEQ_NUM AND AMOUNT_OF_INTEREST > 0 ORDER BY calc_option, reference_text; END interest_calc_select; ```
getPrimaryDebtorName (function) ```sql FUNCTION getPrimaryDebtorName (legal_collection_seq_num_in IN NUMBER) RETURN VARCHAR2 AS v_debtor_name VARCHAR2(50); BEGIN SELECT DEBTOR_NAME INTO v_debtor_name FROM ( -- Get Affiliate debtor_name SELECT trim(aff.AFFILIATE_LAST_NAME) || ', ' || trim(aff.AFFILIATE_FIRST_NAME) || ' ' || trim(aff.AFFILIATE_INITIAL) DEBTOR_NAME FROM legal_collection COL ,legal_collection_debtor debt ,affiliate aff WHERE COL.LEGAL_COLLECTION_SEQ_NUM = debt.LEGAL_COLLECTION_SEQ_NUM AND debt.AFFILIATE_ID = aff.AFFILIATE_SSN AND COL.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in UNION ALL -- Get Corp policy debtor_name SELECT P.POLICY_NAME DEBTOR_NAME FROM legal_collection_debtor debt, legal_collection col, policy p WHERE DEBT.LEGAL_COLLECTION_SEQ_NUM = COL.LEGAL_COLLECTION_SEQ_NUM AND COL.POLICY_NUM = P.POLICY_NUM AND debt.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in UNION ALL -- Get ThirdParty Person debtor_name SELECT trim(p.LAST_NAME) || ', ' || trim(p.FIRST_NAME) || ' ' || trim(P.MIDDLE_INITIAL) DEBTOR_NAME FROM legal_collection_debtor debt ,thrdparty tp ,person p WHERE DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM AND debt.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in UNION ALL -- Get ThirdParty Company debtor_name SELECT trim(C.COMPANY_NAME) DEBTOR_NAME FROM legal_collection_debtor debt ,thrdparty tp ,thrdparty_firm tf ,company c WHERE DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.THRDPARTY_FIRM_SEQ_NUM = tf.THRDPARTY_FIRM_SEQ_NUM AND tf.COMPANY_SEQ_NUM = C.COMPANY_SEQ_NUM AND debt.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in UNION ALL -- Get Fraud Other debtor_name SELECT trim(debt.LAST_NAME) || DECODE(DEBT.Suffix, null, '', ' ' || DEBT.SUFFIX) || ', ' || trim(debt.FIRST_NAME) || ' ' || trim(debt.MIDDLE_INITIAL) DEBTOR_NAME FROM legal_collection_debtor debt, legal_collection col WHERE DEBT.LEGAL_COLLECTION_SEQ_NUM = COL.LEGAL_COLLECTION_SEQ_NUM AND COL.LEGAL_COLLECTION_CODE = 'FOTH' AND debt.LEGAL_COLLECTION_SEQ_NUM = legal_collection_seq_num_in ) WHERE ROWNUM <= 1; RETURN v_debtor_name; END getPrimaryDebtorName; ```
getDebtorName (function) ```sql FUNCTION getDebtorName (debtor_seq_num_in IN NUMBER) RETURN VARCHAR2 AS v_debtor_name VARCHAR2(50); BEGIN SELECT DEBTOR_NAME INTO v_debtor_name FROM ( -- Get Affiliate debtor_name SELECT trim(aff.AFFILIATE_LAST_NAME) || ', ' || trim(aff.AFFILIATE_FIRST_NAME) || ' ' || trim(aff.AFFILIATE_INITIAL) DEBTOR_NAME FROM legal_collection_debtor debt ,affiliate aff WHERE debt.AFFILIATE_ID = aff.AFFILIATE_SSN AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get Corp policy debtor_name SELECT P.POLICY_NAME DEBTOR_NAME FROM legal_collection_debtor debt, legal_collection col, policy p WHERE DEBT.LEGAL_COLLECTION_SEQ_NUM = COL.LEGAL_COLLECTION_SEQ_NUM AND COL.POLICY_NUM = P.POLICY_NUM AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get ThirdParty Person debtor_name SELECT trim(p.LAST_NAME) || ', ' || trim(p.FIRST_NAME) || ' ' || trim(P.MIDDLE_INITIAL) DEBTOR_NAME FROM legal_collection_debtor debt ,thrdparty tp ,person p WHERE DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get ThirdParty Company debtor_name SELECT trim(C.COMPANY_NAME) DEBTOR_NAME FROM legal_collection_debtor debt ,thrdparty tp ,thrdparty_firm tf ,company c WHERE DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.THRDPARTY_FIRM_SEQ_NUM = tf.THRDPARTY_FIRM_SEQ_NUM AND tf.COMPANY_SEQ_NUM = C.COMPANY_SEQ_NUM AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get Fraud Other debtor_name SELECT trim(debt.LAST_NAME) || DECODE(DEBT.Suffix, null, '', ' ' || DEBT.SUFFIX) || ', ' || trim(debt.FIRST_NAME) || ' ' || trim(debt.MIDDLE_INITIAL) DEBTOR_NAME FROM legal_collection_debtor debt, legal_collection col WHERE DEBT.LEGAL_COLLECTION_SEQ_NUM = COL.LEGAL_COLLECTION_SEQ_NUM AND COL.LEGAL_COLLECTION_CODE = 'FOTH' AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in ) WHERE ROWNUM <= 1; RETURN v_debtor_name; END getDebtorName; ```
getDebtorSSN (procedure) ```sql PROCEDURE getDebtorSSN (recordset OUT ref_cursor, debtor_seq_num_in IN VARCHAR2) AS BEGIN OPEN recordset FOR SELECT DEBTOR_SSN FROM ( -- Get policy debtor_ssn SELECT debt.DEBTOR_SSN FROM legal_collection_debtor debt ,affiliate aff WHERE debt.AFFILIATE_ID = aff.AFFILIATE_SSN AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get ThirdParty Person debtor_ssn SELECT P.TAX_ID DEBTOR_SSN FROM legal_collection_debtor debt ,thrdparty tp ,person p WHERE DEBT.THIRD_PARTY_SEQ_NUM = tp.THRDPARTY_SEQ_NUM AND TP.PERSON_SEQ_NUM = P.PERSON_SEQ_NUM AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in UNION ALL -- Get Fraud Other debtor_ssn SELECT DEBT.DEBTOR_SSN FROM legal_collection_debtor debt, legal_collection col WHERE DEBT.LEGAL_COLLECTION_SEQ_NUM = COL.LEGAL_COLLECTION_SEQ_NUM AND COL.LEGAL_COLLECTION_CODE = 'FOTH' AND debt.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in ) WHERE ROWNUM <= 1; END getDebtorSSN; /******************************************** ** GET POLICY PAYMENTS NOT ENTERED IN COLLECTIONS ** DATE: 16-SEP-13 12:29:01 *********************************************/ ```
getPolicyPayments (procedure) ```sql PROCEDURE getPolicyPayments (recordset OUT ref_cursor, selectDate IN Date) AS BEGIN OPEN recordset FOR select PT.POLICY_TRANS_SEQ_NUM Id, c.legal_collection_seq_num CaseId, pt.trans_amt TransactionAmount, pt.trans_date BatchDate from legal_collection c, policy_financial_trans pt where C.POLICY_NUM = PT.POLICY_NUM and C.CURRENT_BALANCE_AMT > 0 and pt.Trans_date >= selectDate and pt.trans_amt < 0 and not exists (select 1 from legal_collection_transaction t where transaction_date >= selectDate and legal_collection_seq_num = c.legal_collection_seq_num and T.transaction_amt = pt.trans_amt * -1); END getPolicyPayments; /******************************************** ** GET Third Party PAYMENTS For claims with a COLLECTION case ** DATE: 16-SEP-13 12:29:01 *********************************************/ ```
getThirdPartyPayments (procedure) ```sql PROCEDURE getThirdPartyPayments (recordset OUT ref_cursor, selectDate IN Date) AS BEGIN OPEN recordset FOR select distinct D.THRDPARTY_DISTRIBUTION_SEQ_NUM Id, C.LEGAL_COLLECTION_SEQ_NUM CaseId, D.CHECK_AMOUNT TransactionAmount, D.CHECK_DATE BatchDate from THRDPARTY_DISTRIBUTION D, thrdparty t, legal_collection_debtor c, legal_collection lc where c.THIRD_PARTY_SEQ_NUM = T.THRDPARTY_SEQ_NUM and T.THRDPARTY_CASE_SEQ_NUM = D.THRDPARTY_CASE_SEQ_NUM and LC.LEGAL_COLLECTION_SEQ_NUM = C.LEGAL_COLLECTION_SEQ_NUM and LC.CURRENT_BALANCE_AMT > 0 and D.CREATE_DATE > selectDate; End getThirdPartyPayments; /******************************************** ** GET list of Debtors without payments ** DATE: 16-SEP-13 12:29:01 *********************************************/ ```
getDebtorsWithoutPayments (procedure) ```sql PROCEDURE getDebtorsWithoutPayments (recordset OUT ref_cursor, selectDate IN Date) AS BEGIN OPEN recordset FOR select d.* from legal_collection_debtor d, legal_collection l where D.HOLD_STATEMENT_FLAG = 'N' and L.LEGAL_COLLECTION_SEQ_NUM = D.LEGAL_COLLECTION_SEQ_NUM and L.CURRENT_BALANCE_AMT > 0 and not exists (select * from legal_collection_transaction t where T.LEGAL_COLLECTION_SEQ_NUM = D.LEGAL_COLLECTION_SEQ_NUM and T.TRANSACTION_DATE > selectDate and t.legal_collection_tran_code = 'DPMT'); END getDebtorsWithoutPayments; /******************************************** ** GET list of Policy Debtors with expired locations ** DATE: 16-SEP-13 12:29:01 *********************************************/ ```
getDebtorsWithExpiredLocs (procedure) ```sql PROCEDURE getDebtorsWithExpiredLocs (recordset OUT ref_cursor, selectDate IN Date) AS BEGIN OPEN recordset FOR select distinct d.* from legal_collection_debtor d , legal_collection C where D.LOC_SEQ_NUM is not null and D.LEGAL_COLLECTION_SEQ_NUM = C.LEGAL_COLLECTION_SEQ_NUM and C.POLICY_NUM is not null and D.HOLD_STATEMENT_FLAG = 'N' and exists (select * from location l where L.LOC_SEQ_NUM = D.LOC_SEQ_NUM and L.LOC_EXP_DATE is not null and L.UPDATE_DATE > selectDate); End getDebtorsWithExpiredLocs; /******************************************** ** GET PAYMENTS Taking balance amount less than minimum amount ($100) ** DATE: 16-SEP-13 12:29:01 *********************************************/ ```
getBalanceBroughtToMinAmount (procedure) ```sql PROCEDURE getBalanceBroughtToMinAmount (recordset OUT ref_cursor, selectDate IN Date) AS BEGIN OPEN recordset FOR select LC.LEGAL_COLLECTION_SEQ_NUM Id, LC.LEGAL_COLLECTION_SEQ_NUM CaseId, LC.CURRENT_BALANCE_AMT TransactionAmount, T.TRANSACTION_DATE BatchDate from legal_collection lc, legal_collection_transaction t where LC.CURRENT_BALANCE_AMT < 100 and LC.LEGAL_COLLECTION_SEQ_NUM = T.LEGAL_COLLECTION_SEQ_NUM and T.LEGAL_COLLECTION_TRAN_CODE = 'DPMT' and T.TRANSACTION_DATE > selectDate and T.LEGAL_COLL_TRAN_SEQ_NUM = (select max(legal_coll_tran_seq_num) from legal_collection_transaction st where ST.LEGAL_COLLECTION_SEQ_NUM = T.LEGAL_COLLECTION_SEQ_NUM); END getBalanceBroughtToMinAmount; ```
legalcol_payment_ref_insert (procedure) ```sql PROCEDURE legalcol_payment_ref_insert ( payment_tran_seq_num_in IN legal_collection_payment_ref.payment_tran_seq_num%TYPE, legal_coll_tran_seq_num_in IN legal_collection_payment_ref.legal_coll_tran_seq_num%TYPE, legal_collection_seq_num_in IN legal_collection_payment_ref.legal_collection_seq_num%TYPE, applied_amt_in IN legal_collection_payment_ref.applied_amt%TYPE, applied_date_in IN legal_collection_payment_ref.applied_date%TYPE, gl_report_date_in IN legal_collection_payment_ref.gl_report_date%TYPE, create_user_in IN legal_collection_payment_ref.create_user%TYPE, create_date_in IN legal_collection_payment_ref.create_date%TYPE ) IS BEGIN INSERT INTO legal_collection_payment_ref ( payment_tran_seq_num, legal_coll_tran_seq_num, legal_collection_seq_num, applied_amt, applied_date, gl_report_date, create_user, create_date ) VALUES ( payment_tran_seq_num_in, legal_coll_tran_seq_num_in, legal_collection_seq_num_in, applied_amt_in, applied_date_in, gl_report_date_in, create_user_in, create_date_in ); END legalcol_payment_ref_insert; ```
get_last_statement_balance (function) ```sql FUNCTION get_last_statement_balance (last_statement_date_in IN legal_collection_debtor.last_statement_date%TYPE, debtor_seq_num_in IN legal_collection_debtor.legal_coll_debtor_seq_num%TYPE) RETURN NUMBER IS v_balance_amt_out NUMBER; BEGIN select sum.LAST_STATEMENT_BALANCE_AMT into v_balance_amt_out from legal_collection_debtor debtHdr, ( select LEGAL_COLLECTION_SEQ_NUM, LEGAL_COLL_DEBTOR_SEQ_NUM, LAST_STATEMENT_DATE, sum(transaction_amt) LAST_STATEMENT_BALANCE_AMT from ( select d.LEGAL_COLLECTION_SEQ_NUM, d.LEGAL_COLL_DEBTOR_SEQ_NUM, d.LAST_STATEMENT_DATE, DECODE(tc.LC_TRAN_CATEGORY_CODE, 'PMT', t.transaction_amt * -1, 'CADJ', t.transaction_amt * -1, t.transaction_amt) transaction_amt ,tc.LC_TRAN_CATEGORY_CODE from legal_collection_transaction t, legal_collection_debtor d, legal_collection_tran_code tc where t.LEGAL_COLLECTION_SEQ_NUM = d.LEGAL_COLLECTION_SEQ_NUM and t.LEGAL_COLLECTION_TRAN_CODE = tc.CODE and trunc(t.TRANSACTION_DATE) <= last_statement_date_in and d.LEGAL_COLL_DEBTOR_SEQ_NUM = debtor_seq_num_in ) dtl group by LEGAL_COLLECTION_SEQ_NUM, LEGAL_COLL_DEBTOR_SEQ_NUM, LAST_STATEMENT_DATE) sum where debthdr.LEGAL_COLL_DEBTOR_SEQ_NUM = sum.LEGAL_COLL_DEBTOR_SEQ_NUM; RETURN v_balance_amt_out; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END get_last_statement_balance; ```
get_interest_start_date (function) ```sql FUNCTION get_interest_start_date (legal_coll_seq_num_in IN legal_collection_note.legal_collection_seq_num%TYPE) RETURN DATE IS v_interest_start_date DATE := NULL; BEGIN SELECT TRUNC(MAX(TRANSACTION_DATE)) INTO v_interest_start_date FROM legal_collection_transaction WHERE LEGAL_COLLECTION_SEQ_NUM = legal_coll_seq_num_in AND REFERENCE_TEXT = 'LCB00003'; IF v_interest_start_date IS NULL THEN SELECT TRUNC(MIN(TRANSACTION_DATE)) INTO v_interest_start_date FROM legal_collection_transaction WHERE LEGAL_COLLECTION_SEQ_NUM = legal_coll_seq_num_in; END IF; ```
## 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.PK80010_LEGAL_COLLECTIONS` - Legends CLI query executed 2026-09-24: `legends oracle query --name PK80010_LEGAL_COLLECTIONS --output analysis/oracle-source/pk80010-legal-collections.sql` - Calling code: `Data/SAIF.Data.NHibernate/Mappings/Collections/Collections.hbm.xml` - Calling code: `Data/SAIF.Data.NHibernate/Repositories/CollectionsRepository.cs`