pk82500_eaip¶

Overview¶

Field	Value
Schema	WCIS_DBA
Type	Package
Status	Found in Oracle during Stage 1b analysis
Calling libraries	`SAIF.Business.Claim.EAIP`
Calling classes	`clsPeriod`, `clsBilling`, `clsExpenseBilling`, `clsCreditCardPayments`, `clsExpense`, `clsPurchase`, `clsPayPeriod`, `clsWageSubsidy`, `clsAmount`, `clsDetailAmount`, `clsReimbursement`, `clsReimbursementDetail`, `clsTargeting`

Purpose¶

pk82500_eaip is the Oracle package backing the Employer-At-Injury Program (EAIP) domain. It provides the create/retrieve/update/delete routines for EAIP periods, billings, expense billings, wage subsidies, expenses, purchases, pay periods, reimbursement amounts, reimbursement detail amounts, reimbursements, reimbursement details, and claim targeting that back the SAIF.Business.Claim.EAIP business objects. Insert/update/delete routines follow a consistent optimistic-concurrency pattern: update and delete functions return SQL%ROWCOUNT so the calling business object can detect when a row was not found or was already changed by another user. Select routines open a ref_cursor (recordset OUT parameter) that the calling business object materializes into rows.

Procedures / Methods¶

eaip_period_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPeriod`

Description: Inserts a new row into EAIP_PERIOD. Insert procedure for table "eaip_period".

Parameters¶

eaip_period_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPeriod`

Description: Updates the matching row in EAIP_PERIOD. Update procedure for table "eaip_period".

Parameters¶

eaip_period_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPeriod`

Description: Deletes the EAIP period row from EAIP_PERIOD (guarded by UPDATE_NUM optimistic concurrency), then cascades cleanup of orphaned EAIP_EXPENSE rows: non-wage-subsidy expenses left without a matching EAIP_PURCHASE row and wage-subsidy expenses left without a matching EAIP_WAGE_SUBSIDY row are also deleted.

Parameters¶

Parameter	Direction	Type	Description
`EaipPeriodID`	IN	eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE	Input value bound to `eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE`.
`LastUpdateNum`	IN	eaip_period.UPDATE_NUM%TYPE	Input value bound to `eaip_period.UPDATE_NUM%TYPE`.

eaip_period_select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsPeriod`

Description: Opens a cursor selecting from EAIP_PERIOD. Select procedure for table "eaip_period".

Parameters¶

Parameter	Direction	Type	Description
`EaipPeriodID`	IN	eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE default 0	Input value bound to `eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE default 0`.
`ClaimID`	IN	eaip_period.CLAIM_ID%TYPE default 0	Input value bound to `eaip_period.CLAIM_ID%TYPE default 0`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

credit_payment_select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsCreditCardPayments`

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_PURCHASE, EAIP_PERIOD, and EAIP_EXPENSE_TYPE_CODE, returning credit-card purchases that have a CREDIT_CARD_PAID_DATE set and whose CREDIT_CARD_USER matches the supplied EaipUser, for supervisor review of pending credit-card payments.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.
`EaipUser`	IN	varchar2	Input value bound to `varchar2`.

update_expense_status¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsCreditCardPayments`

Description: Updates EAIP_EXPENSE_STATUS_CODE, EXPENSE_STATUS_DATE, and the audit columns (UPDATE_DATE, UPDATE_USER, UPDATE_NUM) on the matching EAIP_EXPENSE row, guarded by LastUpdateNum optimistic concurrency, as part of the credit-card payment review workflow.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseId`	IN	EAIP_EXPENSE.EAIP_EXPENSE_SEQ_NUM%TYPE	Input value bound to `EAIP_EXPENSE.EAIP_EXPENSE_SEQ_NUM%TYPE`.
`StatusCode`	IN	EAIP_EXPENSE.EAIP_EXPENSE_STATUS_CODE%TYPE	Input value bound to `EAIP_EXPENSE.EAIP_EXPENSE_STATUS_CODE%TYPE`.
`UPDATEUSER`	IN	eaip_expense.UPDATE_USER%TYPE	Input value bound to `eaip_expense.UPDATE_USER%TYPE`.
`UPDATEDATE`	IN	eaip_expense.UPDATE_DATE%TYPE	Input value bound to `eaip_expense.UPDATE_DATE%TYPE`.
`UPDATENUM`	IN	eaip_expense.UPDATE_NUM%TYPE	Input value bound to `eaip_expense.UPDATE_NUM%TYPE`.
`LastUpdateNum`	IN	eaip_expense.UPDATE_NUM%TYPE	Input value bound to `eaip_expense.UPDATE_NUM%TYPE`.

getBillingsByClaim¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsBilling`

Description: Opens a cursor selecting from EAIP_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`ClaimID`	IN	varchar2	Input value bound to `varchar2`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetBillingsByPeriod¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsBilling`

Description: Opens a cursor selecting from EAIP_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`PeriodID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

EAIP_Billing_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	Not confirmed by a direct .NET caller in this repository

Description: Opens a cursor selecting from EAIP_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_billing_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	Not confirmed by a direct .NET caller in this repository

Description: Inserts a new row into EAIP_BILLING. Insert procedure for table "eaip_billing".

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_billing.eaip_billing_seq_num %TYPE	Input value bound to `eaip_billing.eaip_billing_seq_num %TYPE`.
`BilledDate`	IN	eaip_billing.billed_date %TYPE	Input value bound to `eaip_billing.billed_date %TYPE`.
`ClaimID`	IN	eaip_billing.claim_id %TYPE	Input value bound to `eaip_billing.claim_id %TYPE`.
`PeriodID`	IN	eaip_billing.eaip_period_seq_num %TYPE	Input value bound to `eaip_billing.eaip_period_seq_num %TYPE`.
`AdminFeeAmount`	IN	eaip_billing.admin_fee_amount %TYPE	Input value bound to `eaip_billing.admin_fee_amount %TYPE`.
`AfEaipReimbusementDetailID`	IN	eaip_billing.af_eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_billing.af_eaip_reimb_detail_seq_num%TYPE`.
`BilledAmount`	IN	eaip_billing.billed_amount %TYPE	Input value bound to `eaip_billing.billed_amount %TYPE`.
`TypeCode`	IN	eaip_billing.eaip_billing_type_code %TYPE	Input value bound to `eaip_billing.eaip_billing_type_code %TYPE`.
`CreateUser`	IN	eaip_billing.create_user%TYPE	Input value bound to `eaip_billing.create_user%TYPE`.
`CreateDate`	IN	eaip_billing.create_date%TYPE	Input value bound to `eaip_billing.create_date%TYPE`.
`UpdateUser`	IN	eaip_billing.update_user%TYPE	Input value bound to `eaip_billing.update_user%TYPE`.
`UpdateDate`	IN	eaip_billing.update_date%TYPE	Input value bound to `eaip_billing.update_date%TYPE`.
`UpdateNum`	IN	eaip_billing.update_num%TYPE	Input value bound to `eaip_billing.update_num%TYPE`.

eaip_billing_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	Not confirmed by a direct .NET caller in this repository

Description: Updates the matching row in EAIP_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_billing.eaip_billing_seq_num %TYPE	Input value bound to `eaip_billing.eaip_billing_seq_num %TYPE`.
`BilledDate`	IN	eaip_billing.billed_date %TYPE	Input value bound to `eaip_billing.billed_date %TYPE`.
`ClaimID`	IN	eaip_billing.claim_id %TYPE	Input value bound to `eaip_billing.claim_id %TYPE`.
`PeriodID`	IN	eaip_billing.eaip_period_seq_num %TYPE	Input value bound to `eaip_billing.eaip_period_seq_num %TYPE`.
`AdminFeeAmount`	IN	eaip_billing.admin_fee_amount %TYPE	Input value bound to `eaip_billing.admin_fee_amount %TYPE`.
`AfEaipReimbusementDetailID`	IN	eaip_billing.af_eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_billing.af_eaip_reimb_detail_seq_num%TYPE`.
`BilledAmount`	IN	eaip_billing.billed_amount %TYPE	Input value bound to `eaip_billing.billed_amount %TYPE`.
`TypeCode`	IN	eaip_billing.eaip_billing_type_code %TYPE	Input value bound to `eaip_billing.eaip_billing_type_code %TYPE`.
`CreateUser`	IN	eaip_billing.create_user%TYPE	Input value bound to `eaip_billing.create_user%TYPE`.
`CreateDate`	IN	eaip_billing.create_date%TYPE	Input value bound to `eaip_billing.create_date%TYPE`.
`UpdateUser`	IN	eaip_billing.update_user%TYPE	Input value bound to `eaip_billing.update_user%TYPE`.
`UpdateDate`	IN	eaip_billing.update_date%TYPE	Input value bound to `eaip_billing.update_date%TYPE`.
`UpdateNum`	IN	eaip_billing.update_num%TYPE	Input value bound to `eaip_billing.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_billing.UPDATE_NUM%TYPE	Input value bound to `eaip_billing.UPDATE_NUM%TYPE`.

eaip_billing_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	Not confirmed by a direct .NET caller in this repository

Description: Deletes the matching row from EAIP_BILLING. Delete procedure for table "eaip_billing".

Parameters¶

Parameter	Direction	Type	Description
`EaipBillingID`	IN	eaip_billing.EAIP_billing_SEQ_NUM%TYPE	Input value bound to `eaip_billing.EAIP_billing_SEQ_NUM%TYPE`.
`LastUpdateNum`	IN	eaip_billing.UPDATE_NUM%TYPE	Input value bound to `eaip_billing.UPDATE_NUM%TYPE`.

GetExpenseBillingByBillingID¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsExpenseBilling`

Description: Opens a cursor selecting from EAIP_EXPENSE_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetExpenseBillingByExpenseID¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsExpenseBilling`

Description: Opens a cursor selecting from EAIP_EXPENSE_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetNonReconciledBillings¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsExpenseBilling`

Description: Opens a cursor selecting from EAIP_EXPENSE_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

EAIP_Expense_Billing_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsExpenseBilling`

Description: Opens a cursor selecting from EAIP_EXPENSE_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_expense_billing.eaip_billing_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_billing_seq_num%TYPE`.
`ExpenseID`	IN	eaip_expense_billing.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_expense_billing_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsExpenseBilling`

Description: Inserts a new row into EAIP_EXPENSE_BILLING. Insert procedure for table "eaip_expense_billing".

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_expense_billing.eaip_billing_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_billing_seq_num%TYPE`.
`ExpenseID`	IN	eaip_expense_billing.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_seq_num%TYPE`.
`BilledAmount`	IN	eaip_expense_billing.billed_amount%TYPE	Input value bound to `eaip_expense_billing.billed_amount%TYPE`.
`ReimbursedAmount`	IN	eaip_expense_billing.reimbursed_amount%TYPE	Input value bound to `eaip_expense_billing.reimbursed_amount%TYPE`.
`ReimbursementDetailID`	IN	eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_expense_billing.eaip_expense_type_code%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_type_code%TYPE`.
`CreateUser`	IN	eaip_expense_billing.create_user%TYPE	Input value bound to `eaip_expense_billing.create_user%TYPE`.
`CreateDate`	IN	eaip_expense_billing.create_date%TYPE	Input value bound to `eaip_expense_billing.create_date%TYPE`.
`UpdateUser`	IN	eaip_expense_billing.update_user%TYPE	Input value bound to `eaip_expense_billing.update_user%TYPE`.
`UpdateDate`	IN	eaip_expense_billing.update_date%TYPE	Input value bound to `eaip_expense_billing.update_date%TYPE`.
`UpdateNum`	IN	eaip_expense_billing.update_num%TYPE	Input value bound to `eaip_expense_billing.update_num%TYPE`.

eaip_expense_billing_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsExpenseBilling`

Description: Updates the matching row in EAIP_EXPENSE_BILLING.

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_expense_billing.eaip_billing_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_billing_seq_num%TYPE`.
`ExpenseID`	IN	eaip_expense_billing.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_seq_num%TYPE`.
`BilledAmount`	IN	eaip_expense_billing.billed_amount%TYPE	Input value bound to `eaip_expense_billing.billed_amount%TYPE`.
`ReimbursedAmount`	IN	eaip_expense_billing.reimbursed_amount%TYPE	Input value bound to `eaip_expense_billing.reimbursed_amount%TYPE`.
`ReimbursementDetailID`	IN	eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_expense_billing.eaip_expense_type_code%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_type_code%TYPE`.
`CreateUser`	IN	eaip_expense_billing.create_user%TYPE	Input value bound to `eaip_expense_billing.create_user%TYPE`.
`CreateDate`	IN	eaip_expense_billing.create_date%TYPE	Input value bound to `eaip_expense_billing.create_date%TYPE`.
`UpdateUser`	IN	eaip_expense_billing.update_user%TYPE	Input value bound to `eaip_expense_billing.update_user%TYPE`.
`UpdateDate`	IN	eaip_expense_billing.update_date%TYPE	Input value bound to `eaip_expense_billing.update_date%TYPE`.
`UpdateNum`	IN	eaip_expense_billing.update_num%TYPE	Input value bound to `eaip_expense_billing.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_billing.UPDATE_NUM%TYPE	Input value bound to `eaip_billing.UPDATE_NUM%TYPE`.

eaip_expense_billing_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsExpenseBilling`

Description: Deletes the matching row from EAIP_EXPENSE_BILLING. Delete procedure for table "eaip_expense_billing".

Parameters¶

Parameter	Direction	Type	Description
`BillingID`	IN	eaip_expense_billing.eaip_billing_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_billing_seq_num%TYPE`.
`ExpenseID`	IN	eaip_expense_billing.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense_billing.eaip_expense_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_expense_billing.UPDATE_NUM%TYPE	Input value bound to `eaip_expense_billing.UPDATE_NUM%TYPE`.

EAIP_Wage_Subsidy_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsWageSubsidy`

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_WAGE_SUBSIDY. Selects for eaip_wage_subsidy.

Parameters¶

Parameter	Direction	Type	Description
`EAIPPeriodID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetWageSubsidyByExpenseId¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsWageSubsidy`

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_WAGE_SUBSIDY.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_wage_subsidy_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsWageSubsidy`

Description: Inserts a new row into EAIP_WAGE_SUBSIDY. Insert procedure for table "eaip_wage_subsidy".

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_wage_subsidy.eaip_expense_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_expense_seq_num%TYPE`.
`EaipPeriodID`	IN	eaip_wage_subsidy.eaip_period_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_period_seq_num%TYPE`.
`EffectiveDate`	IN	eaip_wage_subsidy.wage_subsidy_eff_date%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_eff_date%TYPE`.
`ExpirationDate`	IN	eaip_wage_subsidy.wage_subsidy_exp_date%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_exp_date%TYPE`.
`InDays`	IN	eaip_wage_subsidy.wage_subsidy_days%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_days%TYPE`.
`GrossWagesAmount`	IN	eaip_wage_subsidy.gross_wages_amount%TYPE	Input value bound to `eaip_wage_subsidy.gross_wages_amount%TYPE`.

eaip_wage_subsidy_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsWageSubsidy`

Description: Updates the matching row in EAIP_WAGE_SUBSIDY.

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_wage_subsidy.eaip_expense_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_expense_seq_num%TYPE`.
`EaipPeriodID`	IN	eaip_wage_subsidy.eaip_period_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_period_seq_num%TYPE`.
`EffectiveDate`	IN	eaip_wage_subsidy.wage_subsidy_eff_date%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_eff_date%TYPE`.
`ExpirationDate`	IN	eaip_wage_subsidy.wage_subsidy_exp_date%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_exp_date%TYPE`.
`InDays`	IN	eaip_wage_subsidy.wage_subsidy_days%TYPE	Input value bound to `eaip_wage_subsidy.wage_subsidy_days%TYPE`.
`GrossWagesAmount`	IN	eaip_wage_subsidy.gross_wages_amount%TYPE	Input value bound to `eaip_wage_subsidy.gross_wages_amount%TYPE`.

eaip_wage_subsidy_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsWageSubsidy`

Description: Deletes the matching row from EAIP_WAGE_SUBSIDY. Delete procedure for table "eaip_wage_subsidy - also delete on expense.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseID`	IN	eaip_expense.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense.eaip_expense_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_expense.UPDATE_NUM%TYPE	Input value bound to `eaip_expense.UPDATE_NUM%TYPE`.

eaip_expense_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsExpense`

Description: Inserts a new row into EAIP_EXPENSE. Insert procedure for table "eaip_expense".

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_wage_subsidy.eaip_expense_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_expense_seq_num%TYPE`.
`OriginalExpenseID`	IN	eaip_expense.orig_expense_seq_num%TYPE	Input value bound to `eaip_expense.orig_expense_seq_num%TYPE`.
`ReceivedDate`	IN	eaip_expense.received_date%TYPE	Input value bound to `eaip_expense.received_date%TYPE`.
`EaipTypeCode`	IN	eaip_expense.eaip_expense_type_code%TYPE	Input value bound to `eaip_expense.eaip_expense_type_code%TYPE`.
`EaipExpenseStatusCode`	IN	eaip_expense.eaip_expense_status_code%TYPE	Input value bound to `eaip_expense.eaip_expense_status_code%TYPE`.
`ExpenseStatusDate`	IN	eaip_expense.expense_status_date%TYPE	Input value bound to `eaip_expense.expense_status_date%TYPE`.
`WriteOffAmount`	IN	eaip_expense.eaip_writeoff_amount%TYPE	Input value bound to `eaip_expense.eaip_writeoff_amount%TYPE`.
`WriteOffCode`	IN	eaip_expense.eaip_writeoff_code%TYPE	Input value bound to `eaip_expense.eaip_writeoff_code%TYPE`.
`EaipAdjustmentCode`	IN	eaip_expense.eaip_adjustment_code%TYPE	Input value bound to `eaip_expense.eaip_adjustment_code%TYPE`.
`EaipAdjustmentAmount`	IN	eaip_expense.eaip_adjustment_amount%TYPE	Input value bound to `eaip_expense.eaip_adjustment_amount%TYPE`.
`EaipAdjustmentDate`	IN	eaip_expense.eaip_adjustment_date%TYPE	Input value bound to `eaip_expense.eaip_adjustment_date%TYPE`.
`FinalReceiptDate`	IN	eaip_expense.final_receipt_date%TYPE	Input value bound to `eaip_expense.final_receipt_date%TYPE`.
`EaipExpenseDeniedCode`	IN	eaip_expense.eaip_expense_denied_code%TYPE	Input value bound to `eaip_expense.eaip_expense_denied_code%TYPE`.
`AmendmentText`	IN	eaip_expense.ammend_explanation_text%TYPE	Input value bound to `eaip_expense.ammend_explanation_text%TYPE`.
`ExpenseText`	IN	eaip_expense.expense_text%TYPE	Input value bound to `eaip_expense.expense_text%TYPE`.
`PaidAmount`	IN	eaip_expense.paid_amount%TYPE	Input value bound to `eaip_expense.paid_amount%TYPE`.
`NetPaymentAmount`	IN	EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type	Input value bound to `EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type`.
`PaidDate`	IN	eaip_expense.paid_date%TYPE	Input value bound to `eaip_expense.paid_date%TYPE`.
`CreateUser`	IN	eaip_expense.create_user%TYPE	Input value bound to `eaip_expense.create_user%TYPE`.
`CreateDate`	IN	eaip_expense.create_date%TYPE	Input value bound to `eaip_expense.create_date%TYPE`.
`UpdateUser`	IN	eaip_expense.update_user%TYPE	Input value bound to `eaip_expense.update_user%TYPE`.
`UpdateDate`	IN	eaip_expense.update_date%TYPE	Input value bound to `eaip_expense.update_date%TYPE`.
`UpdateNum`	IN	eaip_expense.update_num%TYPE	Input value bound to `eaip_expense.update_num%TYPE`.
`PayeeType`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PolicyLocSeqNum`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PersonId`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PaymentText`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`AttentionLine`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`SpecialHandlingCode`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`IssueDate`	IN	DATE	Input value bound to `DATE`.
`PayeeName`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine1`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine2`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine3`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inCity`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inState`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inZip`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inZipPlus4`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inCountryCode`	IN	VARCHAR2	Input value bound to `VARCHAR2`.

eaip_expense_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsExpense`

Description: Updates the matching row in EAIP_EXPENSE.

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_wage_subsidy.eaip_expense_seq_num%TYPE	Input value bound to `eaip_wage_subsidy.eaip_expense_seq_num%TYPE`.
`OriginalExpenseID`	IN	eaip_expense.orig_expense_seq_num%TYPE	Input value bound to `eaip_expense.orig_expense_seq_num%TYPE`.
`ReceivedDate`	IN	eaip_expense.received_date%TYPE	Input value bound to `eaip_expense.received_date%TYPE`.
`EaipTypeCode`	IN	eaip_expense.eaip_expense_type_code%TYPE	Input value bound to `eaip_expense.eaip_expense_type_code%TYPE`.
`EaipExpenseStatusCode`	IN	eaip_expense.eaip_expense_status_code%TYPE	Input value bound to `eaip_expense.eaip_expense_status_code%TYPE`.
`ExpenseStatusDate`	IN	eaip_expense.expense_status_date%TYPE	Input value bound to `eaip_expense.expense_status_date%TYPE`.
`WriteOffAmount`	IN	eaip_expense.eaip_writeoff_amount%TYPE	Input value bound to `eaip_expense.eaip_writeoff_amount%TYPE`.
`WriteOffCode`	IN	eaip_expense.eaip_writeoff_code%TYPE	Input value bound to `eaip_expense.eaip_writeoff_code%TYPE`.
`EaipAdjustmentCode`	IN	eaip_expense.eaip_adjustment_code%TYPE	Input value bound to `eaip_expense.eaip_adjustment_code%TYPE`.
`EaipAdjustmentAmount`	IN	eaip_expense.eaip_adjustment_amount%TYPE	Input value bound to `eaip_expense.eaip_adjustment_amount%TYPE`.
`EaipAdjustmentDate`	IN	eaip_expense.eaip_adjustment_date%TYPE	Input value bound to `eaip_expense.eaip_adjustment_date%TYPE`.
`FinalReceiptDate`	IN	eaip_expense.final_receipt_date%TYPE	Input value bound to `eaip_expense.final_receipt_date%TYPE`.
`EaipExpenseDeniedCode`	IN	eaip_expense.eaip_expense_denied_code%TYPE	Input value bound to `eaip_expense.eaip_expense_denied_code%TYPE`.
`AmendmentText`	IN	eaip_expense.ammend_explanation_text%TYPE	Input value bound to `eaip_expense.ammend_explanation_text%TYPE`.
`ExpenseText`	IN	eaip_expense.expense_text%TYPE	Input value bound to `eaip_expense.expense_text%TYPE`.
`PaidAmount`	IN	eaip_expense.paid_amount%TYPE	Input value bound to `eaip_expense.paid_amount%TYPE`.
`NetPaymentAmount`	IN	EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type	Input value bound to `EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type`.
`PaidDate`	IN	eaip_expense.paid_date%TYPE	Input value bound to `eaip_expense.paid_date%TYPE`.
`CreateUser`	IN	eaip_expense.create_user%TYPE	Input value bound to `eaip_expense.create_user%TYPE`.
`CreateDate`	IN	eaip_expense.create_date%TYPE	Input value bound to `eaip_expense.create_date%TYPE`.
`UpdateUser`	IN	eaip_expense.update_user%TYPE	Input value bound to `eaip_expense.update_user%TYPE`.
`UpdateDate`	IN	eaip_expense.update_date%TYPE	Input value bound to `eaip_expense.update_date%TYPE`.
`UpdateNum`	IN	eaip_expense.update_num%TYPE	Input value bound to `eaip_expense.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_expense.UPDATE_NUM%TYPE	Input value bound to `eaip_expense.UPDATE_NUM%TYPE`.
`PayeeType`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PolicyLocSeqNum`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PersonId`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`PaymentText`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`AttentionLine`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`SpecialHandlingCode`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`IssueDate`	IN	DATE	Input value bound to `DATE`.
`PayeeName`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine1`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine2`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inAddressLine3`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inCity`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inState`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inZip`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inZipPlus4`	IN	VARCHAR2	Input value bound to `VARCHAR2`.
`inCountryCode`	IN	VARCHAR2	Input value bound to `VARCHAR2`.

EAIP_Purchase_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsPurchase`

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_PURCHASE. Select for eaip_purchase.

Parameters¶

Parameter	Direction	Type	Description
`EAIPPeriodID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetPurchaseByExpenseId¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsPurchase`

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_PURCHASE.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_purchase_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPurchase`

