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;