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_COLLECTIONSis 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-membercodeItems; 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 | 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 | 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 | Payments and credit adjustments are shown negative | Tran code PMT or CADJ | Negate the amount; RPMT is shown as Payments |
| PROC-RULE-004 | 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 | The subtotal excludes payments | Display row 9 | Sum all categories except PMT/RPMT (CADJ negated); Current Balance is row 11 |
| PROC-RULE-006 | Judgment description source | Judgment code NA | Use code_descr; otherwise JUDGMENT_DESCR |
| PROC-RULE-007 | Foreign address flag | LOC_ADDR3 populated* | ForeignAddressFlag derived from LOC_ADDR3 on LOCATION_VIEW |
| PROC-RULE-008 | Debtor location precedence | Debtor has loc_seq_num | Use the debtor location, else the policy's active 'POL' location (rownum = 1) |
| PROC-RULE-009 | 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 | Closed cases are hidden unless requested | show_closed_cases_in = 'N' | Add CURRENT_BALANCE_AMT > 0 to every search branch |
| PROC-RULE-011 | 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 | 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 | Debtor SSN source (PII) | Debtor type | Affiliate match, third-party person TAX_ID, or FOTH debtor SSN |
| PROC-RULE-014 | 'ALL' selects every case | legal_collection_seq_num_in = 'ALL' | Convert to '%' LIKE pattern |
| PROC-RULE-015 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | Every application is recorded | Amount applied to a cost | INSERT LEGAL_COLLECTION_PAYMENT_REF |
| PROC-RULE-028 | 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.
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.
Source Code¶
get_cases_by_reference (procedure)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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¶
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