Description: Inserts a new row into EAIP_PURCHASE. Insert procedure for table "eaip_purchase".

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_purchase.eaip_expense_seq_num%TYPE	Input value bound to `eaip_purchase.eaip_expense_seq_num%TYPE`.
`EaipPeriodID`	IN	eaip_purchase.eaip_period_seq_num%TYPE	Input value bound to `eaip_purchase.eaip_period_seq_num%TYPE`.
`PurchaseDescription`	IN	eaip_purchase.purchase_descr%TYPE	Input value bound to `eaip_purchase.purchase_descr%TYPE`.
`OrderDate`	IN	eaip_purchase.order_date%TYPE	Input value bound to `eaip_purchase.order_date%TYPE`.
`DeliveryDate`	IN	eaip_purchase.delivery_date%TYPE	Input value bound to `eaip_purchase.delivery_date%TYPE`.
`ItemCostAmount`	IN	eaip_purchase.item_cost_amount%TYPE	Input value bound to `eaip_purchase.item_cost_amount%TYPE`.
`CreditCardPaidDate`	IN	eaip_purchase.credit_card_paid_date%TYPE	Input value bound to `eaip_purchase.credit_card_paid_date%TYPE`.
`CreditCardUser`	IN	eaip_purchase.credit_card_user%TYPE	Input value bound to `eaip_purchase.credit_card_user%TYPE`.
`VendorBillRefText`	IN	eaip_purchase.vendor_bill_ref_text%TYPE	Input value bound to `eaip_purchase.vendor_bill_ref_text%TYPE`.

eaip_purchase_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPurchase`

Description: Updates the matching row in EAIP_PURCHASE.

Parameters¶

Parameter	Direction	Type	Description
`EaipExpenseID`	IN	eaip_purchase.eaip_expense_seq_num%TYPE	Input value bound to `eaip_purchase.eaip_expense_seq_num%TYPE`.
`EaipPeriodID`	IN	eaip_purchase.eaip_period_seq_num%TYPE	Input value bound to `eaip_purchase.eaip_period_seq_num%TYPE`.
`PurchaseDescription`	IN	eaip_purchase.purchase_descr%TYPE	Input value bound to `eaip_purchase.purchase_descr%TYPE`.
`OrderDate`	IN	eaip_purchase.order_date%TYPE	Input value bound to `eaip_purchase.order_date%TYPE`.
`DeliveryDate`	IN	eaip_purchase.delivery_date%TYPE	Input value bound to `eaip_purchase.delivery_date%TYPE`.
`ItemCostAmount`	IN	eaip_purchase.item_cost_amount%TYPE	Input value bound to `eaip_purchase.item_cost_amount%TYPE`.
`CreditCardPaidDate`	IN	eaip_purchase.credit_card_paid_date%TYPE	Input value bound to `eaip_purchase.credit_card_paid_date%TYPE`.
`CreditCardUser`	IN	eaip_purchase.credit_card_user%TYPE	Input value bound to `eaip_purchase.credit_card_user%TYPE`.
`VendorBillRefText`	IN	eaip_purchase.vendor_bill_ref_text%TYPE	Input value bound to `eaip_purchase.vendor_bill_ref_text%TYPE`.

eaip_purchase_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPurchase`

Description: Deletes the matching row from EAIP_PURCHASE. Delete procedure for table "eaip_purchase".

Parameters¶

Parameter	Direction	Type	Description
`ExpenseID`	IN	eaip_expense.eaip_expense_seq_num%TYPE	Input value bound to `eaip_expense.eaip_expense_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_expense.UPDATE_NUM%TYPE	Input value bound to `eaip_expense.UPDATE_NUM%TYPE`.

EAIP_PAY_PERIOD_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsPayPeriod`

Description: Opens a cursor selecting from EAIP_PAY_PERIOD. Select for eaip_payperiod.

Parameters¶

Parameter	Direction	Type	Description
`ExpenseId`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetPayPeriodByPayPeriodId¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsPayPeriod`

Description: Opens a cursor selecting from EAIP_PAY_PERIOD.

Parameters¶

Parameter	Direction	Type	Description
`PayPeriodID`	IN	Number	Input value bound to `Number`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_pay_period_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPayPeriod`

Description: Inserts a new row into EAIP_PAY_PERIOD. Insert procedure for table "eaip_pay_period".

Parameters¶

eaip_pay_period_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPayPeriod`

Description: Updates the matching row in EAIP_PAY_PERIOD.

Parameters¶

eaip_pay_period_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsPayPeriod`

Description: Deletes the matching row from EAIP_PAY_PERIOD. Delete procedure for table "eaip_pay_period".

Parameters¶

Parameter	Direction	Type	Description
`PayPeriodID`	IN	eaip_pay_period.eaip_pay_period_seq_num%TYPE	Input value bound to `eaip_pay_period.eaip_pay_period_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_pay_period.UPDATE_NUM%TYPE	Input value bound to `eaip_pay_period.UPDATE_NUM%TYPE`.

EAIP_Reimb_Amount_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsAmount`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT_AMOUNT. Select procedure for table "eaip_reimbursement_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbAmountbyType¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsAmount`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT_AMOUNT.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimbursement_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_expense_type_code%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

EAIP_Reimb_Amount_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsAmount`

Description: Inserts a new row into EAIP_REIMBURSEMENT_AMOUNT. Insert procedure for table "eaip_reimbursement_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimbursement_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_expense_type_code%TYPE`.
`DisablingAmount`	IN	eaip_reimbursement_amount.disabling_amount%TYPE	Input value bound to `eaip_reimbursement_amount.disabling_amount%TYPE`.
`NonDisablingAmount`	IN	eaip_reimbursement_amount.nondisabling_amount%TYPE	Input value bound to `eaip_reimbursement_amount.nondisabling_amount%TYPE`.
`TotalAmount`	IN	eaip_reimbursement_amount.total_amount%TYPE	Input value bound to `eaip_reimbursement_amount.total_amount%TYPE`.
`CreateUser`	IN	eaip_reimbursement_amount.create_user%TYPE	Input value bound to `eaip_reimbursement_amount.create_user%TYPE`.
`CreateDate`	IN	eaip_reimbursement_amount.create_date%TYPE	Input value bound to `eaip_reimbursement_amount.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimbursement_amount.update_user%TYPE	Input value bound to `eaip_reimbursement_amount.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimbursement_amount.update_date%TYPE	Input value bound to `eaip_reimbursement_amount.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimbursement_amount.update_num%TYPE	Input value bound to `eaip_reimbursement_amount.update_num%TYPE`.

EAIP_Reimb_Amount_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsAmount`

Description: Updates the matching row in EAIP_REIMBURSEMENT_AMOUNT.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimbursement_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_expense_type_code%TYPE`.
`DisablingAmount`	IN	eaip_reimbursement_amount.disabling_amount%TYPE	Input value bound to `eaip_reimbursement_amount.disabling_amount%TYPE`.
`NonDisablingAmount`	IN	eaip_reimbursement_amount.nondisabling_amount%TYPE	Input value bound to `eaip_reimbursement_amount.nondisabling_amount%TYPE`.
`TotalAmount`	IN	eaip_reimbursement_amount.total_amount%TYPE	Input value bound to `eaip_reimbursement_amount.total_amount%TYPE`.
`CreateUser`	IN	eaip_reimbursement_amount.create_user%TYPE	Input value bound to `eaip_reimbursement_amount.create_user%TYPE`.
`CreateDate`	IN	eaip_reimbursement_amount.create_date%TYPE	Input value bound to `eaip_reimbursement_amount.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimbursement_amount.update_user%TYPE	Input value bound to `eaip_reimbursement_amount.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimbursement_amount.update_date%TYPE	Input value bound to `eaip_reimbursement_amount.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimbursement_amount.update_num%TYPE	Input value bound to `eaip_reimbursement_amount.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_reimbursement_amount.UPDATE_NUM%TYPE	Input value bound to `eaip_reimbursement_amount.UPDATE_NUM%TYPE`.
`LastExpenseTypeCode`	IN	eaip_reimbursement_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_expense_type_code%TYPE`.

EAIP_Reimb_Amount_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsAmount`

Description: Deletes the matching row from EAIP_REIMBURSEMENT_AMOUNT. Delete procedure for table "eaip_reimbursement_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimbursement_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimbursement_amount.eaip_expense_type_code%TYPE`.
`LastUpdateNum`	IN	eaip_reimbursement_amount.UPDATE_NUM%TYPE	Input value bound to `eaip_reimbursement_amount.UPDATE_NUM%TYPE`.

reimb_detail_amount_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsDetailAmount`

Description: Opens a cursor selecting from EAIP_REIMB_DETAIL_AMOUNT. Select procedure for table "eaip_reimb_detail_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbDetailAmountbyType¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsDetailAmount`

Description: Opens a cursor selecting from EAIP_REIMB_DETAIL_AMOUNT.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimb_detail_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_expense_type_code%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

reimb_detail_amount_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsDetailAmount`

Description: Inserts a new row into EAIP_REIMB_DETAIL_AMOUNT. Insert procedure for table "eaip_reimb_detail_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimb_detail_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_expense_type_code%TYPE`.
`ReimbursedAmount`	IN	eaip_reimb_detail_amount.reimbursed_amount%TYPE	Input value bound to `eaip_reimb_detail_amount.reimbursed_amount%TYPE`.
`CreateUser`	IN	eaip_reimb_detail_amount.create_user%TYPE	Input value bound to `eaip_reimb_detail_amount.create_user%TYPE`.
`CreateDate`	IN	eaip_reimb_detail_amount.create_date%TYPE	Input value bound to `eaip_reimb_detail_amount.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimb_detail_amount.update_user%TYPE	Input value bound to `eaip_reimb_detail_amount.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimb_detail_amount.update_date%TYPE	Input value bound to `eaip_reimb_detail_amount.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimb_detail_amount.update_num%TYPE	Input value bound to `eaip_reimb_detail_amount.update_num%TYPE`.

reimb_detail_amount_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsDetailAmount`

Description: Updates the matching row in EAIP_REIMB_DETAIL_AMOUNT.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimb_detail_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_expense_type_code%TYPE`.
`ReimbursedAmount`	IN	eaip_reimb_detail_amount.reimbursed_amount%TYPE	Input value bound to `eaip_reimb_detail_amount.reimbursed_amount%TYPE`.
`CreateUser`	IN	eaip_reimb_detail_amount.create_user%TYPE	Input value bound to `eaip_reimb_detail_amount.create_user%TYPE`.
`CreateDate`	IN	eaip_reimb_detail_amount.create_date%TYPE	Input value bound to `eaip_reimb_detail_amount.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimb_detail_amount.update_user%TYPE	Input value bound to `eaip_reimb_detail_amount.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimb_detail_amount.update_date%TYPE	Input value bound to `eaip_reimb_detail_amount.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimb_detail_amount.update_num%TYPE	Input value bound to `eaip_reimb_detail_amount.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_reimb_detail_amount.UPDATE_NUM%TYPE	Input value bound to `eaip_reimb_detail_amount.UPDATE_NUM%TYPE`.
`LastExpenseTypeCode`	IN	eaip_reimb_detail_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_expense_type_code%TYPE`.

reimb_detail_amount_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsDetailAmount`

Description: Deletes the matching row from EAIP_REIMB_DETAIL_AMOUNT. Delete procedure for table "eaip_reimb_detail_amount".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE`.
`ExpenseTypeCode`	IN	eaip_reimb_detail_amount.eaip_expense_type_code%TYPE	Input value bound to `eaip_reimb_detail_amount.eaip_expense_type_code%TYPE`.
`LastUpdateNum`	IN	eaip_reimb_detail_amount.UPDATE_NUM%TYPE	Input value bound to `eaip_reimb_detail_amount.UPDATE_NUM%TYPE`.

eaip_reimbursement_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursement`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT. Select procedure for table "eaip_reimbursement".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement.eaip_reimb_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbByReceivedDate¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursement`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT.

Parameters¶

Parameter	Direction	Type	Description
`ReceivedDate`	IN	date	Input value bound to `date`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbByPostedDate¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursement`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT.

Parameters¶

Parameter	Direction	Type	Description
`PostedDate`	IN	date	Input value bound to `date`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbReconciledFlag¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursement`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT.

Parameters¶

Parameter	Direction	Type	Description
`ReconciledFlag`	IN	varchar2	Input value bound to `varchar2`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_reimbursement_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursement`

Description: Inserts a new row into EAIP_REIMBURSEMENT. Insert procedure for table "eaip_reimbursement".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement.eaip_reimb_seq_num%TYPE`.
`ReceivedDate`	IN	eaip_reimbursement.received_date%TYPE	Input value bound to `eaip_reimbursement.received_date%TYPE`.
`PostedDate`	IN	eaip_reimbursement.posted_date%TYPE	Input value bound to `eaip_reimbursement.posted_date%TYPE`.
`ReconciledDate`	IN	eaip_reimbursement.reconciled_date%TYPE	Input value bound to `eaip_reimbursement.reconciled_date%TYPE`.
`IsReconciled`	IN	eaip_reimbursement.reconciled_flag%TYPE	Input value bound to `eaip_reimbursement.reconciled_flag%TYPE`.
`ReimbursementAmount`	IN	eaip_reimbursement.reimbursement_amount%TYPE	Input value bound to `eaip_reimbursement.reimbursement_amount%TYPE`.
`ReconciledAmount`	IN	eaip_reimbursement.reconciled_amount%TYPE	Input value bound to `eaip_reimbursement.reconciled_amount%TYPE`.
`ExplanationText`	IN	eaip_reimbursement.explanation_text%TYPE	Input value bound to `eaip_reimbursement.explanation_text%TYPE`.
`CreateUser`	IN	eaip_reimbursement.create_user%TYPE	Input value bound to `eaip_reimbursement.create_user%TYPE`.
`CreateDate`	IN	eaip_reimbursement.create_date%TYPE	Input value bound to `eaip_reimbursement.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimbursement.update_user%TYPE	Input value bound to `eaip_reimbursement.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimbursement.update_date%TYPE	Input value bound to `eaip_reimbursement.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimbursement.update_num%TYPE	Input value bound to `eaip_reimbursement.update_num%TYPE`.

eaip_reimbursement_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursement`

Description: Updates the matching row in EAIP_REIMBURSEMENT.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement.eaip_reimb_seq_num%TYPE`.
`ReceivedDate`	IN	eaip_reimbursement.received_date%TYPE	Input value bound to `eaip_reimbursement.received_date%TYPE`.
`PostedDate`	IN	eaip_reimbursement.posted_date%TYPE	Input value bound to `eaip_reimbursement.posted_date%TYPE`.
`ReconciledDate`	IN	eaip_reimbursement.reconciled_date%TYPE	Input value bound to `eaip_reimbursement.reconciled_date%TYPE`.
`IsReconciled`	IN	eaip_reimbursement.reconciled_flag%TYPE	Input value bound to `eaip_reimbursement.reconciled_flag%TYPE`.
`ReimbursementAmount`	IN	eaip_reimbursement.reimbursement_amount%TYPE	Input value bound to `eaip_reimbursement.reimbursement_amount%TYPE`.
`ReconciledAmount`	IN	eaip_reimbursement.reconciled_amount%TYPE	Input value bound to `eaip_reimbursement.reconciled_amount%TYPE`.
`ExplanationText`	IN	eaip_reimbursement.explanation_text%TYPE	Input value bound to `eaip_reimbursement.explanation_text%TYPE`.
`CreateUser`	IN	eaip_reimbursement.create_user%TYPE	Input value bound to `eaip_reimbursement.create_user%TYPE`.
`CreateDate`	IN	eaip_reimbursement.create_date%TYPE	Input value bound to `eaip_reimbursement.create_date%TYPE`.
`UpdateUser`	IN	eaip_reimbursement.update_user%TYPE	Input value bound to `eaip_reimbursement.update_user%TYPE`.
`UpdateDate`	IN	eaip_reimbursement.update_date%TYPE	Input value bound to `eaip_reimbursement.update_date%TYPE`.
`UpdateNum`	IN	eaip_reimbursement.update_num%TYPE	Input value bound to `eaip_reimbursement.update_num%TYPE`.
`LastUpdateNum`	IN	eaip_reimbursement.update_num%TYPE	Input value bound to `eaip_reimbursement.update_num%TYPE`.

eaip_reimbursement_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursement`

Description: Deletes the matching row from EAIP_REIMBURSEMENT. Delete procedure for table "eaip_reimbursement".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement.eaip_reimb_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_reimbursement.UPDATE_NUM%TYPE	Input value bound to `eaip_reimbursement.UPDATE_NUM%TYPE`.

eaip_reimb_detail_Select¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursementDetail`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT_DETAIL. Select procedure for table "eaip_reimbursement_detail".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementID`	IN	eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

GetReimbDetailByID¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	`clsReimbursementDetail`

Description: Opens a cursor selecting from EAIP_REIMBURSEMENT_DETAIL.

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE`.
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

eaip_reimb_detail_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursementDetail`

Description: Inserts a new row into EAIP_REIMBURSEMENT_DETAIL. Insert procedure for table "eaip_reimbursement_detail".

Parameters¶

eaip_reimb_detail_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursementDetail`

Description: Updates the matching row in EAIP_REIMBURSEMENT_DETAIL.

Parameters¶

eaip_reimb_detail_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsReimbursementDetail`

Description: Deletes the matching row from EAIP_REIMBURSEMENT_DETAIL. Delete procedure for table "eaip_reimbursement_detail".

Parameters¶

Parameter	Direction	Type	Description
`ReimbursementDetailID`	IN	eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE	Input value bound to `eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE`.
`ReimbursementID`	IN	eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE	Input value bound to `eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE`.
`LastUpdateNum`	IN	eaip_reimbursement.UPDATE_NUM%TYPE	Input value bound to `eaip_reimbursement.UPDATE_NUM%TYPE`.

GetTargetingByClaim_New¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	Not confirmed by a direct .NET caller in this repository

Description: Opens a cursor selecting from EAIP_CLAIM_TARGETING.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	IN	OUT ref_cursor	Output ref cursor returning the result set.
`ClaimID`	IN	eaip_claim_targeting.Claim_id%TYPE	Input value bound to `eaip_claim_targeting.Claim_id%TYPE`.

eaip_targeting_insert¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsTargeting`

Description: Inserts a new row into EAIP_CLAIM_TARGETING. Insert procedure for table "eaip_claim_targeting".

Parameters¶

eaip_targeting_update¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsTargeting`

Description: Updates the matching row in EAIP_CLAIM_TARGETING.

Parameters¶

eaip_targeting_delete¶

Field	Value
Type	Function
Returns	Number of rows affected, via `SQL%ROWCOUNT` (`NUMBER`)
Called by	`clsTargeting`

Description: Deletes the matching row from EAIP_CLAIM_TARGETING. Delete procedure for table "eaip_claim_targeting".

Parameters¶

Parameter	Direction	Type	Description
`TargetID`	IN	eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE	Input value bound to `eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE`.
`LastUpdateNum`	IN	eaip_claim_targeting.update_num%TYPE	Input value bound to `eaip_claim_targeting.update_num%TYPE`.

SelectExpensesInCheckStatus¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	Not confirmed by a direct .NET caller in this repository

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_PERIOD, EAIP_PURCHASE, EAIP_WAGE_SUBSIDY. Select procedure for eaip expenses in check status for billing.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	IN OUT	ref_cursor	Output ref cursor returning the result set.

SelectExpensesByStatus_GW¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	Not confirmed by a direct .NET caller in this repository

Description: Opens a cursor joining EAIP_EXPENSE, EAIP_EXPENSE_TYPE_CODE, EAIP_PERIOD, EAIP_PURCHASE, EAIP_WAGE_SUBSIDY. Select procedure for eaip expenses in selected status.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	OUT	ref_cursor	Output ref cursor returning the result set.
`ExpenseStatus`	IN	varchar2	Input value bound to `varchar2`.
`Specialist`	IN	varchar2	Input value bound to `varchar2`.

EAIPHeaderBySeqNumAndClaimID¶

Field	Value
Type	Procedure
Returns	Result set via the `recordset` `OUT` `ref_cursor` parameter
Called by	Not confirmed by a direct .NET caller in this repository

Description: Opens a cursor selecting the requested data.

Parameters¶

Parameter	Direction	Type	Description
`recordset`	OUT	ref_cursor	Output ref cursor returning the result set.
`ServicePeriodSeqNum`	IN	NUMBER	Input value bound to `NUMBER`.
`ClaimID`	IN	NUMBER	Input value bound to `NUMBER`.

Business Rules¶

Rule ID	Rule	Condition	Action
PR-001	Protect expense status changes with optimistic concurrency	`update_expense_status` receives `LastUpdateNum` that must match `EAIP_EXPENSE.UPDATE_NUM`	Returns `SQL%ROWCOUNT` so callers detect stale updates rather than forcing the write
PR-002	Protect EAIP period updates and deletes with optimistic concurrency	`eaip_period_update` / `eaip_period_delete` receive `LastUpdateNum` that must match `EAIP_PERIOD.UPDATE_NUM`	Returns `SQL%ROWCOUNT` instead of forcing the update or delete
PR-003	Return only credit-card purchases paid for the selected EAIP user	`credit_payment_select` filters on `PR.CREDIT_CARD_PAID_DATE IS NOT NULL` and `PR.CREDIT_CARD_USER = EaipUser`	Exposes only that user's supervisor-review candidates
PR-004	Clean up orphan EAIP expenses after deleting a period	`eaip_period_delete` removes the period, then deletes any `EAIP_EXPENSE` rows left without a parent `EAIP_PURCHASE` (non-wage-subsidy) or `EAIP_WAGE_SUBSIDY` (wage-subsidy) row	Prevents orphaned expense rows from remaining after a period is deleted

These rules are also documented in Business rules.

Operations¶

Operation	Description	Trigger
Maintain EAIP period	Creates, retrieves, updates, and deletes EAIP periods (`eaip_period_insert/select/update/delete`) and cascades orphan-expense cleanup on delete	`clsPeriod` create/retrieve/update/delete
Update expense status	Updates expense status, status date, and audit columns for the credit-card payment workflow (`update_expense_status`)	`clsCreditCardPayments.UpdateExpenseStatus`
Retrieve credit-card payments awaiting review	Returns paid credit-card purchases pending supervisor review for an EAIP user (`credit_payment_select`)	`clsCreditCardPayments` retrieve
Maintain EAIP billing	Creates, retrieves, updates, and deletes EAIP billing rows and looks up billings by claim or period (`eaip_billing_*`, `getBillingsByClaim`, `GetBillingsByPeriod`)	`clsBilling` create/retrieve/update/delete
Maintain EAIP expense billing	Creates, retrieves, updates, and deletes expense-billing allocations and finds non-reconciled billings (`eaip_expense_billing_*`, `GetExpenseBillingByBillingID/ExpenseID`, `GetNonReconciledBillings`)	`clsExpenseBilling` create/retrieve/update/delete
Maintain EAIP wage subsidy	Creates, retrieves, updates, and deletes wage-subsidy rows tied to an expense (`eaip_wage_subsidy_*`, `GetWageSubsidyByExpenseId`)	`clsWageSubsidy` create/retrieve/update/delete
Maintain EAIP expense	Creates and updates expense rows (`eaip_expense_insert/update`)	`clsExpense` create/update
Maintain EAIP purchase	Creates, retrieves, updates, and deletes purchase rows tied to an expense (`eaip_purchase_*`, `GetPurchaseByExpenseId`)	`clsPurchase` create/retrieve/update/delete
Maintain EAIP pay period	Creates, retrieves, updates, and deletes pay-period rows (`eaip_pay_period_*`, `GetPayPeriodByPayPeriodId`)	`clsPayPeriod` create/retrieve/update/delete
Maintain EAIP reimbursement amount	Creates, retrieves, updates, and deletes reimbursement-amount rows by type (`EAIP_Reimb_Amount_*`, `GetReimbAmountbyType`)	`clsAmount` create/retrieve/update/delete
Maintain EAIP reimbursement detail amount	Creates, retrieves, updates, and deletes reimbursement detail-amount rows by type (`reimb_detail_amount_*`, `GetReimbDetailAmountbyType`)	`clsDetailAmount` create/retrieve/update/delete
Maintain EAIP reimbursement	Creates, retrieves, updates, and deletes reimbursements, and retrieves them by received date, posted date, or reconciled flag (`eaip_reimbursement_*`, `GetReimbByReceivedDate`, `GetReimbByPostedDate`, `GetReimbReconciledFlag`)	`clsReimbursement` create/retrieve/update/delete
Maintain EAIP reimbursement detail	Creates, retrieves, updates, and deletes reimbursement detail rows (`eaip_reimb_detail_*`, `GetReimbDetailByID`)	`clsReimbursementDetail` create/retrieve/update/delete
Maintain EAIP claim targeting	Creates, updates, and deletes claim-targeting rows and retrieves targeting by claim (`eaip_targeting_*`, `GetTargetingByClaim`)	`clsTargeting` create/update/delete/retrieve

