---
title: "PK82033_THIRDPARTY stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-28"
source_schema: "WCIS_DBA"
source_name: "PK82033_THIRDPARTY"
source_type: "PACKAGE"
oracle_status: "found"
library: SAIF.Business.Claim.ThirdParty
reference_type: package
---

# PK82033_THIRDPARTY

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Claim.ThirdParty` |
| **Calling classes** | `Multiple recovery entities` |

## Purpose

PK82033_THIRDPARTY centralizes Oracle-side logic consumed by `SAIF.Business.Claim.ThirdParty` for retrieval, persistence, sequence generation, or derived calculations.

## Procedures / Methods

### thrdparty_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `attorneyFirmSeqNum` | IN | thrdparty.attorney_firm_seq_num%TYPE | — | Parameter from package specification. |
| `carrierClaimNum` | IN | thrdparty.carrier_claim_num%TYPE | — | Parameter from package specification. |
| `carrierFirmSeqNum` | IN | thrdparty.carrier_firm_seq_num%TYPE | — | Parameter from package specification. |
| `carrierPolicyNum` | IN | thrdparty.carrier_policy_num%TYPE | — | Parameter from package specification. |
| `createUser` | IN | thrdparty.create_user%TYPE | — | Parameter from package specification. |
| `districtAttorneyNum` | IN | thrdparty.district_attorney_num%TYPE | — | Parameter from package specification. |
| `identifierText` | IN | thrdparty.identifier_text%TYPE | — | Parameter from package specification. |
| `personSeqNum` | IN | thrdparty.person_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartyCaseSeqNum` | IN | thrdparty.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartyFirmSeqNum` | IN | thrdparty.thrdparty_firm_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartySeqNum` | IN | thrdparty.thrdparty_seq_num%TYPE | — | Parameter from package specification. |
| `noteText` | IN | thrdparty.note_text%TYPE | — | Parameter from package specification. |
| `BirthDate` | IN | person.BIRTH_DATE%TYPE | — | Parameter from package specification. |
| `DriversLicenseNum` | IN | person.DRIVERS_LICENSE_NUM%TYPE | — | Parameter from package specification. |
| `DriversLicenseState` | IN | person.DRIVERS_LICENSE_STATE%TYPE | — | Parameter from package specification. |
| `EthnicGroupCode` | IN | person.ETHNIC_GROUP_CODE%TYPE | — | Parameter from package specification. |
| `FirstName` | IN | person.FIRST_NAME%TYPE | — | Parameter from package specification. |
| `GenderCode` | IN | person.GENDER_CODE%TYPE | — | Parameter from package specification. |
| `LastName` | IN | person.LAST_NAME%TYPE | — | Parameter from package specification. |
| `MaidenName` | IN | person.MAIDEN_NAME%TYPE | — | Parameter from package specification. |

### thrdparty_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `attorneyFirmSeqNum` | IN | thrdparty.attorney_firm_seq_num%TYPE | — | Parameter from package specification. |
| `carrierClaimNum` | IN | thrdparty.carrier_claim_num%TYPE | — | Parameter from package specification. |
| `carrierFirmSeqNum` | IN | thrdparty.carrier_firm_seq_num%TYPE | — | Parameter from package specification. |
| `carrierPolicyNum` | IN | thrdparty.carrier_policy_num%TYPE | — | Parameter from package specification. |
| `districtAttorneyNum` | IN | thrdparty.district_attorney_num%TYPE | — | Parameter from package specification. |
| `identifierText` | IN | thrdparty.identifier_text%TYPE | — | Parameter from package specification. |
| `personSeqNum` | IN | thrdparty.person_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartyCaseSeqNum` | IN | thrdparty.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartyFirmSeqNum` | IN | thrdparty.thrdparty_firm_seq_num%TYPE | — | Parameter from package specification. |
| `thrdpartySeqNum` | IN | thrdparty.thrdparty_seq_num%TYPE | — | Parameter from package specification. |
| `noteText` | IN | thrdparty.note_text%TYPE | — | Parameter from package specification. |
| `updateUser` | IN | thrdparty.update_user%TYPE | — | Parameter from package specification. |
| `updateNum` | IN | thrdparty.update_num%TYPE | — | Parameter from package specification. |
| `BirthDate` | IN | person.BIRTH_DATE%TYPE | — | Parameter from package specification. |
| `DriversLicenseNum` | IN | person.DRIVERS_LICENSE_NUM%TYPE | — | Parameter from package specification. |
| `DriversLicenseState` | IN | person.DRIVERS_LICENSE_STATE%TYPE | — | Parameter from package specification. |
| `EthnicGroupCode` | IN | person.ETHNIC_GROUP_CODE%TYPE | — | Parameter from package specification. |
| `FirstName` | IN | person.FIRST_NAME%TYPE | — | Parameter from package specification. |
| `GenderCode` | IN | person.GENDER_CODE%TYPE | — | Parameter from package specification. |
| `LastName` | IN | person.LAST_NAME%TYPE | — | Parameter from package specification. |

### thrdparty_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `thrdpartyseqnum` | IN | thrdparty.thrdparty_seq_num%TYPE | — | Parameter from package specification. |

### thrdparty_task_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_task_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | thrdparty_task.thrdparty_task_seq_num%TYPE | — | Parameter from package specification. |
| `caseseqnum` | IN | thrdparty_task.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `taskduedate` | IN | thrdparty_task.task_due_date%TYPE | — | Parameter from package specification. |
| `taskusername` | IN | thrdparty_task.task_user_name%TYPE | — | Parameter from package specification. |
| `taskcode` | IN | thrdparty_task.thrdparty_task_code%TYPE | — | Parameter from package specification. |
| `tasktext` | IN | thrdparty_task.task_text%TYPE | — | Parameter from package specification. |
| `createuser` | IN | thrdparty_task.create_user%TYPE | — | Parameter from package specification. |

### thrdparty_task_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_task_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | thrdparty_task.thrdparty_task_seq_num%TYPE | — | Parameter from package specification. |
| `caseseqnum` | IN | thrdparty_task.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `taskcode` | IN | thrdparty_task.thrdparty_task_code%TYPE | — | Parameter from package specification. |
| `taskduedate` | IN | thrdparty_task.task_due_date%TYPE | — | Parameter from package specification. |
| `tasktext` | IN | thrdparty_task.task_text%TYPE | — | Parameter from package specification. |
| `taskusername` | IN | thrdparty_task.task_user_name%TYPE | — | Parameter from package specification. |
| `updateuser` | IN | thrdparty_task.update_user%TYPE | — | Parameter from package specification. |

### thrdparty_task_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_task_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `taskseqnum` | IN | thrdparty_task.thrdparty_task_seq_num%TYPE | — | Parameter from package specification. |

### thrdparty_case_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_case_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `claimid` | IN | thrdparty_case.claim_id%TYPE | — | Parameter from package specification. |
| `createuser` | IN | thrdparty_case.create_user%TYPE | — | Parameter from package specification. |
| `lastKnownClaimStatus` | IN | thrdparty_case.tpr0003_last_known_claim_status%TYPE | — | Parameter from package specification. |
| `adjustercode` | IN | thrdparty_case.thrdparty_adjuster_code %TYPE | — | Parameter from package specification. |
| `statuscode` | IN | thrdparty_case.thrdparty_status_code%TYPE | — | Parameter from package specification. |
| `closurecode` | IN | thrdparty_case.thrdparty_closure_code%TYPE | — | Parameter from package specification. |
| `typecode` | IN | thrdparty_case.thrdparty_type_code%TYPE | — | Parameter from package specification. |
| `updateseqnum` | IN | thrdparty_case.update_seq_num%TYPE | — | Parameter from package specification. |
| `caseseqnum` | IN | thrdparty_case.thrdparty_case_seq_num%TYPE, 	causeofactioncode in thrdparty_case.cause_of_action_code%TYPE, 	sicCode in thrdparty_case.SUBSEQ_INTERVENING_CAUSE_CODE%type | — | Parameter from package specification. |
| `cocounselfirmseqnum` | IN | thrdparty_case.cocounsel_firm_seq_num%TYPE | — | Parameter from package specification. |
| `plaintifffirmseqnum` | IN | thrdparty_case.plaintiff_firm_seq_num%TYPE | — | Parameter from package specification. |
| `statutedate` | IN | thrdparty_case.statute_date%TYPE | — | Parameter from package specification. |

### thrdparty_case_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_case_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `causeofactioncode` | IN | thrdparty_case.cause_of_action_code%TYPE | — | Parameter from package specification. |
| `cocounselfirmseqnum` | IN | thrdparty_case.cocounsel_firm_seq_num%TYPE | — | Parameter from package specification. |
| `lastKnownClaimStatus` | IN | thrdparty_case.tpr0003_last_known_claim_status%TYPE | — | Parameter from package specification. |
| `plaintifffirmseqnum` | IN | thrdparty_case.plaintiff_firm_seq_num%TYPE | — | Parameter from package specification. |
| `statutedate` | IN | thrdparty_case.statute_date%TYPE | — | Parameter from package specification. |
| `subseqinterveningcausedate` | IN | thrdparty_case.subseq_intervening_cause_date%TYPE | — | Parameter from package specification. |
| `subseqinterveningcausecode` | IN | thrdparty_case.subseq_intervening_cause_code%TYPE | — | Parameter from package specification. |
| `suitfileddate` | IN | thrdparty_case.suit_filed_date%TYPE | — | Parameter from package specification. |
| `adjustercode` | IN | thrdparty_case.thrdparty_adjuster_code%TYPE | — | Parameter from package specification. |
| `closurecode` | IN | thrdparty_case.thrdparty_closure_code%TYPE | — | Parameter from package specification. |
| `statuscode` | IN | thrdparty_case.thrdparty_status_code%TYPE | — | Parameter from package specification. |
| `typecode` | IN | thrdparty_case.thrdparty_type_code%TYPE | — | Parameter from package specification. |
| `updateuser` | IN | thrdparty_case.update_user%TYPE | — | Parameter from package specification. |
| `accidentlocationtext` | IN | thrdparty_case.accident_location_text%TYPE | — | Parameter from package specification. |
| `NDReimbursementAmt` | IN | thrdparty_case.nd_reimbursement_amount%TYPE | — | Parameter from package specification. |
| `caseseqnum` | IN | thrdparty_case.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `updateseqnum` | IN | thrdparty_case.update_seq_num%TYPE | — | Parameter from package specification. |
| `claimid` | IN | thrdparty_case.claim_id%TYPE | — | Parameter from package specification. |

### thrdparty_case_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_case_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `caseseqnum` | IN | thrdparty_case.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `claimid` | IN | thrdparty_case.claim_id%TYPE | — | Parameter from package specification. |
| `updateuser` | IN | thrdparty_case.update_user%TYPE | — | Parameter from package specification. |

### thrdparty_note_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_note_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `NoteSeqNum` | IN | thrdparty_note.thrdparty_note_seq_num%TYPE | — | Parameter from package specification. |
| `CaseSeqNum` | IN | thrdparty_note.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `NoteText` | IN | thrdparty_note.note_text%TYPE | — | Parameter from package specification. |
| `NoteCode` | IN | thrdparty_note.thrdparty_note_code%TYPE | — | Parameter from package specification. |
| `createuser` | IN | thrdparty_note.create_user%TYPE | — | Parameter from package specification. |
| `createDate` | IN | thrdparty_note.create_date%TYPE | — | Parameter from package specification. |

### thrdparty_note_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_note_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `NoteSeqNum` | IN | thrdparty_note.thrdparty_note_seq_num%TYPE | — | Parameter from package specification. |
| `NoteText` | IN | thrdparty_note.note_text%TYPE | — | Parameter from package specification. |
| `NoteCode` | IN | thrdparty_note.thrdparty_note_code%TYPE | — | Parameter from package specification. |
| `UserId` | IN | thrdparty_note.update_user%TYPE | — | Parameter from package specification. |

### thrdparty_note_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_note_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `NoteSeqNum` | IN | thrdparty_note.thrdparty_note_seq_num%TYPE | — | Parameter from package specification. |

### thrdparty_expense_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_expense_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `approvalDate` | IN | THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE | — | Parameter from package specification. |
| `approverUser` | IN | THRDPARTY_EXPENSE.APPROVER_USER%TYPE | — | Parameter from package specification. |
| `budgetCode` | IN | THRDPARTY_EXPENSE.BUDGET_CODE%TYPE | — | Parameter from package specification. |
| `createUser` | IN | THRDPARTY_EXPENSE.UPDATE_USER%TYPE | — | Parameter from package specification. |
| `expenseAmount` | IN | THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE | — | Parameter from package specification. |
| `expenseDate` | IN | THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE | — | Parameter from package specification. |
| `expenseText` | IN | THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE | — | Parameter from package specification. |
| `recoveryAmount` | IN | THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE | — | Parameter from package specification. |
| `thrdpartyCaseSeqNum` | IN | THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `thrdpartyExpenseSeqNum` | IN | THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE | — | Parameter from package specification. |

### thrdparty_expense_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_expense_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `approvaldate` | IN | THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE | — | Parameter from package specification. |
| `approverUser` | IN | THRDPARTY_EXPENSE.APPROVER_USER%TYPE | — | Parameter from package specification. |
| `budgetCode` | IN | THRDPARTY_EXPENSE.BUDGET_CODE%TYPE | — | Parameter from package specification. |
| `recoveryAmount` | IN | THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE | — | Parameter from package specification. |
| `expenseAmount` | IN | THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE | — | Parameter from package specification. |
| `expenseDate` | IN | THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE | — | Parameter from package specification. |
| `expenseText` | IN | THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE, 	thrdpartyCaseSeqNum in thrdparty_expense.THRDPARTY_CASE_SEQ_NUM%type | — | Parameter from package specification. |
| `thrdpartyExpenseSeqNum` | IN | THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `updateSeqNum` | IN | THRDPARTY_EXPENSE.UPDATE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `updateUser` | IN | THRDPARTY_EXPENSE.UPDATE_USER%TYPE | — | Parameter from package specification. |

### thrdparty_expense_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_expense_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `thrdpartyExpenseSeqNum` | IN | THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE, 	thrdpartyCaseSeqNum in thrdparty_case.THRDPARTY_CASE_SEQ_NUM%type, 	updateUser in thrdparty_case.UPDATE_USER%type,    thrdpartyExpenseUpdateNum in thrdparty_expense.UPDATE_SEQ_NUM%type | — | Parameter from package specification. |

### companion_claim_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** companion_claim_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ClaimID` | IN | THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE | — | Parameter from package specification. |
| `CreateUser` | IN | THRDPARTY_COMPANION_CLAIM.CREATE_USER%TYPE | — | Parameter from package specification. |
| `CaseSeqNum` | IN | THRDPARTY_COMPANION_CLAIM.THRDPARTY_CASE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `CompanionSeqNum` | IN | THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE | — | Parameter from package specification. |

### companion_claim_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** companion_claim_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ClaimID` | IN | THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE | — | Parameter from package specification. |
| `CompanionSeqNum` | IN | THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `UpdateSeqNum` | IN | THRDPARTY_COMPANION_CLAIM.UPDATE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `UpdateUser` | IN | THRDPARTY_COMPANION_CLAIM.UPDATE_USER%TYPE | — | Parameter from package specification. |

### companion_claim_delete

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** companion_claim_delete is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `CompanionSeqNum` | IN | THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE | — | Parameter from package specification. |

### thrdparty_settlement_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_settlement_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `ThrdpartyCaseSeqNum` | IN | thrdparty_settlement.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `ThrdpartySettlementSeqNum` | IN | thrdparty_settlement.thrdparty_settlement_seq_num%TYPE | — | Parameter from package specification. |
| `ThrdpartySettlementCode` | IN | thrdparty_settlement.thrdparty_settlement_code%TYPE | — | Parameter from package specification. |
| `SettlementAmount` | IN | thrdparty_settlement.settlement_amount%TYPE | — | Parameter from package specification. |
| `SettlementDate` | IN | thrdparty_settlement.settlement_date%TYPE | — | Parameter from package specification. |
| `CreateUser` | IN | thrdparty_settlement.create_user%TYPE | — | Parameter from package specification. |

### thrdparty_settlement_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_settlement_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `SettlementSeqNum` | IN | thrdparty_settlement.thrdparty_settlement_seq_num%TYPE | — | Parameter from package specification. |
| `SettlementCode` | IN | thrdparty_settlement.thrdparty_settlement_code%TYPE | — | Parameter from package specification. |
| `SettlementAmount` | IN | thrdparty_settlement.settlement_amount%TYPE | — | Parameter from package specification. |
| `SettlementDate` | IN | thrdparty_settlement.settlement_date%TYPE | — | Parameter from package specification. |
| `UpdateUser` | IN | thrdparty_settlement.update_user%TYPE | — | Parameter from package specification. |
| `UpdateSeqNum` | IN OUT | thrdparty_settlement.update_seq_num%TYPE | — | Parameter from package specification. |

### prediction_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** prediction_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `PredictionSeqNum` | IN | THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `CaseSeqNum` | IN | THRDPARTY_PREDICTION.THRDPARTY_CASE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionStatusCode` | IN | THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE | — | Parameter from package specification. |
| `LienSeqNum` | IN | THRDPARTY_PREDICTION.THRDPARTY_LIEN_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionDate` | IN | THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE | — | Parameter from package specification. |
| `PredictionAmount` | IN | THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE | — | Parameter from package specification. |
| `PredictionPercentFactor` | IN | THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE | — | Parameter from package specification. |
| `PredictionDescription` | IN | THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE | — | Parameter from package specification. |
| `CreateUser` | IN | THRDPARTY_PREDICTION.CREATE_USER%TYPE | — | Parameter from package specification. |
| `PredictionDueDate` | IN | THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE | — | Parameter from package specification. |
| `FinalPredictionFlag` | IN | THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE | — | Parameter from package specification. |

### prediction_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** prediction_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `PredictionSeqNum` | IN | THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionStatusCode` | IN | THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE | — | Parameter from package specification. |
| `PredictionDate` | IN | THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE | — | Parameter from package specification. |
| `PredictionAmount` | IN | THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE | — | Parameter from package specification. |
| `PredictionPercentFactor` | IN | THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE | — | Parameter from package specification. |
| `PredictionDescription` | IN | THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE | — | Parameter from package specification. |
| `UpdateUser` | IN | THRDPARTY_PREDICTION.UPDATE_USER%TYPE | — | Parameter from package specification. |
| `UpdateSeqNum` | IN | THRDPARTY_PREDICTION.UPDATE_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionDueDate` | IN | THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE | — | Parameter from package specification. |
| `FinalPredictionFlag` | IN | THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE | — | Parameter from package specification. |

### prediction_detail_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** prediction_detail_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `PredictionDtlSeqNum` | IN | THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionSeqNum` | IN | THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICTION_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `ClaimCostCategoryCode` | IN | THRDPARTY_PREDICTION_DETAIL.CLAIM_COST_CATEGORY_CODE%TYPE | — | Parameter from package specification. |
| `PredictionAmount` | IN | THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE | — | Parameter from package specification. |
| `CreateUser` | IN | THRDPARTY_PREDICTION_DETAIL.CREATE_USER%TYPE | — | Parameter from package specification. |

### prediction_detail_update

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** prediction_detail_update is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `PredictionDtlSeqNum` | IN | THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE | — | Parameter from package specification. |
| `PredictionAmount` | IN | THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE | — | Parameter from package specification. |
| `UpdateUser` | IN | THRDPARTY_PREDICTION_DETAIL.UPDATE_USER%TYPE | — | Parameter from package specification. |
| `UpdateSeqNum` | IN | THRDPARTY_PREDICTION_DETAIL.UPDATE_SEQ_NUM%TYPE | — | Parameter from package specification. |

### thrdparty_distribution_insert

| Field | Value |
| --- | --- |
| **Returns** | Scalar value |
| **Called by** | `Multiple recovery entities` |

**Description:** thrdparty_distribution_insert is one of the Oracle entry points referenced by this library.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `AraAmount` | IN | thrdparty_distribution.Ara_Amount%TYPE | — | Parameter from package specification. |
| `CheckAmount` | IN | thrdparty_distribution.Check_Amount%TYPE | — | Parameter from package specification. |
| `CheckDate` | IN | thrdparty_distribution.Check_Date%TYPE | — | Parameter from package specification. |
| `CheckNum` | IN | thrdparty_distribution.Check_Num%TYPE | — | Parameter from package specification. |
| `ClaimCostAmount` | IN | thrdparty_distribution.Claim_Cost_Amount%TYPE | — | Parameter from package specification. |
| `CreateDate` | IN | thrdparty_distribution.create_date%TYPE | — | Parameter from package specification. |
| `CreateUser` | IN | thrdparty_distribution.Create_User%TYPE | — | Parameter from package specification. |
| `EmployerCreditAmount` | IN | thrdparty_distribution.Employer_Credit_Amount%TYPE | — | Parameter from package specification. |
| `OtherAmount1` | IN | thrdparty_distribution.Other_Amount1%TYPE | — | Parameter from package specification. |
| `OtherAmount2` | IN | thrdparty_distribution.Other_Amount2%TYPE | — | Parameter from package specification. |
| `OtherDescr1` | IN | thrdparty_distribution.Other_Descr1%TYPE | — | Parameter from package specification. |
| `OtherDescr2` | IN | thrdparty_distribution.Other_Descr2%TYPE | — | Parameter from package specification. |
| `PayeeName` | IN | thrdparty_distribution.Payee_Name%TYPE | — | Parameter from package specification. |
| `RefundAmount` | IN | thrdparty_distribution.Refund_Amount%TYPE | — | Parameter from package specification. |
| `CaseSeqNum` | IN | thrdparty_distribution.thrdparty_case_seq_num%TYPE | — | Parameter from package specification. |
| `DistributionCode` | IN | thrdparty_distribution.thrdparty_Distribution_Code%TYPE | — | Parameter from package specification. |
| `DistributionSeqNum` | IN | thrdparty_distribution.thrdparty_Distribution_Seq_Num%TYPE | — | Parameter from package specification. |
| `SettlementSeqNum` | IN | thrdparty_distribution.thrdparty_Settlement_Seq_Num%TYPE | — | Parameter from package specification. |
| `correctedNum` | IN | thrdparty_distribution.corrected_distribution_seq_num%TYPE | — | Parameter from package specification. |
| `PolicyNum1` | IN | thrdparty_distribution_refund.Policy_Num%TYPE | — | Parameter from package specification. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-001 | Package-backed logic is required by this library. | The library calls this Oracle package. | Return values or mutate data for the calling BOF object. |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| thrdparty_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_task_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_task_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_task_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_case_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_case_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_case_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_note_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_note_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_note_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_expense_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_expense_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_expense_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| companion_claim_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| companion_claim_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| companion_claim_delete | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_settlement_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_settlement_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| prediction_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| prediction_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| prediction_detail_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| prediction_detail_update | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |
| thrdparty_distribution_insert | Oracle routine referenced by `SAIF.Business.Claim.ThirdParty`. | Called from package-backed BOF logic. |

## Source Code

<details>
<summary>PL/SQL package specification and body</summary>