These operations are also listed in Operations and processes.

Source Code¶

PL/SQL package specification

-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk82500_eaip
PACKAGE          pk82500_eaip AS

TYPE ref_cursor IS REF CURSOR;

/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_PERIOD"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION eaip_period_insert
(
     EaipPeriodID                  IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
    ,CLAIMID                       IN eaip_period.CLAIM_ID%TYPE
    ,EAIPUSER                      IN eaip_period.EAIP_USER%TYPE
    ,CLAIMACTIVITYDATE             IN eaip_period.CLAIM_ACTIVITY_DATE%TYPE
    ,EaipPeriodEffectiveDate       IN eaip_period.EAIP_PERIOD_EFF_DATE%TYPE
    ,EaipPeriodExpirationDate      IN eaip_period.EAIP_PERIOD_EXP_DATE%TYPE
    ,EndReasonCode                 IN eaip_period.EAIP_END_REASON_CODE%TYPE
    ,EAIPSTATUSCODE                IN eaip_period.EAIP_STATUS_CODE%TYPE
    ,EAIPSTATUSDATE                IN eaip_period.EAIP_STATUS_DATE%TYPE
    ,EAIPPERIODNOTETEXT            IN eaip_period.EAIP_PERIOD_NOTE_TEXT%TYPE
    ,REGULARJOBDUTIESTEXT          IN eaip_period.REGULAR_JOB_DUTIES_TEXT%TYPE
    ,MODIFIEDJOBDUTIESTEXT         IN eaip_period.MODIFIED_JOB_DUTIES_TEXT%TYPE
    ,AggravationPolicyID           IN eaip_period.AGGRAVATION_POLICY_NUM%TYPE
    ,AggravationProviderLocationID IN eaip_period.AGGRAVATION_PROV_LOC_SEQ_NUM%TYPE
    ,AggravationWcdID              IN eaip_period.AGGRAVATION_WCD_NUM%TYPE
    ,InterruptsClaimNumber         IN eaip_period.INTERRUPTS_CLAIM_NUMBER%TYPE
    ,InterruptedClaimNumber        IN eaip_period.INTERRUPTED_CLAIM_NUMBER%TYPE
    ,InterruptedEffectiveDate      IN eaip_period.INTERRUPTED_EFFECTIVE_DATE%TYPE
    ,InterruptedExpirationDate     IN eaip_period.INTERRUPTED_EXPIRATION_DATE%TYPE
    ,EarliestExpenseReceiveDate    IN eaip_period.EARLIEST_EXPENSE_RECEIVED_DATE%TYPE
    ,CREATEUSER                    IN eaip_period.CREATE_USER%TYPE
    ,CREATEDATE                    IN eaip_period.CREATE_DATE%TYPE
    ,UPDATEUSER                    IN eaip_period.UPDATE_USER%TYPE
    ,UPDATEDATE                    IN eaip_period.UPDATE_DATE%TYPE
    ,UPDATENUM                     IN eaip_period.UPDATE_NUM%TYPE
    ,PURCHASEJUSTIFICATION         IN EAIP_PERIOD.PURCHASE_JUSTIFICATION_TEXT%TYPE

) RETURN NUMBER;