```sql
PACKAGE          pk82033_thirdparty AS

TYPE ref_cursor IS REF CURSOR;

/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION thrdparty_insert
(
	attorneyFirmSeqNum  IN thrdparty.attorney_firm_seq_num%TYPE,
	carrierClaimNum     IN thrdparty.carrier_claim_num%TYPE,
	carrierFirmSeqNum   IN thrdparty.carrier_firm_seq_num%TYPE,
	carrierPolicyNum    IN thrdparty.carrier_policy_num%TYPE,
	createUser          IN thrdparty.create_user%TYPE,
	districtAttorneyNum IN thrdparty.district_attorney_num%TYPE,
	identifierText      IN thrdparty.identifier_text%TYPE,
	personSeqNum        IN thrdparty.person_seq_num%TYPE,
	thrdpartyCaseSeqNum IN thrdparty.thrdparty_case_seq_num%TYPE,
	thrdpartyFirmSeqNum IN thrdparty.thrdparty_firm_seq_num%TYPE,
	thrdpartySeqNum     IN thrdparty.thrdparty_seq_num%TYPE,
    noteText            IN thrdparty.note_text%TYPE,
	BirthDate			IN	  person.BIRTH_DATE%TYPE,
	DriversLicenseNum	IN	  person.DRIVERS_LICENSE_NUM%TYPE,
	DriversLicenseState	IN	  person.DRIVERS_LICENSE_STATE%TYPE,
	EthnicGroupCode		IN	  person.ETHNIC_GROUP_CODE%TYPE,
	FirstName			IN	  person.FIRST_NAME%TYPE,
	GenderCode			IN	  person.GENDER_CODE%TYPE,
    LastName			IN	  person.LAST_NAME%TYPE,
	MaidenName			IN	  person.MAIDEN_NAME%TYPE,
	MarriedName			IN	  person.MARRIED_NAME%TYPE,
    MiddleInitial		IN	  person.MIDDLE_INITIAL%TYPE,
    NickName			IN	  person.NICK_NAME%TYPE,
	TaxId				IN	  person.TAX_ID%TYPE,
	AddressCode         in    address.ADDRESS_CODE%TYPE,
	AddressLine1        IN	  address.ADDRESS_LINE1%TYPE,
	AddressLine2        IN	  address.ADDRESS_LINE2%TYPE,
	AddressLine3        IN	  address.ADDRESS_LINE3%TYPE,
	AddressLine4        IN	  address.ADDRESS_LINE4%TYPE,
	CityName		    IN    address.CITY_NAME%TYPE,
	CountryCode		    IN    address.COUNTRY_CODE%TYPE,
	ForeignAddressFlag	IN    address.FOREIGN_ADDRESS_FLAG%TYPE,
    ForeignPostalCode	IN    address.FOREIGN_POSTAL_CODE%TYPE,
	StateIn             IN    address.STATE%TYPE,
	zipCode             IN    address.ZIP_CODE%TYPE,
    zipCodeExt          IN    address.ZIP_CODE_EXT%TYPE,
	phoneNum            IN phone.phone_num%TYPE,
    faxNum              IN phone.phone_num%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_update
(
	attorneyFirmSeqNum  IN thrdparty.attorney_firm_seq_num%TYPE,
	carrierClaimNum     IN thrdparty.carrier_claim_num%TYPE,
	carrierFirmSeqNum   IN thrdparty.carrier_firm_seq_num%TYPE,
	carrierPolicyNum    IN thrdparty.carrier_policy_num%TYPE,
	districtAttorneyNum IN thrdparty.district_attorney_num%TYPE,
	identifierText      IN thrdparty.identifier_text%TYPE,
	personSeqNum        IN thrdparty.person_seq_num%TYPE,
	thrdpartyCaseSeqNum IN thrdparty.thrdparty_case_seq_num%TYPE,
	thrdpartyFirmSeqNum IN thrdparty.thrdparty_firm_seq_num%TYPE,
	thrdpartySeqNum     IN thrdparty.thrdparty_seq_num%TYPE,
    noteText            IN thrdparty.note_text%TYPE,
	updateUser          IN thrdparty.update_user%TYPE,
	updateNum           IN thrdparty.update_num%TYPE,
	BirthDate			IN	  person.BIRTH_DATE%TYPE,
	DriversLicenseNum	IN	  person.DRIVERS_LICENSE_NUM%TYPE,
	DriversLicenseState	IN	  person.DRIVERS_LICENSE_STATE%TYPE,
	EthnicGroupCode		IN	  person.ETHNIC_GROUP_CODE%TYPE,
	FirstName			IN	  person.FIRST_NAME%TYPE,
	GenderCode			IN	  person.GENDER_CODE%TYPE,
    LastName			IN	  person.LAST_NAME%TYPE,
	MaidenName			IN	  person.MAIDEN_NAME%TYPE,
	MarriedName			IN	  person.MARRIED_NAME%TYPE,
    MiddleInitial		IN	  person.MIDDLE_INITIAL%TYPE,
    NickName			IN	  person.NICK_NAME%TYPE,
	TaxId				IN	  person.TAX_ID%TYPE,
	AddressCode         in    address.ADDRESS_CODE%TYPE,
	AddressLine1        IN	  address.ADDRESS_LINE1%TYPE,
	AddressLine2        IN	  address.ADDRESS_LINE2%TYPE,
	AddressLine3        IN	  address.ADDRESS_LINE3%TYPE,
	AddressLine4        IN	  address.ADDRESS_LINE4%TYPE,
	AddressSeqNum       in    address.ADDRESS_SEQ_NUM%TYPE,
	CityName		    IN    address.CITY_NAME%TYPE,
	CountryCode		    IN    address.COUNTRY_CODE%TYPE,
	ForeignAddressFlag	IN    address.FOREIGN_ADDRESS_FLAG%TYPE,
    ForeignPostalCode	IN    address.FOREIGN_POSTAL_CODE%TYPE,
	StateIn             IN    address.STATE%TYPE,
	zipCode             IN    address.ZIP_CODE%TYPE,
    zipCodeExt          IN    address.ZIP_CODE_EXT%TYPE,
	phoneSeqNum 		IN phone.phone_seq_num%TYPE,
    phoneNum 			IN phone.phone_num%TYPE,
    faxSeqNum 			IN phone.phone_seq_num%TYPE,
    faxNum 				IN phone.phone_num%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION thrdparty_delete
(
	thrdpartyseqnum IN thrdparty.thrdparty_seq_num%TYPE
) RETURN NUMBER;

/********************************************
** Select PROCEDURE FOR TABLE "THRDPARTY"
** DATE: 03-JUL-06 15:10:03
*********************************************/
PROCEDURE thrdparty_select
(
	caseseqnum IN thrdparty.thrdparty_Case_seq_num%TYPE,
	 recordset OUT ref_cursor);

PROCEDURE thrdparty_select_firms
(ThrdPartySeqNum in THRDPARTY.THRDPARTY_SEQ_NUM%TYPE,
  recordset OUT ref_cursor);

PROCEDURE thrdparty_firm_select
(ThrdpartyFirmSeqNum in THRDPARTY_FIRM.THRDPARTY_FIRM_SEQ_NUM%TYPE,
 recordset OUT ref_cursor);

 PROCEDURE case_thrdparty_firm_select
(CaseSeqNum in THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor);

 FUNCTION thrdparty_firm_insert
          (AdjusterName IN thrdparty_firm.adjuster_name%TYPE,
          AttorneyName IN thrdparty_firm.attorney_name%TYPE,
          ContactName IN thrdparty_firm.contact_name%TYPE,
          CreateUser IN thrdparty_firm.create_user%TYPE,
          CompanyCode IN company.company_code%TYPE,
          CompanyName IN company.company_name%TYPE,
          CompanyShortDescr IN company.company_short_descr%TYPE,
          TaxID IN company.tax_id%TYPE,
          AddressCode IN address.address_code%TYPE,
          AddressLine1 IN address.address_line1%TYPE,
          AddressLine2 IN address.address_line2%TYPE,
          AddressLine3 IN address.address_line3%TYPE,
          AddressLine4 IN address.address_line4%TYPE,
          CityName IN address.city_name%TYPE,
          CountryCode IN address.country_code%TYPE,
          ForeignAddressFlag IN address.foreign_address_flag%TYPE,
          ForeignPostalCode IN address.foreign_postal_code%TYPE,
          inState IN address.state%TYPE,
          ZipCode IN address.zip_Code%TYPE,
          ZipCodeExt IN address.zip_Code_Ext%TYPE,
          phoneNum IN phone.phone_num%TYPE,
          faxNum IN phone.phone_num%TYPE,
		  firmSeqNum IN OUT thrdparty_firm.THRDPARTY_FIRM_SEQ_NUM%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_firm_update
(
          AdjusterName IN thrdparty_firm.adjuster_name%TYPE,
          AttorneyName IN thrdparty_firm.attorney_name%TYPE,
          CompanySeqNum IN thrdparty_firm.company_seq_num%TYPE,
          ContactName IN thrdparty_firm.contact_name%TYPE,
          FirmSeqNum IN number,
          CompanyName IN company.company_name%TYPE,
          CompanyShortDescr IN company.company_short_descr%TYPE,
          TaxID IN company.tax_id%TYPE,
          AddressCode IN address.address_code%TYPE,
          AddressLine1 IN address.address_line1%TYPE,
          AddressLine2 IN address.address_line2%TYPE,
          AddressLine3 IN address.address_line3%TYPE,
          AddressLine4 IN address.address_line4%TYPE,
          AddressSeqNum IN address.address_seq_Num%TYPE,
          CityName IN address.city_name%TYPE,
          CountryCode IN address.country_code%TYPE,
          UpdateUser IN address.update_user%TYPE,
		  updateNum  IN thrdparty_firm.update_num%TYPE,
          ForeignAddressFlag IN address.foreign_address_flag%TYPE,
          ForeignPostalCode IN address.foreign_postal_code%TYPE,
          inState IN address.state%TYPE,
          ZipCode IN address.zip_Code%TYPE,
          ZipCodeExt IN address.zip_Code_Ext%TYPE,
          phoneSeqNum IN phone.phone_seq_num%TYPE,
          phoneNum IN phone.phone_num%TYPE,
          faxSeqNum IN phone.phone_seq_num%TYPE,
          faxNum IN phone.phone_num%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_firm_delete
(
	FirmSeqNum IN thrdparty_firm.thrdparty_firm_seq_num%TYPE,
	CompanySeqNum IN company.company_seq_num%TYPE
) RETURN NUMBER;

PROCEDURE selectTasks
          (recordset OUT ref_cursor,
		    	 userName IN Varchar2,
				   taskCode in Varchar2,
				   FromDate in Date,
				   ThruDate in Date,
				   ClaimId  in Number
);

PROCEDURE selectTasksByCase
          (recordset OUT ref_cursor,
          	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
		   			taskCode in thrdparty_task.thrdparty_task_code%TYPE

);


/********************************************
** INSERT FUNCTION FOR TABLE "THRDPARTY_TASK"
** DATE: 11-MAY-06 15:32:59
*********************************************/
FUNCTION thrdparty_task_insert
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE,
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskduedate IN thrdparty_task.task_due_date%TYPE,
	taskusername IN thrdparty_task.task_user_name%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE,
	tasktext IN thrdparty_task.task_text%TYPE,
	createuser IN thrdparty_task.create_user%TYPE
) RETURN NUMBER;

/********************************************
** UPDATE FUNCTION FOR TABLE "THRDPARTY_TASK"
** DATE: 11-MAY-06 15:32:59
*********************************************/
FUNCTION thrdparty_task_update
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE,
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE,
	taskduedate IN thrdparty_task.task_due_date%TYPE,
	tasktext IN thrdparty_task.task_text%TYPE,
	taskusername IN thrdparty_task.task_user_name%TYPE,
	updateuser IN thrdparty_task.update_user%TYPE
) RETURN NUMBER;


/********************************************
** DELETE FUNCTION FOR TABLE "THRDPARTY_TASK"
** DATE: 11-MAY-06 15:32:59
*********************************************/
FUNCTION thrdparty_task_delete
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY_TASK"
** DATE: 25-JULY-06 13:53:00
*********************************************/
PROCEDURE thrdparty_task_delete_by_code
(
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE
);

PROCEDURE thrdparty_task_delete_all
(
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE
);

PROCEDURE thrdparty_case_select
(  	recordset OUT ref_cursor,
 	CaseSeqNum in THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
	ClaimId in THRDPARTY_CASE.CLAIM_ID%TYPE

);

FUNCTION thrdparty_case_insert
(
	claimid IN thrdparty_case.claim_id%TYPE,
	createuser IN thrdparty_case.create_user%TYPE,
	lastKnownClaimStatus IN thrdparty_case.tpr0003_last_known_claim_status%TYPE,
	adjustercode IN thrdparty_case.thrdparty_adjuster_code %TYPE,
	statuscode IN thrdparty_case.thrdparty_status_code%TYPE,
	closurecode  IN thrdparty_case.thrdparty_closure_code%TYPE,
	typecode IN thrdparty_case.thrdparty_type_code%TYPE,
	updateseqnum IN thrdparty_case.update_seq_num%TYPE,
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	causeofactioncode in thrdparty_case.cause_of_action_code%TYPE,
	sicCode in thrdparty_case.SUBSEQ_INTERVENING_CAUSE_CODE%type,
	cocounselfirmseqnum IN thrdparty_case.cocounsel_firm_seq_num%TYPE,
	plaintifffirmseqnum IN thrdparty_case.plaintiff_firm_seq_num%TYPE,
	statutedate IN thrdparty_case.statute_date%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_case_update
(
	causeofactioncode IN thrdparty_case.cause_of_action_code%TYPE,
	cocounselfirmseqnum IN thrdparty_case.cocounsel_firm_seq_num%TYPE,
	lastKnownClaimStatus IN thrdparty_case.tpr0003_last_known_claim_status%TYPE,
	plaintifffirmseqnum IN thrdparty_case.plaintiff_firm_seq_num%TYPE,
	statutedate IN thrdparty_case.statute_date%TYPE,
	subseqinterveningcausedate IN thrdparty_case.subseq_intervening_cause_date%TYPE,
	subseqinterveningcausecode IN thrdparty_case.subseq_intervening_cause_code%TYPE,
	suitfileddate IN thrdparty_case.suit_filed_date%TYPE,
	adjustercode IN thrdparty_case.thrdparty_adjuster_code%TYPE,
	closurecode  IN thrdparty_case.thrdparty_closure_code%TYPE,
	statuscode IN thrdparty_case.thrdparty_status_code%TYPE,
	typecode IN thrdparty_case.thrdparty_type_code%TYPE,
	updateuser IN thrdparty_case.update_user%TYPE,
	accidentlocationtext IN thrdparty_case.accident_location_text%TYPE,
	NDReimbursementAmt IN thrdparty_case.nd_reimbursement_amount%TYPE,
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	updateseqnum IN thrdparty_case.update_seq_num%TYPE,
	claimid IN thrdparty_case.claim_id%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_case_delete
(
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	claimid IN thrdparty_case.claim_id%TYPE,
	updateuser IN thrdparty_case.update_user%TYPE
) RETURN NUMBER;


/********************************************
** PROCEDURE FOR TABLE "THRDPARTY_NOTE"
** DATE: 06-JUL-06
*********************************************/
PROCEDURE thrdparty_note_select
(	CaseSeqNum IN thrdparty_note.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor
);

FUNCTION thrdparty_note_insert
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE,
	CaseSeqNum IN thrdparty_note.thrdparty_case_seq_num%TYPE,
	NoteText   IN thrdparty_note.note_text%TYPE,
	NoteCode   IN thrdparty_note.thrdparty_note_code%TYPE,
	createuser IN thrdparty_note.create_user%TYPE,
	createDate IN thrdparty_note.create_date%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_note_update
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE,
	NoteText   IN thrdparty_note.note_text%TYPE,
	NoteCode   IN thrdparty_note.thrdparty_note_code%TYPE,
	UserId IN thrdparty_note.update_user%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_note_delete
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE
) RETURN NUMBER;

/********************************************
** Select PROCEDURE FOR TABLE "THRDPARTY_EXPENSE
** DATE: 11-JULY-06
*********************************************/

PROCEDURE thrdparty_expense_select
(thrdpartyCaseSeqNum in THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
;
/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_EXPENSE
** DATE: 11-JULY-06 15:32:59
*********************************************/
Function thrdparty_expense_insert
(
    approvalDate IN THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE,
	approverUser IN THRDPARTY_EXPENSE.APPROVER_USER%TYPE,
	budgetCode IN THRDPARTY_EXPENSE.BUDGET_CODE%TYPE,
	createUser IN THRDPARTY_EXPENSE.UPDATE_USER%TYPE,
	expenseAmount IN THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE,
	expenseDate IN THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE,
	expenseText IN THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE,
	recoveryAmount IN THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE,
	thrdpartyCaseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE
)
return number;

PROCEDURE thrdparty_expApproval_select
(
thrdpartyCaseSeqNum in THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor
);

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_EXPENSE "
** DATE: 11-JULY-06 15:32:59
*********************************************/
Function thrdparty_expense_update
(
	approvaldate IN THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE,
	approverUser IN THRDPARTY_EXPENSE.APPROVER_USER%TYPE,
	budgetCode IN THRDPARTY_EXPENSE.BUDGET_CODE%TYPE,
	recoveryAmount IN THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE,
	expenseAmount IN THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE,
	expenseDate IN THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE,
	expenseText IN THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE,
	thrdpartyCaseSeqNum in thrdparty_expense.THRDPARTY_CASE_SEQ_NUM%type,
	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE,
	updateSeqNum IN THRDPARTY_EXPENSE.UPDATE_SEQ_NUM%TYPE,
	updateUser IN THRDPARTY_EXPENSE.UPDATE_USER%TYPE

)
return number;

Function thrdparty_expApproval_update
(
	thrdpartyCaseSeqNum in thrdparty_expense.THRDPARTY_CASE_SEQ_NUM%type,
	updateSeqNum IN Thrdparty_case.UPDATE_SEQ_NUM%TYPE,
	updateUser IN Thrdparty_case.UPDATE_USER%TYPE,
	expenseApprovalCode in Thrdparty_case.EXPENSE_APPROVAL_CODE%type

)
return number;


/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY_EXPENSE "
** DATE: 11-JULY-06 15:32:59
*********************************************/
Function thrdparty_expense_delete
(

	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE,
	thrdpartyCaseSeqNum in thrdparty_case.THRDPARTY_CASE_SEQ_NUM%type,
	updateUser in thrdparty_case.UPDATE_USER%type,
   thrdpartyExpenseUpdateNum in thrdparty_expense.UPDATE_SEQ_NUM%type

)

return number;

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
PROCEDURE companion_claim_select
(	recordset OUT ref_cursor,
	CaseSeqNum in THRDPARTY_COMPANION_CLAIM.THRDPARTY_CASE_SEQ_NUM%TYPE

);

/********************************************
** INSERT FUNCTION FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_insert
(
    ClaimID IN THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE,
	CreateUser IN THRDPARTY_COMPANION_CLAIM.CREATE_USER%TYPE,
	CaseSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_CASE_SEQ_NUM%TYPE,
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE
) RETURN NUMBER;

/********************************************
** UPDATE FUNCTION FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_update
(
	ClaimID IN THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE,
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE,
	UpdateSeqNum IN THRDPARTY_COMPANION_CLAIM.UPDATE_SEQ_NUM%TYPE,
	UpdateUser IN THRDPARTY_COMPANION_CLAIM.UPDATE_USER%TYPE
) RETURN NUMBER;

/********************************************
** DELETE FUNCTION FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_delete
(
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE
) RETURN NUMBER;

/********************************************
** FUNCTION TO RETRIEVE ADJUSTER EMPLOYEE ID "
** DATE: 26-JULY-06 07:27:00
*********************************************/
FUNCTION thrdparty_adjuster_name
(
	thrdpartyadjustercode IN THRDPARTY_CASE.THRDPARTY_ADJUSTER_CODE%TYPE
) RETURN VARCHAR2;

PROCEDURE claim_pymt_letter_recip_select
(   recordset OUT ref_cursor,
  	CaseSeqNum IN Number);

/********************************************
** RETRIEVE PROCEDURE FOR CLAIM PAYMENT - BILLED RECORDS
** DATE: 09-AUG-06
*********************************************/

PROCEDURE billed_cost_select
(   inPayId IN Number,
	inBillId IN Number,
  	recordset OUT ref_cursor
);

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_SETTLEMENT"
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_settlement_select
(	CaseSeqNum IN thrdparty_settlement.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor);

FUNCTION thrdparty_settlement_insert
(	ThrdpartyCaseSeqNum IN thrdparty_settlement.thrdparty_case_seq_num%TYPE,
	ThrdpartySettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE,
	ThrdpartySettlementCode   IN thrdparty_settlement.thrdparty_settlement_code%TYPE,
	SettlementAmount   IN thrdparty_settlement.settlement_amount%TYPE,
	SettlementDate   IN thrdparty_settlement.settlement_date%TYPE,
	CreateUser IN thrdparty_settlement.create_user%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_settlement_update
(	SettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE,
	SettlementCode   IN thrdparty_settlement.thrdparty_settlement_code%TYPE,
	SettlementAmount IN thrdparty_settlement.settlement_amount%TYPE,
	SettlementDate 	 IN thrdparty_settlement.settlement_date%TYPE,
	UpdateUser   	 IN thrdparty_settlement.update_user%TYPE,
	UpdateSeqNum 	 IN OUT thrdparty_settlement.update_seq_num%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_settlement_delete
(	SettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE
) RETURN NUMBER;

PROCEDURE settlement_note_seq_num_select
(	CaseSeqNum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor);

FUNCTION settlement_note_seq_num_update
(	CaseSeqNum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	NoteSeqNum IN thrdparty_case.settlement_note_seq_num%TYPE
) RETURN NUMBER;

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_SETTLEMENT_DETAIL
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_settl_dtl_select
(	SettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	--SettlementCode IN thrdparty_settlement_detail.thrdparty_settlement_code%TYPE,
	recordset OUT ref_cursor);

PROCEDURE thrdparty_settl_dtl_generate
(	SettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	--CaseSeqNum IN thrdparty_settlement_detail.thrdparty_case_seq_num%TYPE,
	SettlementCode IN thrdparty_settlement_category.thrdparty_settlement_code%TYPE,
	recordset OUT ref_cursor);

PROCEDURE thrdparty_settl_dtl_cd_select
(	SettlementCode IN thrdparty_settlement_category.thrdparty_settlement_code%TYPE,
	recordset OUT ref_cursor);

FUNCTION thrdparty_settl_dtl_insert
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE,
	--ThrdpartyCaseSeqNum IN thrdparty_settlement_detail.thrdparty_case_seq_num%TYPE,
	ThrdpartySettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	--ThrdpartySettlementCode   IN thrdparty_settlement_detail.thrdparty_settlement_code%TYPE,
	ThrdpartyFundCategoryCode   IN thrdparty_settlement_detail.thrdparty_fund_category_code%TYPE,
	CategoryAmount   IN thrdparty_settlement_detail.category_amount%TYPE,
	CreateUser IN thrdparty_settlement_detail.create_user%TYPE,
	CreateDate IN thrdparty_settlement_detail.create_date%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_settl_dtl_update
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE,
	CategoryAmount IN thrdparty_settlement_detail.category_amount%TYPE,
	--ThrdpartySettlementCode   IN thrdparty_settlement_detail.thrdparty_settlement_code%TYPE,
	UpdateSeqNum 	 IN OUT thrdparty_settlement_detail.update_seq_num%TYPE,
	UpdateUser   	 IN thrdparty_settlement_detail.update_user%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_settl_dtl_delete
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE
) RETURN NUMBER;


/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_CLAIM_COST"
** DATE: 10-AUG-06 10:16
*********************************************/
FUNCTION claimcost_insert
(
   payID IN THRDPARTY_CLAIM_COST.PAY_ID%TYPE,
    PrimaryKeySubNum IN THRDPARTY_CLAIM_COST.PAY_DTL_SEQ_NUM%TYPE,
    TransactionType IN THRDPARTY_CLAIM_COST.TRANSACTION_TYPE%TYPE,
    ClaimID IN THRDPARTY_CLAIM_COST.CLAIM_ID%TYPE,
    costCategory IN THRDPARTY_CLAIM_COST.CLAIM_COST_CATEGORY_CODE%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    unknownAmt IN THRDPARTY_CLAIM_COST.UNDETERMINED_AMOUNT%TYPE,
	CreateUser IN THRDPARTY_CLAIM_COST.CREATE_USER%TYPE
) RETURN NUMBER;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_CLAIM_COST"
** DATE: 10-AUG06 10:16
*********************************************/
FUNCTION claimcost_update
(
	payID IN THRDPARTY_CLAIM_COST.PAY_ID%TYPE,
    PrimaryKeySubNum IN THRDPARTY_CLAIM_COST.PAY_DTL_SEQ_NUM%TYPE,
    ClaimID IN THRDPARTY_CLAIM_COST.CLAIM_ID%TYPE,
    costCategory IN THRDPARTY_CLAIM_COST.CLAIM_COST_CATEGORY_CODE%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    unknownAmt IN THRDPARTY_CLAIM_COST.UNDETERMINED_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_CLAIM_COST.UPDATE_USER%TYPE,
	updateNum in THRDPARTY_CLAIM_COST.UPDATE_SEQ_NUM%TYPE
)
RETURN NUMBER;
FUNCTION billservice_insert
(
    PayId       IN THRDPARTY_BILL_SERVICE.PAY_ID%TYPE,
 	billID IN THRDPARTY_BILL_SERVICE.BILL_ID%TYPE,
    AuditSeqNum IN THRDPARTY_BILL_SERVICE.AUDIT_SEQ_NUM%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    CreateUser IN THRDPARTY_CLAIM_COST.CREATE_USER%TYPE
	)
RETURN NUMBER;

FUNCTION billservice_update
(
    PayId       IN THRDPARTY_BILL_SERVICE.PAY_ID%TYPE,
	billID IN THRDPARTY_BILL_SERVICE.BILL_ID%TYPE,
    AuditSeqNum IN THRDPARTY_BILL_SERVICE.AUDIT_SEQ_NUM%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_CLAIM_COST.UPDATE_USER%TYPE,
	updateNum in THRDPARTY_CLAIM_COST.UPDATE_SEQ_NUM%TYPE
)
 RETURN NUMBER;
/********************************************
** RETRIEVE PROCEDURE FOR CLAIM COST CATEGORY CODE
** DATE: 09-AUG-06
*********************************************/

PROCEDURE cost_category_select
(
	recordset OUT ref_cursor
);

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_SELECT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_select
(	recordset OUT ref_cursor,
	CaseSeqNum in THRDPARTY_PREDICTION.THRDPARTY_CASE_SEQ_NUM%TYPE

);

/********************************************
** INSERT FUNCTION FOR TABLE "PREDICTION_INSERT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_insert
(
    PredictionSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	CaseSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_CASE_SEQ_NUM%TYPE,
	PredictionStatusCode IN THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE,
	LienSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_LIEN_SEQ_NUM%TYPE,
	PredictionDate IN THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE,
	PredictionPercentFactor IN THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE,
	PredictionDescription IN THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE,
	CreateUser IN THRDPARTY_PREDICTION.CREATE_USER%TYPE,
	PredictionDueDate IN THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE,
	FinalPredictionFlag IN THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE
) RETURN NUMBER;

/********************************************
** UPDATE FUNCTION FOR TABLE "PREDICTION_UPDATE"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_update
(
    PredictionSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	PredictionStatusCode IN THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE,
	PredictionDate IN THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE,
	PredictionPercentFactor IN THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE,
	PredictionDescription IN THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE,
	UpdateUser IN THRDPARTY_PREDICTION.UPDATE_USER%TYPE,
	UpdateSeqNum IN THRDPARTY_PREDICTION.UPDATE_SEQ_NUM%TYPE,
	PredictionDueDate IN THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE,
	FinalPredictionFlag IN THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE
) RETURN NUMBER;

/********************************************
** FUNCTION TO RETRIEVE CURRENT LIEN SEQ NUM
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION current_lien_seq_num
(
caseseqnum IN THRDPARTY_LIEN.THRDPARTY_CASE_SEQ_NUM%TYPE
) RETURN NUMBER;

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_DETAIL"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_detail_select
(PredictionSeqNum in THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
  recordset OUT ref_cursor
);

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_DETAIL"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_new_dtl_categories
(LienSeqNum in THRDPARTY_LIEN.THRDPARTY_LIEN_SEQ_NUM%TYPE,
  recordset OUT ref_cursor
);

/********************************************
** INSERT FUNCTION FOR TABLE "PREDICTION_DETAIL_INSERT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_detail_insert
(
    PredictionDtlSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE,
    PredictionSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	ClaimCostCategoryCode IN THRDPARTY_PREDICTION_DETAIL.CLAIM_COST_CATEGORY_CODE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE,
	CreateUser IN THRDPARTY_PREDICTION_DETAIL.CREATE_USER%TYPE
) RETURN NUMBER;

/********************************************
** UPDATE FUNCTION FOR TABLE "PREDICTION_DETAIL_UPDATE"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_detail_update
(
    PredictionDtlSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_PREDICTION_DETAIL.UPDATE_USER%TYPE,
	UpdateSeqNum IN THRDPARTY_PREDICTION_DETAIL.UPDATE_SEQ_NUM%TYPE
) RETURN NUMBER;


FUNCTION get_policy_waiver_count
(
	PolicyNum IN POLICY.POLICY_NUM%TYPE
)
RETURN NUMBER;

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_DISTRIBUTION
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_distribution_select
(	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor);

PROCEDURE thrdparty_dist_refundee_select
(	recordset OUT ref_cursor,
	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE
	);

PROCEDURE thrdparty_max_lien_select
(	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	nonlien_amount IN OUT number,
	lien_amount IN OUT number);

FUNCTION thrdparty_distribution_update
(	AraAmount 			 IN thrdparty_distribution.Ara_Amount%TYPE,
	CheckAmount 		 IN thrdparty_distribution.Check_Amount%TYPE,
	CheckDate 			 IN thrdparty_distribution.Check_Date%TYPE,
	CheckNum 			 IN thrdparty_distribution.Check_Num%TYPE,
	ClaimCostAmount 	 IN thrdparty_distribution.Claim_Cost_Amount%TYPE,
	CorrectedDate 	 	 IN thrdparty_distribution.Corrected_Date%TYPE,
	correctedNum         IN thrdparty_distribution.corrected_distribution_seq_num%TYPE,
	EmployerCreditAmount IN thrdparty_distribution.Employer_Credit_Amount%TYPE,
	OtherAmount1 		 IN thrdparty_distribution.Other_Amount1%TYPE,
	OtherAmount2 		 IN thrdparty_distribution.Other_Amount2%TYPE,
	OtherDescr1 		 IN thrdparty_distribution.Other_Descr1%TYPE,
	OtherDescr2 		 IN thrdparty_distribution.Other_Descr2%TYPE,
	PayeeName 			 IN thrdparty_distribution.Payee_Name%TYPE,
	RefundAmount 		 IN thrdparty_distribution.Refund_Amount%TYPE,
	DistributionCode 	 IN thrdparty_distribution.Thrdparty_Distribution_Code%TYPE,
	DistributionSeqNum 	 IN thrdparty_distribution.Thrdparty_Distribution_Seq_Num%TYPE,
	UpdateSeqNum 		 IN OUT thrdparty_distribution.Update_Seq_Num%TYPE,
	UpdateUser 			 IN thrdparty_distribution.Update_User%TYPE,

	PolicyNum1 			 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount1 		 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName1			 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum1 	 IN OUT thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum1			 IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum1 		 IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	WorkerID1 			 IN thrdparty_distribution_refund.Worker_ID%TYPE,

	PolicyNum2 			 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount2 		 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName2			 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum2 	 IN OUT thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum2			 IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum2 		 IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	WorkerID2 			 IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_distribution_insert
(	AraAmount IN thrdparty_distribution.Ara_Amount%TYPE,
	CheckAmount IN thrdparty_distribution.Check_Amount%TYPE,
	CheckDate IN thrdparty_distribution.Check_Date%TYPE,
	CheckNum IN thrdparty_distribution.Check_Num%TYPE,
	ClaimCostAmount IN thrdparty_distribution.Claim_Cost_Amount%TYPE,
	CreateDate 			 IN thrdparty_distribution.create_date%TYPE,
	CreateUser IN thrdparty_distribution.Create_User%TYPE,
	EmployerCreditAmount IN thrdparty_distribution.Employer_Credit_Amount%TYPE,
	OtherAmount1 IN thrdparty_distribution.Other_Amount1%TYPE,
	OtherAmount2 IN thrdparty_distribution.Other_Amount2%TYPE,
	OtherDescr1 IN thrdparty_distribution.Other_Descr1%TYPE,
	OtherDescr2 IN thrdparty_distribution.Other_Descr2%TYPE,
	PayeeName IN thrdparty_distribution.Payee_Name%TYPE,
	RefundAmount IN thrdparty_distribution.Refund_Amount%TYPE,
	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	DistributionCode IN thrdparty_distribution.thrdparty_Distribution_Code%TYPE,
	DistributionSeqNum IN thrdparty_distribution.thrdparty_Distribution_Seq_Num%TYPE,
	SettlementSeqNum IN thrdparty_distribution.thrdparty_Settlement_Seq_Num%TYPE,
    correctedNum         IN thrdparty_distribution.corrected_distribution_seq_num%TYPE,
	PolicyNum1 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount1 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName1 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum1 IN OUT thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum1 IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID1 IN thrdparty_distribution_refund.Worker_ID%TYPE,

	PolicyNum2 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount2 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName2 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum2 IN OUT thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum2 IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID2 IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_distribution_delete
(	DistributionSeqNum IN thrdparty_distribution.thrdparty_distribution_seq_num%TYPE
) RETURN NUMBER;

PROCEDURE thrdparty_distr_descr_select
(	inCode IN thrdparty_distribution_code.code%TYPE,
	codeDescr IN OUT varchar2);


/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_DISTRIBUTION_REFUND
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_dist_refund_select
(	DistributionSeqNum IN thrdparty_distribution_refund.thrdparty_distribution_seq_num%TYPE,
	recordset OUT ref_cursor);

FUNCTION thrdparty_dist_refund_update
(	PolicyNum IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum IN thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	UpdateUser IN thrdparty_distribution_refund.Update_User%TYPE,
	WorkerID IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_dist_refund_insert
(	CreateDate 			 IN thrdparty_distribution.create_date%TYPE,
	CreateUser IN thrdparty_distribution_refund.Create_User%TYPE,
	DistributionSeqNum IN thrdparty_distribution.thrdparty_Distribution_Seq_Num%TYPE,
	PolicyNum IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum IN thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER;

FUNCTION thrdparty_dist_refund_delete
(	DistributionRefundSeqNum IN thrdparty_distribution_refund.thrdparty_distr_refund_seq_num%TYPE
) RETURN NUMBER;



/********************************************
** ESTABLISH LIEN PROCEDURES
*********************************************/
   PROCEDURE thrdparty_lien_create(
      thrdpartycaseseqnum   IN       thrdparty_lien.thrdparty_case_seq_num%TYPE,
      recordset             OUT      ref_cursor);

FUNCTION getNextLienSeq RETURN NUMBER;

FUNCTION getNDReimbAmount(thrdpartycaseseqnum IN thrdparty_lien.thrdparty_case_seq_num%TYPE)
RETURN NUMBER;

PROCEDURE thrdparty_LIEN_select(ThrdpartyCaseSeqNum IN THRDPARTY_LIEN.THRDPARTY_CASE_SEQ_NUM%TYPE,
                                                                        recordset OUT ref_cursor);

PROCEDURE thrdparty_lien_select_by_Seq(ThrdpartyLienSeqNum IN THRDPARTY_LIEN.THRDPARTY_LIEN_SEQ_NUM%TYPE,
                                                                        recordset OUT ref_cursor);

FUNCTION thrdparty_lien_update (
      expenseamount         IN   thrdparty_lien.expense_amount%TYPE,
      finallienflag         IN   thrdparty_lien.final_lien_flag%TYPE,
      liendate              IN   thrdparty_lien.lien_date%TYPE,
      updateuser            IN   thrdparty_lien.update_user%TYPE,
      thrdpartylienseqnum   IN   thrdparty_lien.thrdparty_lien_seq_num%TYPE,
      updatenum             IN   thrdparty_lien.update_seq_num%TYPE
   ) RETURN NUMBER;

FUNCTION thrdparty_lien_insert (
      createuser            IN   thrdparty_lien.create_user%TYPE,
      expenseamount         IN   thrdparty_lien.expense_amount%TYPE,
      finallienflag         IN   thrdparty_lien.final_lien_flag%TYPE,
      liendate              IN   thrdparty_lien.lien_date%TYPE,
      thrdpartycaseseqnum   IN   thrdparty_lien.thrdparty_case_seq_num%TYPE,
      thrdpartylienseqnum   IN   thrdparty_lien.thrdparty_lien_seq_num%TYPE
   ) RETURN NUMBER;

PROCEDURE thrdparty_clm_cost_cat_create (
      recordset      OUT      ref_cursor,
      inCaseSeqNum   IN       THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
      inclaimid      IN       thrdparty_claim_cost.claim_id%TYPE,
      inlienseqnum   IN       thrdparty_lien.thrdparty_lien_seq_num%TYPE,
      inuserid       IN       thrdparty_lien.create_user%TYPE
   );

PROCEDURE thrdparty_lien_cat_select(inLienSeqNum IN THRDPARTY_LIEN_CATEGORY.THRDPARTY_LIEN_SEQ_NUM%TYPE,
                                                                        recordset OUT ref_cursor);

FUNCTION thrdparty_lien_category_update (
      LienSeqNum        IN   thrdparty_lien_category.thrdparty_lien_seq_num%TYPE,
      CategoryCode      IN   thrdparty_lien_category.claim_cost_category_code%TYPE,
      LienAmount        IN   thrdparty_lien_category.lien_amount%TYPE,
      NOLienAmount      IN   thrdparty_lien_category.no_lien_amount%TYPE,
      ProjectedAmount   IN   thrdparty_lien_category.projected_amount%TYPE,
      ReserveAmount     IN   thrdparty_lien_category.reserve_amount%TYPE,
      UpdateSeqNum      IN   thrdparty_lien_category.update_seq_num%TYPE,
      UpdateUser        IN   thrdparty_lien_category.update_user%TYPE
   )
      RETURN NUMBER;

FUNCTION thrdparty_lien_category_insert(
        ThrdpartyLienSeqNum IN THRDPARTY_LIEN_CATEGORY.THRDPARTY_LIEN_SEQ_NUM%TYPE,
		ClaimCostCategoryCode IN THRDPARTY_LIEN_CATEGORY.CLAIM_COST_CATEGORY_CODE%TYPE,
        LienAmount IN THRDPARTY_LIEN_CATEGORY.LIEN_AMOUNT%TYPE,
        NOLienAmount IN THRDPARTY_LIEN_CATEGORY.NO_LIEN_AMOUNT%TYPE,
        ReserveAmount IN THRDPARTY_LIEN_CATEGORY.RESERVE_AMOUNT%TYPE,
        ProjectedAmount IN THRDPARTY_LIEN_CATEGORY.PROJECTED_AMOUNT%TYPE,
        CreateUser IN THRDPARTY_LIEN_CATEGORY.CREATE_USER%TYPE
        )
   RETURN NUMBER;

/*********************************************
**select cases with statute date 3 to 6 months in the future
*********************************************/
procedure Statute_Date_List
(inRunDate in date,
recordset out ref_cursor
);
/*********************************************
**select cases with a unit state prediction date in the
**next 60 days
*********************************************/
procedure Unit_Stat_Prediction_List
(recordset out ref_cursor
);

FUNCTION get_recovery_amount
(
	caseseqnum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE
)
RETURN NUMBER;

FUNCTION get_task_delete_code
(
	caseseqnum IN thrdparty_case.THRDPARTY_CASE_SEQ_NUM%TYPE,
	tasksToExclude IN VARCHAR2
)
RETURN NUMBER;

/*********************************************
**select the list of third party employees
*********************************************/
procedure Get_Adjuster_List
(recordset out ref_cursor
);

/*********************************************
**select the list of IME service codes
*********************************************/
procedure Get_IMEService_List
(recordset out ref_cursor
);
END pk82033_thirdparty;
 / 
PACKAGE BODY          Pk82033_ThirdParty AS

FUNCTION thrdparty_insert
(
	attorneyFirmSeqNum  IN thrdparty.attorney_firm_seq_num%TYPE,
	carrierClaimNum     IN thrdparty.carrier_claim_num%TYPE,
	carrierFirmSeqNum   IN thrdparty.carrier_firm_seq_num%TYPE,
	carrierPolicyNum    IN thrdparty.carrier_policy_num%TYPE,
	createUser          IN thrdparty.create_user%TYPE,
	districtAttorneyNum IN thrdparty.district_attorney_num%TYPE,
	identifierText      IN thrdparty.identifier_text%TYPE,
	personSeqNum        IN thrdparty.person_seq_num%TYPE,
	thrdpartyCaseSeqNum IN thrdparty.thrdparty_case_seq_num%TYPE,
	thrdpartyFirmSeqNum IN thrdparty.thrdparty_firm_seq_num%TYPE,
	thrdpartySeqNum     IN thrdparty.thrdparty_seq_num%TYPE,
    noteText            IN thrdparty.note_text%TYPE,
	BirthDate			IN	  person.BIRTH_DATE%TYPE,
	DriversLicenseNum	IN	  person.DRIVERS_LICENSE_NUM%TYPE,
	DriversLicenseState	IN	  person.DRIVERS_LICENSE_STATE%TYPE,
	EthnicGroupCode		IN	  person.ETHNIC_GROUP_CODE%TYPE,
	FirstName			IN	  person.FIRST_NAME%TYPE,
	GenderCode			IN	  person.GENDER_CODE%TYPE,
    LastName			IN	  person.LAST_NAME%TYPE,
	MaidenName			IN	  person.MAIDEN_NAME%TYPE,
	MarriedName			IN	  person.MARRIED_NAME%TYPE,
    MiddleInitial		IN	  person.MIDDLE_INITIAL%TYPE,
    NickName			IN	  person.NICK_NAME%TYPE,
	TaxId				IN	  person.TAX_ID%TYPE,
	AddressCode         in    address.ADDRESS_CODE%TYPE,
	AddressLine1        IN	  address.ADDRESS_LINE1%TYPE,
	AddressLine2        IN	  address.ADDRESS_LINE2%TYPE,
	AddressLine3        IN	  address.ADDRESS_LINE3%TYPE,
	AddressLine4        IN	  address.ADDRESS_LINE4%TYPE,
	CityName		    IN    address.CITY_NAME%TYPE,
	CountryCode		    IN    address.COUNTRY_CODE%TYPE,
	ForeignAddressFlag	IN    address.FOREIGN_ADDRESS_FLAG%TYPE,
    ForeignPostalCode	IN    address.FOREIGN_POSTAL_CODE%TYPE,
	StateIn             IN    address.STATE%TYPE,
	zipCode             IN    address.ZIP_CODE%TYPE,
    zipCodeExt          IN    address.ZIP_CODE_EXT%TYPE,
	phoneNum 			IN phone.phone_num%TYPE,
    faxNum 				IN phone.phone_num%TYPE
) RETURN NUMBER
IS
v_person_seq	      NUMBER;
v_address_seq         NUMBER;
v_phone_seq           NUMBER;
v_fax_seq			  NUMBER;
v_update_count 	   	 NUMBER;

BEGIN
IF LastName IS NOT NULL THEN
  SELECT person_seq.NEXTVAL, address_seq.NEXTVAL
    INTO v_person_seq, v_address_seq
	FROM dual;

	Pk82034_person_company.insert_person(v_person_seq,
                  FirstName,
                  LastName,
                  MiddleInitial,
                  GenderCode,
                  EthnicGroupCode,
                  DriversLicenseNum,
                  DriversLicenseState,
                  NickName,
                  MarriedName,
                  MaidenName,
                  null,
                  birthDate,
                  taxId,
              	  CreateUser);

     Pk82034_person_company.insert_address(v_address_seq,
                    addressCode,
                    addressline1,
                    addressline2,
                    addressline3,
                    addressline4,
                    cityname,
                    stateIn,
                    zipcode,
                    zipcodeext,
                    NULL,
                    ForeignAddressFlag,
                    NULL,
                    CreateUser);

        INSERT INTO PERSON_ADDRESS (PERSON_SEQ_NUM, ADDRESS_SEQ_NUM)
                              VALUES (v_person_seq ,v_address_seq );
	IF phoneNum is not null THEN
	   SELECT phone_seq.NEXTVAL
	     INTO v_phone_seq
	   	 FROM dual;

	   INSERT INTO PHONE (
       phone_seq_num,
       PHONE_CODE,
       phone_num)
         VALUES
       (v_phone_seq, 'P', phoneNum);

	   Insert into person_phone cp
		   (cp.PHONE_SEQ_NUM, cp.PERSON_SEQ_NUM)
		   values
		   (v_phone_seq, v_person_seq);
	END IF;
	IF faxNum is not null THEN
	   SELECT phone_seq.NEXTVAL
	     INTO v_fax_seq
	   	 FROM dual;

	   INSERT INTO PHONE (
         phone_seq_num,
         PHONE_CODE,
         phone_num)
         values (v_fax_seq, 'F', faxNum);

		 Insert into person_phone cp
		   (cp.PHONE_SEQ_NUM, cp.PERSON_SEQ_NUM)
		   values
		   (v_fax_seq, v_person_seq);
	END IF;

End If;

INSERT INTO thrdparty
	(
		thrdparty_seq_num,
		person_seq_num,
		thrdparty_case_seq_num,
		create_date,
		create_user,
		carrier_policy_num,
		carrier_claim_num,
		attorney_firm_seq_num,
		carrier_firm_seq_num,
		identifier_text,
		thrdparty_firm_seq_num,
		district_attorney_num,
		update_num,
        note_text
	)
	VALUES
	(
		thrdpartyseqnum,
		v_person_seq,
		thrdpartycaseseqnum,
		sysdate,
		createuser,
		carrierpolicynum,
		carrierclaimnum,
		attorneyfirmseqnum,
		carrierfirmseqnum,
		identifiertext,
		thrdpartyfirmseqnum,
		districtattorneynum,
		0,
        noteText
	);
	v_update_count := SQL%Rowcount;
	RETURN v_update_count;
END thrdparty_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION thrdparty_update
(
	attorneyFirmSeqNum  IN thrdparty.attorney_firm_seq_num%TYPE,
	carrierClaimNum     IN thrdparty.carrier_claim_num%TYPE,
	carrierFirmSeqNum   IN thrdparty.carrier_firm_seq_num%TYPE,
	carrierPolicyNum    IN thrdparty.carrier_policy_num%TYPE,
	districtAttorneyNum IN thrdparty.district_attorney_num%TYPE,
	identifierText      IN thrdparty.identifier_text%TYPE,
	personSeqNum        IN thrdparty.person_seq_num%TYPE,
	thrdpartyCaseSeqNum IN thrdparty.thrdparty_case_seq_num%TYPE,
	thrdpartyFirmSeqNum IN thrdparty.thrdparty_firm_seq_num%TYPE,
	thrdpartySeqNum     IN thrdparty.thrdparty_seq_num%TYPE,
    noteText            IN thrdparty.note_text%TYPE,
	updateUser          IN thrdparty.update_user%TYPE,
	updateNum           IN thrdparty.update_num%TYPE,
	BirthDate			IN	  person.BIRTH_DATE%TYPE,
	DriversLicenseNum	IN	  person.DRIVERS_LICENSE_NUM%TYPE,
	DriversLicenseState	IN	  person.DRIVERS_LICENSE_STATE%TYPE,
	EthnicGroupCode		IN	  person.ETHNIC_GROUP_CODE%TYPE,
	FirstName			IN	  person.FIRST_NAME%TYPE,
	GenderCode			IN	  person.GENDER_CODE%TYPE,
    LastName			IN	  person.LAST_NAME%TYPE,
	MaidenName			IN	  person.MAIDEN_NAME%TYPE,
	MarriedName			IN	  person.MARRIED_NAME%TYPE,
    MiddleInitial		IN	  person.MIDDLE_INITIAL%TYPE,
    NickName			IN	  person.NICK_NAME%TYPE,
	TaxId				IN	  person.TAX_ID%TYPE,
	AddressCode         in    address.ADDRESS_CODE%TYPE,
	AddressLine1        IN	  address.ADDRESS_LINE1%TYPE,
	AddressLine2        IN	  address.ADDRESS_LINE2%TYPE,
	AddressLine3        IN	  address.ADDRESS_LINE3%TYPE,
	AddressLine4        IN	  address.ADDRESS_LINE4%TYPE,
	AddressSeqNum       in    address.ADDRESS_SEQ_NUM%TYPE,
	CityName		    IN    address.CITY_NAME%TYPE,
	CountryCode		    IN    address.COUNTRY_CODE%TYPE,
	ForeignAddressFlag	IN    address.FOREIGN_ADDRESS_FLAG%TYPE,
    ForeignPostalCode	IN    address.FOREIGN_POSTAL_CODE%TYPE,
	StateIn             IN    address.STATE%TYPE,
	zipCode             IN    address.ZIP_CODE%TYPE,
    zipCodeExt          IN    address.ZIP_CODE_EXT%TYPE,
	phoneSeqNum 		IN phone.phone_seq_num%TYPE,
    phoneNum 			IN phone.phone_num%TYPE,
    faxSeqNum 			IN phone.phone_seq_num%TYPE,
    faxNum 				IN phone.phone_num%TYPE
) RETURN NUMBER
as
v_phone_seq           NUMBER;
v_fax_seq			  NUMBER;
v_update_count 	   	 NUMBER;
BEGIN
    If personSeqNum > 0 then
	Pk82034_person_company.update_person(personseqNum,
                                                FirstName,
                                                LastName,
                                                MiddleInitial,
                                         		GenderCode,
                                         		EthnicGroupCode,
                                                DriversLicenseNum,
                                         		DriversLicenseState,
                                                NickName,
                                                MarriedName,
                                                MaidenName,
                                                NULL,
                                         		birthDate,
                                         		taxid,
                								updateUser);
	Pk82034_person_company.update_address(addressSeqNum,
                                          addressline1,
										  addressline2,
										  addressline3,
										  addressline4,
										  cityname,
										  stateIn,
										  zipcode,
										  zipcodeext,
										  NULL,
										  ForeignAddressFlag,
                                          NULL,
                                          UpdateUser);
 	if phoneNum is not null and length(phoneNum) > 0 then
	    if phoneSeqNum = 0 then
		   SELECT phone_seq.NEXTVAL
	         INTO v_phone_seq
	   	     FROM dual;

	        INSERT INTO PHONE (
            phone_seq_num,
            PHONE_CODE,
            phone_num)
            values (v_phone_seq, 'P', phoneNum);

		  Insert into person_phone cp
		   (cp.PHONE_SEQ_NUM, cp.PERSON_SEQ_NUM)
		   values
		   (v_phone_seq, PersonSeqNum);
		else
		   UPDATE phone
		      set phone_num = phoneNum
		    where phone_seq_num = phoneSeqNum;
		end if;
  	else
     	if phoneSeqNum > 0 then
	       delete from person_phone
		   where phone_seq_num = phoneSeqNum;
	  	   v_update_count := v_update_count + SQL%Rowcount;
		   delete from phone
		   where phone_seq_num = phoneSeqNum;
	  	   v_update_count := v_update_count + SQL%Rowcount;
 		end if;
	end if;

	if faxNum is not null and length(faxNum) > 0 then
	    if faxSeqNum = 0 then
		   SELECT phone_seq.NEXTVAL
	         INTO v_fax_seq
	   	     FROM dual;

	        INSERT INTO PHONE (
                   phone_seq_num,
                   PHONE_CODE,
                   phone_num)
     	      values (v_fax_seq, 'F', faxNum);

		  Insert into person_phone cp
		   (cp.PHONE_SEQ_NUM, cp.PERSON_SEQ_NUM)
		   values
		   (v_fax_seq, PersonSeqNum);

		else
		   UPDATE phone
		      set phone_num = faxNum
		    where phone_seq_num = faxSeqNum;
		end if;
  	else
     	if faxSeqNum > 0 then
      	   delete from person_phone
	   	   where phone_seq_num = faxSeqNum;
	  	   v_update_count := v_update_count + SQL%Rowcount;
	  	   delete from phone
	   	   where phone_seq_num = faxSeqNum;
	  	   v_update_count := v_update_count + SQL%Rowcount;
 		end if;
	 end if;
	END IF;

	UPDATE thrdparty SET
		person_seq_num = personseqnum,
		thrdparty_case_seq_num = thrdpartycaseseqnum,
		carrier_policy_num = carrierpolicynum,
		carrier_claim_num =  carrierclaimnum,
		attorney_firm_seq_num = attorneyfirmseqnum,
		carrier_firm_seq_num = carrierfirmseqnum,
		identifier_text = identifiertext,
		thrdparty_firm_seq_num = thrdpartyfirmseqnum,
		district_attorney_num = districtattorneynum,
        note_text = noteText,
		update_date = sysdate,
		update_user = updateuser,
		update_num = updatenum + 1
	WHERE
		thrdparty_seq_num = thrdpartyseqnum
	 AND update_num = updatenum;
	v_update_count := SQL%Rowcount;
	RETURN v_update_count;
END thrdparty_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION thrdparty_delete
(
	thrdpartyseqnum IN thrdparty.thrdparty_seq_num%TYPE
)
RETURN NUMBER
as
v_update_count 	   	 NUMBER;
v_phone_seq          NUMBER := 0;
v_fax_seq            NUMBER := 0;
v_address_seq		 NUMBER := 0;
v_person_seq         NUMBER := 0;
v_firm_seq			 NUMBER := 0;
v_temp				 NUMBER := 0;
v_company_seq		 NUMBER := 0;

begin

select person_seq_num into v_person_seq
  from thrdparty
 Where THRDPARTY_SEQ_NUM = ThrdPartySeqNum;

 If v_person_seq > 0 THEN
    BEGIN
    select pp.phone_seq_num into v_phone_seq
	  from person_phone pp, phone p
	 where pp.person_seq_num = v_person_seq
	   and pp.PHONE_SEQ_NUM = p.PHONE_SEQ_NUM
	   and p.phone_code = 'P';
	EXCEPTION
	 WHEN NO_DATA_FOUND THEN
	     NULL;
	END;
	BEGIN
	select pp.phone_seq_num into v_fax_seq
	  from person_phone pp, phone p
	 where pp.person_seq_num = v_person_seq
	   and pp.PHONE_SEQ_NUM = p.PHONE_SEQ_NUM
	   and p.phone_code = 'F';
	EXCEPTION
	 WHEN NO_DATA_FOUND THEN
	     NULL;
	END;
	 If v_phone_seq > 0 then
	    Pk82034_person_company.delete_person_phone(v_phone_seq);
	 End If;

	 If v_fax_seq > 0 then
	     Pk82034_person_company.delete_person_phone(v_fax_seq);
	 End If;
	BEGIN
	 select address_seq_num into v_address_seq
	   from person_address
	  where person_seq_num = v_person_seq;
    EXCEPTION
	 WHEN NO_DATA_FOUND THEN
	     NULL;
	END;
	 If v_address_seq > 0 then
	    delete from person_address
		 where person_seq_num = v_person_seq;
		delete from address
		 where address_seq_num = v_address_seq;
	 End If;

 End If;

 BEGIN
 select thrdparty_firm_seq_num into v_firm_seq
  from thrdparty
 Where THRDPARTY_SEQ_NUM = ThrdPartySeqNum;
  EXCEPTION
	 WHEN NO_DATA_FOUND THEN
	     v_firm_seq := 0;
 END;
 if v_firm_seq > 0 then
   	BEGIN
     select company_seq_num into v_company_seq
	   from thrdparty_firm
	  where thrdparty_firm_seq_num = v_firm_seq;
	 EXCEPTION
	 WHEN NO_DATA_FOUND THEN
	     v_company_seq := 0;
	 END;
end if;

Delete THRDPARTY
 Where THRDPARTY_SEQ_NUM = ThrdPartySeqNum;
 v_update_count := SQL%Rowcount;

If v_person_seq > 0 then
    Delete person
    where person_seq_num = v_person_seq;
End If;
if v_firm_seq > 0 then
   v_temp := thrdparty_firm_delete(v_firm_seq, v_company_seq);
End If;

 RETURN v_update_count;
End thrdparty_delete;

PROCEDURE thrdparty_select
(  caseseqnum IN thrdparty.thrdparty_Case_seq_num%TYPE,
   recordset OUT ref_cursor)
Is
Begin open recordset for
  Select
     THRDPARTY.ATTORNEY_FIRM_SEQ_NUM as AttorneyFirmSeqNum,
	 THRDPARTY.CARRIER_CLAIM_NUM as CarrierClaimNum,
	 THRDPARTY.CARRIER_FIRM_SEQ_NUM as CarrierFirmSeqNum,
	 THRDPARTY.CARRIER_POLICY_NUM as CarrierPolicyNum,
	 THRDPARTY.CREATE_DATE as CreateDate,
	 THRDPARTY.CREATE_USER as CreateUser,
	 THRDPARTY.DISTRICT_ATTORNEY_NUM as DistrictAttorneyNum,
	 THRDPARTY.IDENTIFIER_TEXT as IdentifierText,
     THRDPARTY.NOTE_TEXT as NoteText,
	 THRDPARTY.PERSON_SEQ_NUM as PersonSeqNum,
	 THRDPARTY.THRDPARTY_CASE_SEQ_NUM as ThrdpartyCaseSeqNum,
	 THRDPARTY.THRDPARTY_FIRM_SEQ_NUM as ThrdpartyFirmSeqNum,
	 THRDPARTY.THRDPARTY_SEQ_NUM as ThrdpartySeqNum,
	 THRDPARTY.UPDATE_DATE as UpdateDate,
	 THRDPARTY.UPDATE_USER as UpdateUser,
	 THRDPARTY.UPDATE_NUM as UpdateNum,
	 	  PER.NAME as name,
		  PER.LAST_NAME as LastName,
          PER.FIRST_NAME as FirstName,
		  PER.MIDDLE_INITIAL as MiddleInitial,
		  PER.MARRIED_NAME as MarriedName,
          PER.MAIDEN_NAME as MaidenName,
		  PER.NICK_NAME as NickName,
		  PER.DRIVERS_LICENSE_NUM as DriversLicenseNum,
		  PER.DRIVERS_LICENSE_STATE as DriversLicenseState,
		  PER.BIRTH_DATE as BirthDate,
          PER.TAX_ID as TaxId,
		  PER.GENDER_CODE as GenderCode,
		  PER.ETHNIC_GROUP_CODE as EthnicGroupCode,
	   a.ADDRESS_CODE  as AddressCode,
       a.ADDRESS_LINE1 as AddressLine1,
       a.ADDRESS_LINE2 as AddressLine2,
       a.ADDRESS_LINE3 as AddressLine3,
       a.ADDRESS_LINE4 as AddressLine4,
       a.ADDRESS_SEQ_NUM as AddressSeqNum,
       a.CITY_NAME as CityName,
       a.COUNTRY_CODE as CountryCode,
       a.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag,
       a.FOREIGN_POSTAL_CODE as ForeignPostalCode,
       a.STATE as State,
       trim(a.ZIP_CODE) as ZipCode,
       trim(a.ZIP_CODE_EXT) as ZipCodeExt,
       nvl(p_per.phone_seq_num, 0) as PhoneSeqNum,
       nvl(p_per.phone_num, '') as PhoneNum,
       nvl(p_fax.phone_seq_num, 0) as FaxSeqNum,
       nvl(p_fax.phone_num, '') as FaxNum
 From THRDPARTY
 LEFT OUTER JOIN PERSON PER on PER.PERSON_SEQ_NUM = THRDPARTY.PERSON_SEQ_NUM
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, pp.person_seq_num FROM phone p, person_phone pp WHERE p.phone_code = 'P'  AND p.phone_seq_num = pp.phone_seq_num) p_per
       ON per.person_seq_num = p_per.person_seq_num
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, pp.person_seq_num FROM phone p, person_phone pp WHERE p.phone_code = 'F'  AND p.phone_seq_num = pp.phone_seq_num) p_fax
       ON per.person_seq_num = p_fax.person_seq_num
 LEFT OUTER JOIN (SELECT a.address_seq_num, pa.person_seq_num, a.address_line1, a.address_line2, a.CITY_NAME, a.state, a.zip_code, a.zip_code_ext, a.FOREIGN_POSTAL_CODE, a.FOREIGN_ADDRESS_FLAG, a.COUNTRY_CODE, a.ADDRESS_LINE4, a.ADDRESS_LINE3, a.ADDRESS_CODE
  FROM address a, person_address pa WHERE a.address_seq_num = pa.address_seq_num AND a.address_code = 'P') a ON a.person_seq_num = per.person_seq_num
 Where THRDPARTY_CASE_SEQ_NUM = CaseSeqNum;

End thrdparty_select;


PROCEDURE thrdparty_select_firms
(ThrdPartySeqNum in THRDPARTY.THRDPARTY_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
   IS
BEGIN
	OPEN recordset FOR
  Select
       f.ADJUSTER_NAME as AdjusterName,
       f.ATTORNEY_NAME as AttorneyName,
       f.COMPANY_SEQ_NUM as CompanySeqNum,
       f.CONTACT_NAME as ContactName,
       f.CREATE_DATE as CreateDate,
       f.CREATE_USER as CreateUser,
       f.THRDPARTY_FIRM_SEQ_NUM as ThrdpartyFirmSeqNum,
       f.UPDATE_DATE as UpdateDate,
       f.UPDATE_USER as UpdateUser,
	   f.UPDATE_NUM as UpdateNum,
       c.COMPANY_NAME as CompanyName,
       c.COMPANY_SEQ_NUM as CompanySeqNum,
       c.COMPANY_SHORT_DESCR as CompanyShortDescr,
       c.TAX_ID as TaxID,
       a.ADDRESS_CODE as AddressCode,
       a.ADDRESS_LINE1 as AddressLine1,
       a.ADDRESS_LINE2 as AddressLine2,
       a.ADDRESS_LINE3 as AddressLine3,
       a.ADDRESS_LINE4 as AddressLine4,
       a.ADDRESS_SEQ_NUM as AddressSeqNum,
       a.CITY_NAME as CityName,
       a.COUNTRY_CODE as CountryCode,
       a.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag,
       a.FOREIGN_POSTAL_CODE as ForeignPostalCode,
       a.STATE as State,
       trim(a.ZIP_CODE) as ZipCode,
       trim(a.ZIP_CODE_EXT) as ZipCodeExt,
       nvl(p_bus.phone_seq_num, 0) as PhoneSeqNum,
       nvl(p_bus.phone_num, '') as PhoneNum,
       nvl(p_fax.phone_seq_num, 0) as FaxSeqNum,
       nvl(p_fax.phone_num, '') as FaxNum
 FROM  THRDPARTY tp
 JOIN THRDPARTY_FIRM f on tp.ATTORNEY_FIRM_SEQ_NUM = f.THRDPARTY_FIRM_SEQ_NUM
    or tp.CARRIER_FIRM_SEQ_NUM = f.THRDPARTY_FIRM_SEQ_NUM
	or tp.THRDPARTY_FIRM_SEQ_NUM = f.THRDPARTY_FIRM_SEQ_NUM
 join company c on f.company_seq_num = c.company_seq_num
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, cp.company_seq_num FROM phone p, company_phone cp WHERE p.phone_code = 'B'  AND p.phone_seq_num = cp.phone_seq_num) p_bus
       ON c.company_seq_num = p_bus.company_seq_num
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, cp.company_seq_num FROM phone p, company_phone cp WHERE p.phone_code = 'F'  AND p.phone_seq_num = cp.phone_seq_num) p_fax
       ON c.company_seq_num = p_fax.company_seq_num
 LEFT OUTER JOIN (SELECT a.address_seq_num, ca.company_seq_num, a.address_line1, a.address_line2, a.CITY_NAME, a.state, a.zip_code, a.zip_code_ext, a.FOREIGN_POSTAL_CODE, a.FOREIGN_ADDRESS_FLAG, a.COUNTRY_CODE, a.ADDRESS_LINE4, a.ADDRESS_LINE3, a.ADDRESS_CODE
  FROM address a, company_address ca WHERE a.address_seq_num = ca.address_seq_num AND a.address_code = 'B') a ON a.company_seq_num = c.company_seq_num
 where tp.THRDPARTY_SEQ_NUM = ThrdpartySeqNum;


end thrdparty_select_firms;


PROCEDURE thrdparty_firm_select
(ThrdpartyFirmSeqNum in THRDPARTY_FIRM.THRDPARTY_FIRM_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
   IS
BEGIN
	OPEN recordset FOR
  Select
       f.ADJUSTER_NAME as AdjusterName,
       f.ATTORNEY_NAME as AttorneyName,
       f.COMPANY_SEQ_NUM as CompanySeqNum,
       f.CONTACT_NAME as ContactName,
       f.CREATE_DATE as CreateDate,
       f.CREATE_USER as CreateUser,
       f.THRDPARTY_FIRM_SEQ_NUM as ThrdpartyFirmSeqNum,
       f.UPDATE_DATE as UpdateDate,
       f.UPDATE_USER as UpdateUser,
	   f.UPDATE_NUM as UpdateNum,
       c.COMPANY_NAME as CompanyName,
       c.COMPANY_SEQ_NUM as CompanySeqNum,
       c.COMPANY_SHORT_DESCR as CompanyShortDescr,
       c.TAX_ID as TaxID,
       a.ADDRESS_CODE as AddressCode,
       a.ADDRESS_LINE1 as AddressLine1,
       a.ADDRESS_LINE2 as AddressLine2,
       a.ADDRESS_LINE3 as AddressLine3,
       a.ADDRESS_LINE4 as AddressLine4,
       a.ADDRESS_SEQ_NUM as AddressSeqNum,
       a.CITY_NAME as CityName,
       a.COUNTRY_CODE as CountryCode,
       a.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag,
       a.FOREIGN_POSTAL_CODE as ForeignPostalCode,
       a.STATE as State,
       trim(a.ZIP_CODE) as ZipCode,
       trim(a.ZIP_CODE_EXT) as ZipCodeExt,
       p1.phone_seq_num as phoneSeqNum,
       p1.phone_num as phoneNum,
       p2.phone_seq_num as faxSeqNum,
       p2.phone_num as faxNum
 From  THRDPARTY_FIRM f,
       COMPANY c,
       company_address ca,
       ADDRESS a,
       company_phone cp,
       PHONE p1,
       PHONE p2
 Where f.THRDPARTY_FIRM_SEQ_NUM = ThrdpartyFirmSeqNum
   and f.COMPANY_SEQ_NUM = c.COMPANY_SEQ_NUM
   and c.COMPANY_SEQ_NUM = ca.COMPANY_SEQ_NUM
   and ca.ADDRESS_SEQ_NUM = a.ADDRESS_SEQ_NUM
   and c.COMPANY_SEQ_NUM = cp.COMPANY_SEQ_NUM
   and cp.PHONE_SEQ_NUM = p1.PHONE_SEQ_NUM
   and p1.PHONE_CODE = 'B'
   and c.COMPANY_SEQ_NUM = cp.COMPANY_SEQ_NUM
   and cp.PHONE_SEQ_NUM = p2.PHONE_SEQ_NUM
   and p2.PHONE_CODE = 'F';

end thrdparty_firm_select;

PROCEDURE case_thrdparty_firm_select
(CaseSeqNum in THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
   IS
BEGIN
	OPEN recordset FOR
  Select
       f.ADJUSTER_NAME as AdjusterName,
       f.ATTORNEY_NAME as AttorneyName,
       f.COMPANY_SEQ_NUM as CompanySeqNum,
       f.CONTACT_NAME as ContactName,
       f.CREATE_DATE as CreateDate,
       f.CREATE_USER as CreateUser,
       f.THRDPARTY_FIRM_SEQ_NUM as ThrdpartyFirmSeqNum,
       f.UPDATE_DATE as UpdateDate,
       f.UPDATE_USER as UpdateUser,
	   f.UPDATE_NUM as UpdateNum,
       c.COMPANY_NAME as CompanyName,
       c.COMPANY_SEQ_NUM as CompanySeqNum,
       c.COMPANY_SHORT_DESCR as CompanyShortDescr,
       c.TAX_ID as TaxID,
       a.ADDRESS_CODE as AddressCode,
       a.ADDRESS_LINE1 as AddressLine1,
       a.ADDRESS_LINE2 as AddressLine2,
       a.ADDRESS_LINE3 as AddressLine3,
       a.ADDRESS_LINE4 as AddressLine4,
       a.ADDRESS_SEQ_NUM as AddressSeqNum,
       a.CITY_NAME as CityName,
       a.COUNTRY_CODE as CountryCode,
       a.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag,
       a.FOREIGN_POSTAL_CODE as ForeignPostalCode,
       a.STATE as State,
       trim(a.ZIP_CODE) as ZipCode,
       trim(a.ZIP_CODE_EXT) as ZipCodeExt,
       nvl(p_bus.phone_seq_num, 0) as PhoneSeqNum,
       nvl(p_bus.phone_num, '') as PhoneNum,
       nvl(p_fax.phone_seq_num, 0) as FaxSeqNum,
       nvl(p_fax.phone_num, '') as FaxNum
 FROM  THRDPARTY_CASE tc
 JOIN THRDPARTY_FIRM f on tc.PLAINTIFF_FIRM_SEQ_NUM = f.THRDPARTY_FIRM_SEQ_NUM
    or tc.COCOUNSEL_FIRM_SEQ_NUM = f.THRDPARTY_FIRM_SEQ_NUM
 join company c on f.company_seq_num = c.company_seq_num
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, cp.company_seq_num FROM phone p, company_phone cp WHERE p.phone_code = 'B'  AND p.phone_seq_num = cp.phone_seq_num) p_bus
       ON c.company_seq_num = p_bus.company_seq_num
 LEFT OUTER JOIN (SELECT p.phone_num, p.phone_seq_num, cp.company_seq_num FROM phone p, company_phone cp WHERE p.phone_code = 'F'  AND p.phone_seq_num = cp.phone_seq_num) p_fax
       ON c.company_seq_num = p_fax.company_seq_num
 LEFT OUTER JOIN (SELECT a.address_seq_num, ca.company_seq_num, a.address_line1, a.address_line2, a.CITY_NAME, a.state, a.zip_code, a.zip_code_ext, a.FOREIGN_POSTAL_CODE, a.FOREIGN_ADDRESS_FLAG, a.COUNTRY_CODE, a.ADDRESS_LINE4, a.ADDRESS_LINE3, a.ADDRESS_CODE
  FROM address a, company_address ca WHERE a.address_seq_num = ca.address_seq_num AND a.address_code = 'B') a ON a.company_seq_num = c.company_seq_num
 where tc.THRDPARTY_CASE_SEQ_NUM = CaseSeqNum;


end case_thrdparty_firm_select;


FUNCTION thrdparty_firm_insert
          (AdjusterName IN thrdparty_firm.adjuster_name%TYPE,
          AttorneyName IN thrdparty_firm.attorney_name%TYPE,
          ContactName IN thrdparty_firm.contact_name%TYPE,
          CreateUser IN thrdparty_firm.create_user%TYPE,
          CompanyCode IN company.company_code%TYPE,
          CompanyName IN company.company_name%TYPE,
          CompanyShortDescr IN company.company_short_descr%TYPE,
          TaxID IN company.tax_id%TYPE,
          AddressCode IN address.address_code%TYPE,
          AddressLine1 IN address.address_line1%TYPE,
          AddressLine2 IN address.address_line2%TYPE,
          AddressLine3 IN address.address_line3%TYPE,
          AddressLine4 IN address.address_line4%TYPE,
          CityName IN address.city_name%TYPE,
          CountryCode IN address.country_code%TYPE,
          ForeignAddressFlag IN address.foreign_address_flag%TYPE,
          ForeignPostalCode IN address.foreign_postal_code%TYPE,
          inState IN address.state%TYPE,
          ZipCode IN address.zip_Code%TYPE,
          ZipCodeExt IN address.zip_Code_Ext%TYPE,
          phoneNum IN phone.phone_num%TYPE,
          faxNum IN phone.phone_num%TYPE,
		  firmSeqNum IN OUT thrdparty_firm.THRDPARTY_FIRM_SEQ_NUM%TYPE)
RETURN NUMBER
as
v_firm_seq           NUMBER;
v_company_seq        NUMBER;
v_address_seq        NUMBER;
v_phone_seq          NUMBER;
v_fax_seq            NUMBER;
v_update_count 	   	 NUMBER := 0;

BEGIN
   SELECT thrdparty_firm_seq.NEXTVAL, address_seq.NEXTVAL, company_seq.NEXTVAL
     INTO v_firm_seq, v_address_seq, v_company_seq
   	 FROM dual;


	 Insert into COMPANY
	 (COMPANY_CODE,
      COMPANY_NAME,
      COMPANY_SEQ_NUM,
      COMPANY_SHORT_DESCR,
      TAX_ID,
      UPDATE_USER,
      EFF_DT)
	 values
	 (CompanyCode,
     CompanyName,
      v_company_seq,
      CompanyShortDescr,
      TaxID,
      CreateUser,
      trunc(sysdate));
	  v_update_count := v_update_count + SQL%Rowcount;

	  Insert into THRDPARTY_FIRM
       (ADJUSTER_NAME,
       ATTORNEY_NAME,
       COMPANY_SEQ_NUM,
       CONTACT_NAME,
       CREATE_DATE,
       CREATE_USER,
       THRDPARTY_FIRM_SEQ_NUM,
	   UPDATE_NUM)
	  values
	  (AdjusterName,
       AttorneyName,
       v_company_seq,
       ContactName,
       sysdate,
       CreateUser,
       v_firm_seq,
	   0);
	   v_update_count := v_update_count + SQL%Rowcount;

  insert into ADDRESS
	  (ADDRESS_CODE,
       ADDRESS_LINE1,
       ADDRESS_LINE2,
       ADDRESS_LINE3,
       ADDRESS_LINE4,
       ADDRESS_SEQ_NUM,
       CITY_NAME,
       COUNTRY_CODE,
       CREATE_DATE,
       CREATE_USER,
       FOREIGN_ADDRESS_FLAG,
       FOREIGN_POSTAL_CODE,
       STATE,
       ZIP_CODE,
       ZIP_CODE_EXT
        ) Values (
       AddressCode,
       AddressLine1,
       AddressLine2,
       AddressLine3,
       AddressLine4,
       v_Address_Seq,
       CityName,
       CountryCode,
       sysdate,
       CreateUser,
       ForeignAddressFlag,
       ForeignPostalCode,
       inState,
       ZipCode,
       ZipCodeExt);
	   v_update_count := v_update_count + SQL%Rowcount;

	   insert into company_address ca
	   (ca.ADDRESS_SEQ_NUM, ca.COMPANY_SEQ_NUM)
	   values
	   (v_address_seq, v_company_seq);
	   v_update_count := v_update_count + SQL%Rowcount;

IF phoneNum is not null THEN

   SELECT phone_seq.NEXTVAL
     INTO v_phone_seq
   	 FROM dual;

     INSERT INTO PHONE (
                   phone_seq_num,
                   PHONE_CODE,
                   phone_num)
     values (v_phone_seq, 'B', phoneNum);
	 v_update_count := v_update_count + SQL%Rowcount;

   Insert into company_phone cp
	   (cp.PHONE_SEQ_NUM, cp.COMPANY_SEQ_NUM)
	   values
	   (v_phone_seq, v_company_seq);
   	 v_update_count := v_update_count + SQL%Rowcount;
	END IF;
IF faxNum is not null THEN

   SELECT phone_seq.NEXTVAL
     INTO v_fax_seq
   	 FROM dual;

	 INSERT INTO PHONE (
                   phone_seq_num,
                   PHONE_CODE,
                   phone_num)
     values (v_fax_seq, 'F', faxNum);
	 v_update_count := v_update_count + SQL%Rowcount;

	 Insert into company_phone cp
	   (cp.PHONE_SEQ_NUM, cp.COMPANY_SEQ_NUM)
	   values
	   (v_fax_seq, v_company_seq);
	 v_update_count := v_update_count + SQL%Rowcount;
	END IF;

firmSeqNum := v_firm_seq;

	IF v_update_count > 0 THEN
	   RETURN 1;
	else
	   RETURN 0;
	end if;
end thrdparty_firm_insert;

FUNCTION thrdparty_firm_update
(
          AdjusterName IN thrdparty_firm.adjuster_name%TYPE,
          AttorneyName IN thrdparty_firm.attorney_name%TYPE,
          CompanySeqNum IN thrdparty_firm.company_seq_num%TYPE,
          ContactName IN thrdparty_firm.contact_name%TYPE,
          FirmSeqNum IN number,
          CompanyName IN company.company_name%TYPE,
          CompanyShortDescr IN company.company_short_descr%TYPE,
          TaxID IN company.tax_id%TYPE,
          AddressCode IN address.address_code%TYPE,
          AddressLine1 IN address.address_line1%TYPE,
          AddressLine2 IN address.address_line2%TYPE,
          AddressLine3 IN address.address_line3%TYPE,
          AddressLine4 IN address.address_line4%TYPE,
          AddressSeqNum IN address.address_seq_Num%TYPE,
          CityName IN address.city_name%TYPE,
          CountryCode IN address.country_code%TYPE,
          UpdateUser IN address.update_user%TYPE,
		  updateNum  IN thrdparty_firm.update_num%TYPE,
          ForeignAddressFlag IN address.foreign_address_flag%TYPE,
          ForeignPostalCode IN address.foreign_postal_code%TYPE,
          inState IN address.state%TYPE,
          ZipCode IN address.zip_Code%TYPE,
          ZipCodeExt IN address.zip_Code_Ext%TYPE,
          phoneSeqNum IN phone.phone_seq_num%TYPE,
          phoneNum IN phone.phone_num%TYPE,
          faxSeqNum IN phone.phone_seq_num%TYPE,
          faxNum IN phone.phone_num%TYPE)
RETURN NUMBER
as
          v_phone_seq          NUMBER;
v_fax_seq          NUMBER;
v_update_count 	   NUMBER := 0;

BEGIN
Update THRDPARTY_FIRM
   set ADJUSTER_NAME = AdjusterName,
       ATTORNEY_NAME = AttorneyName,
       COMPANY_SEQ_NUM = CompanySeqNum,
       CONTACT_NAME = ContactName,
       UPDATE_DATE = sysdate,
       UPDATE_USER = updateUser,
	   update_num = updatenum + 1
 where THRDPARTY_FIRM_SEQ_NUM = FirmSeqNum
   and UPDATE_NUM = updateNum;
	v_update_count := v_update_count + SQL%Rowcount;

 update COMPANY
    set COMPANY_NAME = CompanyName,
        COMPANY_SHORT_DESCR = CompanyShortDescr,
        TAX_ID = TaxID
  where COMPANY_SEQ_NUM = CompanySeqNum;
	v_update_count := v_update_count + SQL%Rowcount;

update ADDRESS
   Set ADDRESS_CODE = AddressCode,
       ADDRESS_LINE1 = AddressLine1,
       ADDRESS_LINE2 = AddressLine2,
       ADDRESS_LINE3 = AddressLine3,
       ADDRESS_LINE4 = AddressLine4,
       CITY_NAME = CityName,
       COUNTRY_CODE = CountryCode,
       FOREIGN_ADDRESS_FLAG = ForeignAddressFlag,
       FOREIGN_POSTAL_CODE = ForeignPostalCode,
       STATE = inState,
       UPDATE_DATE = sysdate,
       UPDATE_USER = UpdateUser,
       ZIP_CODE = ZipCode,
       ZIP_CODE_EXT = ZipCodeExt
 Where ADDRESS_SEQ_NUM = AddressSeqNum;
	v_update_count := v_update_count + SQL%Rowcount;

 if phoneNum is not null and length(phoneNum) > 0 then
    if phoneSeqNum = 0 then

	   SELECT phone_seq.NEXTVAL
         INTO v_phone_seq
   	     FROM dual;

      INSERT INTO PHONE (
                   phone_seq_num,
                   PHONE_CODE,
                   phone_num)
      values (v_phone_seq, 'B', phoneNum);
	  v_update_count := v_update_count + SQL%Rowcount;

	  Insert into company_phone cp
	   (cp.PHONE_SEQ_NUM, cp.COMPANY_SEQ_NUM)
	   values
	   (v_phone_seq, CompanySeqNum);
	  v_update_count := v_update_count + SQL%Rowcount;
	else
	   UPDATE phone
	      set phone_num = phoneNum
	    where phone_seq_num = phoneSeqNum;
	  	v_update_count := v_update_count + SQL%Rowcount;
	end if;
  else
     if phoneSeqNum > 0 then
	    delete from company_phone
		 where phone_seq_num = phoneSeqNum;
	  	v_update_count := v_update_count + SQL%Rowcount;
		delete from phone
		  where phone_seq_num = phoneSeqNum;
	  	v_update_count := v_update_count + SQL%Rowcount;
 end if;
  end if;
 if faxNum is not null and length(faxNum) > 0 then
    if faxSeqNum = 0 then

	   SELECT phone_seq.NEXTVAL
         INTO v_fax_seq
   	     FROM dual;

      INSERT INTO PHONE (
                   phone_seq_num,
                   PHONE_CODE,
                   phone_num)
      values (v_fax_seq, 'F', faxNum);
	  v_update_count := v_update_count + SQL%Rowcount;

	  Insert into company_phone cp
	   (cp.PHONE_SEQ_NUM, cp.COMPANY_SEQ_NUM)
	   values
	   (v_fax_seq, CompanySeqNum);
	  v_update_count := v_update_count + SQL%Rowcount;
	else
	   UPDATE phone
	      set phone_num = faxNum
	    where phone_seq_num = faxSeqNum;
	  v_update_count := v_update_count + SQL%Rowcount;
	end if;
 else
   if faxSeqNum > 0 then
      delete from company_phone
	   where phone_seq_num = faxSeqNum;
	  v_update_count := v_update_count + SQL%Rowcount;
	  delete from phone
	   where phone_seq_num = faxSeqNum;
	  v_update_count := v_update_count + SQL%Rowcount;
 end if;
 end if;

	IF v_update_count > 0 THEN
	   RETURN 1;
	else
	   RETURN 0;
	end if;
end thrdparty_firm_update;

FUNCTION thrdparty_firm_delete
(
	FirmSeqNum IN thrdparty_firm.thrdparty_firm_seq_num%TYPE,
	CompanySeqNum IN company.company_seq_num%TYPE
)
RETURN NUMBER
as
v_phone_seq          NUMBER := 0;
v_fax_seq            NUMBER := 0;
v_address_seq		 NUMBER := 0;
v_update_count       NUMBER := 0;
begin

Delete THRDPARTY_FIRM
 Where THRDPARTY_FIRM.THRDPARTY_FIRM_SEQ_NUM = FirmSeqNum;
 v_update_count := SQL%Rowcount;

    if CompanySeqNum > 0 then
	    BEGIN
	    select cp.phone_seq_num into v_phone_seq
	      from company_phone cp, phone p
	     where cp.company_seq_num = CompanySeqNum
		   and cp.PHONE_SEQ_NUM = p.PHONE_SEQ_NUM
	       and p.phone_code = 'B';
	    EXCEPTION
	    WHEN NO_DATA_FOUND THEN
	         v_phone_seq := 0;
	     END;
	    if v_phone_seq > 0 then
		   Pk82034_person_company.delete_company_phone(v_phone_seq);
		end if;
		BEGIN
	    select cp.phone_seq_num into v_fax_seq
	      from company_phone cp, phone p
	     where cp.company_seq_num = CompanySeqNum
		   and cp.PHONE_SEQ_NUM = p.PHONE_SEQ_NUM
	       and p.phone_code = 'F';
	    EXCEPTION
	     WHEN NO_DATA_FOUND THEN
	          v_fax_seq := 0;
	    END;
	    if v_fax_seq > 0 then
		   Pk82034_person_company.delete_company_phone(v_fax_seq);
		end if;
		BEGIN
        select address_seq_num into v_address_seq
	      from company_address
	     where company_seq_num = CompanySeqNum;
        EXCEPTION
	     WHEN NO_DATA_FOUND THEN
	          v_address_seq := 0;
	    END;
	   If v_address_seq > 0 then
	      Pk82034_person_company.delete_company_address(v_address_seq);
	   End If;
	   BEGIN
	   delete from company
	    where company_seq_num = CompanySeqNum;
		EXCEPTION
	        WHEN OTHERS THEN
      raise_application_error (-20002, 'companyseqnum: ' || CompanySeqNum || 'Error: ' || sqlerrm);
 	    END;
	 end if;

	RETURN v_update_count;
end thrdparty_firm_delete;

PROCEDURE selectTasks
          (recordset OUT ref_cursor,
           userName IN Varchar2,
		   taskCode in Varchar2,
		   FromDate in Date,
		   ThruDate in Date,
           ClaimId  in Number)
   IS
BEGIN
	OPEN recordset FOR
	  Select /*+ USE_HASH(t c tc adp) */
			t.CREATE_DATE as CreateDate,
			t.CREATE_USER as CreateUser,
			t.TASK_DUE_DATE as TaskDueDate,
			t.TASK_TEXT as TaskText,
			t.TASK_USER_NAME as TaskUserName,
			t.THRDPARTY_CASE_SEQ_NUM as CaseSeqNum,
			t.THRDPARTY_TASK_CODE as TaskCode,
			t.THRDPARTY_TASK_SEQ_NUM as TaskSeqNum,
			t.UPDATE_DATE as UpdateDate,
			t.UPDATE_USER as UpdateUser,
			c.CLAIM_ID as ClaimId,
			tc.code_descr as taskDescr,
	 		ADP.LAST_NAME  || ', ' ||  decode(ADP.PREFERRED_NAME, ' ', ADP.FIRST_NAME, ADP.preferred_name) as adjusterName
	 From THRDPARTY_TASK t,
	 	  THRDPARTY_CASE c,
		  THRDPARTY_TASK_CODE tc,
		  LEGAL.ADP_VIRTUAL_EMPLOYEE_VIEW ADP
	 Where (t.TASK_USER_NAME = userName or userName = 'ALL')
	 and t.THRDPARTY_CASE_SEQ_NUM = c.THRDPARTY_CASE_SEQ_NUM
	 and t.THRDPARTY_TASK_CODE = tc.code
	 and (t.THRDPARTY_TASK_CODE = taskCode or taskCode = 'ALL')
	 and (t.TASK_DUE_DATE >= FromDate or FromDate = '01-JAN-1900')
	 and (t.TASK_DUE_DATE <= ThruDate or ThruDate = '01-JAN-2500')
	 and (c.CLAIM_ID = ClaimId or ClaimId = 0)
	 and t.TASK_USER_NAME = adp.USER_ID
	 order by TaskDueDate, CaseSeqNum;
END selectTasks;

PROCEDURE selectTasksByCase
          (recordset OUT ref_cursor,
          	caseSeqNum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
		   taskCode in thrdparty_task.thrdparty_task_code%TYPE )
   IS
BEGIN
	OPEN recordset FOR
  Select /*+FIRST_ROWS*/
	t.CREATE_DATE as CreateDate,
	t.CREATE_USER as CreateUser,
	t.TASK_DUE_DATE as TaskDueDate,
	t.TASK_TEXT as TaskText,
	t.TASK_USER_NAME as TaskUserName,
	t.THRDPARTY_CASE_SEQ_NUM as CaseSeqNum,
	t.THRDPARTY_TASK_CODE as TaskCode,
	t.THRDPARTY_TASK_SEQ_NUM as TaskSeqNum,
	t.UPDATE_DATE as UpdateDate,
	t.UPDATE_USER as UpdateUser,
	c.CLAIM_ID as ClaimId
 From
 	THRDPARTY_TASK t,
 	THRDPARTY_CASE c
 Where
 	t.THRDPARTY_CASE_SEQ_NUM = c.THRDPARTY_CASE_SEQ_NUM
	 and (t.THRDPARTY_TASK_CODE = taskCode or taskCode = 'ALL')
	 and (t.THRDPARTY_CASE_SEQ_NUM = caseSeqNum)
 order by TaskCode;

END selectTasksByCase;

/********************************************
** INSERT FUNCTION FOR TABLE "THRDPARTY_TASK"
** DATE: 11-MAY-06 15:32:59
*********************************************/
FUNCTION thrdparty_task_insert
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE,
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskduedate IN thrdparty_task.task_due_date%TYPE,
	taskusername IN thrdparty_task.task_user_name%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE,
	tasktext IN thrdparty_task.task_text%TYPE,
	createuser IN thrdparty_task.create_user%TYPE
)
RETURN NUMBER as begin
Insert into THRDPARTY_TASK
       (
      CREATE_DATE,
      CREATE_USER,
      TASK_DUE_DATE,
      TASK_TEXT,
      TASK_USER_NAME,
      THRDPARTY_CASE_SEQ_NUM,
      THRDPARTY_TASK_CODE,
      THRDPARTY_TASK_SEQ_NUM
       ) Values (
      SYSDATE,
      CreateUser,
      TaskDueDate,
      TaskText,
      TaskUserName,
      CaseSeqNum,
      TaskCode,
      TaskSeqNum);

	RETURN SQL%Rowcount;
end thrdparty_task_insert;

FUNCTION thrdparty_task_update
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE,
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE,
	taskduedate IN thrdparty_task.task_due_date%TYPE,
	tasktext IN thrdparty_task.task_text%TYPE,
	taskusername IN thrdparty_task.task_user_name%TYPE,
	updateuser IN thrdparty_task.update_user%TYPE
)
RETURN NUMBER as begin
Update THRDPARTY_TASK
   Set TASK_DUE_DATE = TaskDueDate,
       thrdparty_case_seq_num = caseseqnum,
       TASK_TEXT = TaskText,
       TASK_USER_NAME = TaskUserName,
       THRDPARTY_TASK_CODE = TaskCode,
       UPDATE_DATE = SYSDATE,
       UPDATE_USER = UpdateUser
 Where THRDPARTY_TASK.THRDPARTY_TASK_SEQ_NUM = TaskSeqNum;

	RETURN SQL%Rowcount;
end thrdparty_task_update;

FUNCTION thrdparty_task_delete
(
	taskseqnum IN thrdparty_task.thrdparty_task_seq_num%TYPE
)
RETURN NUMBER as begin
Delete THRDPARTY_TASK
 Where THRDPARTY_TASK.THRDPARTY_TASK_SEQ_NUM = TaskSeqNum;

	RETURN SQL%Rowcount;
end thrdparty_task_delete;

PROCEDURE thrdparty_task_delete_by_code
(
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE,
	taskcode IN thrdparty_task.thrdparty_task_code%TYPE
)
as begin
Delete THRDPARTY_TASK
 Where THRDPARTY_TASK.THRDPARTY_CASE_SEQ_NUM = caseseqnum
   and THRDPARTY_TASK.THRDPARTY_TASK_CODE = taskcode;

end thrdparty_task_delete_by_code;

PROCEDURE thrdparty_task_delete_all
(
	caseseqnum IN thrdparty_task.thrdparty_case_seq_num%TYPE
)
as begin
Delete THRDPARTY_TASK
 Where THRDPARTY_TASK.THRDPARTY_CASE_SEQ_NUM = caseseqnum;

end thrdparty_task_delete_all;

PROCEDURE thrdparty_case_select
(   recordset OUT ref_cursor,
   CaseSeqNum in THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  ClaimId in THRDPARTY_CASE.CLAIM_ID%TYPE
 )
   IS
BEGIN
	OPEN recordset FOR
  SELECT
	   t.CAUSE_OF_ACTION_CODE as CauseOFActionCode,
	   t.CLAIM_ID as ClaimID,
	   t.tpr0003_last_known_claim_status as LastKnownClaimStatus,
	   t.COCOUNSEL_FIRM_SEQ_NUM as CocounselFirmSeqNum,
	   t.CREATE_DATE as CreateDate,
	   t.CREATE_USER as CreateUser,
	   t.PLAINTIFF_FIRM_SEQ_NUM as PlaintiffFirmSeqNum,
	   t.STATUTE_DATE as StatuteDate,
	   t.SUBSEQ_INTERVENING_CAUSE_CODE as SubseqInterveningCauseCode,
	   t.SUBSEQ_INTERVENING_CAUSE_DATE as SubseqInterveningCauseDate,
	   t.SUIT_FILED_DATE as SuitFiledDate,
	   t.THRDPARTY_ADJUSTER_CODE as ThrdpartyAdjusterCode,
	   t.THRDPARTY_CASE_SEQ_NUM as CaseSeqNum,
	   t.THRDPARTY_CLOSURE_CODE as ThrdpartyClosureCode,
	   t.THRDPARTY_STATUS_CODE as ThrdpartyStatusCode,
	   t.THRDPARTY_TYPE_CODE as ThrdpartyTypeCode,
	   t.ACCIDENT_LOCATION_TEXT as AccidentLocationText,
	   t.ND_REIMBURSEMENT_AMOUNT as NDReimbursementAmt,
	   t.UPDATE_DATE as UpdateDate,
	   t.UPDATE_SEQ_NUM as UpdateSeqNum,
	   t.UPDATE_USER as UpdateUser
    FROM THRDPARTY_CASE t
   WHERE (t.THRDPARTY_CASE_SEQ_NUM = CaseSeqNum or CaseSeqNum = 0)
     AND (t.CLAIM_ID = ClaimId or ClaimId = 0)
	 AND (ClaimId > 0 or CaseSeqNum > 0)
     ;

END thrdparty_case_select;

FUNCTION thrdparty_case_insert
(
	claimid IN thrdparty_case.claim_id%TYPE,
	createuser IN thrdparty_case.create_user%TYPE,
	lastKnownClaimStatus IN thrdparty_case.tpr0003_last_known_claim_status%TYPE,
	adjustercode IN thrdparty_case.thrdparty_adjuster_code %TYPE,
	statuscode IN thrdparty_case.thrdparty_status_code%TYPE,
	closurecode  IN thrdparty_case.thrdparty_closure_code%TYPE,
	typecode IN thrdparty_case.thrdparty_type_code%TYPE,
	updateseqnum IN thrdparty_case.update_seq_num%TYPE,
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	causeofactioncode in thrdparty_case.cause_of_action_code%TYPE,
	sicCode in thrdparty_case.SUBSEQ_INTERVENING_CAUSE_CODE%type,
	cocounselfirmseqnum IN thrdparty_case.cocounsel_firm_seq_num%TYPE,
	plaintifffirmseqnum IN thrdparty_case.plaintiff_firm_seq_num%TYPE,
	statutedate IN thrdparty_case.statute_date%TYPE
)
RETURN NUMBER
IS
v_rowcount NUMBER;
begin
   Insert into THRDPARTY_CASE
   	(
	CLAIM_ID,
	tpr0003_last_known_claim_status,
	CREATE_DATE,
	CREATE_USER,
	THRDPARTY_ADJUSTER_CODE,
	THRDPARTY_CASE_SEQ_NUM,
	THRDPARTY_STATUS_CODE,
	THRDPARTY_CLOSURE_CODE,
	THRDPARTY_TYPE_CODE,
	UPDATE_SEQ_NUM,
	cause_of_action_code,
	SUBSEQ_INTERVENING_CAUSE_CODE,
	cocounsel_firm_seq_num,
    plaintiff_firm_seq_num,
	statute_date,
	nd_reimbursement_amount
 	) Values (
	ClaimID,
	lastKnownClaimStatus,
	Sysdate,
	CreateUser,
	AdjusterCode,
	CaseSeqNum,
	StatusCode,
	ClosureCode,
	TypeCode,
	UpdateSeqNum,
	causeofactioncode,
	sicCode,
	cocounselfirmseqnum,
	plaintifffirmseqnum,
	statutedate,
	0);

	v_rowcount := SQL%Rowcount;

	RETURN v_rowcount;
END thrdparty_case_insert;

FUNCTION thrdparty_case_update
(
	causeofactioncode IN thrdparty_case.cause_of_action_code%TYPE,
	cocounselfirmseqnum IN thrdparty_case.cocounsel_firm_seq_num%TYPE,
	lastKnownClaimStatus IN thrdparty_case.tpr0003_last_known_claim_status%TYPE,
	plaintifffirmseqnum IN thrdparty_case.plaintiff_firm_seq_num%TYPE,
	statutedate IN thrdparty_case.statute_date%TYPE,
	subseqinterveningcausedate IN thrdparty_case.subseq_intervening_cause_date%TYPE,
	subseqinterveningcausecode IN thrdparty_case.subseq_intervening_cause_code%TYPE,
	suitfileddate IN thrdparty_case.suit_filed_date%TYPE,
	adjustercode IN thrdparty_case.thrdparty_adjuster_code%TYPE,
	closurecode  IN thrdparty_case.thrdparty_closure_code%TYPE,
	statuscode IN thrdparty_case.thrdparty_status_code%TYPE,
	typecode IN thrdparty_case.thrdparty_type_code%TYPE,
	updateuser IN thrdparty_case.update_user%TYPE,
	accidentlocationtext IN thrdparty_case.accident_location_text%TYPE,
	NDReimbursementAmt IN thrdparty_case.nd_reimbursement_amount%TYPE,
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	updateseqnum IN thrdparty_case.update_seq_num%TYPE,
	claimid IN thrdparty_case.claim_id%TYPE
)
RETURN NUMBER
IS
BEGIN
	UPDATE thrdparty_case
	   SET thrdparty_adjuster_code       = adjustercode,
	       thrdparty_status_code         = statuscode,
	       tpr0003_last_known_claim_status = lastKnownClaimStatus,
	       thrdparty_type_code           = typecode,
	       cause_of_action_code          = causeofactioncode,
	       subseq_intervening_cause_date = subseqinterveningcausedate,
	       subseq_intervening_cause_code = subseqinterveningcausecode,
	       statute_date                  = statutedate,
	       suit_filed_date               = suitfileddate,
	       plaintiff_firm_seq_num        = plaintifffirmseqnum,
	       cocounsel_firm_seq_num        = cocounselfirmseqnum,
	       thrdparty_closure_code        = closurecode,
	       update_date                   = sysdate,
	       update_user                   = updateuser,
	       update_seq_num                = updateseqnum + 1,
	       accident_location_text        = accidentlocationtext,
		   nd_reimbursement_amount       = NDReimbursementAmt
	WHERE  thrdparty_case_seq_num        = caseseqnum
	  AND  update_seq_num 				 = updateseqnum;

	RETURN SQL%Rowcount;
END thrdparty_case_update;

FUNCTION thrdparty_case_delete
(
	caseseqnum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	claimid IN thrdparty_case.claim_id%TYPE,
	updateuser IN thrdparty_case.update_user%TYPE
)
RETURN NUMBER
IS
BEGIN
	--Delete all Tasks first
	DELETE thrdparty_task
	WHERE thrdparty_case_seq_num        = caseseqnum;

	--Delete all Status history second
	DELETE thrdparty_status_hist
	WHERE thrdparty_case_seq_num        = caseseqnum;

	--Delete Case
	DELETE thrdparty_case
	WHERE thrdparty_case_seq_num        = caseseqnum;

	RETURN SQL%Rowcount;
END thrdparty_case_delete;

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_NOTE"
** DATE: 06-JUL-06
*********************************************/
PROCEDURE thrdparty_note_select
(	CaseSeqNum IN thrdparty_note.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT
		n.create_date AS createdate,
		n.create_user AS createuser,
		n.note_text AS notetext,
		n.thrdparty_note_code AS notecode,
        nc.CODE_DESCR as noteCategory,
		n.thrdparty_case_seq_num AS caseseqnum,
		n.thrdparty_note_seq_num AS noteseqnum,
		n.update_date AS updatedate,
		n.update_user AS updateuser
	FROM thrdparty_note n, thrdparty_note_code nc
	WHERE thrdparty_case_seq_num = caseSeqNum
      and n.THRDPARTY_NOTE_CODE = nc.CODE
	ORDER BY n.create_date DESC;
END thrdparty_note_select;

FUNCTION thrdparty_note_insert
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE,
	CaseSeqNum IN thrdparty_note.thrdparty_case_seq_num%TYPE,
	NoteText   IN thrdparty_note.note_text%TYPE,
	NoteCode   IN thrdparty_note.thrdparty_note_code%TYPE,
	createUser IN thrdparty_note.create_user%TYPE,
	createDate IN thrdparty_note.create_date%TYPE
) RETURN NUMBER AS
BEGIN
	INSERT INTO thrdparty_note (
		create_date,
		create_user,
		thrdparty_note_code,
		note_text,
		thrdparty_case_seq_num,
		thrdparty_note_seq_num)
	VALUES (
		CreateDate,
		CreateUser,
		NoteCode,
		NoteText,
		CaseSeqNum,
		NoteSeqNum);

	RETURN SQL%Rowcount;
END thrdparty_note_insert;

FUNCTION thrdparty_note_update
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE,
	NoteText   IN thrdparty_note.note_text%TYPE,
	NoteCode   IN thrdparty_note.thrdparty_note_code%TYPE,
	UserId IN thrdparty_note.update_user%TYPE
) RETURN NUMBER AS
BEGIN
	UPDATE thrdparty_note SET
		note_text = NoteText,
		thrdparty_note_code = NoteCode,
		update_date = SYSDATE,
		update_user = UserId
	WHERE thrdparty_note_seq_num = NoteSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_note_update;

FUNCTION thrdparty_note_delete
(	NoteSeqNum IN thrdparty_note.thrdparty_note_seq_num%TYPE
) RETURN NUMBER AS
BEGIN
	UPDATE thrdparty_case SET settlement_note_seq_num = NULL WHERE settlement_note_seq_num = NoteSeqNum;
   DELETE thrdparty_note WHERE thrdparty_note_seq_num = NoteSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_note_delete;

/********************************************
** Select PROCEDURE FOR TABLE "THRDPARTY_EXPENSE
** DATE: 11-JULY-06
*********************************************/

PROCEDURE thrdparty_expense_select
(thrdpartyCaseSeqNum in THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
   IS
BEGIN
	OPEN recordset FOR

		Select
		APPROVAL_DATE as ApprovalDate,
		APPROVER_USER as ApproverUser,
		BUDGET_CODE as BudgetCode,
		CREATE_USER as CreateUser,
		CREATE_DATE as CreateDate,
		EXPENSE_AMOUNT as ExpenseAmount,
		EXPENSE_DATE as ExpenseDate,
		EXPENSE_TEXT as ExpenseText,
		RECOVERY_AMOUNT as RecoveryAmount,
		THRDPARTY_CASE_SEQ_NUM as ThrdpartyCaseSeqNum,
		THRDPARTY_EXPENSE_SEQ_NUM as ThrdpartyExpenseSeqNum,
		UPDATE_SEQ_NUM as UpdateSeqNum,
		UPDATE_USER as UpdateUser

		 From 		THRDPARTY_EXPENSE THPE
		 Where  	THRDPARTY_CASE_SEQ_NUM = ThrdpartyCaseSeqNum;



end thrdparty_expense_select;

PROCEDURE thrdparty_expApproval_select
(thrdpartyCaseSeqNum in THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
  recordset OUT ref_cursor)
   IS
BEGIN
	OPEN recordset FOR

		Select
		THRDPARTY_CASE_SEQ_NUM as ThrdpartyCaseSeqNum,
		UPDATE_SEQ_NUM as UpdateSeqNum,
		UPDATE_USER as UpdateUser,
		EXPENSE_APPROVAL_CODE AS ExpenseApprovalCode

		 From 		THRDPARTY_CASE
		 Where 		THRDPARTY_CASE_SEQ_NUM = ThrdpartyCaseSeqNum;


end thrdparty_expApproval_select;
/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_EXPENSE
** DATE: 11-JULY-06 15:32:59
*********************************************/
Function thrdparty_expense_insert
(
	approvalDate IN THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE,
	approverUser IN THRDPARTY_EXPENSE.APPROVER_USER%TYPE,
	budgetCode IN THRDPARTY_EXPENSE.BUDGET_CODE%TYPE,
	createUser IN THRDPARTY_EXPENSE.UPDATE_USER%TYPE,
	expenseAmount IN THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE,
	expenseDate in THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE,
	expenseText IN THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE,
	recoveryAmount IN THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE,
	thrdpartyCaseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_CASE_SEQ_NUM%TYPE,
	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE
)
return number
 AS BEGIN

	Insert into
	 	THRDPARTY_EXPENSE
	 (
		APPROVAL_DATE,
		APPROVER_USER,
		BUDGET_CODE,
		CREATE_DATE,
		CREATE_USER,
		EXPENSE_AMOUNT,
		EXPENSE_DATE,
		EXPENSE_TEXT,
		RECOVERY_AMOUNT,
		THRDPARTY_CASE_SEQ_NUM,
		THRDPARTY_EXPENSE_SEQ_NUM,
		UPDATE_DATE,
		UPDATE_SEQ_NUM,
		UPDATE_USER
	 )
	 Values
	 (
		approvalDate,
		approverUser,
		budgetCode,
		sysdate,
		createUser,
		expenseAmount,
		expenseDate,
		expenseText,
		recoveryAmount,
		thrdpartyCaseSeqNum,
		thrdpartyExpenseSeqNum,
		null,
		0,
		null
	  );

	  return sql%rowcount;

  end thrdparty_expense_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_EXPENSE "
** DATE: 11-JULY-06 15:32:59
*********************************************/
Function thrdparty_expense_update
(
	approvaldate IN THRDPARTY_EXPENSE.APPROVAL_DATE%TYPE,
	approverUser IN THRDPARTY_EXPENSE.APPROVER_USER%TYPE,
	budgetCode IN THRDPARTY_EXPENSE.BUDGET_CODE%TYPE,
	recoveryAmount IN THRDPARTY_EXPENSE.RECOVERY_AMOUNT%TYPE,
	expenseAmount IN THRDPARTY_EXPENSE.EXPENSE_AMOUNT%TYPE,
	expenseDate IN THRDPARTY_EXPENSE.EXPENSE_DATE%TYPE,
	expenseText IN THRDPARTY_EXPENSE.EXPENSE_TEXT%TYPE,
	thrdpartyCaseSeqNum in thrdparty_expense.THRDPARTY_CASE_SEQ_NUM%type,
	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE,
	updateSeqNum IN THRDPARTY_EXPENSE.UPDATE_SEQ_NUM%TYPE,
	updateUser IN THRDPARTY_EXPENSE.UPDATE_USER%TYPE

)
return number
as begin

	Update
		THRDPARTY_EXPENSE
    Set
		APPROVAL_DATE = approvalDate,
		APPROVER_USER = approverUser,
		BUDGET_CODE = budgetCode,
		RECOVERY_AMOUNT = recoveryAmount,
		EXPENSE_AMOUNT = expenseAmount,
	    EXPENSE_DATE = expenseDate,
	    EXPENSE_TEXT = expenseText,
		UPDATE_DATE = sysdate,
		UPDATE_SEQ_NUM = updateSeqNum + 1,
		UPDATE_USER = updateUser
	 Where
	 	THRDPARTY_EXPENSE_SEQ_NUM = thrdpartyExpenseSeqNum
		and UPDATE_SEQ_NUM = updateSeqNum;

	return sql%rowcount;

end thrdparty_expense_update;

Function thrdparty_expApproval_update
(
	thrdpartyCaseSeqNum in thrdparty_expense.THRDPARTY_CASE_SEQ_NUM%type,
	updateSeqNum IN Thrdparty_case.UPDATE_SEQ_NUM%TYPE,
	updateUser IN Thrdparty_case.UPDATE_USER%TYPE,
	expenseApprovalCode in Thrdparty_case.EXPENSE_APPROVAL_CODE%type

)
return number
as begin

	 Update thrdparty_case
	 set Expense_approval_code = expenseApprovalCode,
	 	 update_seq_num = update_seq_num + 1,
	 	 update_user = updateUser,
		 update_date = sysdate
	 where
	 	  Thrdparty_case_seq_num = thrdpartyCaseSeqNum
	   and  UPDATE_SEQ_NUM =  updateSeqNum;

	 return sql%rowcount;

	 end thrdparty_expapproval_update;


/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY_EXPENSE "
** DATE: 11-JULY-06 15:32:59
*********************************************/


Function thrdparty_expense_delete
(

	thrdpartyExpenseSeqNum IN THRDPARTY_EXPENSE.THRDPARTY_EXPENSE_SEQ_NUM%TYPE,
	thrdpartyCaseSeqNum in thrdparty_case.THRDPARTY_CASE_SEQ_NUM%type,
	updateUser in thrdparty_case.UPDATE_USER%type,
	thrdpartyExpenseUpdateNum in thrdparty_expense.UPDATE_SEQ_NUM%type
)
return number
is

v_select_count NUMBER;

begin


   Delete from THRDPARTY_EXPENSE
   where THRDPARTY_EXPENSE_SEQ_NUM = thrdpartyExpenseSeqNum
   and UPDATE_SEQ_NUM = thrdpartyExpenseUpdateNum;

   begin
	   select count(THRDPARTY_EXPENSE_SEQ_NUM)
	     into v_select_count
	     from THRDPARTY_EXPENSE
		    where THRDPARTY_CASE_SEQ_NUM = thrdpartyCaseSeqNum;
	    v_select_count := sql%rowcount;
	exception
	when NO_DATA_FOUND then
		v_select_count := 0;
	end;


   if v_select_count = 0 then
		update Thrdparty_case
		set expense_approval_code = null,
		    update_seq_num = update_seq_num + 1,
			update_user = updateUser,
			update_date = sysdate
		where thrdparty_case_seq_num = thrdpartyCaseSeqNum;
   end if;

   return sql%rowcount;

end thrdparty_expense_delete;

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
PROCEDURE companion_claim_select
(
	recordset OUT ref_cursor,
	CaseSeqNum in THRDPARTY_COMPANION_CLAIM.THRDPARTY_CASE_SEQ_NUM%TYPE

)
as
BEGIN
	OPEN recordset FOR
		Select
			cc.THRDPARTY_COMPANION_SEQ_NUM as CompanionSeqNum,
			cc.THRDPARTY_CASE_SEQ_NUM as CaseSeqNum,
			cc.COMPANION_CLAIM_ID as CompanionClaimID,
			cc.CREATE_DATE as CreateDate,
			cc.CREATE_USER as CreateUser,
			cc.UPDATE_DATE as UpdateDate,
			cc.UPDATE_USER as UpdateUser,
			cc.UPDATE_SEQ_NUM as UpdateSeqNum,
			sc.CODE_DESCR ThrdPartyStatus
		  From THRDPARTY_COMPANION_CLAIM cc,
			   THRDPARTY_CASE tc,
			   THRDPARTY_STATUS_CODE sc
		 Where
		    cc.COMPANION_CLAIM_ID = tc.CLAIM_ID (+)
		   and tc.THRDPARTY_STATUS_CODE = sc.CODE (+)
		   and cc.THRDPARTY_CASE_SEQ_NUM = CaseSeqNum  ;
END companion_claim_select;

/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_insert
(
    ClaimID IN THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE,
	CreateUser IN THRDPARTY_COMPANION_CLAIM.CREATE_USER%TYPE,
	CaseSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_CASE_SEQ_NUM%TYPE,
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE
)
RETURN NUMBER
as
BEGIN
	Insert into
	 	THRDPARTY_COMPANION_CLAIM
	 (
		THRDPARTY_COMPANION_SEQ_NUM,
		THRDPARTY_CASE_SEQ_NUM,
		COMPANION_CLAIM_ID,
		CREATE_DATE,
		CREATE_USER,
		UPDATE_DATE,
		UPDATE_USER,
		UPDATE_SEQ_NUM
	 ) Values
	 (
		companionSeqNum,
		caseSeqNum,
		ClaimID,
		sysdate,
		createUser,
		sysdate,
		createUser,
		0
	  );

	RETURN SQL%Rowcount;
END companion_claim_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_update
(
	ClaimID IN THRDPARTY_COMPANION_CLAIM.COMPANION_CLAIM_ID%TYPE,
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE,
	UpdateSeqNum IN THRDPARTY_COMPANION_CLAIM.UPDATE_SEQ_NUM%TYPE,
	UpdateUser IN THRDPARTY_COMPANION_CLAIM.UPDATE_USER%TYPE
)
RETURN NUMBER
as
BEGIN
	Update
		THRDPARTY_COMPANION_CLAIM
    Set
		COMPANION_CLAIM_ID = claimID,
		UPDATE_DATE = sysdate,
		UPDATE_SEQ_NUM = updateSeqNum + 1,
		UPDATE_USER = updateUser
	 Where
	 	THRDPARTY_COMPANION_SEQ_NUM = companionSeqNum
		and UPDATE_SEQ_NUM = UpdateSeqNum;

	RETURN SQL%Rowcount;
END companion_claim_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "THRDPARTY_COMPANION_CLAIM"
** DATE: 26-JULY-06 10:16
*********************************************/
FUNCTION companion_claim_delete
(
	CompanionSeqNum IN THRDPARTY_COMPANION_CLAIM.THRDPARTY_COMPANION_SEQ_NUM%TYPE
)
RETURN NUMBER
as
BEGIN
   Delete from THRDPARTY_COMPANION_CLAIM
   where THRDPARTY_COMPANION_SEQ_NUM = CompanionSeqNum;

	RETURN SQL%Rowcount;
END companion_claim_delete;

/********************************************
** FUNCTION TO RETRIEVE ADJUSTER EMPLOYEE ID "
** DATE: 26-JULY-06 07:27:00
*********************************************/
FUNCTION thrdparty_adjuster_name
(
	thrdpartyadjustercode IN THRDPARTY_CASE.THRDPARTY_ADJUSTER_CODE%TYPE
)
RETURN VARCHAR2
AS
v_emplid VARCHAR2(8);
BEGIN
   SELECT USER_NAME
     INTO v_emplid
     FROM THRDPARTY_ADJUSTER_CODE
    WHERE CODE = thrdpartyadjustercode;

	RETURN v_emplid;

    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          NULL;
END thrdparty_adjuster_name;

PROCEDURE billed_cost_select
(
    inPayId  in Number,
	inBillId IN Number,
  	recordset OUT ref_cursor
)
as
BEGIN
	OPEN recordset FOR
	Select AuditSeqNum,
	BillServDescrEffDate,
	BillServiceTypeCode,
	BillServiceCode,
	NationalDrugCode,
	ServiceEndDate,
	ServiceDate,
	AllowedAmt,
	MCOAdminFeeAmt,
	BillID,
	serviceDescr,
	paymentCategory,
	nvl(bs.lien_amount, 0) lienAmt,
	nvl(bs.no_Lien_Amount, 0) noLienAmt,
	nvl(bs.update_num, 0) updateNum,
	inPayId as payId,
    PaymentRequestID,
    McoWithholdPaymentRequestID
	from (
		 Select
         bas.AUDIT_SEQ_NUM as AuditSeqNum,
         bas.BILL_SERV_DESCR_EFF_DATE as BillServDescrEffDate,
         bas.BILL_SERVICE_TYPE_CODE as BillServiceTypeCode,
         bas.BILL_SERVICE_CODE as BillServiceCode,
         bas.NATIONAL_DRUG_CODE as NationalDrugCode,
         bas.SERVICE_END_DATE as ServiceEndDate,
         bas.SERVICE_DATE as ServiceDate,
         bas.ALLOWED_AMT as AllowedAmt,
		 bas.MCO_ADMIN_FEE_AMT as MCOAdminFeeAmt,
         bas.BILL_ID as BillID,
         bsd.SERVICE_DESCR_TEXT as serviceDescr,
		 bas.BILL_PAYMENT_CATEGORY_CODE as paymentCategory,
         bas.PAYMENT_REQUEST_ID as PaymentRequestID,
         bas.MCO_WITHHOLD_PAYMENT_REQUEST_ID as McoWithholdPaymentRequestID
          From
       --  BILL_AUDIT_SERVICE bas, BILLING_SERVICE_DESCRIPTION bsd
          CBP_AUDITED_SERVICE BAS, CBP_SERVICE_DESCRIPTION BSD
          Where bas.BILL_ID = inbillId
          and bas.BILL_SERVICE_CODE is not null
          and bas.BILL_SERVICE_CODE = bsd.BILL_SERVICE_CODE
		  and bas.BILL_SERVICE_TYPE_CODE = bsd.BILL_SERVICE_TYPE_CODE
		  AND (((bas.SERVICE_DATE >= bsd.SERVICE_DESCR_EFF_DATE)
  		  AND bsd.SERVICE_DESCR_EXP_DATE IS NULL)
		  OR (bas.SERVICE_DATE BETWEEN bsd.SERVICE_DESCR_EFF_DATE AND bsd.SERVICE_DESCR_EXP_DATE)
		  OR bas.SERVICE_DATE is null)

	Union all select  /*+FIRST_ROWS*/
  		 bas.AUDIT_SEQ_NUM as AuditSeqNum,
         bas.BILL_SERV_DESCR_EFF_DATE as BillServDescrEffDate,
         bas.BILL_SERVICE_TYPE_CODE as BillServiceTypeCode,
         bas.BILL_SERVICE_CODE as BillServiceCode,
         bas.NATIONAL_DRUG_CODE as NationalDrugCode,
         bas.SERVICE_END_DATE as ServiceEndDate,
         bas.SERVICE_DATE as ServiceDate,
         bas.ALLOWED_AMT as AllowedAmt,
		 bas.MCO_ADMIN_FEE_AMT as MCOAdminFeeAmt,
         bas.BILL_ID as BillID,
         nvl(ndc.PRODUCT_NAME, bas.NATIONAL_DRUG_CODE) as serviceDescr,
		 bas.BILL_PAYMENT_CATEGORY_CODE as paymentCategory,
         bas.PAYMENT_REQUEST_ID as PaymentRequestID,
         bas.MCO_WITHHOLD_PAYMENT_REQUEST_ID as McoWithholdPaymentRequestID
          From
         CBP_AUDITED_SERVICE bas, CBP_NATIONAL_DRUG ndc
          Where bas.BILL_ID = inbillId
            and bas.NATIONAL_DRUG_CODE = ndc.NATIONAL_DRUG_CODE (+)
			and bas.NATIONAL_DRUG_CODE is not null) a
		left outer join thrdparty_bill_service bs on bs.pay_id = inPayId
		     and bs.bill_id = inBillId
             and bs.AUDIT_SEQ_NUM = a.AuditSeqNum;

 END billed_cost_select;

/********************************************
** get worker, worker attorney, third parties, and carriers for claim cost letter
*********************************************/
PROCEDURE claim_pymt_letter_recip_select
(
  	recordset OUT ref_cursor,
  	CaseSeqNum IN Number
)
as
BEGIN
	OPEN RECORDSET FOR
	SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
			f.attorney_name AS codedescr,
			'Plaintiff Attorney' as codetype,
			0 as sorter
	FROM thrdparty_case tc
		 JOIN thrdparty_firm f ON tc.plaintiff_firm_seq_num = f.thrdparty_firm_seq_num
		 JOIN company c ON f.company_seq_num = c.company_seq_num
	WHERE tc.thrdparty_case_seq_num = CaseSeqNum
	UNION
    SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
            f.attorney_name AS codedescr,
            'Co-cousel' as codetype,
            0 as sorter
    FROM thrdparty_case tc
         JOIN thrdparty_firm f ON TC.COCOUNSEL_FIRM_SEQ_NUM = f.thrdparty_firm_seq_num
         JOIN company c ON f.company_seq_num = c.company_seq_num
    WHERE tc.thrdparty_case_seq_num = CaseSeqNum
    UNION
	SELECT  TO_CHAR(t.thrdparty_seq_num) AS code,
			p.last_name || ', ' || p.first_name || NVL2(p.middle_initial, ' ' || p.middle_initial, '') AS codedescr,
			'Third Party' as codetype,
			t.thrdparty_seq_num as sorter
	FROM thrdparty_case tc
		 JOIN thrdparty t ON tc.thrdparty_case_seq_num = t.thrdparty_case_seq_num
		 JOIN person p ON t.person_seq_num = p.person_seq_num
	WHERE t.thrdparty_firm_seq_num IS NULL AND tc.thrdparty_case_seq_num = CaseSeqNum
	UNION
	SELECT  TO_CHAR(t.thrdparty_seq_num) AS code,
			c.company_name AS codedescr,
			'Third Party' as codetype,
			t.thrdparty_seq_num as sorter
	FROM thrdparty_case tc
		 JOIN thrdparty t ON tc.thrdparty_case_seq_num = t.thrdparty_case_seq_num
		 JOIN thrdparty_firm f ON f.thrdparty_firm_seq_num = t.thrdparty_firm_seq_num
		 JOIN company c ON f.company_seq_num = c.company_seq_num
	WHERE t.person_seq_num IS NULL AND tc.thrdparty_case_seq_num = CaseSeqNum
	UNION
	SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
			c.company_name AS codedescr,
			'Carrier' as codetype,
			t.thrdparty_seq_num as sorter
	FROM thrdparty_case tc
		 JOIN thrdparty t ON tc.thrdparty_case_seq_num = t.thrdparty_case_seq_num
		 JOIN thrdparty_firm f ON f.thrdparty_firm_seq_num = t.carrier_firm_seq_num
		 JOIN company c ON f.company_seq_num = c.company_seq_num
	WHERE tc.thrdparty_case_seq_num = CaseSeqNum
	ORDER BY codedescr, sorter;
END claim_pymt_letter_recip_select;

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_SETTLEMENT"
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_settlement_select
(	CaseSeqNum IN thrdparty_settlement.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT
		create_date AS createdate,
		create_user AS createuser,
		settlement_amount AS settlementamount,
		settlement_date AS settlementdate,
		thrdparty_case_seq_num AS thrdpartycaseseqnum,
		thrdparty_settlement_code AS thrdpartysettlementcode,
		thrdparty_settlement_seq_num AS thrdpartysettlementseqnum,
		update_date AS updatedate,
		update_seq_num AS updateseqnum,
		update_user AS updateuser
	FROM thrdparty_settlement s
	WHERE thrdparty_case_seq_num = caseSeqNum
	ORDER BY settlement_date;-- DESC;
END thrdparty_settlement_select;

FUNCTION thrdparty_settlement_insert
(	ThrdpartyCaseSeqNum IN thrdparty_settlement.thrdparty_case_seq_num%TYPE,
	ThrdpartySettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE,
	ThrdpartySettlementCode   IN thrdparty_settlement.thrdparty_settlement_code%TYPE,
	SettlementAmount   IN thrdparty_settlement.settlement_amount%TYPE,
	SettlementDate   IN thrdparty_settlement.settlement_date%TYPE,
	CreateUser IN thrdparty_settlement.create_user%TYPE
) RETURN NUMBER AS
BEGIN
	INSERT INTO thrdparty_settlement
	(	create_date,
		create_user,
		settlement_amount,
		settlement_date,
		thrdparty_case_seq_num,
		thrdparty_settlement_code,
		thrdparty_settlement_seq_num,
		update_seq_num)
	VALUES (
		SYSDATE,
		createuser,
		settlementamount,
		settlementdate,
		thrdpartycaseseqnum,
		thrdpartysettlementcode,
		thrdpartysettlementseqnum,
		0);

	RETURN SQL%Rowcount;
END thrdparty_settlement_insert;

FUNCTION thrdparty_settlement_update
(	SettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE,
	SettlementCode   IN thrdparty_settlement.thrdparty_settlement_code%TYPE,
	SettlementAmount IN thrdparty_settlement.settlement_amount%TYPE,
	SettlementDate 	 IN thrdparty_settlement.settlement_date%TYPE,
	UpdateUser   	 IN thrdparty_settlement.update_user%TYPE,
	UpdateSeqNum 	 IN OUT thrdparty_settlement.update_seq_num%TYPE
) RETURN NUMBER AS
v_rowcount NUMBER;
BEGIN
	UPDATE thrdparty_settlement SET
		settlement_amount 				= SettlementAmount,
		settlement_date 				= SettlementDate,
		thrdparty_settlement_code 		= SettlementCode,
		update_date 					= SYSDATE,
		update_seq_num 					= UpdateSeqNum + 1,
		update_user 					= UpdateUser
	WHERE thrdparty_settlement_seq_num = SettlementSeqNum AND
		  update_seq_num = UpdateSeqNum;

	v_rowcount := SQL%Rowcount;
	UpdateSeqNum := UpdateSeqNum + v_rowcount;

	RETURN v_rowcount;
END thrdparty_settlement_update;

FUNCTION thrdparty_settlement_delete
(	SettlementSeqNum IN thrdparty_settlement.thrdparty_settlement_seq_num%TYPE
) RETURN NUMBER AS
	CURSOR detail_csr(p_SettlementSeqNum thrdparty_settlement.thrdparty_settlement_seq_num%TYPE) IS
		SELECT thrdparty_settle_dtl_seq_num
		FROM thrdparty_settlement_detail sd
		WHERE sd.thrdparty_settlement_seq_num = p_SettlementSeqNum
		FOR UPDATE OF thrdparty_settlement_seq_num NOWAIT;

	CURSOR distribution_csr(p_SettlementSeqNum thrdparty_distribution.thrdparty_settlement_seq_num%TYPE) IS
		SELECT thrdparty_distribution_seq_num
		FROM thrdparty_distribution d
		WHERE d.thrdparty_settlement_seq_num = p_SettlementSeqNum
		FOR UPDATE OF thrdparty_distribution_seq_num NOWAIT;

	v_rowcount NUMBER;

	v_settlementDtlSeqNum thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE;
	v_fundCatgCode thrdparty_settlement_detail.thrdparty_fund_category_code%TYPE;

	v_distributionSeqNum thrdparty_distribution.thrdparty_distribution_seq_num%TYPE;
BEGIN
	-- delete settlement_detail
	FOR detail_rec IN detail_csr(SettlementSeqNum) LOOP
		SELECT detail_rec.thrdparty_settle_dtl_seq_num
		INTO v_settlementDtlSeqNum
		FROM DUAL;
		--WHERE CURRENT OF detail_csr;

		v_rowcount := thrdparty_settl_dtl_delete(v_settlementDtlSeqNum);
	END LOOP;

	--delete settlement_distribution
	FOR distribution_rec IN distribution_csr(SettlementSeqNum) LOOP
		SELECT distribution_rec.thrdparty_distribution_seq_num
		INTO v_distributionSeqNum
		FROM DUAL;
		--WHERE CURRENT OF distribution_csr;

		v_rowcount := thrdparty_distribution_delete(v_distributionSeqNum);
	END LOOP;

	DELETE thrdparty_settlement WHERE thrdparty_settlement_seq_num = SettlementSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_settlement_delete;



/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_SETTLEMENT_DETAIL
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_settl_dtl_select
(	SettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT 'EXISTING' AS RowType,
		sd.thrdparty_settle_dtl_seq_num as ThrdpartySettleDtlSeqNum,
		sd.category_amount AS categoryamount,
		s.settlement_date AS SettlementDate,
		sd.create_date AS createdate,
		sd.create_user AS createuser,
		sd.thrdparty_fund_category_code AS thrdpartyfundcategorycode,
		sd.thrdparty_settlement_seq_num AS thrdpartysettlementseqnum,
		sd.update_date AS updatedate,
		sd.update_seq_num AS updateseqnum,
		sd.update_user AS updateuser,
		sc.sort_order AS sortorder
	FROM thrdparty_settlement_detail sd
		JOIN thrdparty_settlement s ON sd.thrdparty_settlement_seq_num = s.thrdparty_settlement_seq_num
		JOIN thrdparty_fund_category_code fc ON sd.thrdparty_fund_category_code = fc.code
		LEFT JOIN thrdparty_settlement_category sc ON sd.thrdparty_fund_category_code = sc.thrdparty_fund_category_code AND sc.thrdparty_settlement_code = s.thrdparty_settlement_code
	WHERE sd.thrdparty_settlement_seq_num = SettlementSeqNum
	ORDER BY sc.sort_order, fc.code_descr;
END thrdparty_settl_dtl_select;



PROCEDURE thrdparty_settl_dtl_generate
(	SettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	SettlementCode IN thrdparty_settlement_category.thrdparty_settlement_code%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	IF SettlementCode <> 'OTHER' THEN
		OPEN recordset FOR
		SELECT 'RESET' AS RowType,
			thrdparty_settle_dtl_seq.NEXTVAL AS ThrdpartySettleDtlSeqNum,
			SettlementSeqNum as thrdpartysettlementseqnum,
			fc.code AS thrdpartyfundCategoryCode,
			sort_order AS sortorder
		FROM thrdparty_settlement_category sc
			JOIN thrdparty_fund_category_code fc ON sc.thrdparty_fund_category_code = fc.code
		WHERE sc.thrdparty_settlement_code = SettlementCode;
		--ORDER BY fc.code_descr;	--the order by clause breaks nextval
	ELSE
		--Dummy select for returning 0 rows
		OPEN recordset FOR
		SELECT 'RESET' AS RowType,
			0 AS ThrdpartySettleDtlSeqNum,
			0 as thrdpartysettlementseqnum,
			'' AS thrdpartyfundCategoryCode,
			0 AS sortorder
		FROM thrdparty_settlement_category sc
		WHERE sc.thrdparty_settlement_code IS NULL AND
			  sc.thrdparty_settlement_code = SettlementCode;
	END IF;
END thrdparty_settl_dtl_generate;



PROCEDURE thrdparty_settl_dtl_cd_select
(	SettlementCode IN thrdparty_settlement_category.thrdparty_settlement_code%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT DISTINCT sc.thrdparty_fund_category_code Code,
		 sc.sort_order SortOrder,
		 fc.code_descr CodeDescr
	FROM thrdparty_settlement_category sc,
		 thrdparty_fund_category_code fc
	WHERE sc.thrdparty_fund_category_code = fc.code AND
		  sc.thrdparty_settlement_code = SettlementCode
	ORDER BY sort_order, CodeDescr;
END thrdparty_settl_dtl_cd_select;



FUNCTION thrdparty_settl_dtl_insert
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE,
	ThrdpartySettlementSeqNum IN thrdparty_settlement_detail.thrdparty_settlement_seq_num%TYPE,
	ThrdpartyFundCategoryCode   IN thrdparty_settlement_detail.thrdparty_fund_category_code%TYPE,
	CategoryAmount   IN thrdparty_settlement_detail.category_amount%TYPE,
	CreateUser IN thrdparty_settlement_detail.create_user%TYPE,
	CreateDate IN thrdparty_settlement_detail.create_date%TYPE
) RETURN NUMBER AS
BEGIN
	INSERT INTO thrdparty_settlement_detail
	(	category_amount,
		create_date,
		create_user,
		thrdparty_settle_dtl_seq_num,
		thrdparty_fund_category_code,
		thrdparty_settlement_seq_num,
		update_seq_num)
	VALUES (
		categoryamount,
		nvl(createDate,SYSDATE),
		createuser,
		thrdpartySettleDtlSeqNum,
		thrdpartyfundcategorycode,
		thrdpartysettlementseqnum,
		1);

	RETURN SQL%Rowcount;
END thrdparty_settl_dtl_insert;



FUNCTION thrdparty_settl_dtl_update
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE,
	CategoryAmount IN thrdparty_settlement_detail.category_amount%TYPE,
	UpdateSeqNum 	 IN OUT thrdparty_settlement_detail.update_seq_num%TYPE,
	UpdateUser   	 IN thrdparty_settlement_detail.update_user%TYPE
) RETURN NUMBER AS
v_rowcount NUMBER;
BEGIN
	UPDATE thrdparty_settlement_detail SET
		category_amount = categoryamount,
		update_date = SYSDATE,
		update_seq_num = updateseqnum + 1,
		update_user = updateuser
	WHERE thrdparty_settle_dtl_seq_num = thrdpartySettleDtlSeqNum AND
		update_seq_num = updateseqnum;

	v_rowcount := SQL%Rowcount;
	UpdateSeqNum := UpdateSeqNum + v_rowcount;

	RETURN v_rowcount;
END thrdparty_settl_dtl_update;



FUNCTION thrdparty_settl_dtl_delete
(	ThrdpartySettleDtlSeqNum IN thrdparty_settlement_detail.thrdparty_settle_dtl_seq_num%TYPE
) RETURN NUMBER AS
BEGIN
	DELETE thrdparty_settlement_detail WHERE thrdparty_settle_dtl_seq_num = thrdpartySettleDtlSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_settl_dtl_delete;



PROCEDURE settlement_note_seq_num_select
(	CaseSeqNum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
v_seqNum NUMBER(10);
BEGIN
	OPEN recordset FOR
	SELECT settlement_note_seq_num
	FROM thrdparty_case
	WHERE thrdparty_case_seq_num = CaseSeqNum;

--	RETURN v_seqNum;
END settlement_note_seq_num_select;



FUNCTION settlement_note_seq_num_update
(	CaseSeqNum IN thrdparty_case.thrdparty_case_seq_num%TYPE,
	NoteSeqNum IN thrdparty_case.settlement_note_seq_num%TYPE
) RETURN NUMBER AS
BEGIN
	UPDATE thrdparty_case set settlement_note_seq_num = NoteSeqNum WHERE thrdparty_case_seq_num = CaseSeqNum;

	RETURN SQL%Rowcount;
END settlement_note_seq_num_update;



/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_CLAIM_COST"
** DATE: 10-AUG-06 10:16
*********************************************/
FUNCTION claimcost_insert
(
 	payID IN THRDPARTY_CLAIM_COST.PAY_ID%TYPE,
    PrimaryKeySubNum IN THRDPARTY_CLAIM_COST.PAY_DTL_SEQ_NUM%TYPE,
    TransactionType IN THRDPARTY_CLAIM_COST.TRANSACTION_TYPE%TYPE,
    ClaimID IN THRDPARTY_CLAIM_COST.CLAIM_ID%TYPE,
    costCategory IN THRDPARTY_CLAIM_COST.CLAIM_COST_CATEGORY_CODE%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    unknownAmt IN THRDPARTY_CLAIM_COST.UNDETERMINED_AMOUNT%TYPE,
	CreateUser IN THRDPARTY_CLAIM_COST.CREATE_USER%TYPE
	)
RETURN NUMBER

As BEGIN
	Insert into	THRDPARTY_CLAIM_COST
	(
	    pay_ID,
		PAY_DTL_SEQ_NUM,
		TRANSACTION_TYPE,
		CLAIM_COST_CATEGORY_CODE,
		LIEN_AMOUNT,
		NO_LIEN_AMOUNT,
		UNDETERMINED_AMOUNT,
		CLAIM_ID,
		CREATE_DATE,
		CREATE_USER,
		UPDATE_SEQ_NUM
	 ) Values
	 (
	    payid,
		PrimaryKeySubNum,
		TransactionType,
		costCategory,
		lienAmt,
		noLienAmt,
		unknownAmt,
		ClaimID,
		sysdate,
		createUser,
		1
  );
  RETURN SQL%Rowcount;
  End claimcost_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_CLAIM_COST"
** DATE: 10-AUG06 10:16
*********************************************/
FUNCTION claimcost_update
(
	payID IN THRDPARTY_CLAIM_COST.PAY_ID%TYPE,
    PrimaryKeySubNum IN THRDPARTY_CLAIM_COST.PAY_DTL_SEQ_NUM%TYPE,
    ClaimID IN THRDPARTY_CLAIM_COST.CLAIM_ID%TYPE,
    costCategory IN THRDPARTY_CLAIM_COST.CLAIM_COST_CATEGORY_CODE%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    unknownAmt IN THRDPARTY_CLAIM_COST.UNDETERMINED_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_CLAIM_COST.UPDATE_USER%TYPE,
	updateNum in THRDPARTY_CLAIM_COST.UPDATE_SEQ_NUM%TYPE
)
RETURN NUMBER
	as
BEGIN
	Update
		THRDPARTY_CLAIM_COST
    Set
		CLAIM_COST_CATEGORY_CODE = costCategory,
		LIEN_AMOUNT = lienAmt,
		NO_LIEN_AMOUNT = noLienAmt,
		UNDETERMINED_AMOUNT = unknownAmt,
		UPDATE_USER = updateUser,
		UPDATE_DATE = sysdate,
		UPDATE_SEQ_NUM = updateNum + 1
	Where pay_id = payID
	  and Pay_dtl_seq_num = PrimaryKeySubNum
	  and UPDATE_SEQ_NUM = updateNum;

RETURN SQL%Rowcount;
End claimcost_update;

/********************************************
** INSERT PROCEDURE FOR TABLE "THRDPARTY_BILL_SERVICE
** DATE: 10-AUG-06 10:16
*********************************************/
FUNCTION billservice_insert
(
    PayId       IN THRDPARTY_BILL_SERVICE.PAY_ID%TYPE,
	billId      IN THRDPARTY_BILL_SERVICE.BILL_ID%TYPE,
    AuditSeqNum IN THRDPARTY_BILL_SERVICE.AUDIT_SEQ_NUM%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
    CreateUser IN THRDPARTY_CLAIM_COST.CREATE_USER%TYPE
	)
RETURN NUMBER

As BEGIN
	Insert into	THRDPARTY_BILL_SERVICE
	(
	    PAY_ID,
	    BILL_ID,
		AUDIT_SEQ_NUM,
		LIEN_AMOUNT,
		NO_LIEN_AMOUNT,
		CREATE_DATE,
		CREATE_USER,
		UPDATE_NUM
	 ) Values
	 (
	    payId,
	    billid,
		auditSeqNum,
		lienAmt,
		noLienAmt,
		sysdate,
		createUser,
		1
	  );
  RETURN SQL%Rowcount;

  End billservice_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "THRDPARTY_BILL_SERVICE"
** DATE: 10-AUG06 10:16
*********************************************/
FUNCTION billservice_update
(
    PayId       IN THRDPARTY_BILL_SERVICE.PAY_ID%TYPE,
	billID IN THRDPARTY_BILL_SERVICE.BILL_ID%TYPE,
    AuditSeqNum IN THRDPARTY_BILL_SERVICE.AUDIT_SEQ_NUM%TYPE,
    lienAmt IN THRDPARTY_CLAIM_COST.LIEN_AMOUNT%TYPE,
    noLienAmt IN THRDPARTY_CLAIM_COST.NO_LIEN_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_CLAIM_COST.UPDATE_USER%TYPE,
	updateNum in THRDPARTY_CLAIM_COST.UPDATE_SEQ_NUM%TYPE
)
RETURN NUMBER
	as
BEGIN
	Update
		THRDPARTY_BILL_SERVICE
    Set
		LIEN_AMOUNT = lienAmt,
		NO_LIEN_AMOUNT = noLienAmt,
		UPDATE_USER = updateUser,
		UPDATE_DATE = sysdate,
		UPDATE_NUM = updateNum + 1
	Where pay_id = payId
	  and bill_id = billID
	  and audit_seq_num = AuditSeqNum
	  and UPDATE_NUM = updateNum;

RETURN SQL%Rowcount;
End billservice_update;

/********************************************
** RETRIEVE PROCEDURE FOR CLAIM COST CATEGORY CODE
** DATE: 09-AUG-06
*********************************************/

PROCEDURE cost_category_select
(
	recordset OUT ref_cursor
)
AS BEGIN
open recordset for
     Select CODE as Code,
       CODE_DESCR_SHORT as CodeShortDescr,
	   SORT_ORDER
 From
       TP_CLAIM_COST_CATEGORY_CODE
	   order by 3;
END cost_category_select;


/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_SELECT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_select
(
	recordset OUT ref_cursor,
	CaseSeqNum in THRDPARTY_PREDICTION.THRDPARTY_CASE_SEQ_NUM%TYPE

)
as
BEGIN
	OPEN recordset FOR
		Select
			THRDPARTY_PREDICTION_SEQ_NUM as PredictionSeqNum,
			p.THRDPARTY_CASE_SEQ_NUM as CaseSeqNum,
			PREDICTION_STATUS_CODE as PredictionStatusCode,
			THRDPARTY_LIEN_SEQ_NUM as LienSeqNum,
			PREDICTION_AMOUNT as PredictionAmount,
			PREDICTION_DATE as PredictionDate,
			PREDICTION_DESCRIPTION as PredictionDescription,
			PREDICTION_PERCENT_FACTOR as PredictionPercentFactor,
			p.CREATE_DATE as CreateDate,
			p.CREATE_USER as CreateUser,
			p.UPDATE_DATE as UpdateDate,
			p.UPDATE_USER as UpdateUser,
			p.UPDATE_SEQ_NUM as UpdateSeqNum,
			p.PREDICTION_DUE_DATE as PredictionDueDate,
			p.FINAL_PREDICTION_FLAG as FinalPredictionFlag,
			c.claim_id as ClaimId
		  FROM THRDPARTY_PREDICTION p, THRDPARTY_CASE c
		 WHERE p.THRDPARTY_CASE_SEQ_NUM = c.THRDPARTY_CASE_SEQ_NUM
		   AND p.THRDPARTY_CASE_SEQ_NUM = CaseSeqNum
	  ORDER BY THRDPARTY_PREDICTION_SEQ_NUM DESC;
END prediction_select;

/********************************************
** INSERT FUNCTION FOR TABLE "PREDICTION_INSERT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_insert
(
    PredictionSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	CaseSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_CASE_SEQ_NUM%TYPE,
	PredictionStatusCode IN THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE,
	LienSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_LIEN_SEQ_NUM%TYPE,
	PredictionDate IN THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE,
	PredictionPercentFactor IN THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE,
	PredictionDescription IN THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE,
	CreateUser IN THRDPARTY_PREDICTION.CREATE_USER%TYPE,
	PredictionDueDate IN THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE,
	FinalPredictionFlag IN THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE
)
RETURN NUMBER
as
BEGIN

	Insert into
		THRDPARTY_PREDICTION
		 (
		THRDPARTY_PREDICTION_SEQ_NUM,
		THRDPARTY_CASE_SEQ_NUM,
		PREDICTION_STATUS_CODE,
		THRDPARTY_LIEN_SEQ_NUM,
		PREDICTION_DATE,
		PREDICTION_PERCENT_FACTOR,
		PREDICTION_AMOUNT,
		PREDICTION_DESCRIPTION,
		CREATE_DATE,
		CREATE_USER,
		UPDATE_DATE,
		UPDATE_USER,
		UPDATE_SEQ_NUM,
		PREDICTION_DUE_DATE,
		FINAL_PREDICTION_FLAG
	 ) Values
	 (
		PredictionSeqNum,
		CaseSeqNum,
		PredictionStatusCode,
		LienSeqNum,
		PredictionDate,
		PredictionPercentFactor,
		PredictionAmount,
		PredictionDescription,
		sysdate,
		createUser,
		sysdate,
		createUser,
		0,
		PredictionDueDate,
		FinalPredictionFlag
	  );

	RETURN SQL%Rowcount;
END prediction_insert;

/********************************************
** UPDATE FUNCTION FOR TABLE "PREDICTION_UPDATE"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_update
(
    PredictionSeqNum IN THRDPARTY_PREDICTION.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	PredictionStatusCode IN THRDPARTY_PREDICTION.PREDICTION_STATUS_CODE%TYPE,
	PredictionDate IN THRDPARTY_PREDICTION.PREDICTION_DATE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION.PREDICTION_AMOUNT%TYPE,
	PredictionPercentFactor IN THRDPARTY_PREDICTION.PREDICTION_PERCENT_FACTOR%TYPE,
	PredictionDescription IN THRDPARTY_PREDICTION.PREDICTION_DESCRIPTION%TYPE,
	UpdateUser IN THRDPARTY_PREDICTION.UPDATE_USER%TYPE,
	UpdateSeqNum IN THRDPARTY_PREDICTION.UPDATE_SEQ_NUM%TYPE,
	PredictionDueDate IN THRDPARTY_PREDICTION.PREDICTION_DUE_DATE%TYPE,
	FinalPredictionFlag IN THRDPARTY_PREDICTION.FINAL_PREDICTION_FLAG%TYPE
)
RETURN NUMBER
as
BEGIN
	Update
		THRDPARTY_PREDICTION
    Set
		PREDICTION_STATUS_CODE = PredictionStatusCode,
		PREDICTION_DATE = PredictionDate,
		PREDICTION_AMOUNT = PredictionAmount,
		PREDICTION_PERCENT_FACTOR = PredictionPercentFactor,
		PREDICTION_DESCRIPTION = PredictionDescription,
		UPDATE_DATE = sysdate,
		UPDATE_USER = updateUser,
		UPDATE_SEQ_NUM = updateSeqNum + 1,
		PREDICTION_DUE_DATE = PredictionDueDate,
		FINAL_PREDICTION_FLAG = FinalPredictionFlag
	 Where
	 	THRDPARTY_PREDICTION_SEQ_NUM = PredictionSeqNum
		and UPDATE_SEQ_NUM = UpdateSeqNum;

	RETURN SQL%Rowcount;
END prediction_update;

/********************************************
** FUNCTION TO RETRIEVE CURRENT LIEN SEQ NUM
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION current_lien_seq_num
(
	caseseqnum IN THRDPARTY_LIEN.THRDPARTY_CASE_SEQ_NUM%TYPE
)
RETURN NUMBER
AS
v_lienseqnum NUMBER := 0;
BEGIN
   SELECT nvl(max(THRDPARTY_LIEN_SEQ_NUM), 0)
     INTO v_lienseqnum
     FROM THRDPARTY_LIEN
    WHERE THRDPARTY_CASE_SEQ_NUM = caseseqnum;

	RETURN v_lienseqnum;

    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RETURN 0;
END current_lien_seq_num;

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_DETAIL_SELECT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_detail_select
(PredictionSeqNum in THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
  recordset OUT ref_cursor
)
AS
BEGIN
	OPEN recordset FOR
		Select
			pd.THRDPARTY_PREDICT_DTL_SEQ_NUM as PredictionDtlSeqNum,
			pd.THRDPARTY_PREDICTION_SEQ_NUM as PredictionSeqNum,
			pd.CLAIM_COST_CATEGORY_CODE as ClaimCostCategoryCode,
			cc.CODE_DESCR_SHORT ClaimCostCategory,
			pd.PREDICTION_AMOUNT as PredictionAmount,
			pd.CREATE_DATE as CreateDate,
			pd.CREATE_USER as CreateUser,
			pd.UPDATE_DATE as UpdateDate,
			pd.UPDATE_USER as UpdateUser,
			pd.UPDATE_SEQ_NUM as UpdateSeqNum,
            lc.LIEN_AMOUNT as LienAmount,
            lc.PROJECTED_AMOUNT as ProjectedAmount,
			cc.MEDICAL_INDEMNITY_DISPLAY_CODE as MedicalIndemnityDisplayCode,
			cc.SORT_ORDER SortOrder
		  FROM THRDPARTY_PREDICTION p
		   	   , THRDPARTY_PREDICTION_DETAIL pd
		  	   , THRDPARTY_LIEN_CATEGORY lc
			   , TP_CLAIM_COST_CATEGORY_CODE cc
		 WHERE pd.THRDPARTY_PREDICTION_SEQ_NUM = p.THRDPARTY_PREDICTION_SEQ_NUM
		   AND p.THRDPARTY_LIEN_SEQ_NUM = lc.THRDPARTY_LIEN_SEQ_NUM
		   AND pd.CLAIM_COST_CATEGORY_CODE = lc.CLAIM_COST_CATEGORY_CODE
		   AND lc.CLAIM_COST_CATEGORY_CODE = cc.CODE
		   AND p.THRDPARTY_PREDICTION_SEQ_NUM = PredictionSeqNum;
END prediction_detail_select;

/********************************************
** RETRIEVE PROCEDURE FOR TABLE "PREDICTION_DETAIL_GENERATE"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
PROCEDURE prediction_new_dtl_categories
(LienSeqNum in THRDPARTY_LIEN.THRDPARTY_LIEN_SEQ_NUM%TYPE,
  recordset OUT ref_cursor
)
AS
BEGIN
	OPEN recordset FOR
		SELECT
			thrdparty_predict_dtl_seq.nextval as PredictionDtlSeqNum,
			lc.CLAIM_COST_CATEGORY_CODE as ClaimCostCategoryCode,
			cc.CODE_DESCR_SHORT as ClaimCostCategory,
            lc.LIEN_AMOUNT as LienAmount,
            lc.PROJECTED_AMOUNT as ProjectedAmount,
			cc.MEDICAL_INDEMNITY_DISPLAY_CODE as MedicalIndemnityDisplayCode,
			cc.SORT_ORDER SortOrder
		  FROM THRDPARTY_LIEN l
		     , THRDPARTY_LIEN_CATEGORY lc
			 , TP_CLAIM_COST_CATEGORY_CODE cc
		 WHERE l.THRDPARTY_LIEN_SEQ_NUM = lc.THRDPARTY_LIEN_SEQ_NUM
		   AND lc.CLAIM_COST_CATEGORY_CODE = cc.CODE
		   AND l.THRDPARTY_LIEN_SEQ_NUM = LienSeqNum;
END prediction_new_dtl_categories;

/********************************************
** INSERT FUNCTION FOR TABLE "PREDICTION_DETAIL_INSERT"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_detail_insert
(
    PredictionDtlSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE,
    PredictionSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICTION_SEQ_NUM%TYPE,
	ClaimCostCategoryCode IN THRDPARTY_PREDICTION_DETAIL.CLAIM_COST_CATEGORY_CODE%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE,
	CreateUser IN THRDPARTY_PREDICTION_DETAIL.CREATE_USER%TYPE
)
RETURN NUMBER
as
BEGIN
	Insert into
		THRDPARTY_PREDICTION_DETAIL
		 (
		THRDPARTY_PREDICT_DTL_SEQ_NUM,
		THRDPARTY_PREDICTION_SEQ_NUM,
		CLAIM_COST_CATEGORY_CODE,
		PREDICTION_AMOUNT,
		CREATE_DATE,
		CREATE_USER,
		UPDATE_DATE,
		UPDATE_USER,
		UPDATE_SEQ_NUM
	 ) Values
	 (
		PredictionDtlSeqNum,
		PredictionSeqNum,
		ClaimCostCategoryCode,
		PredictionAmount,
		sysdate,
		createUser,
		sysdate,
		createUser,
		0
	  );

	RETURN SQL%Rowcount;
END prediction_detail_insert;

/********************************************
** UPDATE FUNCTION FOR TABLE "PREDICTION_DETAIL_UPDATE"
** DATE: 25-AUGUST-06 10:00:00
*********************************************/
FUNCTION prediction_detail_update
(
    PredictionDtlSeqNum IN THRDPARTY_PREDICTION_DETAIL.THRDPARTY_PREDICT_DTL_SEQ_NUM%TYPE,
	PredictionAmount IN THRDPARTY_PREDICTION_DETAIL.PREDICTION_AMOUNT%TYPE,
	UpdateUser IN THRDPARTY_PREDICTION_DETAIL.UPDATE_USER%TYPE,
	UpdateSeqNum IN THRDPARTY_PREDICTION_DETAIL.UPDATE_SEQ_NUM%TYPE
)
RETURN NUMBER
as
BEGIN
	Update
		THRDPARTY_PREDICTION_DETAIL
    Set
		PREDICTION_AMOUNT = PredictionAmount,
		UPDATE_DATE = sysdate,
		UPDATE_USER = updateUser,
		UPDATE_SEQ_NUM = updateSeqNum + 1
	 Where
	 	THRDPARTY_PREDICT_DTL_SEQ_NUM = PredictionDtlSeqNum
		and UPDATE_SEQ_NUM = UpdateSeqNum;

	RETURN SQL%Rowcount;
END prediction_detail_update;


/********************************************
** FUNCTION TO RETRIEVE POLICY WAIVER COUNT
** DATE: 26-JULY-06 07:27:00
*********************************************/
FUNCTION get_policy_waiver_count
(
	PolicyNum IN POLICY.POLICY_NUM%TYPE
)
RETURN NUMBER
AS
v_count NUMBER := 0;
BEGIN
	 SELECT COUNT(*)
	   INTO v_count
 	   FROM WAIVER_DETAIL
   	  WHERE POLICY_NUM = PolicyNum;

	RETURN v_count;
END get_policy_waiver_count;


/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_DISTRIBUTION
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_distribution_select
(	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT
		d.ara_amount AS araamount,
		d.check_amount AS checkamount,
		d.check_date AS checkdate,
		d.check_num AS checknum,
		d.claim_cost_amount AS claimcostamount,
		d.corrected_date AS correcteddate,
		d.corrected_distribution_seq_num AS correctedNum,
		d.create_date AS createdate,
		d.create_user AS createuser,
		d.employer_credit_amount AS employercreditamount,
		d.other_amount1 AS otheramount1,
		d.other_amount2 AS otheramount2,
		d.other_descr1 AS otherdescr1,
		d.other_descr2 AS otherdescr2,
		d.payee_name AS payeename,
		d.refund_amount AS refundamount,
		d.thrdparty_case_seq_num AS caseseqnum,
		d.thrdparty_distribution_code AS distributioncode,
		d.thrdparty_distribution_seq_num AS distributionseqnum,
		d.thrdparty_settlement_seq_num AS settlementseqnum,
		d.update_date AS updatedate,
		d.update_seq_num AS updateseqnum,
		d.update_user AS updateuser,
		r1.create_date AS r1createdate,
		r1.create_user AS r1createuser,
		r1.policy_num AS r1policynum,
		r1.refund_amount AS r1refundamount,
		r1.refund_name AS r1refundname,
		r1.thrdparty_distribution_seq_num AS r1distributionseqnum,
		r1.thrdparty_distr_refund_seq_num AS r1distrrefundseqnum,
		r1.thrdparty_firm_seq_num AS r1firmseqnum,
		r1.update_date AS r1updatedate,
		r1.update_seq_num AS r1updateseqnum,
		r1.update_user AS r1updateuser,
		r1.worker_id AS r1workerid,
		r2.create_date AS r2createdate,
		r2.create_user AS r2createuser,
		r2.policy_num AS r2policynum,
		r2.refund_amount AS r2refundamount,
		r2.refund_name AS r2refundname,
		r2.thrdparty_distribution_seq_num AS r2distributionseqnum,
		r2.thrdparty_distr_refund_seq_num AS r2distrrefundseqnum,
		r2.thrdparty_firm_seq_num AS r2firmseqnum,
		r2.update_date AS r2updatedate,
		r2.update_seq_num AS r2updateseqnum,
		r2.update_user AS r2updateuser,
		r2.worker_id AS r2workerid
	FROM thrdparty_distribution d
		 LEFT JOIN thrdpart_distribution_view rv ON d.thrdparty_distribution_seq_num = rv.thrdparty_distribution_seq_num
		 LEFT JOIN thrdparty_distribution_refund r1 ON r1.thrdparty_distr_refund_seq_num = rv.THRDPARTY_DISTR_REFND_SEQ_NUM1
		 LEFT JOIN thrdparty_distribution_refund r2 ON r2.thrdparty_distr_refund_seq_num = rv.THRDPARTY_DISTR_REFND_SEQ_NUM2
	WHERE d.thrdparty_case_seq_num = CaseSeqNum;
END thrdparty_distribution_select;

PROCEDURE thrdparty_dist_refundee_select
(	recordset OUT ref_cursor,
	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE

) AS
BEGIN
	OPEN recordset FOR

	SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
			c.company_name AS codedescr,
			'Carrier' as codetype,
			t.thrdparty_seq_num as sorter
	FROM thrdparty_case tc
		 JOIN thrdparty t ON tc.thrdparty_case_seq_num = t.thrdparty_case_seq_num
		 JOIN thrdparty_firm f ON f.thrdparty_firm_seq_num = t.carrier_firm_seq_num
		 JOIN company c ON f.company_seq_num = c.company_seq_num
	WHERE tc.thrdparty_case_seq_num = CaseSeqNum
    UNION
     SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
            c.company_name AS codedescr,
            'Plaintiff' as codetype,
            99999998 as sorter
    FROM thrdparty_case tc
          JOIN thrdparty_firm f ON f.thrdparty_firm_seq_num = TC.PLAINTIFF_FIRM_SEQ_NUM
         JOIN company c ON f.company_seq_num = c.company_seq_num
    WHERE tc.thrdparty_case_seq_num = CaseSeqNum
        UNION
    SELECT  TO_CHAR(f.thrdparty_firm_seq_num) AS code,
            c.company_name AS codedescr,
            'Cocounsel' as codetype,
            99999999 as sorter
    FROM thrdparty_case tc
          JOIN thrdparty_firm f ON f.thrdparty_firm_seq_num = TC.COCOUNSEL_FIRM_SEQ_NUM
         JOIN company c ON f.company_seq_num = c.company_seq_num
    WHERE tc.thrdparty_case_seq_num = CaseSeqNum
	ORDER BY codedescr, sorter;
END thrdparty_dist_refundee_select;

PROCEDURE thrdparty_max_lien_select
(	CaseSeqNum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	nonlien_amount IN OUT number,
	lien_amount IN OUT number
) AS
v_nonlien_amount number := 0;
v_lien_amount number := 0;
BEGIN
  BEGIN
	SELECT sum(c.NO_LIEN_AMOUNT), sum(c.LIEN_AMOUNT)
	into v_nonlien_amount, v_lien_amount
	FROM   thrdparty_lien_category c
	join thrdparty_lien l on l.THRDPARTY_LIEN_SEQ_NUM = c.THRDPARTY_LIEN_SEQ_NUM
		   JOIN (SELECT max(l2.THRDPARTY_LIEN_SEQ_NUM) lien_seq
		 	  	 FROM thrdparty_lien l2
				 WHERE l2.thrdparty_case_seq_num = CaseSeqNum) maxLien ON l.THRDPARTY_LIEN_SEQ_NUM = maxLien.lien_seq
	WHERE  l.thrdparty_case_seq_num = CaseSeqNum
	group by c.THRDPARTY_LIEN_SEQ_NUM;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
	v_nonlien_amount := 0;
	v_lien_amount := 0;
  END;
nonlien_amount := v_nonlien_amount;
lien_amount := v_lien_amount;
END thrdparty_max_lien_select;

FUNCTION thrdparty_distribution_update
(	AraAmount 			 IN thrdparty_distribution.Ara_Amount%TYPE,
	CheckAmount 		 IN thrdparty_distribution.Check_Amount%TYPE,
	CheckDate 			 IN thrdparty_distribution.Check_Date%TYPE,
	CheckNum 			 IN thrdparty_distribution.Check_Num%TYPE,
	ClaimCostAmount 	 IN thrdparty_distribution.Claim_Cost_Amount%TYPE,
	CorrectedDate 	 	 IN thrdparty_distribution.Corrected_Date%TYPE,
	correctedNum         IN thrdparty_distribution.corrected_distribution_seq_num%TYPE,
	EmployerCreditAmount IN thrdparty_distribution.Employer_Credit_Amount%TYPE,
	OtherAmount1 		 IN thrdparty_distribution.Other_Amount1%TYPE,
	OtherAmount2 		 IN thrdparty_distribution.Other_Amount2%TYPE,
	OtherDescr1 		 IN thrdparty_distribution.Other_Descr1%TYPE,
	OtherDescr2 		 IN thrdparty_distribution.Other_Descr2%TYPE,
	PayeeName 			 IN thrdparty_distribution.Payee_Name%TYPE,
	RefundAmount 		 IN thrdparty_distribution.Refund_Amount%TYPE,
	DistributionCode 	 IN thrdparty_distribution.Thrdparty_Distribution_Code%TYPE,
 	DistributionSeqNum 	 IN thrdparty_distribution.Thrdparty_Distribution_Seq_Num%TYPE,
	UpdateSeqNum 		 IN OUT thrdparty_distribution.Update_Seq_Num%TYPE,
	UpdateUser 			 IN thrdparty_distribution.Update_User%TYPE,

	PolicyNum1 			 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount1 		 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName1			 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum1 	 IN OUT thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum1			 IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum1 		 IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	WorkerID1 			 IN thrdparty_distribution_refund.Worker_ID%TYPE,

	PolicyNum2 			 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount2 		 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName2			 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum2 	 IN OUT thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum2			 IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum2 		 IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	WorkerID2 			 IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER AS

v_rowcount NUMBER;
v_rowcountRfnd1 NUMBER;
v_rowcountRfnd2 NUMBER;

BEGIN
	UPDATE thrdparty_distribution
	SET ara_amount = araamount,
		check_amount = checkamount,
		check_date = checkdate,
		check_num = checknum,
		claim_cost_amount = claimcostamount,
		corrected_date = correcteddate,
		corrected_distribution_seq_num = correctedNum,
		employer_credit_amount = employercreditamount,
		other_amount1 = otheramount1,
		other_amount2 = otheramount2,
		other_descr1 = otherdescr1,
		other_descr2 = otherdescr2,
		payee_name = payeename,
		refund_amount = refundamount,
		thrdparty_distribution_code = distributioncode,
		update_date = SYSDATE,
		update_user = updateuser,
		update_seq_num = UpdateSeqNum + 1
	WHERE thrdparty_distribution_seq_num = DistributionSeqNum
		AND update_seq_num = updateseqnum;

	v_rowcount := SQL%Rowcount;
	UpdateSeqNum := UpdateSeqNum + v_rowcount;

	IF DistrRefundSeqNum1 IS NULL AND RefundAmount1 IS NOT NULL AND RefundAmount1 <> 0 THEN --AND NOT (PolicyNum1 IS NULL OR RefundName1 = '' OR FirmSeqNum1 IS NULL OR WorkerId1 IS NULL) THEN
		SELECT thrdparty_distr_refund_seq.NEXTVAL
		INTO DistrRefundSeqNum1
		FROM dual;

		v_rowcountRfnd1 := thrdparty_dist_refund_insert(SYSDATE, UpdateUser, DistributionSeqNum, PolicyNum1, RefundAmount1, RefundName1, DistrRefundSeqNum1, FirmSeqNum1, WorkerID1);
	ELSE
		v_rowcountRfnd1 := thrdparty_dist_refund_update(PolicyNum1, RefundAmount1, RefundName1, DistrRefundSeqNum1, FirmSeqNum1, UpdateSeqNum1, UpdateUser, WorkerID1);
	END IF;

	IF DistrRefundSeqNum2 IS NULL AND RefundAmount2 IS NOT NULL AND RefundAmount2 <> 0 THEN --AND NOT (PolicyNum2 IS NULL OR RefundName2 = '' OR FirmSeqNum2 IS NULL OR WorkerId2 IS NULL) THEN
		SELECT THRDPARTY_DISTR_REFUND_SEQ.NEXTVAL
		INTO DistrRefundSeqNum2
		FROM DUAL;

		v_rowcountRfnd2 := thrdparty_dist_refund_insert(SYSDATE, UpdateUser, DistributionSeqNum, PolicyNum2, RefundAmount2, RefundName2, DistrRefundSeqNum2, FirmSeqNum2, WorkerID2);
	ELSE
	 	v_rowcountRfnd2 := thrdparty_dist_refund_update(PolicyNum2, RefundAmount2, RefundName2, DistrRefundSeqNum2, FirmSeqNum2, UpdateSeqNum2, UpdateUser, WorkerID2);
	END IF;

	return v_rowcount + v_rowcountRfnd1 + v_rowcountRfnd2; --+ v_rowcountRfnd1 + v_rowcountRfnd2;
END thrdparty_distribution_update;

FUNCTION thrdparty_distribution_insert
(	AraAmount 			 IN thrdparty_distribution.Ara_Amount%TYPE,
	CheckAmount 		 IN thrdparty_distribution.Check_Amount%TYPE,
	CheckDate 			 IN thrdparty_distribution.Check_Date%TYPE,
	CheckNum 			 IN thrdparty_distribution.Check_Num%TYPE,
	ClaimCostAmount 	 IN thrdparty_distribution.Claim_Cost_Amount%TYPE,
	CreateDate 			 IN thrdparty_distribution.create_date%TYPE,
	CreateUser 			 IN thrdparty_distribution.Create_User%TYPE,
	EmployerCreditAmount IN thrdparty_distribution.Employer_Credit_Amount%TYPE,
	OtherAmount1 		 IN thrdparty_distribution.Other_Amount1%TYPE,
	OtherAmount2 		 IN thrdparty_distribution.Other_Amount2%TYPE,
	OtherDescr1 		 IN thrdparty_distribution.Other_Descr1%TYPE,
	OtherDescr2 		 IN thrdparty_distribution.Other_Descr2%TYPE,
	PayeeName 			 IN thrdparty_distribution.Payee_Name%TYPE,
	RefundAmount 		 IN thrdparty_distribution.Refund_Amount%TYPE,
	CaseSeqNum 			 IN thrdparty_distribution.thrdparty_case_seq_num%TYPE,
	DistributionCode 	 IN thrdparty_distribution.thrdparty_Distribution_Code%TYPE,
	DistributionSeqNum 	 IN thrdparty_distribution.thrdparty_Distribution_Seq_Num%TYPE,
	SettlementSeqNum 	 IN thrdparty_distribution.thrdparty_Settlement_Seq_Num%TYPE,
	correctedNum         IN thrdparty_distribution.corrected_distribution_seq_num%TYPE,

	PolicyNum1 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount1 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName1 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum1 IN OUT thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum1 IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID1 IN thrdparty_distribution_refund.Worker_ID%TYPE,

	PolicyNum2 IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount2 IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName2 IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum2 IN OUT thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum2 IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID2 IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER AS

v_rowcount NUMBER;
v_rowcountInsert NUMBER;

BEGIN
	INSERT INTO thrdparty_distribution
	(	ara_amount,
		check_amount,
		check_date,
		check_num,
		claim_cost_amount,
		create_date,
		create_user,
		employer_credit_amount,
		other_amount1,
		other_amount2,
		other_descr1,
		other_descr2,
		payee_name,
		refund_amount,
		thrdparty_case_seq_num,
		thrdparty_distribution_code,
		thrdparty_distribution_seq_num,
		thrdparty_settlement_seq_num,
		update_seq_num,
		corrected_distribution_seq_num
	) VALUES (
		araamount,
		checkamount,
		checkdate,
		checknum,
		claimcostamount,
		CreateDate,
		createuser,
		employercreditamount,
		otheramount1,
		otheramount2,
		otherdescr1,
		otherdescr2,
		payeename,
		refundamount,
		caseseqnum,
		distributioncode,
		distributionseqnum,
		settlementseqnum,
		0,
		correctedNum);

	v_rowcount := SQL%Rowcount;

	IF RefundAmount1 <> 0 THEN
		SELECT thrdparty_distr_refund_seq.NEXTVAL
		INTO DistrRefundSeqNum1
		FROM dual;

		v_rowcountInsert := thrdparty_dist_refund_insert(CreateDate, CreateUser, DistributionSeqNum, PolicyNum1, RefundAmount1, RefundName1, DistrRefundSeqNum1, FirmSeqNum1, WorkerID1);
	END IF;

	IF RefundAmount2 <> 0 THEN
		SELECT thrdparty_distr_refund_seq.NEXTVAL
		INTO DistrRefundSeqNum2
		FROM dual;

		v_rowcountInsert := thrdparty_dist_refund_insert(CreateDate, CreateUser, DistributionSeqNum, PolicyNum2, RefundAmount2, RefundName2, DistrRefundSeqNum2, FirmSeqNum2, WorkerID2);
	END IF;

	RETURN v_rowcount;
END thrdparty_distribution_insert;

FUNCTION thrdparty_distribution_delete
(	DistributionSeqNum IN thrdparty_distribution.thrdparty_distribution_seq_num%TYPE
) RETURN NUMBER AS
	CURSOR refund_csr(p_DistributionSeqNum thrdparty_distribution_refund.thrdparty_distribution_seq_num%TYPE) IS
		SELECT thrdparty_distr_refund_seq_num
		FROM thrdparty_distribution_refund dr
		WHERE dr.thrdparty_distribution_seq_num = p_DistributionSeqNum
		FOR UPDATE OF thrdparty_distr_refund_seq_num NOWAIT;

	v_refundSeqNum thrdparty_distribution.thrdparty_distribution_seq_num%TYPE;
	v_rowcount NUMBER;
BEGIN
	FOR refund_rec IN refund_csr(DistributionSeqNum) LOOP
		SELECT refund_rec.thrdparty_distr_refund_seq_num
		INTO v_refundSeqNum
		FROM DUAL;
		--WHERE CURRENT OF refund_csr;

		v_rowcount := thrdparty_dist_refund_delete(v_refundSeqNum);
	END LOOP;

	DELETE thrdparty_distribution WHERE thrdparty_distribution_seq_num = DistributionSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_distribution_delete;

PROCEDURE thrdparty_distr_descr_select
(	inCode IN thrdparty_distribution_code.code%TYPE,
	codeDescr IN OUT varchar2
) AS
v_descr VARCHAR2(65);
BEGIN
	BEGIN
		SELECT code_descr
		INTO v_descr
		FROM thrdparty_distribution_code
		WHERE code = inCode;
	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		v_descr := '';
	END;

	codeDescr := v_descr;
END thrdparty_distr_descr_select;

/********************************************
** PROCEDURES FOR TABLE "THRDPARTY_DISTRIBUTION_REFUND
** DATE: 26-JUL-06
*********************************************/

PROCEDURE thrdparty_dist_refund_select
(	DistributionSeqNum IN thrdparty_distribution_refund.thrdparty_distribution_seq_num%TYPE,
	recordset OUT ref_cursor
) AS
BEGIN
	OPEN recordset FOR
	SELECT thrdparty_distr_refund_seq_num,
		thrdparty_distribution_seq_num,
		policy_num,
		refund_name,
		worker_id,
		thrdparty_firm_seq_num,
		refund_amount,
		create_date,
		create_user,
		update_date,
		update_user,
		update_seq_num
	FROM thrdparty_distribution_refund
	WHERE thrdparty_distribution_seq_num = DistributionSeqNum;
END thrdparty_dist_refund_select;

FUNCTION thrdparty_dist_refund_update
(	PolicyNum IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum IN thrdparty_distribution_refund.Thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum IN thrdparty_distribution_refund.Thrdparty_Firm_Seq_Num%TYPE,
	UpdateSeqNum IN OUT thrdparty_distribution_refund.Update_Seq_Num%TYPE,
	UpdateUser IN thrdparty_distribution_refund.Update_User%TYPE,
	WorkerID IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER AS
v_rowcount NUMBER;
BEGIN
	UPDATE thrdparty_distribution_refund
	SET policy_num                     = policyNum,
		refund_name                    = refundName,
		worker_id                      = workerId,
		thrdparty_firm_seq_num         = FirmSeqNum,
		refund_amount                  = refundAmount,
		update_date                    = SYSDATE,
		update_user                    = updateUser,
		update_seq_num                 = update_seq_num + 1
	WHERE thrdparty_distr_refund_seq_num = DistrRefundSeqNum
		  AND update_seq_num = updateSeqNum;

	v_rowcount := SQL%Rowcount;
	UpdateSeqNum := UpdateSeqNum + v_rowcount;

	RETURN v_rowcount;
END thrdparty_dist_refund_update;

FUNCTION thrdparty_dist_refund_insert
(	CreateDate 			 IN thrdparty_distribution.create_date%TYPE,
	CreateUser IN thrdparty_distribution_refund.Create_User%TYPE,
	DistributionSeqNum IN thrdparty_distribution.thrdparty_Distribution_Seq_Num%TYPE,
	PolicyNum IN thrdparty_distribution_refund.Policy_Num%TYPE,
	RefundAmount IN thrdparty_distribution_refund.Refund_Amount%TYPE,
	RefundName IN thrdparty_distribution_refund.Refund_Name%TYPE,
	DistrRefundSeqNum IN thrdparty_distribution_refund.thrdparty_Distr_Refund_Seq_Num%TYPE,
	FirmSeqNum IN thrdparty_distribution_refund.thrdparty_Firm_Seq_Num%TYPE,
	WorkerID IN thrdparty_distribution_refund.Worker_ID%TYPE
) RETURN NUMBER AS
BEGIN
	INSERT INTO
	thrdparty_distribution_refund (
		create_date,
		create_user,
		policy_num,
		refund_amount,
		refund_name,
		thrdparty_distribution_seq_num,
		thrdparty_distr_refund_seq_num,
		thrdparty_firm_seq_num,
		update_seq_num,
		worker_id
	) VALUES (
		CreateDate,
		CreateUser,
		PolicyNum,
		RefundAmount,
		RefundName,
		DistributionSeqNum,
		DistrRefundSeqNum,
		FirmSeqNum,
		0,
		WorkerID);

	RETURN SQL%Rowcount;
END thrdparty_dist_refund_insert;

FUNCTION thrdparty_dist_refund_delete
(	DistributionRefundSeqNum IN thrdparty_distribution_refund.thrdparty_distr_refund_seq_num%TYPE
) RETURN NUMBER AS
BEGIN
	DELETE thrdparty_distribution_refund WHERE thrdparty_distr_refund_seq_num = DistributionRefundSeqNum;

	RETURN SQL%Rowcount;
END thrdparty_dist_refund_delete;

/********************************************
** Establish Lien Procedures
*********************************************/
   PROCEDURE thrdparty_lien_create(
      thrdpartycaseseqnum   IN       thrdparty_lien.thrdparty_case_seq_num%TYPE,
      recordset             OUT      ref_cursor)
   IS
    BEGIN
	  OPEN recordset FOR
     	  SELECT getNextLienSeq() as ThrdpartyLienSeq,
     	    	 NVL(SUM(e.EXPENSE_AMOUNT),0) AS ExpenseTotal,
				 getNDReimbAmount(thrdpartycaseseqnum) AS NdReimbAmount
             FROM THRDPARTY_CASE c, THRDPARTY_EXPENSE e
            WHERE c.THRDPARTY_CASE_SEQ_NUM = e.THRDPARTY_CASE_SEQ_NUM
			  AND c.EXPENSE_APPROVAL_CODE in ('NOTR', 'APPR')
		      AND c.THRDPARTY_CASE_SEQ_NUM = thrdpartycaseseqnum;
   END thrdparty_lien_create;

FUNCTION getNextLienSeq
   RETURN NUMBER
   AS
   v_LienSeq NUMBER := 0;
   BEGIN
      BEGIN
       SELECT THRDPARTY_LIEN_SEQ.NEXTVAL INTO v_LienSeq
		 FROM dual;
	  EXCEPTION
	     WHEN OTHERS THEN
		   NULL;
	  END;
	  RETURN v_LienSeq;
  END getNextLienSeq ;

FUNCTION getNDReimbAmount(thrdpartycaseseqnum IN thrdparty_lien.thrdparty_case_seq_num%TYPE)
RETURN NUMBER
AS
v_amount NUMBER := 0;
BEGIN
  BEGIN
     SELECT NVL(THRDPARTY_CASE.ND_REIMBURSEMENT_AMOUNT,0)
       INTO v_amount
       FROM THRDPARTY_CASE
      WHERE THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM = thrdpartycaseseqnum;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
	   NULL;
  END;
  RETURN v_amount;
END getNDReimbAmount;

   PROCEDURE thrdparty_lien_select (
      thrdpartycaseseqnum   IN       thrdparty_lien.thrdparty_case_seq_num%TYPE,
      recordset             OUT      ref_cursor
   )
   AS
   BEGIN
      OPEN recordset FOR
         SELECT thrdparty_lien.create_date AS createdate,
                thrdparty_lien.create_user AS createuser,
                thrdparty_lien.expense_amount AS expenseamount,
                thrdparty_lien.final_lien_flag AS finallienflag,
                thrdparty_lien.lien_date AS liendate,
                thrdparty_lien.thrdparty_case_seq_num AS thrdpartycaseseqnum,
                thrdparty_lien.thrdparty_lien_seq_num AS thrdpartylienseqnum,
                thrdparty_lien.update_date AS updatedate,
                thrdparty_lien.update_seq_num AS updateseqnum,
                thrdparty_lien.update_user AS updateuser,
				NVL(thrdparty_case.ND_REIMBURSEMENT_AMOUNT,0) as NdReimbursementAmount
           FROM thrdparty_lien, thrdparty_case
          WHERE thrdparty_lien.thrdparty_case_seq_num = thrdpartycaseseqnum
		    AND thrdparty_case.THRDPARTY_CASE_SEQ_NUM = thrdpartycaseseqnum
		  order by lien_date, thrdparty_lien_seq_num;
   END thrdparty_lien_select;

   PROCEDURE thrdparty_lien_select_by_seq (
      thrdpartylienseqnum   IN       thrdparty_lien.thrdparty_lien_seq_num%TYPE,
      recordset             OUT      ref_cursor
   )
   AS
   BEGIN
      OPEN recordset FOR
         SELECT thrdparty_lien.create_date AS createdate,
                thrdparty_lien.create_user AS createuser,
                thrdparty_lien.expense_amount AS expenseamount,
                thrdparty_lien.final_lien_flag AS finallienflag,
                thrdparty_lien.lien_date AS liendate,
                thrdparty_lien.thrdparty_case_seq_num AS thrdpartycaseseqnum,
                thrdparty_lien.thrdparty_lien_seq_num AS thrdpartylienseqnum,
                thrdparty_lien.update_date AS updatedate,
                thrdparty_lien.update_seq_num AS updateseqnum,
                thrdparty_lien.update_user AS updateuser,
				NVL(thrdparty_case.ND_REIMBURSEMENT_AMOUNT,0) as NdReimbursementAmount
           FROM thrdparty_lien, thrdparty_case
          WHERE thrdparty_lien.thrdparty_lien_seq_num = thrdpartylienseqnum
		    and thrdparty_case.THRDPARTY_CASE_SEQ_NUM = thrdparty_lien.THRDPARTY_CASE_SEQ_NUM;
   END thrdparty_lien_select_by_seq;

   FUNCTION thrdparty_lien_update (
      expenseamount         IN   thrdparty_lien.expense_amount%TYPE,
      finallienflag         IN   thrdparty_lien.final_lien_flag%TYPE,
      liendate              IN   thrdparty_lien.lien_date%TYPE,
      updateuser            IN   thrdparty_lien.update_user%TYPE,
      thrdpartylienseqnum   IN   thrdparty_lien.thrdparty_lien_seq_num%TYPE,
      updatenum             IN   thrdparty_lien.update_seq_num%TYPE
   ) RETURN NUMBER
   AS
   BEGIN
      UPDATE thrdparty_lien
         SET expense_amount = expenseamount,
             final_lien_flag = finallienflag,
             lien_date = liendate,
             update_date = SYSDATE,
             update_seq_num = updatenum + 1,
             update_user = updateuser
       WHERE thrdparty_lien.thrdparty_lien_seq_num = thrdpartylienseqnum
         AND update_seq_num = updatenum;
	   RETURN SQL%ROWCOUNT;
   END thrdparty_lien_update;

   FUNCTION thrdparty_lien_insert (
      createuser            IN   thrdparty_lien.create_user%TYPE,
      expenseamount         IN   thrdparty_lien.expense_amount%TYPE,
      finallienflag         IN   thrdparty_lien.final_lien_flag%TYPE,
      liendate              IN   thrdparty_lien.lien_date%TYPE,
      thrdpartycaseseqnum   IN   thrdparty_lien.thrdparty_case_seq_num%TYPE,
      thrdpartylienseqnum   IN   thrdparty_lien.thrdparty_lien_seq_num%TYPE
   ) RETURN NUMBER
   AS
   BEGIN
      INSERT INTO thrdparty_lien
                  (thrdparty_lien_seq_num, thrdparty_case_seq_num,
                   lien_date, expense_amount, final_lien_flag, create_date,
                   create_user, update_date, update_user, update_seq_num
                  )
           VALUES (thrdpartylienseqnum, thrdpartycaseseqnum,
                   liendate, expenseamount, finallienflag, SYSDATE,
                   createuser, NULL, NULL, 0
                  );
		RETURN SQL%ROWCOUNT;
   END thrdparty_lien_insert;

/********************************************
** RETRIEVE PROCEDURE FOR THIRD LIEN COST CATEGORY CREATE
** DATE: 21-AUG-06
*********************************************/
   PROCEDURE thrdparty_clm_cost_cat_create (
      recordset      OUT      ref_cursor,
      inCaseSeqNum   IN       THRDPARTY_CASE.THRDPARTY_CASE_SEQ_NUM%TYPE,
      inclaimid      IN       thrdparty_claim_cost.claim_id%TYPE,
      inlienseqnum   IN       thrdparty_lien.thrdparty_lien_seq_num%TYPE,
      inuserid       IN       thrdparty_lien.create_user%TYPE
   )
   AS
   BEGIN
      OPEN recordset FOR
         SELECT ccc.code AS claimcostcategorycode,
	     		a.lienamount,
	     		a.nolienamount,
	            nvl(c.PROJECTED_AMOUNT, 0) AS projectedamount,
				0 as reserveAmt,
	            inlienseqnum AS thrdpartylienseqnum,
	            SYSDATE AS createdate,
	            inuserid AS createuser,
	            NULL AS updatedate,
	            0 AS updateseqnum,
	            NULL AS updateuser,
				nvl(a.lienamount, 0) as ClaimCostLienAmount,
				nvl(a.nolienamount, 0) as ClaimCostNoLienAmount,
				nvl(a.UndeterminedAmount, 0) as UndeterminedAmount,
				'NEW' as rowStatus,
				ccc.sort_order
           FROM TP_CLAIM_COST_CATEGORY_CODE ccc,
                (SELECT   SUM (tcc.lien_amount) AS lienamount,
                          SUM (tcc.no_lien_amount) AS nolienamount,
						  SUM (tcc.UNDETERMINED_AMOUNT) as UndeterminedAmount,
                          claim_cost_category_code
                     FROM thrdparty_claim_cost tcc
                    WHERE tcc.claim_id = inclaimid
                 GROUP BY claim_cost_category_code) a,
				(SELECT lc.PROJECTED_AMOUNT, lc.CLAIM_COST_CATEGORY_CODE
				   FROM THRDPARTY_LIEN_CATEGORY lc,
				        THRDPARTY_LIEN l
				 WHERE  l.THRDPARTY_CASE_SEQ_NUM = inCaseSeqNum
				   and  l.thrdparty_lien_seq_num = (SELECT MAX(thrdparty_lien_seq_num)
				                         FROM THRDPARTY_LIEN l2
										WHERE l2.THRDPARTY_CASE_SEQ_NUM = inCaseSeqNum)
				   and lc.THRDPARTY_LIEN_SEQ_NUM = l.THRDPARTY_LIEN_SEQ_NUM) c
          WHERE ccc.code = a.claim_cost_category_code(+)
			and ccc.code = c.claim_cost_category_code(+)
		 order by sort_order;
   END thrdparty_clm_cost_cat_create;

   PROCEDURE thrdparty_lien_cat_select (
      inlienseqnum   IN       thrdparty_lien_category.thrdparty_lien_seq_num%TYPE,
      recordset      OUT      ref_cursor
   )
   AS
   v_claim_id THRDPARTY_CASE.CLAIM_ID%type;
   BEGIN
      BEGIN
	     SELECT c.claim_id
		   INTO v_claim_id
		   FROM THRDPARTY_CASE c,
		        THRDPARTY_LIEN l
	      WHERE l.THRDPARTY_LIEN_SEQ_NUM = inlienseqnum
		  AND   c.THRDPARTY_CASE_SEQ_NUM = l.THRDPARTY_CASE_SEQ_NUM;
	  EXCEPTION
	     WHEN NO_DATA_FOUND THEN
		    v_claim_id := 0;
	  END;

      OPEN recordset FOR
         SELECT lc.claim_cost_category_code AS claimcostcategorycode,
                lc.lien_amount AS lienamount,
                lc.no_lien_amount AS nolienamount,
                lc.projected_amount AS projectedamount,
				0 as incurredCost,
                lc.reserve_amount AS reserveamount,
                lc.thrdparty_lien_seq_num AS thrdpartylienseqnum,
                lc.create_date AS createdate,
                lc.create_user AS createuser,
                lc.update_date AS updatedate,
                lc.update_seq_num AS updateseqnum,
                lc.update_user AS updateuser,
				nvl(a.lienamount,0) as ClaimCostLienAmount,
				nvl(a.nolienamount,0) as ClaimCostNoLienAmount,
				nvl(a.UndeterminedAmount,0) as UndeterminedAmount,
				'OLD' as rowStatus,
				c.sort_order
           FROM thrdparty_lien_category lc,
		    (SELECT   SUM (tcc.lien_amount) AS lienamount,
                          SUM (tcc.no_lien_amount) AS nolienamount,
						  SUM(tcc.UNDETERMINED_AMOUNT) as UndeterminedAmount,
                          claim_cost_category_code
                     FROM thrdparty_claim_cost tcc
                    WHERE tcc.claim_id = v_claim_id
                 GROUP BY claim_cost_category_code) a,
				TP_CLAIM_COST_CATEGORY_CODE c
          WHERE lc.thrdparty_lien_seq_num = inlienseqnum
		    AND lc.claim_cost_category_code = a.claim_cost_category_code (+)
			and c.CODE = lc.CLAIM_COST_CATEGORY_CODE
		  UNION
		  SELECT c.code AS claimcostcategorycode,
                0 AS lienamount,
                0 AS nolienamount,
                0 AS projectedamount,
				0 as incurredCost,
                0 AS reserveamount,
                0 AS thrdpartylienseqnum,
                sysdate as  createdate,
                null AS createuser,
                null AS updatedate,
                0 AS updateseqnum,
                null AS updateuser,
				nvl(a.lienamount,0) as ClaimCostLienAmount,
				nvl(a.nolienamount,0) as ClaimCostNoLienAmount,
				nvl(a.UndeterminedAmount,0) as UndeterminedAmount,
				'NEW' as rowStatus,
				c.sort_order
			FROM TP_CLAIM_COST_CATEGORY_CODE c,
			(SELECT   SUM (tcc.lien_amount) AS lienamount,
                          SUM (tcc.no_lien_amount) AS nolienamount,
						  SUM (tcc.UNDETERMINED_AMOUNT) as UndeterminedAmount,
                          claim_cost_category_code
                     FROM thrdparty_claim_cost tcc
                    WHERE tcc.claim_id = v_claim_id
                 GROUP BY claim_cost_category_code) a
			WHERE NOT EXISTS (SELECT 1 FROM thrdparty_lien_category lc
			                   WHERE c.code = lc.CLAIM_COST_CATEGORY_CODE
								 AND lc.THRDPARTY_LIEN_SEQ_NUM = inlienseqnum)
				and a.claim_cost_category_code (+) = c.code
           ORDER BY sort_order;
   END thrdparty_lien_cat_select;

   FUNCTION thrdparty_lien_category_update (
      LienSeqNum        IN   thrdparty_lien_category.thrdparty_lien_seq_num%TYPE,
      CategoryCode      IN   thrdparty_lien_category.claim_cost_category_code%TYPE,
      LienAmount        IN   thrdparty_lien_category.lien_amount%TYPE,
      NOLienAmount      IN   thrdparty_lien_category.no_lien_amount%TYPE,
      ProjectedAmount   IN   thrdparty_lien_category.projected_amount%TYPE,
      ReserveAmount     IN   thrdparty_lien_category.reserve_amount%TYPE,
      UpdateSeqNum      IN   thrdparty_lien_category.update_seq_num%TYPE,
      UpdateUser        IN   thrdparty_lien_category.update_user%TYPE
   )
      RETURN NUMBER
   IS
   BEGIN
      UPDATE thrdparty_lien_category
         SET lien_amount = LienAmount,
             no_lien_amount = NOLienAmount,
             projected_amount = ProjectedAmount,
             reserve_amount = ReserveAmount,
             update_date = SYSDATE,
             update_seq_num = update_seq_num + 1,
             update_user = UpdateUser
       WHERE thrdparty_lien_seq_num = LienSeqNum
         AND claim_cost_category_code = CategoryCode
         AND update_seq_num = UpdateSeqNum;

      RETURN SQL%ROWCOUNT;
   END thrdparty_lien_category_update;


   FUNCTION thrdparty_lien_category_insert(
        ThrdpartyLienSeqNum IN THRDPARTY_LIEN_CATEGORY.THRDPARTY_LIEN_SEQ_NUM%TYPE,
		ClaimCostCategoryCode IN THRDPARTY_LIEN_CATEGORY.CLAIM_COST_CATEGORY_CODE%TYPE,
        LienAmount IN THRDPARTY_LIEN_CATEGORY.LIEN_AMOUNT%TYPE,
        NOLienAmount IN THRDPARTY_LIEN_CATEGORY.NO_LIEN_AMOUNT%TYPE,
        ReserveAmount IN THRDPARTY_LIEN_CATEGORY.RESERVE_AMOUNT%TYPE,
        ProjectedAmount IN THRDPARTY_LIEN_CATEGORY.PROJECTED_AMOUNT%TYPE,
        CreateUser IN THRDPARTY_LIEN_CATEGORY.CREATE_USER%TYPE
        )
   RETURN NUMBER
   IS
   BEGIN
      Insert into THRDPARTY_LIEN_CATEGORY (
	         THRDPARTY_LIEN_SEQ_NUM,
	         CLAIM_COST_CATEGORY_CODE,
			 LIEN_AMOUNT,
             NO_LIEN_AMOUNT,
			 RESERVE_AMOUNT,
			 PROJECTED_AMOUNT,
			 CATEGORY_TOTAL,
             CREATE_DATE,
             CREATE_USER,
             UPDATE_SEQ_NUM)
        Values (
			  ThrdpartyLienSeqNum,
			  ClaimCostCategoryCode,
			  LienAmount,
              NOLienAmount,
			  ReserveAmount,
			  ProjectedAmount,
			  LienAmount + ProjectedAmount,
			  SYSDATE,
			  CreateUser,
			  0);

		RETURN SQL%ROWCOUNT;
   END thrdparty_lien_category_insert;



/*********************************************
**select cases with statute date 3 to 6 months in the future
*********************************************/
procedure Statute_Date_List
(inRunDate in date,
recordset out ref_cursor)
as
begin
	 open recordset for
		select round(months_between(inRunDate,tc.STATUTE_DATE) * -1)  as MonthsToAdd,
		 tc.statute_date as statuteDate,
		 tc.thrdparty_case_seq_num as caseSeqNum,
		   ac.user_name as userName
		 FROM  THRDPARTY_CASE tc
		 join thrdparty_adjuster_code ac on tc.thrdparty_adjuster_code = ac.code
		where  ((tc.statute_date between add_months(inRunDate,6) and add_months(inRunDate+7,6))
		or (tc.STATUTE_DATE between add_months(inRunDate,3) and add_months(inRunDate + 7, 3)))
		and  tc.thrdparty_status_code  in ('OPEN', 'DEFER', 'INACT')
		and  tc.SUIT_FILED_DATE is null;
end Statute_Date_List;

procedure Unit_Stat_Prediction_List
(recordset out ref_cursor)
as
begin
	 open recordset for
		 select tc.thrdparty_case_seq_num as CaseSeqNum
				 ,tc.claim_id 			  	 		 as ClaimId
				 ,ac.user_name 					  as UserName
				 ,max(p.prediction_date) 	   as LastPrediction
		  from thrdparty_case tc
			    ,thrdparty_adjuster_code ac
				,thrdparty_prediction p
		where tc.thrdparty_status_code in ('OPEN', 'INACT')
		and tc.thrdparty_adjuster_code = ac.code
		and tc.thrdparty_case_seq_num = p.thrdparty_case_seq_num (+)
		and p.final_prediction_flag (+) = 'Y'
		group by tc.thrdparty_case_seq_num, tc.claim_id, ac.user_name;

end Unit_Stat_Prediction_List;

FUNCTION get_recovery_amount
(
	caseseqnum IN thrdparty_distribution.thrdparty_case_seq_num%TYPE
)
RETURN NUMBER
AS
v_recoveryamount NUMBER;
BEGIN

	SELECT sum(check_amount)
	INTO v_recoveryamount
	  FROM thrdparty_distribution
	 WHERE thrdparty_case_seq_num = caseseqnum
	   AND corrected_date is null;

	RETURN nvl(v_recoveryamount, 0);

    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RETURN 0;
END get_recovery_amount;

FUNCTION get_task_delete_code
(
	caseseqnum IN thrdparty_case.THRDPARTY_CASE_SEQ_NUM%TYPE,
	tasksToExclude IN VARCHAR2
)
RETURN NUMBER
AS
v_count  	  NUMBER;
v_sql 		  VARCHAR2(500);
v_status	  thrdparty_case.THRDPARTY_STATUS_CODE%TYPE := ' ';
v_return_code NUMBER := 0;
BEGIN
	 --Get the current case status.
	 SELECT tc.THRDPARTY_STATUS_CODE
	   INTO v_status
	   FROM thrdparty_case tc
	  WHERE thrdparty_case_seq_num = caseseqnum;

	 --Set return code based on Status
	 IF v_status = 'PEND' THEN
	 	-- This means the task should not be deleted when the Case is in pending status.
	 	v_return_code := 1;
	 ELSE
	 	 v_sql := 'SELECT count(*)' ||
		 	   	  ' FROM thrdparty_task' ||
		 		  ' WHERE THRDPARTY_CASE_SEQ_NUM = ' || caseseqnum ||
				  ' AND thrdparty_task_seq_num not in (' || tasksToExclude || ')';

		 IF v_status = 'OPEN' OR v_status = 'DEFER' THEN
		 	-- Deferred and Open cases must have at least one future task.  Delete should not be allowed.
		 	v_return_code := 2;
		 END IF;

		 IF v_status = 'CLOSE' OR v_status = 'INACT' THEN
		 	-- This means a warning message should be displayed to confirm delete of the task.
		 	v_return_code := 3;
		 END IF;

		 EXECUTE IMMEDIATE v_sql INTO v_count;

		 IF v_count > 0 then
		 	-- This means this is not the last task being deleted for the case.  The task can be deleted.
		 	v_return_code := 4;
		 END IF;
	 END IF;

	 RETURN v_return_code;

     EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RETURN 0;
END get_task_delete_code;

procedure Get_Adjuster_List
(recordset OUT ref_cursor
)
as
begin open recordset for
 SELECT USER_ID, LAST_NAME  || ', ' ||  decode(PREFERRED_NAME, ' ', FIRST_NAME, preferred_name) as adjusterName,
  EMPLID FROM LEGAL.ADP_EMPLOYEE Vw
 where Vw.USER_ID in ('BRIMAT', 'DELCAM', 'SAMCRA', 'CANJON', 'RUTRAG', 'SANWIN', 'JOYGRE', 'BRAWHE')
 ORDER BY 2 ASC;
End Get_Adjuster_List;

procedure Get_IMEService_List
(recordset OUT ref_cursor
)
as
begin open recordset for
     select D.BILL_SERVICE_CODE code, D.SERVICE_DESCR_TEXT code_descr, D.SERVICE_DESCR_EFF_DATE
       from CBP_CPT_SERVICE s, CBP_SERVICE_DESCRIPTION D
      where S.IME_FLAG = 'Y'
        and S.CBP_SERVICE_CODE = D.BILL_SERVICE_CODE
        and D.SERVICE_DESCR_EXP_DATE is null
        and D.BILL_SERVICE_CODE not in ('L8691','W0001')
      order by 1;
End Get_IMEService_List;
END Pk82033_ThirdParty;
```

</details>

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

## Sources
- `Business\SAIF.Business.Claim.ThirdParty\clsThirdPartyCase.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThrdparty.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThrdpartyExpense.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThrdpartyLien.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThrdpartySettlement.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThirdpartyDistribution.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThirdpartyTask.vb`
- `Business\SAIF.Business.Claim.ThirdParty\clsThrdpartyPrediction.vb`