FUNCTION eaip_period_update
(
     EaipPeriodID                  IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
    ,CLAIMID                       IN eaip_period.CLAIM_ID%TYPE
    ,EAIPUSER                      IN eaip_period.EAIP_USER%TYPE
    ,CLAIMACTIVITYDATE             IN eaip_period.CLAIM_ACTIVITY_DATE%TYPE
    ,EaipPeriodEffectiveDate       IN eaip_period.EAIP_PERIOD_EFF_DATE%TYPE
    ,EaipPeriodExpirationDate      IN eaip_period.EAIP_PERIOD_EXP_DATE%TYPE
    ,EndReasonCode                 IN eaip_period.EAIP_END_REASON_CODE%TYPE
    ,EAIPSTATUSCODE                IN eaip_period.EAIP_STATUS_CODE%TYPE
    ,EAIPSTATUSDATE                IN eaip_period.EAIP_STATUS_DATE%TYPE
    ,EAIPPERIODNOTETEXT            IN eaip_period.EAIP_PERIOD_NOTE_TEXT%TYPE
    ,REGULARJOBDUTIESTEXT          IN eaip_period.REGULAR_JOB_DUTIES_TEXT%TYPE
    ,MODIFIEDJOBDUTIESTEXT         IN eaip_period.MODIFIED_JOB_DUTIES_TEXT%TYPE
    ,AggravationPolicyID           IN eaip_period.AGGRAVATION_POLICY_NUM%TYPE
    ,AggravationProviderLocationID IN eaip_period.AGGRAVATION_PROV_LOC_SEQ_NUM%TYPE
    ,AggravationWcdID              IN eaip_period.AGGRAVATION_WCD_NUM%TYPE
    ,InterruptsClaimNumber         IN eaip_period.INTERRUPTS_CLAIM_NUMBER%TYPE
    ,InterruptedClaimNumber        IN eaip_period.INTERRUPTED_CLAIM_NUMBER%TYPE
    ,InterruptedEffectiveDate      IN eaip_period.INTERRUPTED_EFFECTIVE_DATE%TYPE
    ,InterruptedExpirationDate     IN eaip_period.INTERRUPTED_EXPIRATION_DATE%TYPE
    ,EarliestExpenseReceiveDate    IN eaip_period.EARLIEST_EXPENSE_RECEIVED_DATE%TYPE
    ,CREATEUSER                    IN eaip_period.CREATE_USER%TYPE
    ,CREATEDATE                    IN eaip_period.CREATE_DATE%TYPE
    ,UPDATEUSER                    IN eaip_period.UPDATE_USER%TYPE
    ,UPDATEDATE                    IN eaip_period.UPDATE_DATE%TYPE
    ,UPDATENUM                     IN eaip_period.UPDATE_NUM%TYPE
    ,LastUpdateNum                 IN eaip_period.UPDATE_NUM%TYPE
    ,PURCHASEJUSTIFICATION         IN EAIP_PERIOD.PURCHASE_JUSTIFICATION_TEXT%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_PERIOD"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION eaip_period_delete
(
    EaipPeriodID IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
  , LastUpdateNum              IN eaip_period.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
** Select PROCEDURE FOR TABLE "EAIP_PERIOD"
** DATE: 03-JUL-06 15:10:03
*********************************************/
PROCEDURE eaip_period_select
(
    EaipPeriodID IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE Default 0,
    ClaimID IN eaip_period.CLAIM_ID%TYPE default 0,
     recordset IN OUT ref_cursor);

-- Select credit card payments awaiting approval
PROCEDURE credit_payment_select
(  recordset IN OUT ref_cursor,
EaipUser IN varchar2);

/********************************************
** UPDATE the Status of an expense
** DATE: 16-DEC-10 15:10:03
*********************************************/
FUNCTION update_expense_status
(    ExpenseId                   IN EAIP_EXPENSE.EAIP_EXPENSE_SEQ_NUM%TYPE
    ,StatusCode                  IN EAIP_EXPENSE.EAIP_EXPENSE_STATUS_CODE%TYPE
    ,UPDATEUSER                  IN eaip_expense.UPDATE_USER%TYPE
    ,UPDATEDATE                  IN eaip_expense.UPDATE_DATE%TYPE
    ,UPDATENUM                   IN eaip_expense.UPDATE_NUM%TYPE
    ,LastUpdateNum               IN eaip_expense.UPDATE_NUM%TYPE
    ) RETURN NUMBER;


-- EAIP Billing Selects
PROCEDURE getBillingsByClaim
(  ClaimID IN varchar2,
   recordset IN OUT ref_cursor);

PROCEDURE GetBillingsByPeriod
(  PeriodID IN Number,
   recordset IN OUT ref_cursor);

PROCEDURE EAIP_Billing_Select
(  BillingID IN Number,
   recordset IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_BILLING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_billing_insert
(
     BillingID                  IN eaip_billing.eaip_billing_seq_num %TYPE
    ,BilledDate                 IN eaip_billing.billed_date %TYPE
    ,ClaimID                    IN eaip_billing.claim_id %TYPE
    ,PeriodID                   IN eaip_billing.eaip_period_seq_num %TYPE
    ,AdminFeeAmount             IN eaip_billing.admin_fee_amount %TYPE
    ,AfEaipReimbusementDetailID IN eaip_billing.af_eaip_reimb_detail_seq_num%TYPE
    ,BilledAmount               IN eaip_billing.billed_amount %TYPE
    ,TypeCode                   IN eaip_billing.eaip_billing_type_code %TYPE
    ,CreateUser                 IN eaip_billing.create_user%TYPE
    ,CreateDate                 IN eaip_billing.create_date%TYPE
    ,UpdateUser                 IN eaip_billing.update_user%TYPE
    ,UpdateDate                 IN eaip_billing.update_date%TYPE
    ,UpdateNum                  IN eaip_billing.update_num%TYPE

) RETURN NUMBER;

FUNCTION eaip_billing_update
(
     BillingID                  IN eaip_billing.eaip_billing_seq_num %TYPE
    ,BilledDate                 IN eaip_billing.billed_date %TYPE
    ,ClaimID                    IN eaip_billing.claim_id %TYPE
    ,PeriodID                   IN eaip_billing.eaip_period_seq_num %TYPE
    ,AdminFeeAmount             IN eaip_billing.admin_fee_amount %TYPE
    ,AfEaipReimbusementDetailID IN eaip_billing.af_eaip_reimb_detail_seq_num%TYPE
    ,BilledAmount               IN eaip_billing.billed_amount %TYPE
    ,TypeCode                   IN eaip_billing.eaip_billing_type_code %TYPE
    ,CreateUser                 IN eaip_billing.create_user%TYPE
    ,CreateDate                 IN eaip_billing.create_date%TYPE
    ,UpdateUser                 IN eaip_billing.update_user%TYPE
    ,UpdateDate                 IN eaip_billing.update_date%TYPE
    ,UpdateNum                  IN eaip_billing.update_num%TYPE
    ,LastUpdateNum              IN eaip_billing.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Billing"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_billing_delete
(
    EaipBillingID IN eaip_billing.EAIP_billing_SEQ_NUM%TYPE
  , LastUpdateNum              IN eaip_billing.UPDATE_NUM%TYPE
) RETURN NUMBER;


-- EAIP Expense Billing Selects
PROCEDURE GetExpenseBillingByBillingID
(  BillingID IN Number,
   recordset IN OUT ref_cursor);

   PROCEDURE GetExpenseBillingByExpenseID
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor);

 PROCEDURE GetNonReconciledBillings
(    recordset IN OUT ref_cursor);

PROCEDURE EAIP_Expense_Billing_Select
(   BillingID IN eaip_expense_billing.eaip_billing_seq_num%TYPE
  , ExpenseID IN eaip_expense_billing.eaip_expense_seq_num%TYPE
  , recordset IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_EXPENSE_BILLING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_expense_billing_insert
(
     BillingID             IN eaip_expense_billing.eaip_billing_seq_num%TYPE
    ,ExpenseID             IN eaip_expense_billing.eaip_expense_seq_num%TYPE
    ,BilledAmount          IN eaip_expense_billing.billed_amount%TYPE
    ,ReimbursedAmount      IN eaip_expense_billing.reimbursed_amount%TYPE
    ,ReimbursementDetailID IN eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_expense_billing.eaip_expense_type_code%TYPE
    ,CreateUser            IN eaip_expense_billing.create_user%TYPE
    ,CreateDate            IN eaip_expense_billing.create_date%TYPE
    ,UpdateUser            IN eaip_expense_billing.update_user%TYPE
    ,UpdateDate            IN eaip_expense_billing.update_date%TYPE
    ,UpdateNum             IN eaip_expense_billing.update_num%TYPE

) RETURN NUMBER;

FUNCTION eaip_expense_billing_update
(
     BillingID             IN eaip_expense_billing.eaip_billing_seq_num%TYPE
    ,ExpenseID             IN eaip_expense_billing.eaip_expense_seq_num%TYPE
    ,BilledAmount          IN eaip_expense_billing.billed_amount%TYPE
    ,ReimbursedAmount      IN eaip_expense_billing.reimbursed_amount%TYPE
    ,ReimbursementDetailID IN eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_expense_billing.eaip_expense_type_code%TYPE
    ,CreateUser            IN eaip_expense_billing.create_user%TYPE
    ,CreateDate            IN eaip_expense_billing.create_date%TYPE
    ,UpdateUser            IN eaip_expense_billing.update_user%TYPE
    ,UpdateDate            IN eaip_expense_billing.update_date%TYPE
    ,UpdateNum             IN eaip_expense_billing.update_num%TYPE
    ,LastUpdateNum              IN eaip_billing.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Expense_Billing"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_expense_billing_delete
(
    BillingID IN eaip_expense_billing.eaip_billing_seq_num%TYPE
  , ExpenseID IN eaip_expense_billing.eaip_expense_seq_num%TYPE
  , LastUpdateNum  IN eaip_expense_billing.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
**  SELECTS FOR EAIP_WAGE_SUBSIDY
*********************************************/
PROCEDURE EAIP_Wage_Subsidy_Select
(  EAIPPeriodID IN Number,
   recordset IN OUT ref_cursor);

PROCEDURE GetWageSubsidyByExpenseId
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_WAGE_SUBSIDY"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_wage_subsidy_insert
(
     EaipExpenseID      IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    ,EaipPeriodID       IN eaip_wage_subsidy.eaip_period_seq_num%TYPE
    ,EffectiveDate      IN eaip_wage_subsidy.wage_subsidy_eff_date%TYPE
    ,ExpirationDate     IN eaip_wage_subsidy.wage_subsidy_exp_date%TYPE
    ,InDays             IN eaip_wage_subsidy.wage_subsidy_days%TYPE
    ,GrossWagesAmount   IN eaip_wage_subsidy.gross_wages_amount%TYPE

) RETURN NUMBER;

FUNCTION eaip_wage_subsidy_update
(
     EaipExpenseID      IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    ,EaipPeriodID       IN eaip_wage_subsidy.eaip_period_seq_num%TYPE
    ,EffectiveDate      IN eaip_wage_subsidy.wage_subsidy_eff_date%TYPE
    ,ExpirationDate     IN eaip_wage_subsidy.wage_subsidy_exp_date%TYPE
    ,InDays             IN eaip_wage_subsidy.wage_subsidy_days%TYPE
    ,GrossWagesAmount   IN eaip_wage_subsidy.gross_wages_amount%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Wage_Subsidy"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_wage_subsidy_delete
(
    ExpenseID      IN eaip_expense.eaip_expense_seq_num%TYPE
  , LastUpdateNum  IN eaip_expense.UPDATE_NUM%TYPE
  ) RETURN NUMBER;

/********************************************************
** INSERT PROCEDURE FOR TABLE "EAIP_EXPENSE"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_expense_insert
(
     EaipExpenseID                   IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    , OriginalExpenseID              IN eaip_expense.orig_expense_seq_num%TYPE
    , ReceivedDate                   IN eaip_expense.received_date%TYPE
    , EaipTypeCode                   IN eaip_expense.eaip_expense_type_code%TYPE
    , EaipExpenseStatusCode          IN eaip_expense.eaip_expense_status_code%TYPE
    , ExpenseStatusDate              IN eaip_expense.expense_status_date%TYPE
    , WriteOffAmount                 IN eaip_expense.eaip_writeoff_amount%TYPE
    , WriteOffCode                   IN eaip_expense.eaip_writeoff_code%TYPE
    , EaipAdjustmentCode             IN eaip_expense.eaip_adjustment_code%TYPE
    , EaipAdjustmentAmount           IN eaip_expense.eaip_adjustment_amount%TYPE
    , EaipAdjustmentDate             IN eaip_expense.eaip_adjustment_date%TYPE
    , FinalReceiptDate               IN eaip_expense.final_receipt_date%TYPE
    , EaipExpenseDeniedCode          IN eaip_expense.eaip_expense_denied_code%TYPE
    , AmendmentText                  IN eaip_expense.ammend_explanation_text%TYPE
    , ExpenseText                    IN eaip_expense.expense_text%TYPE
    , PaidAmount                     IN eaip_expense.paid_amount%TYPE
    , NetPaymentAmount               IN EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type
    , PaidDate                       IN eaip_expense.paid_date%TYPE
    , CreateUser                     IN eaip_expense.create_user%TYPE
    , CreateDate                     IN eaip_expense.create_date%TYPE
    , UpdateUser                  IN eaip_expense.update_user%TYPE
    , UpdateDate                  IN eaip_expense.update_date%TYPE
    , UpdateNum                   IN eaip_expense.update_num%TYPE
    , PayeeType                   IN VARCHAR2
    , PolicyLocSeqNum             IN VARCHAR2
    , PersonId                    IN VARCHAR2
    , PaymentText                 IN VARCHAR2
    , AttentionLine               IN VARCHAR2
    , SpecialHandlingCode         IN VARCHAR2
    , IssueDate                   IN DATE
    , PayeeName                   IN VARCHAR2
    , inAddressLine1              IN VARCHAR2
    , inAddressLine2              IN VARCHAR2
    , inAddressLine3              IN VARCHAR2
    , inCity                      IN VARCHAR2
    , inState                     IN VARCHAR2
    , inZip                       IN VARCHAR2
    , inZipPlus4                  IN VARCHAR2
    , inCountryCode               IN VARCHAR2
) RETURN NUMBER;

FUNCTION eaip_expense_update
(
      EaipExpenseID               IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    , OriginalExpenseID           IN eaip_expense.orig_expense_seq_num%TYPE
    , ReceivedDate                   IN eaip_expense.received_date%TYPE
    , EaipTypeCode                   IN eaip_expense.eaip_expense_type_code%TYPE
    , EaipExpenseStatusCode          IN eaip_expense.eaip_expense_status_code%TYPE
    , ExpenseStatusDate              IN eaip_expense.expense_status_date%TYPE
    , WriteOffAmount                 IN eaip_expense.eaip_writeoff_amount%TYPE
    , WriteOffCode                   IN eaip_expense.eaip_writeoff_code%TYPE
    , EaipAdjustmentCode             IN eaip_expense.eaip_adjustment_code%TYPE
    , EaipAdjustmentAmount           IN eaip_expense.eaip_adjustment_amount%TYPE
    , EaipAdjustmentDate             IN eaip_expense.eaip_adjustment_date%TYPE
    , FinalReceiptDate               IN eaip_expense.final_receipt_date%TYPE
    , EaipExpenseDeniedCode          IN eaip_expense.eaip_expense_denied_code%TYPE
    , AmendmentText                  IN eaip_expense.ammend_explanation_text%TYPE
    , ExpenseText                    IN eaip_expense.expense_text%TYPE
    , PaidAmount                     IN eaip_expense.paid_amount%TYPE
    , NetPaymentAmount               IN EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type
    , PaidDate                       IN eaip_expense.paid_date%TYPE
    , CreateUser                     IN eaip_expense.create_user%TYPE
    , CreateDate                     IN eaip_expense.create_date%TYPE
    , UpdateUser                  IN eaip_expense.update_user%TYPE
    , UpdateDate                  IN eaip_expense.update_date%TYPE
    , UpdateNum                   IN eaip_expense.update_num%TYPE
    ,LastUpdateNum                IN eaip_expense.UPDATE_NUM%TYPE
    , PayeeType                   IN VARCHAR2
    , PolicyLocSeqNum             IN VARCHAR2
    , PersonId                    IN VARCHAR2
    , PaymentText                 IN VARCHAR2
    , AttentionLine               IN VARCHAR2
    , SpecialHandlingCode         IN VARCHAR2
    , IssueDate                   IN DATE
    , PayeeName                   IN VARCHAR2
    , inAddressLine1              IN VARCHAR2
    , inAddressLine2              IN VARCHAR2
    , inAddressLine3              IN VARCHAR2
    , inCity                      IN VARCHAR2
    , inState                     IN VARCHAR2
    , inZip                       IN VARCHAR2
    , inZipPlus4                  IN VARCHAR2
    , inCountryCode               IN VARCHAR2
) RETURN NUMBER;

/********************************************
**  SELECT FOR EAIP_PURCHASE
*********************************************/
PROCEDURE EAIP_Purchase_Select
(  EAIPPeriodID IN Number,
   recordset IN OUT ref_cursor);

PROCEDURE GetPurchaseByExpenseId
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_PURCHASE"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_purchase_insert
(
     EaipExpenseID              IN eaip_purchase.eaip_expense_seq_num%TYPE
    ,EaipPeriodID               IN eaip_purchase.eaip_period_seq_num%TYPE
    ,PurchaseDescription        IN eaip_purchase.purchase_descr%TYPE
    ,OrderDate                  IN eaip_purchase.order_date%TYPE
    ,DeliveryDate               IN eaip_purchase.delivery_date%TYPE
    ,ItemCostAmount             IN eaip_purchase.item_cost_amount%TYPE
    ,CreditCardPaidDate         IN eaip_purchase.credit_card_paid_date%TYPE
    ,CreditCardUser             IN eaip_purchase.credit_card_user%TYPE
    ,VendorBillRefText          IN eaip_purchase.vendor_bill_ref_text%TYPE

) RETURN NUMBER;

FUNCTION eaip_purchase_update
(
     EaipExpenseID              IN eaip_purchase.eaip_expense_seq_num%TYPE
    ,EaipPeriodID               IN eaip_purchase.eaip_period_seq_num%TYPE
    ,PurchaseDescription        IN eaip_purchase.purchase_descr%TYPE
    ,OrderDate                  IN eaip_purchase.order_date%TYPE
    ,DeliveryDate               IN eaip_purchase.delivery_date%TYPE
    ,ItemCostAmount             IN eaip_purchase.item_cost_amount%TYPE
    ,CreditCardPaidDate         IN eaip_purchase.credit_card_paid_date%TYPE
    ,CreditCardUser             IN eaip_purchase.credit_card_user%TYPE
    ,VendorBillRefText          IN eaip_purchase.vendor_bill_ref_text%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Purchase"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_purchase_delete
(
    ExpenseID      IN eaip_expense.eaip_expense_seq_num%TYPE
  , LastUpdateNum  IN eaip_expense.UPDATE_NUM%TYPE
  ) RETURN NUMBER;


/********************************************
**  SELECT FOR EAIP_PAYPERIOD
*********************************************/
PROCEDURE EAIP_PAY_PERIOD_Select
(  ExpenseId IN Number,
   recordset IN OUT ref_cursor);

PROCEDURE GetPayPeriodByPayPeriodId
(  PayPeriodID IN Number,
   recordset IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_PAY_PERIOD"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_pay_period_insert
(
     PayPeriodID               IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
    ,ExpenseID                 IN eaip_pay_period.eaip_expense_seq_num%TYPE
    ,EffectiveDate             IN eaip_pay_period.eaip_pay_period_eff_date%TYPE
    ,ExpirationDate            IN eaip_pay_period.eaip_pay_period_exp_date%TYPE
    ,RegularPayRate            IN eaip_pay_period.regular_pay_rate%TYPE
    ,OvertimePayRate           IN eaip_pay_period.overtime_pay_rate%TYPE
    ,OtherPayRate              IN eaip_pay_period.other_pay_rate%TYPE
    ,RegularHours              IN eaip_pay_period.regular_hours%TYPE
    ,OvertimeHours             IN eaip_pay_period.overtime_hours%TYPE
    , OtherHours               IN eaip_pay_period.other_hours%TYPE
    , RegularPayAmount          IN eaip_pay_period.regular_pay_amount%TYPE
    , OvertimePayAmount         IN eaip_pay_period.overtime_pay_amount%TYPE
    , OtherPayAmount            IN eaip_pay_period.other_pay_amount%TYPE
    , GrossWageAmount           IN eaip_pay_period.gross_wage_amount%TYPE
    , WorkerWageAmount          IN eaip_pay_period.worker_wage_amount%TYPE
    , UnitCode                  IN eaip_pay_period.cl_wage_period_pay_unit_code%TYPE
    , PotentialUnitQuantity     IN eaip_pay_period.potential_unit_quantity%TYPE
    , AveragePayRate            IN eaip_pay_period.average_pay_rate%TYPE
    , ActualUnitQuantity        IN eaip_pay_period.actual_unit_quantity%TYPE
    , PayPeriodDays             IN eaip_pay_period.pay_period_days%TYPE
    , CreateUser                IN eaip_pay_period.create_user%TYPE
    , CreateDate                IN eaip_pay_period.create_date%TYPE
    , UpdateUser                IN eaip_pay_period.update_user%TYPE
    , UpdateDate                IN eaip_pay_period.update_date%TYPE
    , UpdateNum                 IN eaip_pay_period.update_num%TYPE
) RETURN NUMBER;

FUNCTION eaip_pay_period_update
(
     PayPeriodID               IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
    ,ExpenseID                 IN eaip_pay_period.eaip_expense_seq_num%TYPE
    ,EffectiveDate             IN eaip_pay_period.eaip_pay_period_eff_date%TYPE
    ,ExpirationDate            IN eaip_pay_period.eaip_pay_period_exp_date%TYPE
    ,RegularPayRate            IN eaip_pay_period.regular_pay_rate%TYPE
    ,OvertimePayRate           IN eaip_pay_period.overtime_pay_rate%TYPE
    ,OtherPayRate              IN eaip_pay_period.other_pay_rate%TYPE
    ,RegularHours              IN eaip_pay_period.regular_hours%TYPE
    ,OvertimeHours             IN eaip_pay_period.overtime_hours%TYPE
    , OtherHours               IN eaip_pay_period.other_hours%TYPE
    , RegularPayAmount          IN eaip_pay_period.regular_pay_amount%TYPE
    , OvertimePayAmount         IN eaip_pay_period.overtime_pay_amount%TYPE
    , OtherPayAmount            IN eaip_pay_period.other_pay_amount%TYPE
    , GrossWageAmount           IN eaip_pay_period.gross_wage_amount%TYPE
    , WorkerWageAmount          IN eaip_pay_period.worker_wage_amount%TYPE
    , UnitCode                  IN eaip_pay_period.cl_wage_period_pay_unit_code%TYPE
    , PotentialUnitQuantity     IN eaip_pay_period.potential_unit_quantity%TYPE
    , AveragePayRate            IN eaip_pay_period.average_pay_rate%TYPE
    , ActualUnitQuantity        IN eaip_pay_period.actual_unit_quantity%TYPE
    , PayPeriodDays             IN eaip_pay_period.pay_period_days%TYPE
    , CreateUser                IN eaip_pay_period.create_user%TYPE
    , CreateDate                IN eaip_pay_period.create_date%TYPE
    , UpdateUser                IN eaip_pay_period.update_user%TYPE
    , UpdateDate                IN eaip_pay_period.update_date%TYPE
    , UpdateNum                 IN eaip_pay_period.update_num%TYPE
    ,LastUpdateNum              IN eaip_pay_period.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_PAY_PERIOD"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_pay_period_delete
(
     PayPeriodID   IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
  , LastUpdateNum  IN eaip_pay_period.UPDATE_NUM%TYPE
  ) RETURN NUMBER;

/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE EAIP_Reimb_Amount_Select
(   ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
  , recordset            IN OUT ref_cursor);

PROCEDURE GetReimbAmountbyType
(   ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
  , ExpenseTypeCode      IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
  , recordset            IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION EAIP_Reimb_Amount_insert
(
     ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,DisablingAmount       IN eaip_reimbursement_amount.disabling_amount%TYPE
    ,NonDisablingAmount   IN eaip_reimbursement_amount.nondisabling_amount%TYPE
    ,TotalAmount           IN eaip_reimbursement_amount.total_amount%TYPE
    ,CreateUser           IN eaip_reimbursement_amount.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_amount.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_amount.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_amount.update_date%TYPE
    ,UpdateNum             IN eaip_reimbursement_amount.update_num%TYPE

) RETURN NUMBER;

FUNCTION EAIP_Reimb_Amount_update
(
     ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,DisablingAmount       IN eaip_reimbursement_amount.disabling_amount%TYPE
    ,NonDisablingAmount   IN eaip_reimbursement_amount.nondisabling_amount%TYPE
    ,TotalAmount           IN eaip_reimbursement_amount.total_amount%TYPE
    ,CreateUser           IN eaip_reimbursement_amount.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_amount.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_amount.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_amount.update_date%TYPE
    ,UpdateNum             IN eaip_reimbursement_amount.update_num%TYPE
    ,LastUpdateNum         IN eaip_reimbursement_amount.UPDATE_NUM%TYPE
    ,LastExpenseTypeCode   IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:14-FEB-11
*********************************************/
FUNCTION EAIP_Reimb_Amount_delete
(
     ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
     ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,LastUpdateNum        IN eaip_reimbursement_amount.UPDATE_NUM%TYPE
) RETURN NUMBER;

/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE reimb_detail_amount_Select
(   ReimbursementDetailID      IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
  , recordset                  IN OUT ref_cursor);

  PROCEDURE GetReimbDetailAmountbyType
(   ReimbursementDetailID      IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
  , ExpenseTypeCode            IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
  , recordset                  IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION reimb_detail_amount_insert
(
     ReimbursementDetailID     IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode           IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,ReimbursedAmount          IN eaip_reimb_detail_amount.reimbursed_amount%TYPE
    ,CreateUser                IN eaip_reimb_detail_amount.create_user%TYPE
    ,CreateDate                IN eaip_reimb_detail_amount.create_date%TYPE
    ,UpdateUser                IN eaip_reimb_detail_amount.update_user%TYPE
    ,UpdateDate                IN eaip_reimb_detail_amount.update_date%TYPE
    ,UpdateNum                 IN eaip_reimb_detail_amount.update_num%TYPE

) RETURN NUMBER;

FUNCTION reimb_detail_amount_update
(
     ReimbursementDetailID     IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode           IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,ReimbursedAmount          IN eaip_reimb_detail_amount.reimbursed_amount%TYPE
    ,CreateUser                IN eaip_reimb_detail_amount.create_user%TYPE
    ,CreateDate                IN eaip_reimb_detail_amount.create_date%TYPE
    ,UpdateUser                IN eaip_reimb_detail_amount.update_user%TYPE
    ,UpdateDate                IN eaip_reimb_detail_amount.update_date%TYPE
    ,UpdateNum                 IN eaip_reimb_detail_amount.update_num%TYPE
    ,LastUpdateNum             IN eaip_reimb_detail_amount.UPDATE_NUM%TYPE
    ,LastExpenseTypeCode       IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:14-FEB-11
*********************************************/
FUNCTION reimb_detail_amount_delete
(
     ReimbursementDetailID      IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
     ,ExpenseTypeCode     IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,LastUpdateNum        IN eaip_reimb_detail_amount.UPDATE_NUM%TYPE
) RETURN NUMBER;


/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE eaip_reimbursement_Select
(   ReimbursementID            IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
  , recordset                  IN OUT ref_cursor);

PROCEDURE GetReimbByReceivedDate
(   ReceivedDate      IN date
   , recordset                  IN OUT ref_cursor);

PROCEDURE GetReimbByPostedDate
(  PostedDate      IN date
   , recordset                  IN OUT ref_cursor);

 PROCEDURE GetReimbReconciledFlag
(  ReconciledFlag      IN varchar2
   , recordset                  IN OUT ref_cursor);
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimbursement_insert
(
     ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,ReceivedDate         IN eaip_reimbursement.received_date%TYPE
    ,PostedDate           IN eaip_reimbursement.posted_date%TYPE
    ,ReconciledDate       IN eaip_reimbursement.reconciled_date%TYPE
    ,IsReconciled         IN eaip_reimbursement.reconciled_flag%TYPE
    ,ReimbursementAmount  IN eaip_reimbursement.reimbursement_amount%TYPE
    ,ReconciledAmount     IN eaip_reimbursement.reconciled_amount%TYPE
    ,ExplanationText      IN eaip_reimbursement.explanation_text%TYPE
    ,CreateUser           IN eaip_reimbursement.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement.update_num%TYPE

) RETURN NUMBER;

FUNCTION eaip_reimbursement_update
(
    ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,ReceivedDate         IN eaip_reimbursement.received_date%TYPE
    ,PostedDate           IN eaip_reimbursement.posted_date%TYPE
    ,ReconciledDate       IN eaip_reimbursement.reconciled_date%TYPE
    ,IsReconciled         IN eaip_reimbursement.reconciled_flag%TYPE
    ,ReimbursementAmount  IN eaip_reimbursement.reimbursement_amount%TYPE
    ,ReconciledAmount     IN eaip_reimbursement.reconciled_amount%TYPE
    ,ExplanationText      IN eaip_reimbursement.explanation_text%TYPE
    ,CreateUser           IN eaip_reimbursement.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement.update_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.update_num%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimbursement_delete
(
     ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.UPDATE_NUM%TYPE
) RETURN NUMBER;


/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:  DATE:02-Nov-21
*********************************************/

PROCEDURE eaip_reimb_detail_Select
(   ReimbursementID            IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
  , recordset                  IN OUT ref_cursor);

PROCEDURE GetReimbDetailByID
(   ReimbursementDetailID      IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
  , recordset                  IN OUT ref_cursor);

/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimb_detail_insert
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,DcbsSaifClaimNum        IN eaip_reimbursement_detail.dcbs_saif_claim_num%TYPE
    ,DcbsWcdClaimNum         IN eaip_reimbursement_detail.dcbs_wcd_claim_num%TYPE
    ,WorkerName              IN eaip_reimbursement_detail.worker_name%TYPE
    ,RequestReceivedDate     IN eaip_reimbursement_detail.request_received_date%TYPE
    ,InjuryDate              IN eaip_reimbursement_detail.injury_date%TYPE
    ,TotalAmount             IN eaip_reimbursement_detail.detail_total_amount%TYPE
    ,IsDisablingClaim        IN eaip_reimbursement_detail.disabling_claim_flag%TYPE
    ,IsReconciled            IN eaip_reimbursement_detail.reconciled_flag%TYPE
    ,HasMatchingName         IN eaip_reimbursement_detail.name_matches_flag%TYPE
    ,CreateUser           IN eaip_reimbursement_detail.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_detail.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_detail.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_detail.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement_detail.update_num%TYPE

) RETURN NUMBER;

FUNCTION eaip_reimb_detail_update
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,DcbsSaifClaimNum        IN eaip_reimbursement_detail.dcbs_saif_claim_num%TYPE
    ,DcbsWcdClaimNum         IN eaip_reimbursement_detail.dcbs_wcd_claim_num%TYPE
    ,WorkerName              IN eaip_reimbursement_detail.worker_name%TYPE
    ,RequestReceivedDate     IN eaip_reimbursement_detail.request_received_date%TYPE
    ,InjuryDate              IN eaip_reimbursement_detail.injury_date%TYPE
    ,TotalAmount             IN eaip_reimbursement_detail.detail_total_amount%TYPE
    ,IsDisablingClaim        IN eaip_reimbursement_detail.disabling_claim_flag%TYPE
    ,IsReconciled            IN eaip_reimbursement_detail.reconciled_flag%TYPE
    ,HasMatchingName         IN eaip_reimbursement_detail.name_matches_flag%TYPE
    ,CreateUser           IN eaip_reimbursement_detail.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_detail.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_detail.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_detail.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement_detail.update_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.update_num%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimb_detail_delete
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.UPDATE_NUM%TYPE
) RETURN NUMBER;


PROCEDURE GetTargetingByClaim_New
(   recordset                IN  OUT ref_cursor,
    ClaimID           IN eaip_claim_targeting.Claim_id%TYPE);

/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_CLAIM_TARGETING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_targeting_insert
(
     TargetID                    IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    ,ClaimID                     IN eaip_claim_targeting.claim_id%TYPE
    ,CompletedDate               IN eaip_claim_targeting.completed_date%TYPE
    ,EaipTargetingCode           IN eaip_claim_targeting.eaip_targeting_code%TYPE
    ,WageSubsidyEffectiveDate    IN eaip_claim_targeting.wage_subsidy_eff_date%TYPE
    ,WageSubsidyExpirationDate   IN eaip_claim_targeting.wage_subsidy_exp_date%TYPE
    ,ReviewerUser                IN eaip_claim_targeting.reviewer_user%TYPE
    ,TargetingText               IN eaip_claim_targeting.targeting_text%TYPE
    ,ClaimStatusDate             IN eaip_claim_targeting.CLAIM_STATUS_EFF_DATE%TYPE
    ,ClaimStatusCode             IN eaip_claim_targeting.CLAIM_STATUS_CODE%TYPE
    ,CreateUser                  IN eaip_claim_targeting.create_user%TYPE
    ,CreateDate                  IN eaip_claim_targeting.create_date%TYPE
    ,UpdateUser                  IN eaip_claim_targeting.update_user%TYPE
    ,UpdateDate                  IN eaip_claim_targeting.update_date%TYPE
    ,UpdateNum                   IN eaip_claim_targeting.update_num%TYPE

) RETURN NUMBER;

FUNCTION eaip_targeting_update
(
     TargetID                    IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    ,ClaimID                     IN eaip_claim_targeting.claim_id%TYPE
    ,CompletedDate               IN eaip_claim_targeting.completed_date%TYPE
    ,EaipTargetingCode           IN eaip_claim_targeting.eaip_targeting_code%TYPE
    ,WageSubsidyEffectiveDate    IN eaip_claim_targeting.wage_subsidy_eff_date%TYPE
    ,WageSubsidyExpirationDate   IN eaip_claim_targeting.wage_subsidy_exp_date%TYPE
    ,ReviewerUser                IN eaip_claim_targeting.reviewer_user%TYPE
    ,TargetingText               IN eaip_claim_targeting.targeting_text%TYPE
    ,ClaimStatusDate             IN eaip_claim_targeting.CLAIM_STATUS_EFF_DATE%TYPE
    ,ClaimStatusCode             IN eaip_claim_targeting.CLAIM_STATUS_CODE%TYPE
    ,CreateUser                  IN eaip_claim_targeting.create_user%TYPE
    ,CreateDate                  IN eaip_claim_targeting.create_date%TYPE
    ,UpdateUser                  IN eaip_claim_targeting.update_user%TYPE
    ,UpdateDate                  IN eaip_claim_targeting.update_date%TYPE
    ,UpdateNum                   IN eaip_claim_targeting.update_num%TYPE
    ,LastUpdateNum               IN eaip_claim_targeting.update_num%TYPE
) RETURN NUMBER;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_CLAIM_TARGETING"
** DATE:16-FEB-11
*********************************************/
FUNCTION eaip_targeting_delete
(
     TargetID                  IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    ,LastUpdateNum             IN eaip_claim_targeting.update_num%TYPE
) RETURN NUMBER;


/********************************************
** SELECT PROCEDURE FOR EAIP EXPENSES IN CHECK STATUS FOR BILLING
** DATE:  DATE:23-FEB-11
*********************************************/

PROCEDURE SelectExpensesInCheckStatus
(   recordset                  IN OUT ref_cursor);

/********************************************
** SELECT PROCEDURE FOR EAIP EXPENSES IN SELECTED STATUS
** DATE:  DATE:07-APR-2011
*********************************************/

PROCEDURE SelectExpensesByStatus_GW
(   recordset                  OUT ref_cursor,
    ExpenseStatus              IN varchar2,
    Specialist                 IN varchar2);

PROCEDURE EAIPHeaderBySeqNumAndClaimID
(   recordset                  OUT ref_cursor,
    ServicePeriodSeqNum        IN Number,
    ClaimID                   IN NUMBER);

END pk82500_eaip;
 /

PL/SQL package/procedure body

-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk82500_eaip
PACKAGE BODY          Pk82500_eaip AS

FUNCTION eaip_period_insert
(
     EaipPeriodID                  IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
    ,CLAIMID                       IN eaip_period.CLAIM_ID%TYPE
    ,EAIPUSER                      IN eaip_period.EAIP_USER%TYPE
    ,CLAIMACTIVITYDATE             IN eaip_period.CLAIM_ACTIVITY_DATE%TYPE
    ,EaipPeriodEffectiveDate       IN eaip_period.EAIP_PERIOD_EFF_DATE%TYPE
    ,EaipPeriodExpirationDate      IN eaip_period.EAIP_PERIOD_EXP_DATE%TYPE
    ,EndReasonCode                 IN eaip_period.EAIP_END_REASON_CODE%TYPE
    ,EAIPSTATUSCODE                IN eaip_period.EAIP_STATUS_CODE%TYPE
    ,EAIPSTATUSDATE                IN eaip_period.EAIP_STATUS_DATE%TYPE
    ,EAIPPERIODNOTETEXT            IN eaip_period.EAIP_PERIOD_NOTE_TEXT%TYPE
    ,REGULARJOBDUTIESTEXT          IN eaip_period.REGULAR_JOB_DUTIES_TEXT%TYPE
    ,MODIFIEDJOBDUTIESTEXT         IN eaip_period.MODIFIED_JOB_DUTIES_TEXT%TYPE
    ,AggravationPolicyID           IN eaip_period.AGGRAVATION_POLICY_NUM%TYPE
    ,AggravationProviderLocationID IN eaip_period.AGGRAVATION_PROV_LOC_SEQ_NUM%TYPE
    ,AggravationWcdID              IN eaip_period.AGGRAVATION_WCD_NUM%TYPE
    ,InterruptsClaimNumber         IN eaip_period.INTERRUPTS_CLAIM_NUMBER%TYPE
    ,InterruptedClaimNumber        IN eaip_period.INTERRUPTED_CLAIM_NUMBER%TYPE
    ,InterruptedEffectiveDate      IN eaip_period.INTERRUPTED_EFFECTIVE_DATE%TYPE
    ,InterruptedExpirationDate     IN eaip_period.INTERRUPTED_EXPIRATION_DATE%TYPE
    ,EarliestExpenseReceiveDate    IN eaip_period.EARLIEST_EXPENSE_RECEIVED_DATE%TYPE
    ,CREATEUSER                    IN eaip_period.CREATE_USER%TYPE
    ,CREATEDATE                    IN eaip_period.CREATE_DATE%TYPE
    ,UPDATEUSER                    IN eaip_period.UPDATE_USER%TYPE
    ,UPDATEDATE                    IN eaip_period.UPDATE_DATE%TYPE
    ,UPDATENUM                     IN eaip_period.UPDATE_NUM%TYPE
    ,PURCHASEJUSTIFICATION         IN EAIP_PERIOD.PURCHASE_JUSTIFICATION_TEXT%TYPE
   ) RETURN NUMBER
IS

v_update_count       NUMBER;

BEGIN

INSERT
INTO   eaip_period
       (
              eaip_period_seq_num         ,
              claim_id                    ,
              eaip_user                   ,
              claim_activity_date         ,
              eaip_period_eff_date        ,
              eaip_period_exp_date        ,
              eaip_end_reason_code        ,
              eaip_status_code            ,
              eaip_status_date            ,
              eaip_period_note_text       ,
              regular_job_duties_text     ,
              modified_job_duties_text    ,
              aggravation_policy_num      ,
              aggravation_prov_loc_seq_num,
              aggravation_wcd_num         ,
              interrupts_claim_number     ,
              interrupted_claim_number    ,
              interrupted_effective_date  ,
              interrupted_expiration_date ,
              earliest_expense_received_date,
              create_user                 ,
              create_date                 ,
              update_user                 ,
              update_date                 ,
              update_num                  ,
              purchase_justification_text
       )
       VALUES
       (
              EaipPeriodID                 ,
              ClaimID                      ,
              EaipUser                     ,
              ClaimActivityDate            ,
              EaipPeriodEffectiveDate      ,
              EaipPeriodExpirationDate     ,
              EndReasonCode                ,
              EaipStatusCode               ,
              EaipStatusDate               ,
              EaipPeriodNoteText           ,
              RegularJobDutiesText         ,
              ModifiedJobDutiesText        ,
              AggravationPolicyID          ,
              AggravationProviderLocationID,
              AggravationWcdID             ,
              InterruptsClaimNumber        ,
              InterruptedClaimNumber       ,
              InterruptedEffectiveDate     ,
              InterruptedExpirationDate    ,
              EarliestExpenseReceiveDate   ,
              CreateUser                   ,
              CreateDate                   ,
              UpdateUser                   ,
              UpdateDate                   ,
              UpdateNum,
              PurchaseJustification);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_period_insert;

/********************************************
** UPDATE PROCEDURE FOR TABLE "EAIP_PERIOD"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION eaip_period_update
(
     EaipPeriodID                  IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
    ,CLAIMID                       IN eaip_period.CLAIM_ID%TYPE
    ,EAIPUSER                      IN eaip_period.EAIP_USER%TYPE
    ,CLAIMACTIVITYDATE             IN eaip_period.CLAIM_ACTIVITY_DATE%TYPE
    ,EaipPeriodEffectiveDate       IN eaip_period.EAIP_PERIOD_EFF_DATE%TYPE
    ,EaipPeriodExpirationDate      IN eaip_period.EAIP_PERIOD_EXP_DATE%TYPE
    ,EndReasonCode                 IN eaip_period.EAIP_END_REASON_CODE%TYPE
    ,EAIPSTATUSCODE                IN eaip_period.EAIP_STATUS_CODE%TYPE
    ,EAIPSTATUSDATE                IN eaip_period.EAIP_STATUS_DATE%TYPE
    ,EAIPPERIODNOTETEXT            IN eaip_period.EAIP_PERIOD_NOTE_TEXT%TYPE
    ,REGULARJOBDUTIESTEXT          IN eaip_period.REGULAR_JOB_DUTIES_TEXT%TYPE
    ,ModifiedJobDutiesText         IN eaip_period.MODIFIED_JOB_DUTIES_TEXT%TYPE
    ,AggravationPolicyID           IN eaip_period.AGGRAVATION_POLICY_NUM%TYPE
    ,AggravationProviderLocationID IN eaip_period.AGGRAVATION_PROV_LOC_SEQ_NUM%TYPE
    ,AggravationWcdID              IN eaip_period.AGGRAVATION_WCD_NUM%TYPE
    ,InterruptsClaimNumber         IN eaip_period.INTERRUPTS_CLAIM_NUMBER%TYPE
    ,InterruptedClaimNumber        IN eaip_period.INTERRUPTED_CLAIM_NUMBER%TYPE
    ,InterruptedEffectiveDate      IN eaip_period.INTERRUPTED_EFFECTIVE_DATE%TYPE
    ,InterruptedExpirationDate     IN eaip_period.INTERRUPTED_EXPIRATION_DATE%TYPE
    ,EarliestExpenseReceiveDate    IN eaip_period.EARLIEST_EXPENSE_RECEIVED_DATE%TYPE
    ,CREATEUSER                    IN eaip_period.CREATE_USER%TYPE
    ,CREATEDATE                    IN eaip_period.CREATE_DATE%TYPE
    ,UPDATEUSER                    IN eaip_period.UPDATE_USER%TYPE
    ,UPDATEDATE                    IN eaip_period.UPDATE_DATE%TYPE
    ,UPDATENUM                     IN eaip_period.UPDATE_NUM%TYPE
    ,LastUpdateNum                 IN eaip_period.UPDATE_NUM%TYPE
    ,PURCHASEJUSTIFICATION         IN EAIP_PERIOD.PURCHASE_JUSTIFICATION_TEXT%TYPE
) RETURN NUMBER
as
v_update_count       NUMBER;
BEGIN
   UPDATE eaip_period
SET    eaip_period_seq_num          = EaipPeriodID                 ,
       claim_id                     = ClaimID                      ,
       eaip_user                    = EaipUser                     ,
       claim_activity_date          = ClaimActivityDate            ,
       eaip_period_eff_date         = EaipPeriodEffectiveDate      ,
       eaip_period_exp_date         = EaipPeriodExpirationDate     ,
       eaip_end_reason_code         = EndReasonCode                ,
       eaip_status_code             = EaipStatusCode               ,
       eaip_status_date             = EaipStatusDate               ,
       eaip_period_note_text        = EaipPeriodNoteText           ,
       regular_job_duties_text      = RegularJobDutiesText         ,
       modified_job_duties_text     = ModifiedJobDutiesText        ,
       aggravation_policy_num       = AggravationPolicyID          ,
       aggravation_prov_loc_seq_num = AggravationProviderLocationID,
       aggravation_wcd_num          = AggravationWcdID             ,
       interrupts_claim_number      = InterruptsClaimNumber        ,
       interrupted_claim_number     = InterruptedClaimNumber       ,
       interrupted_effective_date   = InterruptedEffectiveDate     ,
       interrupted_expiration_date  = InterruptedExpirationDate    ,
       earliest_expense_received_date = EarliestExpenseReceiveDate ,
       create_user                  = CreateUser                   ,
       create_date                  = CreateDate                   ,
       update_user                  = UpdateUser                   ,
       update_date                  = UpdateDate                   ,
       update_num                   = UpdateNum                    ,
       PURCHASE_JUSTIFICATION_TEXT  = PURCHASEJUSTIFICATION
       WHERE  eaip_period_seq_num   = EaipPeriodID
         AND    update_num          = LastUpdateNum;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_period_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_PERIOD"
** DATE: 03-JUL-06 15:10:03
*********************************************/
FUNCTION eaip_period_delete
(
    EaipPeriodID               IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE
  , LastUpdateNum              IN eaip_period.UPDATE_NUM%TYPE
)
RETURN NUMBER
as
v_update_count       NUMBER;


begin

delete from eaip_period
where eaip_period_seq_num = EaipPeriodID
  and update_num = LastUpdateNum;
 v_update_count := SQL%Rowcount;

delete from eaip_expense x
where X.EAIP_EXPENSE_TYPE_CODE <> 'W'
and not exists (select 1 from eaip_purchase w
where W.EAIP_EXPENSE_SEQ_NUM = X.EAIP_EXPENSE_SEQ_NUM);

delete from eaip_expense x
where X.EAIP_EXPENSE_TYPE_CODE = 'W'
and not exists (select 1 from eaip_wage_subsidy w
where W.EAIP_EXPENSE_SEQ_NUM = X.EAIP_EXPENSE_SEQ_NUM);

 RETURN v_update_count;
End eaip_period_delete;

PROCEDURE eaip_period_select
(  EaipPeriodID IN eaip_period.EAIP_PERIOD_SEQ_NUM%TYPE default 0,
   ClaimID IN eaip_period.CLAIM_ID%TYPE default 0,
   recordset IN OUT ref_cursor)
Is
Begin open recordset for
  SELECT eaip_period_seq_num          as EaipPeriodID                 ,
       claim_id                       as ClaimID                      ,
       eaip_user                      as EaipUser                     ,
       claim_activity_date            as ClaimActivityDate            ,
       eaip_period_eff_date           as EaipPeriodEffectiveDate      ,
       eaip_period_exp_date           as EaipPeriodExpirationDate     ,
       eaip_end_reason_code           as EndReasonCode                ,
       eaip_status_code               as EaipStatusCode               ,
       eaip_status_date               as EaipStatusDate               ,
       eaip_period_note_text          as EaipPeriodNoteText           ,
       regular_job_duties_text        as RegularJobDutiesText         ,
       modified_job_duties_text       as ModifiedJobDutiesText        ,
       purchase_justification_text    as PurchaseJustification,
       aggravation_policy_num         as AggravationPolicyID          ,
       aggravation_prov_loc_seq_num   as AggravationProviderLocationID,
       aggravation_wcd_num            as AggravationWcdID             ,
       interrupts_claim_number        as InterruptsClaimNumber        ,
       interrupted_claim_number       as InterruptedClaimNumber       ,
       interrupted_effective_date     as InterruptedEffectiveDate     ,
       interrupted_expiration_date    as InterruptedExpirationDate    ,
       earliest_expense_received_date as EarliestExpenseReceiveDate   ,
       create_user                    as CreateUser                   ,
       create_date                    as CreateDate                   ,
       update_user                    as UpdateUser                   ,
       update_date                    as UpdateDate                   ,
       update_num                     as UpdateNum
       FROM   eaip_period
       where (eaip_period_seq_num = EaipPeriodID or EaipPeriodID = 0 )
       and   (claim_id = ClaimID or ClaimID = 0 );

End eaip_period_select;

-- Select credit card payments awaiting approval
PROCEDURE credit_payment_select
(recordset IN OUT ref_cursor,
EaipUser IN varchar2)
Is
Begin open recordset for
  select pr.credit_card_paid_date AS CreditCardPaidDate,
         ep.claim_id AS ClaimId,
       TC.CODE_DESCR as ExpenseTypeDesc,
       pr.purchase_descr as Description,
       PR.VENDOR_BILL_REF_TEXT as VendorInvoiceNum,
       ex.paid_amount as PaidAmount,
       EX.EAIP_EXPENSE_SEQ_NUM as ExpenseId,
       EX.UPDATE_NUM as UpdateNum
from eaip_expense ex, eaip_purchase pr, eaip_period ep,
      eaip_expense_type_code tc
where
  PR.EAIP_PERIOD_SEQ_NUM = EP.EAIP_PERIOD_SEQ_NUM
  and PR.EAIP_EXPENSE_SEQ_NUM = EX.EAIP_EXPENSE_SEQ_NUM
 -- and EX.EAIP_EXPENSE_STATUS_CODE = 'PSUP'
  and PR.CREDIT_CARD_PAID_DATE is not null
   and EX.EAIP_EXPENSE_TYPE_CODE = TC.CODE
  and PR.CREDIT_CARD_USER = EaipUser;

End credit_payment_select;

/********************************************
** UPDATE the Status of an expense
** DATE: 16-DEC-10 15:10:03
*********************************************/
FUNCTION update_expense_status
(    ExpenseId                   IN EAIP_EXPENSE.EAIP_EXPENSE_SEQ_NUM%TYPE
    ,StatusCode                  IN EAIP_EXPENSE.EAIP_EXPENSE_STATUS_CODE%TYPE
    ,UPDATEUSER                  IN eaip_expense.UPDATE_USER%TYPE
    ,UPDATEDATE                  IN eaip_expense.UPDATE_DATE%TYPE
    ,UPDATENUM                   IN eaip_expense.UPDATE_NUM%TYPE
    ,LastUpdateNum               IN eaip_expense.UPDATE_NUM%TYPE
    ) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
   UPDATE eaip_expense ex
    set EX.EAIP_EXPENSE_STATUS_CODE = StatusCode,
        EX.EXPENSE_STATUS_DATE = UPDATEDATE,
        EX.UPDATE_DATE = UPDATEDATE,
        EX.UPDATE_USER = UPDATEUSER,
        EX.UPDATE_NUM = UPDATENUM
  where EX.EAIP_EXPENSE_SEQ_NUM = ExpenseId
    AND EX.update_num = LastUpdateNum;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END update_expense_status;


-- EAIP Billing Selects
PROCEDURE getBillingsByClaim
(  ClaimID IN varchar2,
   recordset IN OUT ref_cursor)
   Is
Begin open recordset for
SELECT eaip_billing_seq_num         as BillingID                 ,
       billed_date                  as BilledDate                ,
       claim_id                     as ClaimID                   ,
       eaip_period_seq_num          as PeriodID                   ,
       admin_fee_amount             as AdminFeeAmount            ,
       af_eaip_reimb_detail_seq_num as AfEaipReimbusementDetailID,
       billed_amount                as BilledAmount              ,
       eaip_billing_type_code       as TypeCode                  ,
       create_user                  as CreateUser                ,
       create_date                  as CreateDate                ,
       update_user                  as UpdateUser                ,
       update_date                  as UpdateDate                ,
       update_num                   as UpdateNum
 FROM   eaip_billing
 WHERE  claim_id = ClaimID ORDER BY billed_date;

 End getBillingsByClaim;

PROCEDURE GetBillingsByPeriod
(  PeriodID IN Number,
   recordset IN OUT ref_cursor)
      Is
Begin open recordset for
SELECT eaip_billing_seq_num         as BillingID                 ,
       billed_date                  as BilledDate                ,
       claim_id                     as ClaimID                   ,
       eaip_period_seq_num          as PeriodID                   ,
       admin_fee_amount             as AdminFeeAmount            ,
       af_eaip_reimb_detail_seq_num as AfEaipReimbusementDetailID,
       billed_amount                as BilledAmount              ,
       eaip_billing_type_code       as TypeCode                  ,
       create_user                  as CreateUser                ,
       create_date                  as CreateDate                ,
       update_user                  as UpdateUser                ,
       update_date                  as UpdateDate                ,
       update_num                   as UpdateNum
FROM   eaip_billing
WHERE  eaip_period_seq_num = PeriodID ORDER BY billed_date;
End GetBillingsByPeriod;

PROCEDURE EAIP_Billing_Select
(  BillingID IN Number,
   recordset IN OUT ref_cursor)
      Is
Begin open recordset for
SELECT eaip_billing_seq_num         as BillingID                 ,
       billed_date                  as BilledDate                ,
       claim_id                     as ClaimID                   ,
       eaip_period_seq_num          as PeriodID                   ,
       admin_fee_amount             as AdminFeeAmount            ,
       af_eaip_reimb_detail_seq_num as AfEaipReimbusementDetailID,
       billed_amount                as BilledAmount              ,
       eaip_billing_type_code       as TypeCode                  ,
       create_user                  as CreateUser                ,
       create_date                  as CreateDate                ,
       update_user                  as UpdateUser                ,
       update_date                  as UpdateDate                ,
       update_num                   as UpdateNum
FROM   eaip_billing
WHERE  eaip_billing_seq_num = BillingID;
End EAIP_Billing_Select;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_BILLING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_billing_insert
(
     BillingID                  IN eaip_billing.eaip_billing_seq_num %TYPE
    ,BilledDate                 IN eaip_billing.billed_date %TYPE
    ,ClaimID                    IN eaip_billing.claim_id %TYPE
    ,PeriodID                   IN eaip_billing.eaip_period_seq_num %TYPE
    ,AdminFeeAmount             IN eaip_billing.admin_fee_amount %TYPE
    ,AfEaipReimbusementDetailID IN eaip_billing.af_eaip_reimb_detail_seq_num%TYPE
    ,BilledAmount               IN eaip_billing.billed_amount %TYPE
    ,TypeCode                   IN eaip_billing.eaip_billing_type_code %TYPE
    ,CreateUser                 IN eaip_billing.create_user%TYPE
    ,CreateDate                 IN eaip_billing.create_date%TYPE
    ,UpdateUser                 IN eaip_billing.update_user%TYPE
    ,UpdateDate                 IN eaip_billing.update_date%TYPE
    ,UpdateNum                  IN eaip_billing.update_num%TYPE
       ) RETURN NUMBER
IS

v_update_count             NUMBER;

BEGIN
INSERT INTO   eaip_billing
       (
               eaip_billing_seq_num
             , billed_date
             , claim_id
             , eaip_period_seq_num
             , admin_fee_amount
             , af_eaip_reimb_detail_seq_num
             , billed_amount
             , eaip_billing_type_code
             , create_user
             , create_date
             , update_user
             , update_date
             , update_num
       )
       VALUES
       (
              BillingID                 ,
              BilledDate                ,
              ClaimID                   ,
              PeriodID                  ,
              AdminFeeAmount            ,
              AfEaipReimbusementDetailID,
              BilledAmount              ,
              TypeCode                  ,
              CreateUser                ,
              CreateDate                ,
              UpdateUser                ,
              UpdateDate                ,
              UpdateNum
              );
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_billing_insert;

FUNCTION eaip_billing_update
(
     BillingID                  IN eaip_billing.eaip_billing_seq_num %TYPE
    ,BilledDate                 IN eaip_billing.billed_date %TYPE
    ,ClaimID                    IN eaip_billing.claim_id %TYPE
    ,PeriodID                   IN eaip_billing.eaip_period_seq_num %TYPE
    ,AdminFeeAmount             IN eaip_billing.admin_fee_amount %TYPE
    ,AfEaipReimbusementDetailID IN eaip_billing.af_eaip_reimb_detail_seq_num%TYPE
    ,BilledAmount               IN eaip_billing.billed_amount %TYPE
    ,TypeCode                   IN eaip_billing.eaip_billing_type_code %TYPE
    ,CreateUser                 IN eaip_billing.create_user%TYPE
    ,CreateDate                 IN eaip_billing.create_date%TYPE
    ,UpdateUser                 IN eaip_billing.update_user%TYPE
    ,UpdateDate                 IN eaip_billing.update_date%TYPE
    ,UpdateNum                  IN eaip_billing.update_num%TYPE
    ,LastUpdateNum              IN eaip_billing.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

UPDATE eaip_billing
SET    eaip_billing_seq_num         = BillingID                 ,
       billed_date                  = BilledDate                ,
       claim_id                     = ClaimID                   ,
       eaip_period_seq_num          = PeriodID                  ,
       admin_fee_amount             = AdminFeeAmount            ,
       af_eaip_reimb_detail_seq_num = AfEaipReimbusementDetailID,
       billed_amount                = BilledAmount              ,
       eaip_billing_type_code       = TypeCode                  ,
       create_user                  = CreateUser                ,
       create_date                  = CreateDate                ,
       update_user                  = UpdateUser                ,
       update_date                  = UpdateDate                ,
       update_num                   = UpdateNum
WHERE  eaip_billing_seq_num      = BillingID
AND    update_num                = LastUpdateNum;
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_billing_update;

/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Billing"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_billing_delete
(
    EaipBillingID IN eaip_billing.EAIP_billing_SEQ_NUM%TYPE
  , LastUpdateNum              IN eaip_billing.UPDATE_NUM%TYPE)
    RETURN NUMBER
as
v_update_count             NUMBER;

begin

delete from eaip_billing
where eaip_billing_seq_num = EaipBillingID
  and update_num = LastUpdateNum;
 v_update_count := SQL%Rowcount;


 RETURN v_update_count;
End eaip_billing_delete;

-- EAIP Expense Billing Selects
PROCEDURE GetExpenseBillingByBillingID
(  BillingID IN Number,
   recordset IN OUT ref_cursor)
      Is
Begin open recordset for
   SELECT eaip_billing_seq_num   as BillingID            ,
       eaip_expense_seq_num      as ExpenseID            ,
       billed_amount             as BilledAmount         ,
       reimbursed_amount         as ReimbursedAmount     ,
       eaip_reimb_detail_seq_num as ReimbursementDetailID,
       eaip_expense_type_code    as ExpenseTypeCode      ,
       create_user               as CreateUser           ,
       create_date               as CreateDate           ,
       update_user               as UpdateUser           ,
       update_date               as UpdateDate           ,
       update_num                as UpdateNum
   FROM   eaip_expense_billing
   WHERE eaip_billing_seq_num = BillingID;

END GetExpenseBillingByBillingID;

PROCEDURE GetExpenseBillingByExpenseID
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor)
      Is
Begin open recordset for
   SELECT eaip_billing_seq_num   as BillingID            ,
       eaip_expense_seq_num      as ExpenseID            ,
       billed_amount             as BilledAmount         ,
       reimbursed_amount         as ReimbursedAmount     ,
       eaip_reimb_detail_seq_num as ReimbursementDetailID,
       eaip_expense_type_code    as ExpenseTypeCode      ,
       create_user               as CreateUser           ,
       create_date               as CreateDate           ,
       update_user               as UpdateUser           ,
       update_date               as UpdateDate           ,
       update_num                as UpdateNum
   FROM   eaip_expense_billing
   WHERE eaip_expense_seq_num = ExpenseID;

END GetExpenseBillingByExpenseID;

 PROCEDURE GetNonReconciledBillings
(    recordset IN OUT ref_cursor)
      Is
Begin open recordset for
 SELECT eaip_billing_seq_num   as BillingID            ,
       eaip_expense_seq_num      as ExpenseID            ,
       billed_amount             as BilledAmount         ,
       reimbursed_amount         as ReimbursedAmount     ,
       eaip_reimb_detail_seq_num as ReimbursementDetailID,
       eaip_expense_type_code    as ExpenseTypeCode      ,
       create_user               as CreateUser           ,
       create_date               as CreateDate           ,
       update_user               as UpdateUser           ,
       update_date               as UpdateDate           ,
       update_num                as UpdateNum
  FROM   eaip_expense_billing
  WHERE reimbursed_amount <> billed_amount;
END GetNonReconciledBillings;
PROCEDURE EAIP_Expense_Billing_Select
(   BillingID IN eaip_expense_billing.eaip_billing_seq_num%TYPE
  , ExpenseID IN eaip_expense_billing.eaip_expense_seq_num%TYPE
  , recordset IN OUT ref_cursor)
      Is
Begin open recordset for
 SELECT eaip_billing_seq_num   as BillingID            ,
       eaip_expense_seq_num      as ExpenseID            ,
       billed_amount             as BilledAmount         ,
       reimbursed_amount         as ReimbursedAmount     ,
       eaip_reimb_detail_seq_num as ReimbursementDetailID,
       eaip_expense_type_code    as ExpenseTypeCode      ,
       create_user               as CreateUser           ,
       create_date               as CreateDate           ,
       update_user               as UpdateUser           ,
       update_date               as UpdateDate           ,
       update_num                as UpdateNum
  FROM   eaip_expense_billing
  WHERE  eaip_billing_seq_num = BillingID
    and eaip_expense_seq_num = ExpenseID;
END EAIP_Expense_Billing_Select;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_EXPENSE_BILLING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_expense_billing_insert
(
     BillingID             IN eaip_expense_billing.eaip_billing_seq_num%TYPE
    ,ExpenseID             IN eaip_expense_billing.eaip_expense_seq_num%TYPE
    ,BilledAmount          IN eaip_expense_billing.billed_amount%TYPE
    ,ReimbursedAmount      IN eaip_expense_billing.reimbursed_amount%TYPE
    ,ReimbursementDetailID IN eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_expense_billing.eaip_expense_type_code%TYPE
    ,CreateUser            IN eaip_expense_billing.create_user%TYPE
    ,CreateDate            IN eaip_expense_billing.create_date%TYPE
    ,UpdateUser            IN eaip_expense_billing.update_user%TYPE
    ,UpdateDate            IN eaip_expense_billing.update_date%TYPE
    ,UpdateNum             IN eaip_expense_billing.update_num%TYPE
       ) RETURN NUMBER
IS

v_update_count             NUMBER;

BEGIN
    INSERT INTO   eaip_expense_billing
       (   eaip_billing_seq_num
         ,  eaip_expense_seq_num
         ,  billed_amount
         ,  reimbursed_amount
         ,  eaip_reimb_detail_seq_num
         ,  eaip_expense_type_code
         ,  create_user
         ,  create_date
         ,  update_user
         ,  update_date
         ,  update_num )
      VALUES
       (   BillingID             ,
           ExpenseID             ,
           BilledAmount         ,
           ReimbursedAmount     ,
           ReimbursementDetailID,
           ExpenseTypeCode           ,
           CreateUser           ,
           CreateDate           ,
           UpdateUser           ,
           UpdateDate           ,
           UpdateNum);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_expense_billing_insert;


FUNCTION eaip_expense_billing_update
(
     BillingID               IN eaip_expense_billing.eaip_billing_seq_num%TYPE
    ,ExpenseID               IN eaip_expense_billing.eaip_expense_seq_num%TYPE
    ,BilledAmount           IN eaip_expense_billing.billed_amount%TYPE
    ,ReimbursedAmount       IN eaip_expense_billing.reimbursed_amount%TYPE
    ,ReimbursementDetailID  IN eaip_expense_billing.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode        IN eaip_expense_billing.eaip_expense_type_code%TYPE
    ,CreateUser             IN eaip_expense_billing.create_user%TYPE
    ,CreateDate             IN eaip_expense_billing.create_date%TYPE
    ,UpdateUser             IN eaip_expense_billing.update_user%TYPE
    ,UpdateDate             IN eaip_expense_billing.update_date%TYPE
    ,UpdateNum              IN eaip_expense_billing.update_num%TYPE
    ,LastUpdateNum          IN eaip_billing.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

UPDATE eaip_expense_billing
SET    eaip_billing_seq_num       = BillingID              ,
       eaip_expense_seq_num       = ExpenseID              ,
       billed_amount              = BilledAmount          ,
       reimbursed_amount          = ReimbursedAmount      ,
       eaip_reimb_detail_seq_num  = ReimbursementDetailID,
       eaip_expense_type_code     = ExpenseTypeCode      ,
       create_user                = CreateUser           ,
       create_date                = CreateDate           ,
       update_user                = UpdateUser           ,
       update_date                = UpdateDate           ,
       update_num                 = UpdateNum
    WHERE  eaip_billing_seq_num       = BillingID
      AND    eaip_expense_seq_num       = ExpenseID
      AND    update_num                 = LastUpdateNum;
v_update_count := SQL%Rowcount;
RETURN v_update_count;
END eaip_expense_billing_update;


/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Expense_Billing"
** DATE:14-FEB-11
*********************************************/

FUNCTION eaip_expense_billing_delete
(
    BillingID IN eaip_expense_billing.eaip_billing_seq_num%TYPE
  , ExpenseID IN eaip_expense_billing.eaip_expense_seq_num%TYPE
  , LastUpdateNum    IN eaip_expense_billing.UPDATE_NUM%TYPE)
    RETURN NUMBER
as
v_update_count             NUMBER;

begin

delete from eaip_expense_billing
where eaip_billing_seq_num = BillingID
  and eaip_expense_seq_num = ExpenseID
  and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

End eaip_expense_billing_delete;

/********************************************
**  SELECT FOR EAIP_WAGE_SUBSIDY
*********************************************/
PROCEDURE EAIP_Wage_Subsidy_Select
(  EAIPPeriodID IN Number,
   recordset IN OUT ref_cursor)
         Is
BEGIN open recordset for
     SELECT eaip_wage_subsidy.eaip_period_seq_num    as EaipPeriodID    ,
           eaip_wage_subsidy.wage_subsidy_eff_date  as EffectiveDate     ,
           eaip_wage_subsidy.wage_subsidy_exp_date  as ExpirationDate     ,
           eaip_wage_subsidy.wage_subsidy_days      as Days             ,
           eaip_wage_subsidy.gross_wages_amount     as GrossWagesAmount,
           eaip_expense.eaip_expense_seq_num          as EaipExpenseID            ,
            eaip_expense.orig_expense_seq_num          as OriginalExpenseID        ,
            eaip_expense.received_date                 as ReceivedDate             ,
            eaip_expense.eaip_expense_type_code        as EaipTypeCode             ,
            eaip_expense.eaip_expense_status_code      as EaipExpenseStatusCode    ,
            eaip_expense.expense_status_date           as ExpenseStatusDate        ,
            eaip_expense.eaip_writeoff_amount          as WriteOffAmount           ,
            eaip_expense.eaip_writeoff_code            as WriteOffCode             ,
            eaip_expense.eaip_adjustment_code          as EaipAdjustmentCode       ,
            eaip_expense.eaip_adjustment_amount        as EaipAdjustmentAmount     ,
            eaip_expense.eaip_adjustment_date          as EaipAdjustmentDate       ,
            eaip_expense.final_receipt_date            as FinalReceiptDate         ,
            eaip_expense.eaip_expense_denied_code      as EaipExpenseDeniedCode    ,
            eaip_expense.ammend_explanation_text       as AmendmentText            ,
            eaip_expense.expense_text                  as ExpenseText              ,
            eaip_expense.paid_amount                   as PaidAmount               ,
            eaip_expense.net_payment_amount            as NetPaymentAmount               ,
            eaip_expense.paid_date                     as PaidDate               ,
            eaip_expense.create_user                   as CreateUser               ,
            eaip_expense.create_date                   as CreateDate               ,
            eaip_expense.update_user                   as UpdateUser               ,
            eaip_expense.update_date                   as UpdateDate               ,
            eaip_expense.update_num                    as UpdateNum                 ,
            eaip_expense.payee_type                    as PayeeType,
            eaip_expense.policy_loc_seq_num            as PolicyLocSeqNum,
            eaip_expense.person_id                     as PersonId,
            eaip_expense.payment_text                  as PaymentText,
            eaip_expense.attention_line                as AttentionLine,
            eaip_expense.special_handling_code         as SpecialHandlingCode,
            eaip_expense.issue_date                    as IssueDate,
            eaip_expense.payee_name                    as PayeeName
     FROM   eaip_wage_subsidy, eaip_expense
     WHERE  eaip_wage_subsidy.eaip_expense_seq_num = eaip_expense.eaip_expense_seq_num
     AND    EAIP_WAGE_SUBSIDY.EAIP_PERIOD_SEQ_NUM = EAIPPeriodID;

END EAIP_Wage_Subsidy_select;
PROCEDURE GetWageSubsidyByExpenseId
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor)
            Is
BEGIN open recordset for
     SELECT eaip_wage_subsidy.eaip_period_seq_num    as EaipPeriodID    ,
           eaip_wage_subsidy.wage_subsidy_eff_date  as EffectiveDate     ,
           eaip_wage_subsidy.wage_subsidy_exp_date  as ExpirationDate     ,
           eaip_wage_subsidy.wage_subsidy_days      as Days             ,
           eaip_wage_subsidy.gross_wages_amount     as GrossWagesAmount,
           eaip_expense.eaip_expense_seq_num          as EaipExpenseID            ,
            eaip_expense.orig_expense_seq_num          as OriginalExpenseID        ,
            eaip_expense.received_date                 as ReceivedDate             ,
            eaip_expense.eaip_expense_type_code        as EaipTypeCode             ,
            eaip_expense.eaip_expense_status_code      as EaipExpenseStatusCode    ,
            eaip_expense.expense_status_date           as ExpenseStatusDate        ,
            eaip_expense.eaip_writeoff_amount          as WriteOffAmount           ,
            eaip_expense.eaip_writeoff_code            as WriteOffCode             ,
            eaip_expense.eaip_adjustment_code          as EaipAdjustmentCode       ,
            eaip_expense.eaip_adjustment_amount        as EaipAdjustmentAmount     ,
            eaip_expense.eaip_adjustment_date          as EaipAdjustmentDate       ,
            eaip_expense.final_receipt_date            as FinalReceiptDate         ,
            eaip_expense.eaip_expense_denied_code      as EaipExpenseDeniedCode    ,
            eaip_expense.ammend_explanation_text       as AmendmentText            ,
            eaip_expense.expense_text                  as ExpenseText              ,
            eaip_expense.paid_amount                   as PaidAmount               ,
            eaip_expense.net_payment_amount            as NetPaymentAmount               ,
            eaip_expense.paid_date                     as PaidDate               ,
            eaip_expense.create_user                   as CreateUser               ,
            eaip_expense.create_date                   as CreateDate               ,
            eaip_expense.update_user                   as UpdateUser               ,
            eaip_expense.update_date                   as UpdateDate               ,
            eaip_expense.update_num                    as UpdateNum,
            eaip_expense.payee_type                    as PayeeType,
            eaip_expense.policy_loc_seq_num            as PolicyLocSeqNum,
            eaip_expense.person_id                     as PersonId,
            eaip_expense.payment_text                  as PaymentText,
            eaip_expense.attention_line                as AttentionLine,
            eaip_expense.special_handling_code         as SpecialHandlingCode,
            eaip_expense.issue_date                    as IssueDate,
            eaip_expense.payee_name                    as PayeeName
     FROM   eaip_wage_subsidy, eaip_expense
     WHERE  eaip_wage_subsidy.eaip_expense_seq_num = eaip_expense.eaip_expense_seq_num
     AND    eaip_wage_subsidy.eaip_expense_seq_num = ExpenseID;

END GetWageSubsidyByExpenseId;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_WAGE_SUBSIDY"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_wage_subsidy_insert
(
     EaipExpenseID      IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    ,EaipPeriodID       IN eaip_wage_subsidy.eaip_period_seq_num%TYPE
    ,EffectiveDate        IN eaip_wage_subsidy.wage_subsidy_eff_date%TYPE
    ,ExpirationDate        IN eaip_wage_subsidy.wage_subsidy_exp_date%TYPE
    ,InDays                IN eaip_wage_subsidy.wage_subsidy_days%TYPE
    ,GrossWagesAmount   IN eaip_wage_subsidy.gross_wages_amount%TYPE

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
   INSERT INTO   eaip_wage_subsidy
       (
              eaip_expense_seq_num  ,
              eaip_period_seq_num   ,
              wage_subsidy_eff_date ,
              wage_subsidy_exp_date ,
              wage_subsidy_days     ,
              gross_wages_amount
       )
       VALUES
       (
              EaipExpenseID        ,
              EaipPeriodID            ,
              EffectiveDate        ,
              ExpirationDate        ,
              InDays                ,
              GrossWagesAmount );
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_wage_subsidy_insert;

FUNCTION eaip_wage_subsidy_update
(
     EaipExpenseID      IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    ,EaipPeriodID       IN eaip_wage_subsidy.eaip_period_seq_num%TYPE
    ,EffectiveDate      IN eaip_wage_subsidy.wage_subsidy_eff_date%TYPE
    ,ExpirationDate     IN eaip_wage_subsidy.wage_subsidy_exp_date%TYPE
    ,InDays             IN eaip_wage_subsidy.wage_subsidy_days%TYPE
    ,GrossWagesAmount   IN eaip_wage_subsidy.gross_wages_amount%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

UPDATE eaip_wage_subsidy
SET    eaip_expense_seq_num  = EaipExpenseID   ,
       eaip_period_seq_num   = EaipPeriodID    ,
       wage_subsidy_eff_date = EffectiveDate    ,
       wage_subsidy_exp_date = ExpirationDate    ,
       wage_subsidy_days     = InDays            ,
       gross_wages_amount    = GrossWagesAmount
WHERE  eaip_expense_seq_num  = EaipExpenseID;
v_update_count := SQL%Rowcount;
RETURN v_update_count;

END eaip_wage_subsidy_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Wage_Subsidy - also delete on expense
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_wage_subsidy_delete
(
    ExpenseID      IN eaip_expense.eaip_expense_seq_num%TYPE
  , LastUpdateNum  IN eaip_expense.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  Delete from eaip_wage_subsidy
  WHERE  eaip_expense_seq_num =
       (SELECT eaip_expense_seq_num
       FROM    eaip_expense
       WHERE   eaip_expense_seq_num = ExpenseID
       AND     update_num           = LastUpdateNum);

 v_update_count := SQL%Rowcount;

 delete from eaip_expense
  where eaip_expense_seq_num = ExpenseID;
 RETURN v_update_count;

END eaip_wage_subsidy_delete;
/********************************************************
** INSERT PROCEDURE FOR TABLE "EAIP_EXPENSE"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_expense_insert
(
     EaipExpenseID                   IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    , OriginalExpenseID              IN eaip_expense.orig_expense_seq_num%TYPE
    , ReceivedDate                   IN eaip_expense.received_date%TYPE
    , EaipTypeCode                   IN eaip_expense.eaip_expense_type_code%TYPE
    , EaipExpenseStatusCode          IN eaip_expense.eaip_expense_status_code%TYPE
    , ExpenseStatusDate              IN eaip_expense.expense_status_date%TYPE
    , WriteOffAmount                 IN eaip_expense.eaip_writeoff_amount%TYPE
    , WriteOffCode                IN eaip_expense.eaip_writeoff_code%TYPE
    , EaipAdjustmentCode             IN eaip_expense.eaip_adjustment_code%TYPE
    , EaipAdjustmentAmount           IN eaip_expense.eaip_adjustment_amount%TYPE
    , EaipAdjustmentDate             IN eaip_expense.eaip_adjustment_date%TYPE
    , FinalReceiptDate               IN eaip_expense.final_receipt_date%TYPE
    , EaipExpenseDeniedCode          IN eaip_expense.eaip_expense_denied_code%TYPE
    , AmendmentText                  IN eaip_expense.ammend_explanation_text%TYPE
    , ExpenseText                    IN eaip_expense.expense_text%TYPE
    , PaidAmount                     IN eaip_expense.paid_amount%TYPE
    , NetPaymentAmount               in EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type
    , PaidDate                       IN eaip_expense.paid_date%TYPE
    , CreateUser                     IN eaip_expense.create_user%TYPE
    , CreateDate                     IN eaip_expense.create_date%TYPE
    , UpdateUser                  IN eaip_expense.update_user%TYPE
    , UpdateDate                  IN eaip_expense.update_date%TYPE
    , UpdateNum                   IN eaip_expense.update_num%TYPE
    , PayeeType                   IN VARCHAR2
    , PolicyLocSeqNum             IN VARCHAR2
    , PersonId                    IN VARCHAR2
    , PaymentText                 IN VARCHAR2
    , AttentionLine               IN VARCHAR2
    , SpecialHandlingCode         IN VARCHAR2
    , IssueDate                   IN DATE
    , PayeeName                   IN VARCHAR2
    , inAddressLine1              IN VARCHAR2
    , inAddressLine2              IN VARCHAR2
    , inAddressLine3              IN VARCHAR2
    , inCity                      IN VARCHAR2
    , inState                     IN VARCHAR2
    , inZip                       IN VARCHAR2
    , inZipPlus4                  IN VARCHAR2
    , inCountryCode               IN VARCHAR2

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
      INSERT INTO   eaip_expense
       (
              eaip_expense_seq_num          ,
              orig_expense_seq_num          ,
              received_date                 ,
              eaip_expense_type_code        ,
              eaip_expense_status_code      ,
              expense_status_date           ,
              eaip_writeoff_amount          ,
              eaip_writeoff_code            ,
              eaip_adjustment_code          ,
              eaip_adjustment_amount        ,
              eaip_adjustment_date          ,
              final_receipt_date            ,
              eaip_expense_denied_code      ,
              ammend_explanation_text       ,
              expense_text                  ,
              paid_amount                   ,
              net_payment_amount            ,
              paid_date                     ,
              create_user                   ,
              create_date                   ,
              update_user                   ,
              update_date                   ,
              update_num                    ,
              payee_type                    ,
              policy_loc_seq_num            ,
              person_id                     ,
              payment_text                  ,
              attention_line                ,
              special_handling_code         ,
              issue_date                    ,
              payee_name                    ,
              address_line1                 ,
              address_line2                 ,
              address_line3                 ,
              city                          ,
              state                         ,
              zip                           ,
              zip_plus_4                    ,
              country_code)
       VALUES (
              EaipExpenseID            ,
              OriginalExpenseID        ,
              ReceivedDate             ,
              EaipTypeCode             ,
              EaipExpenseStatusCode    ,
              ExpenseStatusDate        ,
              WriteOffAmount           ,
              WriteOffCode             ,
              EaipAdjustmentCode       ,
              EaipAdjustmentAmount     ,
              EaipAdjustmentDate       ,
              FinalReceiptDate         ,
              EaipExpenseDeniedCode    ,
              AmendmentText            ,
              ExpenseText              ,
              PaidAmount               ,
              NetPaymentAmount         ,
              PaidDate                 ,
              CreateUser               ,
              CreateDate               ,
              UpdateUser               ,
              UpdateDate               ,
              UpdateNum                ,
              PayeeType                ,
              PolicyLocSeqNum          ,
              PersonId                 ,
              PaymentText              ,
              AttentionLine            ,
              SpecialHandlingCode      ,
              IssueDate                ,
              PayeeName                ,
              inAddressLine1           ,
              inAddressLine2           ,
              inAddressLine3           ,
              inCity                   ,
              inState                  ,
              inZip                    ,
              inZipPlus4               ,
              inCountryCode);
    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END  eaip_expense_insert;
FUNCTION eaip_expense_update
(
      EaipExpenseID                 IN eaip_wage_subsidy.eaip_expense_seq_num%TYPE
    , OriginalExpenseID             IN eaip_expense.orig_expense_seq_num%TYPE
    , ReceivedDate                  IN eaip_expense.received_date%TYPE
    , EaipTypeCode                  IN eaip_expense.eaip_expense_type_code%TYPE
    , EaipExpenseStatusCode         IN eaip_expense.eaip_expense_status_code%TYPE
    , ExpenseStatusDate             IN eaip_expense.expense_status_date%TYPE
    , WriteOffAmount                IN eaip_expense.eaip_writeoff_amount%TYPE
    , WriteOffCode                  IN eaip_expense.eaip_writeoff_code%TYPE
    , EaipAdjustmentCode            IN eaip_expense.eaip_adjustment_code%TYPE
    , EaipAdjustmentAmount          IN eaip_expense.eaip_adjustment_amount%TYPE
    , EaipAdjustmentDate            IN eaip_expense.eaip_adjustment_date%TYPE
    , FinalReceiptDate              IN eaip_expense.final_receipt_date%TYPE
    , EaipExpenseDeniedCode         IN eaip_expense.eaip_expense_denied_code%TYPE
    , AmendmentText                 IN eaip_expense.ammend_explanation_text%TYPE
    , ExpenseText                   IN eaip_expense.expense_text%TYPE
    , PaidAmount                    IN eaip_expense.paid_amount%TYPE
    , NetPaymentAmount              IN EAIP_EXPENSE.NET_PAYMENT_AMOUNT%type
    , PaidDate                      IN eaip_expense.paid_date%TYPE
    , CreateUser                    IN eaip_expense.create_user%TYPE
    , CreateDate                    IN eaip_expense.create_date%TYPE
    , UpdateUser                    IN eaip_expense.update_user%TYPE
    , UpdateDate                    IN eaip_expense.update_date%TYPE
    , UpdateNum                     IN eaip_expense.update_num%TYPE
    , LastUpdateNum                 IN eaip_expense.UPDATE_NUM%TYPE
    , PayeeType                     IN VARCHAR2
    , PolicyLocSeqNum               IN VARCHAR2
    , PersonId                      IN VARCHAR2
    , PaymentText                   IN VARCHAR2
    , AttentionLine                 IN VARCHAR2
    , SpecialHandlingCode           IN VARCHAR2
    , IssueDate                     IN DATE
    , PayeeName                     IN VARCHAR2
    , inAddressLine1                IN VARCHAR2
    , inAddressLine2                IN VARCHAR2
    , inAddressLine3                IN VARCHAR2
    , inCity                        IN VARCHAR2
    , inState                       IN VARCHAR2
    , inZip                         IN VARCHAR2
    , inZipPlus4                    IN VARCHAR2
    , inCountryCode                 IN VARCHAR2
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
 UPDATE eaip_expense
SET    eaip_expense_seq_num          = EaipExpenseID            ,
       orig_expense_seq_num          = OriginalExpenseID        ,
       received_date                 = ReceivedDate             ,
       eaip_expense_type_code        = EaipTypeCode             ,
       eaip_expense_status_code      = EaipExpenseStatusCode    ,
       expense_status_date           = ExpenseStatusDate        ,
       eaip_writeoff_amount          = WriteOffAmount           ,
       eaip_writeoff_code            = WriteOffCode             ,
       eaip_adjustment_code          = EaipAdjustmentCode       ,
       eaip_adjustment_amount        = EaipAdjustmentAmount     ,
       eaip_adjustment_date          = EaipAdjustmentDate       ,
       final_receipt_date            = FinalReceiptDate         ,
       eaip_expense_denied_code      = EaipExpenseDeniedCode    ,
       ammend_explanation_text       = AmendmentText            ,
       expense_text                  = ExpenseText              ,
       paid_amount                   = PaidAmount               ,
       net_payment_amount            = NetPaymentAmount         ,
       paid_date                     = PaidDate                 ,
       create_user                   = CreateUser               ,
       create_date                   = CreateDate               ,
       update_user                   = UpdateUser               ,
       update_date                   = UpdateDate               ,
       update_num                    = UpdateNum                ,
       payee_type                    = PayeeType                ,
       policy_loc_seq_num            = PolicyLocSeqNum          ,
       person_id                     = PersonId                 ,
       payment_text                  = PaymentText              ,
       attention_line                = AttentionLine            ,
       special_handling_code         = SpecialHandlingCode      ,
       issue_date                    = IssueDate                ,
       payee_name                    = PayeeName                ,
       address_line1                 = inAddressLine1           ,
       address_line2                 = inAddressLine2           ,
       address_line3                 = inAddressLine3           ,
       city                          = inCity                   ,
       state                         = inState                  ,
       zip                           = inZip                    ,
       zip_plus_4                    = inZipPlus4               ,
       country_code                  = inCountryCode
  WHERE  eaip_expense_seq_num        = EaipExpenseID
  AND    update_num                  = LastUpdateNum;

v_update_count := SQL%Rowcount;
RETURN v_update_count;
END eaip_expense_update;

/********************************************
**  SELECT FOR EAIP_PURCHASE
*********************************************/
PROCEDURE EAIP_Purchase_Select
(  EAIPPeriodID IN Number,
   recordset IN OUT ref_cursor)
            Is
BEGIN open recordset for
     SELECT p.eaip_period_seq_num   as EaipPeriodID    ,
            p.purchase_descr        AS PurchaseDescription,
            p.order_date            AS OrderDate          ,
            p.delivery_date         AS DeliveryDate       ,
            p.item_cost_amount      AS ItemCostAmount     ,
            p.vendor_bill_ref_text  AS VendorBillRefText  ,
            p.credit_card_paid_date AS CreditCardPaidDate ,
            p.credit_card_user      AS CreditCardUser     ,
            x.eaip_expense_seq_num          as EaipExpenseID   ,
            x.orig_expense_seq_num          as OriginalExpenseID        ,
            x.received_date                 as ReceivedDate             ,
            x.eaip_expense_type_code        as EaipTypeCode             ,
            x.eaip_expense_status_code      as EaipExpenseStatusCode    ,
            x.expense_status_date           as ExpenseStatusDate        ,
            x.eaip_writeoff_amount          as WriteOffAmount           ,
            x.eaip_writeoff_code            as WriteOffCode             ,
            x.eaip_adjustment_code          as EaipAdjustmentCode       ,
            x.eaip_adjustment_amount        as EaipAdjustmentAmount     ,
            x.eaip_adjustment_date          as EaipAdjustmentDate       ,
            x.final_receipt_date            as FinalReceiptDate         ,
            x.eaip_expense_denied_code      as EaipExpenseDeniedCode    ,
            x.ammend_explanation_text       as AmendmentText            ,
            x.expense_text                  as ExpenseText              ,
            x.paid_amount                   as PaidAmount               ,
            x.net_payment_amount            as NetPaymentAmount               ,
            x.paid_date                     as PaidDate                 ,
            x.create_user                   as CreateUser               ,
            x.create_date                   as CreateDate               ,
            x.update_user                   as UpdateUser               ,
            x.update_date                   as UpdateDate               ,
            x.update_num                    as UpdateNum,
            x.payee_type as PayeeType,
            x.policy_loc_seq_num as PolicyLocSeqNum,
            x.person_id as PersonId,
            x.payment_text as PaymentText,
            x.attention_line as AttentionLine,
            x.special_handling_code as SpecialHandlingCode,
            x.issue_date as IssueDate,
            x.payee_name as PayeeName
     FROM   eaip_purchase p, eaip_expense x
     WHERE  p.eaip_expense_seq_num = x.eaip_expense_seq_num
     AND    p.EAIP_PERIOD_SEQ_NUM = EAIPPeriodID;

END EAIP_Purchase_Select;

PROCEDURE GetPurchaseByExpenseId
(  ExpenseID IN Number,
   recordset IN OUT ref_cursor)
            Is
BEGIN open recordset for
     SELECT p.eaip_period_seq_num           as EaipPeriodID,
            p.purchase_descr                AS PurchaseDescription,
            p.order_date                    AS OrderDate,
            p.delivery_date                 AS DeliveryDate,
            p.item_cost_amount              AS ItemCostAmount,
            p.vendor_bill_ref_text          AS VendorBillRefText,
            p.credit_card_paid_date         AS CreditCardPaidDate,
            p.credit_card_user              AS CreditCardUser,
            x.eaip_expense_seq_num          as EaipExpenseID,
            x.orig_expense_seq_num          as OriginalExpenseID,
            x.received_date                 as ReceivedDate,
            x.eaip_expense_type_code        as EaipTypeCode,
            x.eaip_expense_status_code      as EaipExpenseStatusCode,
            x.expense_status_date           as ExpenseStatusDate,
            x.eaip_writeoff_amount          as WriteOffAmount,
            x.eaip_writeoff_code            as WriteOffCode,
            x.eaip_adjustment_code          as EaipAdjustmentCode,
            x.eaip_adjustment_amount        as EaipAdjustmentAmount,
            x.eaip_adjustment_date          as EaipAdjustmentDate,
            x.final_receipt_date            as FinalReceiptDate,
            x.eaip_expense_denied_code      as EaipExpenseDeniedCode,
            x.ammend_explanation_text       as AmendmentText,
            x.expense_text                  as ExpenseText,
            x.paid_amount                   as PaidAmount,
            x.net_payment_amount            as NetPaymentAmount,
            x.paid_date                     as PaidDate,
            x.create_user                   as CreateUser,
            x.create_date                   as CreateDate,
            x.update_user                   as UpdateUser,
            x.update_date                   as UpdateDate,
            x.update_num                    as UpdateNum,
            x.payee_type                    as PayeeType,
            x.policy_loc_seq_num            as PolicyLocSeqNum,
            x.person_id                     as PersonId,
            x.payment_text                  as PaymentText,
            x.attention_line                as AttentionLine,
            x.special_handling_code         as SpecialHandlingCode,
            x.issue_date                    as IssueDate,
            x.payee_name                    as PayeeName
     FROM   eaip_purchase p, eaip_expense x
     WHERE  p.eaip_expense_seq_num = x.eaip_expense_seq_num
     AND    p.eaip_expense_seq_num = ExpenseID;

END GetPurchaseByExpenseId;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_PURCHASE"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_purchase_insert
(
     EaipExpenseID              IN eaip_purchase.eaip_expense_seq_num%TYPE
    ,EaipPeriodID               IN eaip_purchase.eaip_period_seq_num%TYPE
    ,PurchaseDescription        IN eaip_purchase.purchase_descr%TYPE
    ,OrderDate                  IN eaip_purchase.order_date%TYPE
    ,DeliveryDate               IN eaip_purchase.delivery_date%TYPE
    ,ItemCostAmount             IN eaip_purchase.item_cost_amount%TYPE
    ,CreditCardPaidDate         IN eaip_purchase.credit_card_paid_date%TYPE
    ,CreditCardUser             IN eaip_purchase.credit_card_user%TYPE
    ,VendorBillRefText          IN eaip_purchase.vendor_bill_ref_text%TYPE

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
    INSERT INTO   eaip_purchase
       (
              eaip_expense_seq_num          ,
              eaip_period_seq_num           ,
              purchase_descr                ,
              order_date                    ,
              delivery_date                 ,
              item_cost_amount              ,
              credit_card_paid_date         ,
              credit_card_user              ,
              vendor_bill_ref_text)
         Values (
            EaipExpenseID            ,
            EaipPeriodID             ,
            PurchaseDescription     ,
            OrderDate                ,
            DeliveryDate             ,
            ItemCostAmount           ,
            CreditCardPaidDate       ,
            CreditCardUser           ,
            VendorBillRefText);

    v_update_count := SQL%Rowcount;
    RETURN v_update_count;
END eaip_purchase_insert;
FUNCTION eaip_purchase_update
(
     EaipExpenseID              IN eaip_purchase.eaip_expense_seq_num%TYPE
    ,EaipPeriodID               IN eaip_purchase.eaip_period_seq_num%TYPE
    ,PurchaseDescription        IN eaip_purchase.purchase_descr%TYPE
    ,OrderDate                  IN eaip_purchase.order_date%TYPE
    ,DeliveryDate               IN eaip_purchase.delivery_date%TYPE
    ,ItemCostAmount             IN eaip_purchase.item_cost_amount%TYPE
    ,CreditCardPaidDate         IN eaip_purchase.credit_card_paid_date%TYPE
    ,CreditCardUser             IN eaip_purchase.credit_card_user%TYPE
    ,VendorBillRefText          IN eaip_purchase.vendor_bill_ref_text%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
       UPDATE eaip_purchase
        SET    eaip_expense_seq_num  = EaipExpenseID      ,
               eaip_period_seq_num   = EaipPeriodID       ,
               purchase_descr        = PurchaseDescription,
               order_date            = OrderDate          ,
               delivery_date         = DeliveryDate       ,
               item_cost_amount      = ItemCostAmount     ,
               credit_card_paid_date = CreditCardPaidDate ,
               credit_card_user      = CreditCardUser     ,
               vendor_bill_ref_text  = VendorBillRefText
        WHERE  eaip_expense_seq_num  = EaipExpenseID;

v_update_count := SQL%Rowcount;
RETURN v_update_count;

END eaip_purchase_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_Purchase"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_purchase_delete
(
    ExpenseID      IN eaip_expense.eaip_expense_seq_num%TYPE
  , LastUpdateNum  IN eaip_expense.UPDATE_NUM%TYPE
 ) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  Delete from eaip_purchase
  WHERE  eaip_expense_seq_num =
       (SELECT eaip_expense_seq_num
       FROM    eaip_expense
       WHERE   eaip_expense_seq_num = ExpenseID
       AND     update_num           = LastUpdateNum);

 v_update_count := SQL%Rowcount;

 delete from eaip_expense
  where eaip_expense_seq_num = ExpenseID;

 RETURN v_update_count;

END eaip_purchase_delete;

/********************************************
**  SELECT FOR EAIP_PAYPERIOD
*********************************************/
PROCEDURE EAIP_PAY_PERIOD_Select
(  ExpenseId IN Number,
   recordset IN OUT ref_cursor)
   Is
BEGIN open recordset for
      Select
        eaip_pay_period_seq_num       as PayPeriodID          ,
        eaip_expense_seq_num          as ExpenseID            ,
        eaip_pay_period_eff_date      as EffectiveDate        ,
        eaip_pay_period_exp_date      as ExpirationDate       ,
        regular_pay_rate              as RegularPayRate       ,
        overtime_pay_rate             as OvertimePayRate      ,
        other_pay_rate                as OtherPayRate         ,
        regular_hours                 as RegularHours         ,
        overtime_hours                as OvertimeHours        ,
        other_hours                   as OtherHours           ,
        regular_pay_amount            as RegularPayAmount     ,
        overtime_pay_amount           as OvertimePayAmount    ,
        other_pay_amount              as OtherPayAmount       ,
        gross_wage_amount             as GrossWageAmount      ,
        worker_wage_amount            as WorkerWageAmount     ,
        cl_wage_period_pay_unit_code  as UnitCode             ,
        potential_unit_quantity       as PotentialUnitQuantity,
        average_pay_rate              as AveragePayRate       ,
        actual_unit_quantity          as ActualUnitQuantity   ,
        pay_period_days               as PayPeriodDays        ,
        create_user                   as CreateUser           ,
        create_date                   as CreateDate           ,
        update_user                   as UpdateUser           ,
        update_date                   as UpdateDate           ,
        update_num                    as UpdateNum
   From eaip_pay_period
   WHERE  eaip_expense_seq_num = ExpenseId Order by eaip_pay_period_eff_date, eaip_pay_period_seq_num;
END EAIP_PAY_PERIOD_SELECT;
PROCEDURE GetPayPeriodByPayPeriodId
(  PayPeriodID IN Number,
    recordset IN OUT ref_cursor)
   Is
BEGIN open recordset for
      Select
        eaip_pay_period_seq_num       as PayPeriodID          ,
        eaip_expense_seq_num          as ExpenseID            ,
        eaip_pay_period_eff_date      as EffectiveDate        ,
        eaip_pay_period_exp_date      as ExpirationDate       ,
        regular_pay_rate              as RegularPayRate       ,
        overtime_pay_rate             as OvertimePayRate      ,
        other_pay_rate                as OtherPayRate         ,
        regular_hours                 as RegularHours         ,
        overtime_hours                as OvertimeHours        ,
        other_hours                   as OtherHours           ,
        regular_pay_amount            as RegularPayAmount     ,
        overtime_pay_amount           as OvertimePayAmount    ,
        other_pay_amount              as OtherPayAmount       ,
        gross_wage_amount             as GrossWageAmount      ,
        worker_wage_amount            as WorkerWageAmount     ,
        cl_wage_period_pay_unit_code  as UnitCode             ,
        potential_unit_quantity       as PotentialUnitQuantity,
        average_pay_rate              as AveragePayRate       ,
        actual_unit_quantity          as ActualUnitQuantity   ,
        pay_period_days               as PayPeriodDays        ,
        create_user                   as CreateUser           ,
        create_date                   as CreateDate           ,
        update_user                   as UpdateUser           ,
        update_date                   as UpdateDate           ,
        update_num                    as UpdateNum
   From eaip_pay_period
   WHERE  eaip_pay_period_seq_num = PayPeriodID;

END GetPayPeriodByPayPeriodId;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_PAY_PERIOD"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_pay_period_insert
(
     PayPeriodID               IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
    ,ExpenseID                 IN eaip_pay_period.eaip_expense_seq_num%TYPE
    ,EffectiveDate             IN eaip_pay_period.eaip_pay_period_eff_date%TYPE
    ,ExpirationDate            IN eaip_pay_period.eaip_pay_period_exp_date%TYPE
    ,RegularPayRate            IN eaip_pay_period.regular_pay_rate%TYPE
    ,OvertimePayRate           IN eaip_pay_period.overtime_pay_rate%TYPE
    ,OtherPayRate              IN eaip_pay_period.other_pay_rate%TYPE
    ,RegularHours              IN eaip_pay_period.regular_hours%TYPE
    ,OvertimeHours             IN eaip_pay_period.overtime_hours%TYPE
     , OtherHours                  IN eaip_pay_period.other_hours%TYPE
     , RegularPayAmount          IN eaip_pay_period.regular_pay_amount%TYPE
     , OvertimePayAmount         IN eaip_pay_period.overtime_pay_amount%TYPE
     , OtherPayAmount            IN eaip_pay_period.other_pay_amount%TYPE
    , GrossWageAmount           IN eaip_pay_period.gross_wage_amount%TYPE
    , WorkerWageAmount          IN eaip_pay_period.worker_wage_amount%TYPE
    , UnitCode                  IN eaip_pay_period.cl_wage_period_pay_unit_code%TYPE
    , PotentialUnitQuantity        IN eaip_pay_period.potential_unit_quantity%TYPE
    , AveragePayRate            IN eaip_pay_period.average_pay_rate%TYPE
    , ActualUnitQuantity        IN eaip_pay_period.actual_unit_quantity%TYPE
    , PayPeriodDays                IN eaip_pay_period.pay_period_days%TYPE
    , CreateUser                IN eaip_pay_period.create_user%TYPE
    , CreateDate                IN eaip_pay_period.create_date%TYPE
    , UpdateUser                IN eaip_pay_period.update_user%TYPE
    , UpdateDate                IN eaip_pay_period.update_date%TYPE
    , UpdateNum                    IN eaip_pay_period.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  INSERT INTO   eaip_pay_period
       (
            eaip_pay_period_seq_num       ,
            eaip_expense_seq_num          ,
            eaip_pay_period_eff_date      ,
            eaip_pay_period_exp_date      ,
            regular_pay_rate              ,
            overtime_pay_rate             ,
            other_pay_rate                ,
            regular_hours                 ,
            overtime_hours                ,
            other_hours                   ,
            regular_pay_amount            ,
            overtime_pay_amount           ,
            other_pay_amount              ,
            gross_wage_amount             ,
            worker_wage_amount            ,
            cl_wage_period_pay_unit_code  ,
            potential_unit_quantity       ,
            average_pay_rate              ,
            actual_unit_quantity          ,
            pay_period_days               ,
            create_user                   ,
            create_date                   ,
            update_user                   ,
            update_date                   ,
            update_num
       )
       VALUES
       (
            PayPeriodID                  ,
            ExpenseID                    ,
            EffectiveDate                ,
            ExpirationDate               ,
            RegularPayRate               ,
            OvertimePayRate              ,
            OtherPayRate                 ,
            RegularHours                 ,
            OvertimeHours                ,
            OtherHours                   ,
            RegularPayAmount             ,
            OvertimePayAmount            ,
            OtherPayAmount               ,
            GrossWageAmount              ,
            WorkerWageAmount             ,
            UnitCode                     ,
            PotentialUnitQuantity        ,
            AveragePayRate               ,
            ActualUnitQuantity           ,
            PayPeriodDays                ,
            CreateUser                   ,
            CreateDate                   ,
            UpdateUser                   ,
            UpdateDate                   ,
            UpdateNum);

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_pay_period_insert;

FUNCTION eaip_pay_period_update
(
     PayPeriodID               IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
    ,ExpenseID                 IN eaip_pay_period.eaip_expense_seq_num%TYPE
    ,EffectiveDate             IN eaip_pay_period.eaip_pay_period_eff_date%TYPE
    ,ExpirationDate            IN eaip_pay_period.eaip_pay_period_exp_date%TYPE
    ,RegularPayRate            IN eaip_pay_period.regular_pay_rate%TYPE
    ,OvertimePayRate           IN eaip_pay_period.overtime_pay_rate%TYPE
    ,OtherPayRate              IN eaip_pay_period.other_pay_rate%TYPE
    ,RegularHours              IN eaip_pay_period.regular_hours%TYPE
    ,OvertimeHours             IN eaip_pay_period.overtime_hours%TYPE
     , OtherHours                  IN eaip_pay_period.other_hours%TYPE
     , RegularPayAmount          IN eaip_pay_period.regular_pay_amount%TYPE
     , OvertimePayAmount         IN eaip_pay_period.overtime_pay_amount%TYPE
     , OtherPayAmount            IN eaip_pay_period.other_pay_amount%TYPE
    , GrossWageAmount           IN eaip_pay_period.gross_wage_amount%TYPE
    , WorkerWageAmount          IN eaip_pay_period.worker_wage_amount%TYPE
    , UnitCode                  IN eaip_pay_period.cl_wage_period_pay_unit_code%TYPE
    , PotentialUnitQuantity        IN eaip_pay_period.potential_unit_quantity%TYPE
    , AveragePayRate            IN eaip_pay_period.average_pay_rate%TYPE
    , ActualUnitQuantity        IN eaip_pay_period.actual_unit_quantity%TYPE
    , PayPeriodDays                IN eaip_pay_period.pay_period_days%TYPE
    , CreateUser                IN eaip_pay_period.create_user%TYPE
    , CreateDate                IN eaip_pay_period.create_date%TYPE
    , UpdateUser                IN eaip_pay_period.update_user%TYPE
    , UpdateDate                IN eaip_pay_period.update_date%TYPE
    , UpdateNum                    IN eaip_pay_period.update_num%TYPE
    ,LastUpdateNum              IN eaip_pay_period.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  UPDATE eaip_pay_period
     SET eaip_pay_period_seq_num    = PayPeriodID          ,
       eaip_expense_seq_num         = ExpenseID            ,
       eaip_pay_period_eff_date     = EffectiveDate        ,
       eaip_pay_period_exp_date     = ExpirationDate       ,
       regular_pay_rate             = RegularPayRate       ,
       overtime_pay_rate            = OvertimePayRate      ,
       other_pay_rate               = OtherPayRate         ,
       regular_hours                = RegularHours         ,
       overtime_hours               = OvertimeHours        ,
       other_hours                  = OtherHours           ,
       regular_pay_amount           = RegularPayAmount     ,
       overtime_pay_amount          = OvertimePayAmount    ,
       other_pay_amount             = OtherPayAmount       ,
       gross_wage_amount            = GrossWageAmount      ,
       worker_wage_amount           = WorkerWageAmount     ,
       cl_wage_period_pay_unit_code = UnitCode             ,
       potential_unit_quantity      = PotentialUnitQuantity,
       average_pay_rate             = AveragePayRate       ,
       actual_unit_quantity         = ActualUnitQuantity   ,
       pay_period_days              = PayPeriodDays        ,
       create_user                  = CreateUser           ,
       create_date                  = CreateDate           ,
       update_user                  = UpdateUser           ,
       update_date                  = UpdateDate           ,
       update_num                   = UpdateNum
    WHERE  eaip_pay_period_seq_num  = PayPeriodID
      AND  update_num               = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_pay_period_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_PAY_PERIOD"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_pay_period_delete
(
     PayPeriodID   IN eaip_pay_period.eaip_pay_period_seq_num%TYPE
  , LastUpdateNum  IN eaip_pay_period.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  delete from eaip_pay_period
   where eaip_pay_period_seq_num = PayPeriodID
     and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_pay_period_delete;

/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE EAIP_Reimb_Amount_Select
(   ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
  , recordset            IN OUT ref_cursor)
  IS
Begin open recordset for
 SELECT  eaip_reimb_seq_num          as ReimbursementID      ,
        eaip_expense_type_code           as ExpenseTypeCode        ,
        disabling_amount               as DisablingAmount        ,
        nondisabling_amount            as NonDisablingAmount    ,
        total_amount                   as TotalAmount            ,
        create_user                     as CreateUser           ,
        create_date                     as CreateDate           ,
        update_user                     as UpdateUser           ,
        update_date                     as UpdateDate           ,
        update_num                      as UpdateNum
  FROM  eaip_reimbursement_amount
  WHERE eaip_reimb_seq_num = ReimbursementID;
END EAIP_Reimb_Amount_Select;

PROCEDURE GetReimbAmountbyType
(   ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
  , ExpenseTypeCode      IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    , recordset            IN OUT ref_cursor)
  IS
Begin open recordset for
 SELECT  eaip_reimb_seq_num          as ReimbursementID      ,
        eaip_expense_type_code           as ExpenseTypeCode        ,
        disabling_amount               as DisablingAmount        ,
        nondisabling_amount            as NonDisablingAmount    ,
        total_amount                   as TotalAmount            ,
        create_user                     as CreateUser           ,
        create_date                     as CreateDate           ,
        update_user                     as UpdateUser           ,
        update_date                     as UpdateDate           ,
        update_num                      as UpdateNum
  FROM  eaip_reimbursement_amount
  WHERE eaip_reimb_seq_num = ReimbursementID
    AND eaip_expense_type_code = ExpenseTypeCode;
END GetReimbAmountbyType;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION EAIP_Reimb_Amount_insert
(
     ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,DisablingAmount       IN eaip_reimbursement_amount.disabling_amount%TYPE
    ,NonDisablingAmount   IN eaip_reimbursement_amount.nondisabling_amount%TYPE
    ,TotalAmount           IN eaip_reimbursement_amount.total_amount%TYPE
    ,CreateUser           IN eaip_reimbursement_amount.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_amount.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_amount.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_amount.update_date%TYPE
    ,UpdateNum             IN eaip_reimbursement_amount.update_num%TYPE

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

 INSERT
 INTO   eaip_reimbursement_amount
       (
              eaip_reimb_seq_num    ,
              eaip_expense_type_code,
              disabling_amount      ,
              nondisabling_amount   ,
              total_amount          ,
              create_user           ,
              create_date           ,
              update_user           ,
              update_date           ,
              update_num
       )
       VALUES
       (
              ReimbursementID      ,
              ExpenseTypeCode        ,
              DisablingAmount        ,
              NonDisablingAmount    ,
              TotalAmount            ,
              CreateUser           ,
              CreateDate           ,
              UpdateUser           ,
              UpdateDate           ,
              UpdateNum);

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_Reimb_Amount_insert;
FUNCTION EAIP_Reimb_Amount_update
(
     ReimbursementID       IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
    ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,DisablingAmount       IN eaip_reimbursement_amount.disabling_amount%TYPE
    ,NonDisablingAmount    IN eaip_reimbursement_amount.nondisabling_amount%TYPE
    ,TotalAmount           IN eaip_reimbursement_amount.total_amount%TYPE
    ,CreateUser            IN eaip_reimbursement_amount.create_user%TYPE
    ,CreateDate            IN eaip_reimbursement_amount.create_date%TYPE
    ,UpdateUser            IN eaip_reimbursement_amount.update_user%TYPE
    ,UpdateDate            IN eaip_reimbursement_amount.update_date%TYPE
    ,UpdateNum             IN eaip_reimbursement_amount.update_num%TYPE
    ,LastUpdateNum         IN eaip_reimbursement_amount.UPDATE_NUM%TYPE
    ,LastExpenseTypeCode   IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

  UPDATE eaip_reimbursement_amount
  SET  eaip_reimb_seq_num     = ReimbursementID    ,
       eaip_expense_type_code = ExpenseTypeCode    ,
       disabling_amount       = DisablingAmount    ,
       nondisabling_amount    = NonDisablingAmount ,
       total_amount           = TotalAmount        ,
       create_user            = CreateUser         ,
       create_date            = CreateDate         ,
       update_user            = UpdateUser         ,
       update_date            = UpdateDate         ,
       update_num             = UpdateNum
  WHERE  eaip_reimb_seq_num   = ReimbursementID
    AND  eaip_expense_type_code = LastExpenseTypeCode
    AND  update_num             = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_Reimb_Amount_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_AMOUNT"
** DATE:14-FEB-11
*********************************************/
FUNCTION EAIP_Reimb_Amount_delete
(
     ReimbursementID      IN eaip_reimbursement_amount.eaip_reimb_seq_num%TYPE
     ,ExpenseTypeCode       IN eaip_reimbursement_amount.eaip_expense_type_code%TYPE
    ,LastUpdateNum        IN eaip_reimbursement_amount.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

  delete from eaip_reimbursement_amount
   where eaip_reimb_seq_num = ReimbursementID
     and eaip_expense_type_code = ExpenseTypeCode
     and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_Reimb_Amount_delete;

/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE reimb_detail_amount_Select
(   ReimbursementDetailID      IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
  , recordset            IN OUT ref_cursor)
  IS
  Begin open recordset for
  SELECT  eaip_reimb_detail_seq_num   as ReimbursementDetailID,
        eaip_expense_type_code           as ExpenseTypeCode        ,
        reimbursed_amount           as ReimbursedAmount        ,
        create_user                     as CreateUser           ,
        create_date                     as CreateDate           ,
        update_user                     as UpdateUser           ,
        update_date                     as UpdateDate           ,
        update_num                      as UpdateNum
   FROM eaip_reimb_detail_amount
  WHERE eaip_reimb_detail_seq_num = ReimbursementDetailID;

  END reimb_detail_amount_Select;

    PROCEDURE GetReimbDetailAmountbyType
(   ReimbursementDetailID      IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
  , ExpenseTypeCode            IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
  , recordset            IN OUT ref_cursor)
 IS
  Begin open recordset for
  SELECT  eaip_reimb_detail_seq_num   as ReimbursementDetailID,
        eaip_expense_type_code           as ExpenseTypeCode        ,
        reimbursed_amount           as ReimbursedAmount        ,
        create_user                     as CreateUser           ,
        create_date                     as CreateDate           ,
        update_user                     as UpdateUser           ,
        update_date                     as UpdateDate           ,
        update_num                      as UpdateNum
   FROM eaip_reimb_detail_amount
  WHERE eaip_reimb_detail_seq_num = ReimbursementDetailID
    AND eaip_expense_type_code = ExpenseTypeCode;

  END GetReimbDetailAmountbyType;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION reimb_detail_amount_insert
(
     ReimbursementDetailID     IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode           IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,ReimbursedAmount           IN eaip_reimb_detail_amount.reimbursed_amount%TYPE
    ,CreateUser                IN eaip_reimb_detail_amount.create_user%TYPE
    ,CreateDate                IN eaip_reimb_detail_amount.create_date%TYPE
    ,UpdateUser                IN eaip_reimb_detail_amount.update_user%TYPE
    ,UpdateDate                IN eaip_reimb_detail_amount.update_date%TYPE
    ,UpdateNum                 IN eaip_reimb_detail_amount.update_num%TYPE

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  INSERT INTO   eaip_reimb_detail_amount
       (
              eaip_reimb_detail_seq_num ,
              eaip_expense_type_code    ,
              reimbursed_amount         ,
              create_user               ,
              create_date               ,
              update_user               ,
              update_date               ,
              update_num               )
             VALUES (
              ReimbursementDetailID    ,
              ExpenseTypeCode            ,
              ReimbursedAmount            ,
              CreateUser               ,
              CreateDate               ,
              UpdateUser               ,
              UpdateDate               ,
              UpdateNum);

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END reimb_detail_amount_insert;

FUNCTION reimb_detail_amount_update
(
     ReimbursementDetailID     IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
    ,ExpenseTypeCode           IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,ReimbursedAmount           IN eaip_reimb_detail_amount.reimbursed_amount%TYPE
    ,CreateUser                IN eaip_reimb_detail_amount.create_user%TYPE
    ,CreateDate                IN eaip_reimb_detail_amount.create_date%TYPE
    ,UpdateUser                IN eaip_reimb_detail_amount.update_user%TYPE
    ,UpdateDate                IN eaip_reimb_detail_amount.update_date%TYPE
    ,UpdateNum                 IN eaip_reimb_detail_amount.update_num%TYPE
    ,LastUpdateNum             IN eaip_reimb_detail_amount.UPDATE_NUM%TYPE
    ,LastExpenseTypeCode       IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

 UPDATE eaip_reimb_detail_amount
    SET    eaip_reimb_detail_seq_num  = ReimbursementDetailID ,
           eaip_expense_type_code     = ExpenseTypeCode       ,
           reimbursed_amount          = ReimbursedAmount       ,
           create_user                = CreateUser            ,
           create_date                = CreateDate            ,
           update_user                = UpdateUser            ,
           update_date                = UpdateDate            ,
           update_num                 = UpdateNum
    WHERE  eaip_reimb_detail_seq_num  = ReimbursementDetailID
    AND    eaip_expense_type_code     = LastExpenseTypeCode
    AND    update_num                 = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END reimb_detail_amount_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMB_DETAIL_AMOUNT"
** DATE:14-FEB-11
*********************************************/
FUNCTION reimb_detail_amount_delete
(
     ReimbursementDetailID IN eaip_reimb_detail_amount.eaip_reimb_detail_seq_num%TYPE
     ,ExpenseTypeCode      IN eaip_reimb_detail_amount.eaip_expense_type_code%TYPE
    ,LastUpdateNum         IN eaip_reimb_detail_amount.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
  delete from eaip_reimb_detail_amount
   where eaip_reimb_detail_seq_num = ReimbursementDetailID
   and eaip_expense_type_code = ExpenseTypeCode
   and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END reimb_detail_amount_delete;


/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:  DATE:14-FEB-11
*********************************************/

PROCEDURE eaip_reimbursement_Select
(   ReimbursementID            IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
  , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for
  SELECT  eaip_reimb_seq_num    as ReimbursementID  ,
          received_date         as ReceivedDate     ,
          posted_date           as PostedDate           ,
          reconciled_date       as ReconciledDate       ,
          reconciled_flag       as IsReconciled     ,
          reimbursement_amount  as ReimbursementAmount,
          reconciled_amount     as ReconciledAmount ,
          explanation_text      as ExplanationText  ,
          create_user           as CreateUser         ,
          create_date           as CreateDate         ,
          update_user           as UpdateUser         ,
          update_date           as UpdateDate         ,
          update_num            as UpdateNum
  FROM    eaip_reimbursement
  Where   eaip_reimb_seq_num = ReimbursementID;

  END eaip_reimbursement_Select;
 PROCEDURE GetReimbByReceivedDate
(   ReceivedDate      IN date
 , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for
   SELECT  eaip_reimb_seq_num    as ReimbursementID ,
          received_date         as ReceivedDate     ,
          posted_date           as PostedDate           ,
          reconciled_date       as ReconciledDate       ,
          reconciled_flag       as IsReconciled     ,
          reimbursement_amount  as ReimbursementAmount,
          reconciled_amount     as ReconciledAmount ,
          explanation_text      as ExplanationText  ,
          create_user           as CreateUser         ,
          create_date           as CreateDate         ,
          update_user           as UpdateUser         ,
          update_date           as UpdateDate         ,
          update_num            as UpdateNum
  FROM    eaip_reimbursement
  WHERE received_date = ReceivedDate;
  END GetReimbByReceivedDate;

PROCEDURE GetReimbByPostedDate
(  PostedDate      IN date
 , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for
   SELECT  eaip_reimb_seq_num    as ReimbursementID ,
          received_date         as ReceivedDate     ,
          posted_date           as PostedDate           ,
          reconciled_date       as ReconciledDate       ,
          reconciled_flag       as IsReconciled     ,
          reimbursement_amount  as ReimbursementAmount,
          reconciled_amount     as ReconciledAmount ,
          explanation_text      as ExplanationText  ,
          create_user           as CreateUser         ,
          create_date           as CreateDate         ,
          update_user           as UpdateUser         ,
          update_date           as UpdateDate         ,
          update_num            as UpdateNum
  FROM    eaip_reimbursement
  WHERE posted_date = PostedDate;
  END  GetReimbByPostedDate;

 PROCEDURE GetReimbReconciledFlag
(  ReconciledFlag      IN varchar2
 , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for
   SELECT  eaip_reimb_seq_num    as ReimbursementID ,
          received_date         as ReceivedDate     ,
          posted_date           as PostedDate           ,
          reconciled_date       as ReconciledDate       ,
          reconciled_flag       as IsReconciled     ,
          reimbursement_amount  as ReimbursementAmount,
          reconciled_amount     as ReconciledAmount ,
          explanation_text      as ExplanationText  ,
          create_user           as CreateUser         ,
          create_date           as CreateDate         ,
          update_user           as UpdateUser         ,
          update_date           as UpdateDate         ,
          update_num            as UpdateNum
  FROM    eaip_reimbursement
   WHERE reconciled_flag = ReconciledFlag;
  END GetReimbReconciledFlag;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimbursement_insert
(
     ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,ReceivedDate         IN eaip_reimbursement.received_date%TYPE
    ,PostedDate           IN eaip_reimbursement.posted_date%TYPE
    ,ReconciledDate       IN eaip_reimbursement.reconciled_date%TYPE
    ,IsReconciled         IN eaip_reimbursement.reconciled_flag%TYPE
    ,ReimbursementAmount  IN eaip_reimbursement.reimbursement_amount%TYPE
    ,ReconciledAmount     IN eaip_reimbursement.reconciled_amount%TYPE
    ,ExplanationText      IN eaip_reimbursement.explanation_text%TYPE
    ,CreateUser           IN eaip_reimbursement.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
INSERT INTO   eaip_reimbursement
        (     eaip_reimb_seq_num          ,
              received_date               ,
              posted_date                 ,
              reconciled_date             ,
              reconciled_flag             ,
              reimbursement_amount        ,
              reconciled_amount           ,
              explanation_text            ,
              create_user                 ,
              create_date                 ,
              update_user                 ,
              update_date                 ,
              update_num )
       VALUES (
              ReimbursementID             ,
              ReceivedDate                ,
              PostedDate                  ,
              ReconciledDate              ,
              IsReconciled                ,
              ReimbursementAmount        ,
              ReconciledAmount            ,
              ExplanationText             ,
              CreateUser                 ,
              CreateDate                 ,
              UpdateUser                 ,
              UpdateDate                 ,
              UpdateNum );
 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimbursement_insert;

FUNCTION eaip_reimbursement_update
(
    ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,ReceivedDate         IN eaip_reimbursement.received_date%TYPE
    ,PostedDate           IN eaip_reimbursement.posted_date%TYPE
    ,ReconciledDate       IN eaip_reimbursement.reconciled_date%TYPE
    ,IsReconciled         IN eaip_reimbursement.reconciled_flag%TYPE
    ,ReimbursementAmount  IN eaip_reimbursement.reimbursement_amount%TYPE
    ,ReconciledAmount     IN eaip_reimbursement.reconciled_amount%TYPE
    ,ExplanationText      IN eaip_reimbursement.explanation_text%TYPE
    ,CreateUser           IN eaip_reimbursement.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement.update_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

UPDATE eaip_reimbursement
SET    eaip_reimb_seq_num         = ReimbursementID ,
       received_date              = ReceivedDate        ,
       posted_date                = PostedDate          ,
       reconciled_date            = ReconciledDate      ,
       reconciled_flag            = IsReconciled        ,
       reimbursement_amount       = ReimbursementAmount,
       reconciled_amount          = ReconciledAmount    ,
       explanation_text           = ExplanationText ,
       create_user                = CreateUser         ,
       create_date                = CreateDate         ,
       update_user                = UpdateUser         ,
       update_date                = UpdateDate         ,
       update_num                 = UpdateNum
WHERE  eaip_reimb_seq_num         = ReimbursementID
AND    update_num                 = LastUpdateNum;
 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimbursement_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimbursement_delete
(
     ReimbursementID      IN eaip_reimbursement.eaip_reimb_seq_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

  delete from eaip_reimbursement
  where eaip_reimb_seq_num = ReimbursementID
    and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimbursement_delete;





/********************************************
** SELECT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:  DATE:02-NOV-21
*********************************************/

PROCEDURE eaip_reimb_detail_Select
(   ReimbursementID            IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
 , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for

   SELECT  rd.eaip_reimb_detail_seq_num     as ReimbursementDetailID,
           rd.eaip_reimb_seq_num            as ReimbursementID        ,
           rd.dcbs_saif_claim_num           as DcbsSaifClaimNum        ,
           rd.dcbs_wcd_claim_num            as DcbsWcdClaimNum        ,
           rd.worker_name                   as WorkerName            ,
           rd.request_received_date         as RequestReceivedDate  ,
           RD.INJURY_DATE                   as InjuryDate  ,
           rd.detail_total_amount           as TotalAmount            ,
           rd.disabling_claim_flag          as IsDisablingClaim        ,
           rd.reconciled_flag               as IsReconciled            ,
           rd.name_matches_flag             as HasMatchingName      ,
           rd.create_user                   as CreateUser           ,
           rd.create_date                   as CreateDate           ,
           rd.update_user                   as UpdateUser           ,
           rd.update_date                   as UpdateDate           ,
           rd.update_num                    as UpdateNum
   FROM    eaip_reimbursement_detail rd
   WHERE   eaip_reimb_seq_num = ReimbursementID;

   END eaip_reimb_detail_Select;

   PROCEDURE GetReimbDetailByID
(   ReimbursementDetailID      IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
  , recordset                  IN OUT ref_cursor)
   IS
  Begin open recordset for

   SELECT  rd.eaip_reimb_detail_seq_num     as ReimbursementDetailID,
           rd.eaip_reimb_seq_num            as ReimbursementID        ,
           rd.dcbs_saif_claim_num           as DcbsSaifClaimNum        ,
           rd.dcbs_wcd_claim_num            as DcbsWcdClaimNum        ,
           rd.worker_name                   as WorkerName            ,
           rd.request_received_date         as RequestReceivedDate  ,
           RD.INJURY_DATE                   as InjuryDate  ,
           rd.detail_total_amount           as TotalAmount            ,
           rd.disabling_claim_flag          as IsDisablingClaim        ,
           rd.reconciled_flag               as IsReconciled            ,
           rd.name_matches_flag             as HasMatchingName      ,
           rd.create_user                   as CreateUser           ,
           rd.create_date                   as CreateDate           ,
           rd.update_user                   as UpdateUser           ,
           rd.update_date                   as UpdateDate           ,
           rd.update_num                    as UpdateNum
   FROM    eaip_reimbursement_detail rd
   WHERE   eaip_reimb_detail_seq_num    = ReimbursementDetailID;


   END GetReimbDetailByID;
/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimb_detail_insert
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,DcbsSaifClaimNum        IN eaip_reimbursement_detail.dcbs_saif_claim_num%TYPE
    ,DcbsWcdClaimNum         IN eaip_reimbursement_detail.dcbs_wcd_claim_num%TYPE
    ,WorkerName              IN eaip_reimbursement_detail.worker_name%TYPE
    ,RequestReceivedDate     IN eaip_reimbursement_detail.request_received_date%TYPE
    ,InjuryDate              IN eaip_reimbursement_detail.injury_date%TYPE
    ,TotalAmount             IN eaip_reimbursement_detail.detail_total_amount%TYPE
    ,IsDisablingClaim        IN eaip_reimbursement_detail.disabling_claim_flag%TYPE
    ,IsReconciled            IN eaip_reimbursement_detail.reconciled_flag%TYPE
    ,HasMatchingName         IN eaip_reimbursement_detail.name_matches_flag%TYPE
    ,CreateUser           IN eaip_reimbursement_detail.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_detail.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_detail.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_detail.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement_detail.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
INSERT INTO   eaip_reimbursement_detail
       (
              eaip_reimb_detail_seq_num   ,
              eaip_reimb_seq_num          ,
              dcbs_saif_claim_num         ,
              dcbs_wcd_claim_num          ,
              worker_name                 ,
              request_received_date       ,
              injury_date                 ,
              detail_total_amount         ,
              disabling_claim_flag        ,
              reconciled_flag             ,
              name_matches_flag           ,
              create_user                 ,
              create_date                 ,
              update_user                 ,
              update_date                 ,
              update_num
       )
       VALUES
       (
              ReimbursementDetailID       ,
              ReimbursementID             ,
              DcbsSaifClaimNum            ,
              DcbsWcdClaimNum             ,
              WorkerName                  ,
              RequestReceivedDate         ,
              InjuryDate                  ,
              TotalAmount                 ,
              IsDisablingClaim            ,
              IsReconciled                ,
              HasMatchingName             ,
              CreateUser                 ,
              CreateDate                 ,
              UpdateUser                 ,
              UpdateDate                 ,
              UpdateNum);
v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimb_detail_insert;

FUNCTION eaip_reimb_detail_update
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,DcbsSaifClaimNum        IN eaip_reimbursement_detail.dcbs_saif_claim_num%TYPE
    ,DcbsWcdClaimNum         IN eaip_reimbursement_detail.dcbs_wcd_claim_num%TYPE
    ,WorkerName              IN eaip_reimbursement_detail.worker_name%TYPE
    ,RequestReceivedDate     IN eaip_reimbursement_detail.request_received_date%TYPE
    ,InjuryDate              IN eaip_reimbursement_detail.injury_date%TYPE
    ,TotalAmount             IN eaip_reimbursement_detail.detail_total_amount%TYPE
    ,IsDisablingClaim        IN eaip_reimbursement_detail.disabling_claim_flag%TYPE
    ,IsReconciled            IN eaip_reimbursement_detail.reconciled_flag%TYPE
    ,HasMatchingName         IN eaip_reimbursement_detail.name_matches_flag%TYPE
    ,CreateUser           IN eaip_reimbursement_detail.create_user%TYPE
    ,CreateDate           IN eaip_reimbursement_detail.create_date%TYPE
    ,UpdateUser           IN eaip_reimbursement_detail.update_user%TYPE
    ,UpdateDate           IN eaip_reimbursement_detail.update_date%TYPE
    ,UpdateNum            IN eaip_reimbursement_detail.update_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
UPDATE eaip_reimbursement_detail
SET    eaip_reimb_detail_seq_num    = ReimbursementDetailID,
       eaip_reimb_seq_num           = ReimbursementID       ,
       dcbs_saif_claim_num          = DcbsSaifClaimNum      ,
       dcbs_wcd_claim_num           = DcbsWcdClaimNum       ,
       worker_name                  = WorkerName            ,
       request_received_date        = RequestReceivedDate  ,
       injury_date                  = InjuryDate            ,
       detail_total_amount          = TotalAmount           ,
       disabling_claim_flag         = IsDisablingClaim      ,
       reconciled_flag              = IsReconciled          ,
       name_matches_flag            = HasMatchingName      ,
       create_user                  = CreateUser           ,
       create_date                  = CreateDate           ,
       update_user                  = UpdateUser           ,
       update_date                  = UpdateDate           ,
       update_num                   = UpdateNum
WHERE  eaip_reimb_detail_seq_num    = ReimbursementDetailID
AND    update_num                   = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimb_detail_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_REIMBURSEMENT_DETAIL"
** DATE:14-FEB-11
*********************************************/
FUNCTION eaip_reimb_detail_delete
(
     ReimbursementDetailID   IN eaip_reimbursement_detail.eaip_reimb_detail_seq_num%TYPE
    ,ReimbursementID         IN eaip_reimbursement_detail.eaip_reimb_seq_num%TYPE
    ,LastUpdateNum        IN eaip_reimbursement.UPDATE_NUM%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

  delete from eaip_reimbursement_detail
  where eaip_reimb_detail_seq_num = ReimbursementDetailID
   and  update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_reimb_detail_delete;

PROCEDURE GetTargetingByClaim_New
(   recordset                IN  OUT ref_cursor,
    ClaimID           IN eaip_claim_targeting.Claim_id%TYPE)
   IS
  Begin open recordset for
      SELECT  TAR.EAIP_CLAIM_TARGETING_SEQ_NUM as TargetID,
              tar.claim_id             as ClaimID                    ,
              completed_date           as CompletedDate            ,
              eaip_targeting_code       as EaipTargetingCode        ,
              wage_subsidy_eff_date     as WageSubsidyEffectiveDate    ,
              wage_subsidy_exp_date     as WageSubsidyExpirationDate,
              reviewer_user             as ReviewerUser                ,
              targeting_text            as TargetingText            ,
              TAR.CLAIM_STATUS_EFF_DATE as ClaimStatusDate,
              TAR.CLAIM_STATUS_CODE     as ClaimStatusCode,
              tar.create_user            as CreateUser               ,
              tar.create_date            as CreateDate               ,
              tar.update_user            as UpdateUser               ,
              tar.update_date            as UpdateDate               ,
              tar.update_num             as UpdateNum
         FROM eaip_claim_targeting tar
        WHERE TAR.claim_id = ClaimID;

  END GetTargetingByClaim_New;

/********************************************
** INSERT PROCEDURE FOR TABLE "EAIP_CLAIM_TARGETING"
** DATE:  DATE:14-FEB-11
*********************************************/
FUNCTION eaip_targeting_insert
(
     TargetID                    IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    , ClaimID                    IN eaip_claim_targeting.claim_id%TYPE
    ,CompletedDate               IN eaip_claim_targeting.completed_date%TYPE
    ,EaipTargetingCode           IN eaip_claim_targeting.eaip_targeting_code%TYPE
    ,WageSubsidyEffectiveDate    IN eaip_claim_targeting.wage_subsidy_eff_date%TYPE
    ,WageSubsidyExpirationDate   IN eaip_claim_targeting.wage_subsidy_exp_date%TYPE
    ,ReviewerUser                IN eaip_claim_targeting.reviewer_user%TYPE
    ,TargetingText               IN eaip_claim_targeting.targeting_text%TYPE
    ,ClaimStatusDate             IN eaip_claim_targeting.CLAIM_STATUS_EFF_DATE%TYPE
    ,ClaimStatusCode             IN eaip_claim_targeting.CLAIM_STATUS_CODE%TYPE
    ,CreateUser                  IN eaip_claim_targeting.create_user%TYPE
    ,CreateDate                  IN eaip_claim_targeting.create_date%TYPE
    ,UpdateUser                  IN eaip_claim_targeting.update_user%TYPE
    ,UpdateDate                  IN eaip_claim_targeting.update_date%TYPE
    ,UpdateNum                   IN eaip_claim_targeting.update_num%TYPE

) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
    INSERT INTO   eaip_claim_targeting
                  (   EAIP_CLAIM_TARGETING_SEQ_NUM,
                      claim_id              ,
                      completed_date        ,
                      eaip_targeting_code   ,
                      wage_subsidy_eff_date ,
                      wage_subsidy_exp_date ,
                      reviewer_user         ,
                      targeting_text        ,
                      CLAIM_STATUS_EFF_DATE ,
                      CLAIM_STATUS_CODE     ,
                      create_user           ,
                      create_date           ,
                      update_user           ,
                      update_date           ,
                      update_num
                  )
                  VALUES
                  (   TargetID ,
                      ClaimID                   ,
                      CompletedDate         ,
                      EaipTargetingCode     ,
                      WageSubsidyEffectiveDate  ,
                      WageSubsidyExpirationDate,
                      ReviewerUser              ,
                      TargetingText         ,
                      ClaimStatusDate    ,
                      ClaimStatusCode       ,
                      CreateUser            ,
                      CreateDate            ,
                      UpdateUser               ,
                      UpdateDate               ,
                      UpdateNum);

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_targeting_insert;

FUNCTION eaip_targeting_update
(
    TargetID                     IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    ,ClaimID                     IN eaip_claim_targeting.claim_id%TYPE
    ,CompletedDate               IN eaip_claim_targeting.completed_date%TYPE
    ,EaipTargetingCode           IN eaip_claim_targeting.eaip_targeting_code%TYPE
    ,WageSubsidyEffectiveDate    IN eaip_claim_targeting.wage_subsidy_eff_date%TYPE
    ,WageSubsidyExpirationDate   IN eaip_claim_targeting.wage_subsidy_exp_date%TYPE
    ,ReviewerUser                IN eaip_claim_targeting.reviewer_user%TYPE
    ,TargetingText               IN eaip_claim_targeting.targeting_text%TYPE
    ,ClaimStatusDate             IN eaip_claim_targeting.CLAIM_STATUS_EFF_DATE%TYPE
    ,ClaimStatusCode             IN eaip_claim_targeting.CLAIM_STATUS_CODE%TYPE
    ,CreateUser                  IN eaip_claim_targeting.create_user%TYPE
    ,CreateDate                  IN eaip_claim_targeting.create_date%TYPE
    ,UpdateUser                  IN eaip_claim_targeting.update_user%TYPE
    ,UpdateDate                  IN eaip_claim_targeting.update_date%TYPE
    ,UpdateNum                   IN eaip_claim_targeting.update_num%TYPE
    ,LastUpdateNum               IN eaip_claim_targeting.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN
UPDATE eaip_claim_targeting
        SET    claim_id             = ClaimID                   ,
               completed_date       = CompletedDate         ,
               eaip_targeting_code  = EaipTargetingCode     ,
               wage_subsidy_eff_date= WageSubsidyEffectiveDate  ,
               wage_subsidy_exp_date= WageSubsidyExpirationDate,
               reviewer_user        = ReviewerUser              ,
               targeting_text       = TargetingText         ,
               claim_status_eff_date = ClaimStatusDate ,
               claim_status_code     = ClaimStatusCode ,
               create_user           = CreateUser               ,
               create_date           = CreateDate               ,
               update_user           = UpdateUser               ,
               update_date           = UpdateDate               ,
               update_num            = UpdateNum
        WHERE  EAIP_CLAIM_TARGETING_SEQ_NUM = TargetID
        AND    update_num           = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_targeting_update;
/********************************************
** DELETE PROCEDURE FOR TABLE "EAIP_CLAIM_TARGETING"
** DATE:16-FEB-11
*********************************************/
FUNCTION eaip_targeting_delete
(
      TargetID                    IN eaip_claim_targeting.EAIP_CLAIM_TARGETING_SEQ_NUM%TYPE
    ,LastUpdateNum           IN eaip_claim_targeting.update_num%TYPE
) RETURN NUMBER
as
v_update_count             NUMBER;
BEGIN

  delete from eaip_claim_targeting
   where EAIP_CLAIM_TARGETING_SEQ_NUM = TargetID
     and update_num = LastUpdateNum;

 v_update_count := SQL%Rowcount;
 RETURN v_update_count;

END eaip_targeting_delete;


/********************************************
** SELECT PROCEDURE FOR EAIP EXPENSES IN CHECK STATUS FOR BILLING
** DATE:  DATE:23-FEB-11
*********************************************/

PROCEDURE SelectExpensesInCheckStatus
(    recordset                  IN OUT ref_cursor)
     IS
  Begin open recordset for
      select P.EAIP_PERIOD_SEQ_NUM PeriodID
     from eaip_period p, eaip_wage_subsidy w, eaip_expense x
     where P.EAIP_PERIOD_SEQ_NUM = W.EAIP_PERIOD_SEQ_NUM
      and W.EAIP_EXPENSE_SEQ_NUM = X.EAIP_EXPENSE_SEQ_NUM
      and X.EAIP_EXPENSE_STATUS_CODE = 'CHECK'
      and X.ORIG_EXPENSE_SEQ_NUM is null
    union
     select P.EAIP_PERIOD_SEQ_NUM
       from eaip_period p, eaip_expense x,  eaip_purchase c
      where P.EAIP_PERIOD_SEQ_NUM = C.EAIP_PERIOD_SEQ_NUM
        and C.EAIP_EXPENSE_SEQ_NUM = X.EAIP_EXPENSE_SEQ_NUM
        and X.EAIP_EXPENSE_STATUS_CODE = 'CHECK'
        and X.ORIG_EXPENSE_SEQ_NUM is null;
  End  SelectExpensesInCheckStatus;

/********************************************
** SELECT PROCEDURE FOR EAIP EXPENSES IN SELECTED STATUS
** DATE:  DATE:07-APR-2011
*********************************************/

PROCEDURE SelectExpensesByStatus_GW
(   recordset                  OUT ref_cursor,
    ExpenseStatus              IN varchar2,
    Specialist                 IN varchar2)
Is
Begin open recordset for
  select ex.EAIP_EXPENSE_SEQ_NUM as ExpenseId,
       ep.claim_id as ClaimId,
       TC.CODE_DESCR as ExpenseTypeDesc,
       pr.purchase_descr as Description,
       ex.paid_amount as PaidAmount,
       EX.EXPENSE_STATUS_DATE as StatusDate,
       EP.EAIP_USER as Specialist,
       PR.CREDIT_CARD_PAID_DATE as CreditCardDate
from eaip_expense ex, eaip_purchase pr, eaip_period ep,
      eaip_expense_type_code tc
where PR.EAIP_PERIOD_SEQ_NUM = EP.EAIP_PERIOD_SEQ_NUM
  and PR.EAIP_EXPENSE_SEQ_NUM = EX.EAIP_EXPENSE_SEQ_NUM
  and EX.EAIP_EXPENSE_STATUS_CODE = ExpenseStatus
  and EX.EAIP_EXPENSE_STATUS_CODE = ExpenseStatus
  and EX.EAIP_EXPENSE_TYPE_CODE = TC.CODE
  and (EP.EAIP_USER = Specialist or Specialist= 'ALL')
 Union
  select ex.EAIP_EXPENSE_SEQ_NUM as ExpenseId,
       ep.claim_id as ClaimId,
       TC.CODE_DESCR as ExpenseTypeDesc,
       to_char(WS.WAGE_SUBSIDY_EFF_DATE,'MM/dd/yyyy')  || ' - ' || to_char(WS.WAGE_SUBSIDY_EXP_DATE,'MM/dd/yyyy') as Description,
       ex.paid_amount as PaidAmount,
       EX.EXPENSE_STATUS_DATE as StatusDate,
       EP.EAIP_USER as Specialist,
       null as CreditCardDate
from eaip_expense ex, eaip_wage_subsidy ws, eaip_period ep,
      eaip_expense_type_code tc
where
   WS.EAIP_PERIOD_SEQ_NUM = EP.EAIP_PERIOD_SEQ_NUM
  and WS.EAIP_EXPENSE_SEQ_NUM = EX.EAIP_EXPENSE_SEQ_NUM
  and EX.EAIP_EXPENSE_STATUS_CODE = ExpenseStatus
  and EX.EAIP_EXPENSE_TYPE_CODE = TC.CODE
  and (EP.EAIP_USER = Specialist or Specialist= 'ALL')
  order by 6, 1;

End SelectExpensesByStatus_GW;




PROCEDURE EAIPHeaderBySeqNumAndClaimID
(   recordset                  OUT ref_cursor,
    ServicePeriodSeqNum        IN NUMBER,
    ClaimID                IN NUMBER)
AS
Begin open recordset for

SELECT /*+FIRST_ROWS */ eaip_user AS EAIPUser ,
eaip_status_code AS ServiceStatusCode,
code_descr AS ServiceDescr ,
EAIP_status_date AS StatusDate ,
EAIP.claim_Id AS ClaimID
FROM eaip_period EAIP
join eaip_status_code ESC on EAIP.eaip_status_code = ESC.CODE
Where (EAIP.eaip_period_seq_num = ServicePeriodSeqNum OR eaip_period_seq_num <> NULL) OR (EAIP.claim_id = ClaimID);

END EAIPHeaderBySeqNumAndClaimID;

END Pk82500_eaip;

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified. EAIP_Billing_Select, eaip_billing_insert, eaip_billing_update, eaip_billing_delete, GetTargetingByClaim_New, SelectExpensesInCheckStatus, SelectExpensesByStatus_GW, and EAIPHeaderBySeqNumAndClaimID exist in the package but have no confirmed .NET caller in this repository; they may be invoked from another repository/screen, called dynamically, or unused.

Sources¶

Business\SAIF.Business.Claim.EAIP\Period\clsPeriod.vb
Business\SAIF.Business.Claim.EAIP\Period\clsCreditCardPayments.vb
Business\SAIF.Business.Claim.EAIP\Period\clsExpense.vb
Business\SAIF.Business.Claim.EAIP\Period\clsPurchase.vb
Business\SAIF.Business.Claim.EAIP\Period\clsPayPeriod.vb
Business\SAIF.Business.Claim.EAIP\Period\clsWageSubsidy.vb
Business\SAIF.Business.Claim.EAIP\Billing\clsBilling.vb
Business\SAIF.Business.Claim.EAIP\Billing\clsExpenseBilling.vb
Business\SAIF.Business.Claim.EAIP\Reimbursement\clsAmount.vb
Business\SAIF.Business.Claim.EAIP\Reimbursement\clsDetailAmount.vb
Business\SAIF.Business.Claim.EAIP\Reimbursement\clsReimbursement.vb
Business\SAIF.Business.Claim.EAIP\Reimbursement\clsReimbursementDetail.vb
Business\SAIF.Business.Claim.EAIP\Targeting\clsTargeting.vb
Oracle DBA_SOURCE / ALL_SOURCE: WCIS_DBA.pk82500_eaip