Skip to content

PK82538_ONLINE_801_CLAIM

Overview

Field Value
Schema WCIS_DBA
Type PACKAGE
Status Found in Oracle during Stage 1b analysis
Calling libraries SAIF.Business.Claim.Entry
Calling classes cls801Document, cls801Submission, cls801SubmissionData, clsSubmissionResult

Purpose

This package is invoked by the library's BOF persistence or retrieval paths and is documented here from the calling-code contracts captured during Stage 1b analysis.

Procedures / Methods

document_insert

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801Document

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:UpdateNum Input Unknown — Passed from cls801Document via BeforeInsert.
:UpdateUser Input Unknown — Passed from cls801Document via BeforeInsert.
:UpdateDate Input Unknown — Passed from cls801Document via BeforeInsert.
:CreateUser Input Unknown — Passed from cls801Document via BeforeInsert.
:CreateDate Input Unknown — Passed from cls801Document via BeforeInsert.

document_update

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801Document

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:LastUpdateNum Input Unknown — Passed from cls801Document via BeforeUpdate.
:UpdateNum Input Unknown — Passed from cls801Document via BeforeUpdate.
:UpdateUser Input Unknown — Passed from cls801Document via BeforeUpdate.
:UpdateDate Input Unknown — Passed from cls801Document via BeforeUpdate.

document_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by See package overview

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

document_select_by_imageseqnum

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by See package overview

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

submission_insert

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801Submission

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:CreateUser Input Unknown — Passed from cls801Submission via BeforeInsert.
:CreateDate Input Unknown — Passed from cls801Submission via BeforeInsert.

submission_update

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801Submission

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:LastUpdateNum Input Unknown — Passed from cls801Submission via BeforeUpdate.
:UpdateNum Input Unknown — Passed from cls801Submission via BeforeUpdate.
:UpdateUser Input Unknown — Passed from cls801Submission via BeforeUpdate.
:UpdateDate Input Unknown — Passed from cls801Submission via BeforeUpdate.

submission_delete

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801Submission

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:SubSeqNum Input Unknown — Passed from cls801Submission via BeforeDelete.
:LastUpdateNum Input Unknown — Passed from cls801Submission via BeforeDelete.

submission_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by See package overview

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

submission_claim_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by See package overview

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

submission_policy_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by clsSubmissionResult

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
— — — — No explicit bind parameters were found in the matched caller.

submission_cleanup_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by See package overview

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

incomplete_submission_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by clsSubmissionResult

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
— — — — No explicit bind parameters were found in the matched caller.

submission_data_insert

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801SubmissionData

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:CreateDate Input Unknown — Passed from cls801SubmissionData via BeforeInsert.
:CreateUser Input Unknown — Passed from cls801SubmissionData via BeforeInsert.

submission_data_update

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801SubmissionData

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:LastUpdateNum Input Unknown — Passed from cls801SubmissionData via BeforeUpdate.
:UpdateDate Input Unknown — Passed from cls801SubmissionData via BeforeUpdate.
:UpdateUser Input Unknown — Passed from cls801SubmissionData via BeforeUpdate.
:UpdateNum Input Unknown — Passed from cls801SubmissionData via BeforeUpdate.

submission_data_delete

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801SubmissionData

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:SubSeqNum Input Unknown — Passed from cls801SubmissionData via BeforeDelete.
:LastUpdateNum Input Unknown — Passed from cls801SubmissionData via BeforeDelete.

submission_data_select

Field Value
Returns Result set or rowcount contract depends on the calling SQL wrapper.
Called by cls801SubmissionData

Description: Oracle procedure call surfaced through the BOF criteria wrapper.

Parameters

Parameter Direction Type Default Description
:SubSeqNum Input Unknown — Passed from cls801SubmissionData via BeforeRetrieve.

Business Rules

Rule ID Rule Condition Action
PR-001 Updates only succeed when the caller-supplied LastUpdateNum matches the current row version. WHERE ... UPDATE_NUM = LastUpdateNum in submission_update. Returns SQL%ROWCOUNT; the BOF caller treats non-1 results as a contention failure.
PR-002 Deletes only succeed when the caller-supplied LastUpdateNum matches the current row version. WHERE ... UPDATE_NUM = LastUpdateNum in submission_delete and submission_data_delete. Returns SQL%ROWCOUNT so the caller can reject stale deletes.
PR-003 Document updates are version-checked with LastUpdateNum. WHERE CL_801_DOCUMENT_SEQ_NUM = DocSeqNum and update_num = LastUpdateNum. Protects Online 801 document rows from lost updates.

These rules are also documented in Business rules.

Operations

Operation Description Trigger
Retrieve Online 801 submissions Returns submission rows by submission ID, claim ID, policy number, or retention-date cleanup mode. cls801Submission retrieval helpers and cleanup utilities.
Persist Online 801 submission headers Inserts, updates, and deletes Online 801 submission header rows. cls801Submission.BeforeInsert, BeforeUpdate, and BeforeDelete.
Persist Online 801 submission detail Inserts, updates, retrieves, and deletes Online 801 submission detail rows. cls801SubmissionData CRUD lifecycle methods.
Persist Online 801 documents Retrieves and maintains Online 801 document metadata keyed by claim or image sequence number. cls801Document CRUD and helper retrieval methods.

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.PK82538_ONLINE_801_CLAIM
PACKAGE                   PK82538_ONLINE_801_CLAIM
AS
   /*****************************************************************
   *PROGRAMMER     ANALYST                 DATE       MOD #
   * Gregg Milks                        02/06/2012   New.
   *******************************************************************/
   TYPE ref_cursor IS REF CURSOR;

   PROCEDURE submission_select (
      SubSeqNum   IN     CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      recordset OUT ref_cursor);

   PROCEDURE submission_claim_select (
      ClaimID   IN     CL_801_SUBMISSION.CLAIM_ID%TYPE,
      recordset OUT ref_cursor);

   PROCEDURE submission_policy_select (
      PolicyNum   IN     CL_801_SUBMISSION.POLICY_NUM%TYPE,
      recordset   OUT ref_cursor);

  PROCEDURE submission_cleanup_select (
      RetentionDate   IN     Date,
      recordset   OUT ref_cursor);

  PROCEDURE Incomplete_Submission_select (
      RetentionDate   IN     Date,
      recordset   OUT ref_cursor);


   FUNCTION submission_insert (
      SubSeqNum                 IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      ClaimID                   IN CL_801_SUBMISSION.CLAIM_ID%TYPE,
      PolicyNum                    IN CL_801_SUBMISSION.POLICY_NUM%TYPE,
      SubmissionStatusCode      IN CL_801_SUBMISSION.SUBMISSION_STATUS_CODE%TYPE,
      Box8InjuryDescr           IN CL_801_SUBMISSION.BOX8_INJURY_DESCR%TYPE,
      Box8LeftSideInjuryFlag    IN CL_801_SUBMISSION.BOX8_LEFT_SIDE_INJURY_FLAG%TYPE,
      Box8RightSideInjuryFlag   IN CL_801_SUBMISSION.BOX8_RIGHT_SIDE_INJURY_FLAG%TYPE,
      Box10SaifEventDescr        IN CL_801_SUBMISSION.BOX10_SAIF_EVENT_DESCR%TYPE,
      InitialSubmitDate         IN CL_801_SUBMISSION.INITIAL_SUBMIT_DATE%TYPE,
      CreateUser                IN CL_801_SUBMISSION.CREATE_USER%TYPE,
      CreateDate                IN CL_801_SUBMISSION.CREATE_DATE%TYPE)
      RETURN NUMBER;
   FUNCTION submission_update (
      SubSeqNum                 IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      ClaimID                   IN CL_801_SUBMISSION.CLAIM_ID%TYPE,
      PolicyNum                    IN CL_801_SUBMISSION.POLICY_NUM%TYPE,
      SubmissionStatusCode      IN CL_801_SUBMISSION.SUBMISSION_STATUS_CODE%TYPE,
      Box8InjuryDescr           IN CL_801_SUBMISSION.BOX8_INJURY_DESCR%TYPE,
      Box8LeftSideInjuryFlag    IN CL_801_SUBMISSION.BOX8_LEFT_SIDE_INJURY_FLAG%TYPE,
      Box8RightSideInjuryFlag   IN CL_801_SUBMISSION.BOX8_RIGHT_SIDE_INJURY_FLAG%TYPE,
      Box10SaifEventDescr        IN CL_801_SUBMISSION.BOX10_SAIF_EVENT_DESCR%TYPE,
      InitialSubmitDate         IN CL_801_SUBMISSION.INITIAL_SUBMIT_DATE%TYPE,
      UpdateNum                 IN CL_801_SUBMISSION.UPDATE_NUM%TYPE,
      UpdateUser                IN CL_801_SUBMISSION.UPDATE_USER%TYPE,
      UpdateDate                IN CL_801_SUBMISSION.UPDATE_DATE%TYPE,
      LastUpdateNum             IN CL_801_SUBMISSION.UPDATE_NUM%TYPE)
      RETURN NUMBER;
   FUNCTION submission_delete (
      SubSeqNum IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      LastUpdateNum           IN CL_801_SUBMISSION.UPDATE_NUM%TYPE)
      RETURN NUMBER;


    PROCEDURE submission_data_select (
        SubSeqNum   IN     CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        recordset OUT ref_cursor);

    FUNCTION submission_data_insert (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        WorkerID IN CL_801_SUBMISSION_DATA.WORKER_ID%TYPE,
        WorkerPersonID IN CL_801_SUBMISSION_DATA.WORKER_PERSON_SEQ_NUM%TYPE,
        SubmissionPersonID IN CL_801_SUBMISSION_DATA.SUBMISSION_PERSON_SEQ_NUM%TYPE,
        ProviderPersonID IN CL_801_SUBMISSION_DATA.PROVIDER_PERSON_SEQ_NUM%TYPE,
        InsurancePersonID IN CL_801_SUBMISSION_DATA.INSURANCE_PERSON_SEQ_NUM%TYPE,
        SupervisedPersonID IN CL_801_SUBMISSION_DATA.SUPERVISED_PERSON_SEQ_NUM%TYPE,
        EventPersonID IN CL_801_SUBMISSION_DATA.EVENT_PERSON_SEQ_NUM%TYPE,
        Box1InjuryDate IN CL_801_SUBMISSION_DATA.BOX1_INJURY_DATE%TYPE,
        Box2LeaveWorkDate IN CL_801_SUBMISSION_DATA.BOX2_LEAVE_WORK_DATE%TYPE,
        Box3BeganWorkTime IN CL_801_SUBMISSION_DATA.BOX3_BEGAN_WORK_TIME%TYPE,
        Box4MondayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_MONDAY_OFF_FLAG%TYPE,
        Box4TuesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_TUESDAY_OFF_FLAG%TYPE,
        Box4WednesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_WEDNESDAY_OFF_FLAG%TYPE,
        Box4ThursdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_THURSDAY_OFF_FLAG%TYPE,
        Box4FridayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_FRIDAY_OFF_FLAG%TYPE,
        Box4SaturdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SATURDAY_OFF_FLAG%TYPE,
        Box4SundayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SUNDAY_OFF_FLAG%TYPE,
        Box5InjuryTime IN CL_801_SUBMISSION_DATA.BOX5_INJURY_TIME%TYPE,
        Box6LeaveWorkTime IN CL_801_SUBMISSION_DATA.BOX6_LEAVE_WORK_TIME%TYPE,
        Box7ShiftStartTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_START_TIME%TYPE,
        Box7ShiftEndTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_END_TIME%TYPE,
        Box9MultipleEmployerFlag IN CL_801_SUBMISSION_DATA.BOX9_MULTIPLE_EMPR_FLAG%TYPE,
        Box11PrimaryFirstName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_FIRST_NAME%TYPE,
        Box11PrimaryLastName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_LAST_NAME%TYPE,
        Box11PrimaryMiddleName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_MIDDLE_NAME%TYPE,
        Box11PrimaryNameExtension IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_EXTENSION_NAME%TYPE,
        Box12LanguagePrefCode IN CL_801_SUBMISSION_DATA.BOX12_LANGUAGE_PREF_CODE%TYPE,
        Box12WorkerLanguageOtherText IN CL_801_SUBMISSION_DATA.BOX12_WORKER_LANG_OTHER_TEXT%TYPE,
        Box13BirthDate IN CL_801_SUBMISSION_DATA.BOX13_BIRTH_DATE%TYPE,
        Box14GenderCode IN CL_801_SUBMISSION_DATA.BOX14_GENDER_CODE%TYPE,
        Box17PrimaryTaxID IN CL_801_SUBMISSION_DATA.BOX17_PRIMARY_TAX_ID%TYPE,
        Box18WorkerOccupationDescr IN CL_801_SUBMISSION_DATA.BOX18_WORKER_OCCUPATION_DESCR%TYPE,
        Box20WitnessNames IN CL_801_SUBMISSION_DATA.BOX20_WITNESS_NAMES%TYPE,
        Box23PriorBodyPartFlagCode IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BDY_PART_FLAG_CODE%TYPE,
        Box23PriorBodyPartText IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BODY_PART_TEXT%TYPE,
        Box24InpatientFlagCode IN CL_801_SUBMISSION_DATA.BOX24_INPATIENT_FLAG_CODE%TYPE,
        Box24OvernightInpatientText IN CL_801_SUBMISSION_DATA.BOX24_OVERNIGHT_INPATIENT_TEXT%TYPE,
        Box25ERTreatmentFlagCode IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_FLAG_CODE%TYPE,
        Box25ERTreatmentText IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_TEXT%TYPE,
        Box30EmployerLegalName IN CL_801_SUBMISSION_DATA.BOX30_EMPLOYER_LEGAL_NAME%TYPE,
        Box32FEINText IN CL_801_SUBMISSION_DATA.BOX32_FEIN_TEXT%TYPE,
        Box33WLClientBusinessName IN CL_801_SUBMISSION_DATA.BOX33_WL_CLIENT_BUS_NAME%TYPE,
        Box34ClientFEINText IN CL_801_SUBMISSION_DATA.BOX34_CLIENT_FEIN_TEXT%TYPE,
        Box36InsurancePolicyNum IN CL_801_SUBMISSION_DATA.BOX36_INS_POLICY_NUM%TYPE,
        Box38NatureOfBusiness IN CL_801_SUBMISSION_DATA.BOX38_NATURE_OF_BUS_TXT%TYPE,
        Box40HardwareCauseFlagCode IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSE_FLAG_CODE%TYPE,
        Box40HardwareCausedText IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSED_TEXT%TYPE,
        Box40PersonCausedText IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_TEXT%TYPE,
        Box40PersonCausedFlagCode IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_FLAG_CODE%TYPE,
        Box41SAIFProviderPayrollClass IN CL_801_SUBMISSION_DATA.BOX41_SAIF_PROV_PAY_CLASS_CODE%TYPE,
        Box42MultiWorkerInjFlagCode IN CL_801_SUBMISSION_DATA.BOX42_MULTI_WORKER_FLAG_CODE%TYPE,
        Box43EmploymentInjuryCode IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_CODE%TYPE,
        Box43EmploymentInjuryText IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_TEXT%TYPE,
        Box44OSHACaseNum IN CL_801_SUBMISSION_DATA.BOX44_OSHA_CASE_NUM%TYPE,
        Box45KnowledgeDate IN CL_801_SUBMISSION_DATA.BOX45_KNOWLEDGE_DATE%TYPE,
        Box46WageAmount IN CL_801_SUBMISSION_DATA.BOX46_WAGE_AMT%TYPE,
        Box46WageUnitCode IN CL_801_SUBMISSION_DATA.BOX46_WAGE_UNIT_CODE%TYPE,
        Box47WorkerHireDate IN CL_801_SUBMISSION_DATA.BOX47_WORKER_HIRE_DATE%TYPE,
        Box48DeathDate IN CL_801_SUBMISSION_DATA.BOX48_DEATH_DATE%TYPE,
        Box49RTWNotReturnedFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_NOT_RETURNED_FLAG%TYPE,
        Box49RTWRegularDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_DATE%TYPE,
        Box49RTWModifiedDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_DATE%TYPE,
        Box49RTWRegularWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_FLAG%TYPE,
        Box49RTWModifiedWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_FLAG%TYPE,
        Box50RTWRegularHWFlagCode IN CL_801_SUBMISSION_DATA.BOX50_RTW_REGULAR_HW_FLAG_CODE%TYPE,
        EmployerProvidedExtraText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_EXTRA_TEXT%TYPE,
        EmployerProvidedWorkerAreaText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_WORKER_AREA_TXT%TYPE,
        EmployerProvidedWorkerDeptDesc IN CL_801_SUBMISSION_DATA.EMPR_PROV_WORKER_DEPT_DESCR%TYPE,
        EmployerProvidedShiftCode IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_SHIFT_CODE%TYPE,
        EmployerProvidedPayClassCode IN CL_801_SUBMISSION_DATA.EMPR_PROV_PAYROLL_CLASS_CODE%TYPE,
        ClaimNotesText IN CL_801_SUBMISSION_DATA.CLAIM_NOTES_TEXT%TYPE,
        Use801SSNFlag IN CL_801_SUBMISSION_DATA.USE_801_SSN_FLAG%TYPE,
        Use801NameFlag IN CL_801_SUBMISSION_DATA.USE_801_NAME_FLAG%TYPE,
        WorkerDataVerifiedFlag IN CL_801_SUBMISSION_DATA.WORKER_DATA_VERIFIED_FLAG%TYPE,
        CreateDate IN CL_801_SUBMISSION_DATA.CREATE_DATE%TYPE,
        CreateUser IN CL_801_SUBMISSION_DATA.CREATE_USER%TYPE,
        AllowDataReplacementFlag IN CL_801_SUBMISSION_DATA.ALLOW_DATA_REPLACEMENT_FLAG%TYPE)
        RETURN NUMBER;

    FUNCTION submission_data_update (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        WorkerID IN CL_801_SUBMISSION_DATA.WORKER_ID%TYPE,
        WorkerPersonID IN CL_801_SUBMISSION_DATA.WORKER_PERSON_SEQ_NUM%TYPE,
        SubmissionPersonID IN CL_801_SUBMISSION_DATA.SUBMISSION_PERSON_SEQ_NUM%TYPE,
        ProviderPersonID IN CL_801_SUBMISSION_DATA.PROVIDER_PERSON_SEQ_NUM%TYPE,
        InsurancePersonID IN CL_801_SUBMISSION_DATA.INSURANCE_PERSON_SEQ_NUM%TYPE,
        SupervisedPersonID IN CL_801_SUBMISSION_DATA.SUPERVISED_PERSON_SEQ_NUM%TYPE,
        EventPersonID IN CL_801_SUBMISSION_DATA.EVENT_PERSON_SEQ_NUM%TYPE,
        Box1InjuryDate IN CL_801_SUBMISSION_DATA.BOX1_INJURY_DATE%TYPE,
        Box2LeaveWorkDate IN CL_801_SUBMISSION_DATA.BOX2_LEAVE_WORK_DATE%TYPE,
        Box3BeganWorkTime IN CL_801_SUBMISSION_DATA.BOX3_BEGAN_WORK_TIME%TYPE,
        Box4MondayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_MONDAY_OFF_FLAG%TYPE,
        Box4TuesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_TUESDAY_OFF_FLAG%TYPE,
        Box4WednesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_WEDNESDAY_OFF_FLAG%TYPE,
        Box4ThursdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_THURSDAY_OFF_FLAG%TYPE,
        Box4FridayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_FRIDAY_OFF_FLAG%TYPE,
        Box4SaturdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SATURDAY_OFF_FLAG%TYPE,
        Box4SundayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SUNDAY_OFF_FLAG%TYPE,
        Box5InjuryTime IN CL_801_SUBMISSION_DATA.BOX5_INJURY_TIME%TYPE,
        Box6LeaveWorkTime IN CL_801_SUBMISSION_DATA.BOX6_LEAVE_WORK_TIME%TYPE,
        Box7ShiftStartTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_START_TIME%TYPE,
        Box7ShiftEndTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_END_TIME%TYPE,
        Box9MultipleEmployerFlag IN CL_801_SUBMISSION_DATA.BOX9_MULTIPLE_EMPR_FLAG%TYPE,
        Box11PrimaryFirstName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_FIRST_NAME%TYPE,
        Box11PrimaryLastName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_LAST_NAME%TYPE,
        Box11PrimaryMiddleName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_MIDDLE_NAME%TYPE,
        Box11PrimaryNameExtension IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_EXTENSION_NAME%TYPE,
        Box12LanguagePrefCode IN CL_801_SUBMISSION_DATA.BOX12_LANGUAGE_PREF_CODE%TYPE,
        Box12WorkerLanguageOtherText IN CL_801_SUBMISSION_DATA.BOX12_WORKER_LANG_OTHER_TEXT%TYPE,
        Box13BirthDate IN CL_801_SUBMISSION_DATA.BOX13_BIRTH_DATE%TYPE,
        Box14GenderCode IN CL_801_SUBMISSION_DATA.BOX14_GENDER_CODE%TYPE,
        Box17PrimaryTaxID IN CL_801_SUBMISSION_DATA.BOX17_PRIMARY_TAX_ID%TYPE,
        Box18WorkerOccupationDescr IN CL_801_SUBMISSION_DATA.BOX18_WORKER_OCCUPATION_DESCR%TYPE,
        Box20WitnessNames IN CL_801_SUBMISSION_DATA.BOX20_WITNESS_NAMES%TYPE,
        Box23PriorBodyPartFlagCode IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BDY_PART_FLAG_CODE%TYPE,
        Box23PriorBodyPartText IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BODY_PART_TEXT%TYPE,
        Box24InpatientFlagCode IN CL_801_SUBMISSION_DATA.BOX24_INPATIENT_FLAG_CODE%TYPE,
        Box24OvernightInpatientText IN CL_801_SUBMISSION_DATA.BOX24_OVERNIGHT_INPATIENT_TEXT%TYPE,
        Box25ERTreatmentFlagCode IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_FLAG_CODE%TYPE,
        Box25ERTreatmentText IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_TEXT%TYPE,
        Box30EmployerLegalName IN CL_801_SUBMISSION_DATA.BOX30_EMPLOYER_LEGAL_NAME%TYPE,
        Box32FEINText IN CL_801_SUBMISSION_DATA.BOX32_FEIN_TEXT%TYPE,
        Box33WLClientBusinessName IN CL_801_SUBMISSION_DATA.BOX33_WL_CLIENT_BUS_NAME%TYPE,
        Box34ClientFEINText IN CL_801_SUBMISSION_DATA.BOX34_CLIENT_FEIN_TEXT%TYPE,
        Box36InsurancePolicyNum IN CL_801_SUBMISSION_DATA.BOX36_INS_POLICY_NUM%TYPE,
        Box38NatureOfBusiness IN CL_801_SUBMISSION_DATA.BOX38_NATURE_OF_BUS_TXT%TYPE,
        Box40HardwareCauseFlagCode IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSE_FLAG_CODE%TYPE,
        Box40HardwareCausedText IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSED_TEXT%TYPE,
        Box40PersonCausedText IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_TEXT%TYPE,
        Box40PersonCausedFlagCode IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_FLAG_CODE%TYPE,
        Box41SAIFProviderPayrollClass IN CL_801_SUBMISSION_DATA.BOX41_SAIF_PROV_PAY_CLASS_CODE%TYPE,
        Box42MultiWorkerInjFlagCode IN CL_801_SUBMISSION_DATA.BOX42_MULTI_WORKER_FLAG_CODE%TYPE,
        Box43EmploymentInjuryCode IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_CODE%TYPE,
        Box43EmploymentInjuryText IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_TEXT%TYPE,
        Box44OSHACaseNum IN CL_801_SUBMISSION_DATA.BOX44_OSHA_CASE_NUM%TYPE,
        Box45KnowledgeDate IN CL_801_SUBMISSION_DATA.BOX45_KNOWLEDGE_DATE%TYPE,
        Box46WageAmount IN CL_801_SUBMISSION_DATA.BOX46_WAGE_AMT%TYPE,
        Box46WageUnitCode IN CL_801_SUBMISSION_DATA.BOX46_WAGE_UNIT_CODE%TYPE,
        Box47WorkerHireDate IN CL_801_SUBMISSION_DATA.BOX47_WORKER_HIRE_DATE%TYPE,
        Box48DeathDate IN CL_801_SUBMISSION_DATA.BOX48_DEATH_DATE%TYPE,
        Box49RTWNotReturnedFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_NOT_RETURNED_FLAG%TYPE,
        Box49RTWRegularDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_DATE%TYPE,
        Box49RTWModifiedDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_DATE%TYPE,
        Box49RTWRegularWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_FLAG%TYPE,
        Box49RTWModifiedWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_FLAG%TYPE,
        Box50RTWRegularHWFlagCode IN CL_801_SUBMISSION_DATA.BOX50_RTW_REGULAR_HW_FLAG_CODE%TYPE,
        EmployerProvidedExtraText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_EXTRA_TEXT%TYPE,
        EmployerProvidedWorkerAreaText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_WORKER_AREA_TXT%TYPE,
        EmployerProvidedWorkerDeptDesc IN CL_801_SUBMISSION_DATA.EMPR_PROV_WORKER_DEPT_DESCR%TYPE,
        EmployerProvidedShiftCode IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_SHIFT_CODE%TYPE,
        EmployerProvidedPayClassCode IN CL_801_SUBMISSION_DATA.EMPR_PROV_PAYROLL_CLASS_CODE%TYPE,
        ClaimNotesText IN CL_801_SUBMISSION_DATA.CLAIM_NOTES_TEXT%TYPE,
        Use801SSNFlag IN CL_801_SUBMISSION_DATA.USE_801_SSN_FLAG%TYPE,
        Use801NameFlag IN CL_801_SUBMISSION_DATA.USE_801_NAME_FLAG%TYPE,
        WorkerDataVerifiedFlag IN CL_801_SUBMISSION_DATA.WORKER_DATA_VERIFIED_FLAG%TYPE,
        LastUpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE,
        UpdateDate IN CL_801_SUBMISSION_DATA.UPDATE_DATE%TYPE,
        UpdateUser IN CL_801_SUBMISSION_DATA.UPDATE_USER%TYPE,
        UpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE,
        AllowDataReplacementFlag IN CL_801_SUBMISSION_DATA.ALLOW_DATA_REPLACEMENT_FLAG%TYPE
        )
        RETURN NUMBER;

    FUNCTION submission_data_delete (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        LastUpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE)
        RETURN NUMBER;


   PROCEDURE document_select (
      ClaimID   IN     CL_801_DOCUMENT.CLAIM_ID%TYPE,
      recordset  OUT ref_cursor);

   PROCEDURE document_select_by_imageseqnum (
      ImageSeqNum   IN     CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      recordset  OUT ref_cursor);

   FUNCTION document_insert (
      DocSeqNum     IN CL_801_DOCUMENT.CL_801_DOCUMENT_SEQ_NUM%TYPE,
      ClaimID     IN CL_801_DOCUMENT.CLAIM_ID%TYPE,
      FileName      IN CL_801_DOCUMENT.FILE_NAME%TYPE,
      DocTypeCode   IN CL_801_DOCUMENT.CL_801_DOCUMENT_TYPE_CODE%TYPE,
      ImageSeqNum   IN CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      UpdateNum     IN CL_801_DOCUMENT.UPDATE_NUM%TYPE,
      UpdateUser    IN CL_801_DOCUMENT.UPDATE_USER%TYPE,
      UpdateDate    IN CL_801_DOCUMENT.UPDATE_DATE%TYPE,
      CreateUser    IN CL_801_DOCUMENT.CREATE_USER%TYPE,
      CreateDate    IN CL_801_DOCUMENT.CREATE_DATE%TYPE)
      RETURN NUMBER;

   FUNCTION document_update (
      DocSeqNum     IN CL_801_DOCUMENT.CL_801_DOCUMENT_SEQ_NUM%TYPE,
      ClaimID     IN CL_801_DOCUMENT.CLAIM_ID%TYPE,
      FileName      IN CL_801_DOCUMENT.FILE_NAME%TYPE,
      DocTypeCode   IN CL_801_DOCUMENT.CL_801_DOCUMENT_TYPE_CODE%TYPE,
      ImageSeqNum   IN CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      UpdateNum     IN CL_801_DOCUMENT.UPDATE_NUM%TYPE,
      UpdateUser    IN CL_801_DOCUMENT.UPDATE_USER%TYPE,
      UpdateDate    IN CL_801_DOCUMENT.UPDATE_DATE%TYPE,
      LastUpdateNum IN CL_801_DOCUMENT.UPDATE_NUM%TYPE)
      RETURN NUMBER;

END;
 /
PL/SQL package/procedure body 
-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.PK82538_ONLINE_801_CLAIM
PACKAGE BODY                            PK82538_ONLINE_801_CLAIM
AS
   /*****************************************************************
   *PROGRAMMER     ANALYST                 DATE       MOD
   * Gregg Milks                        02/06/2012   New.
   *******************************************************************/

   PROCEDURE submission_select (
      SubSeqNum   IN     CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      recordset OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
         SELECT CL_801_SUBMISSION_SEQ_NUM AS SubSeqNum,
                CLAIM_ID AS ClaimID,
                POLICY_NUM as PolicyNum,
                SUBMISSION_STATUS_CODE AS SubmissionStatusCode,
                BOX8_INJURY_DESCR AS Box8InjuryDescr,
                BOX8_LEFT_SIDE_INJURY_FLAG AS Box8LeftSideInjuryFlag,
                BOX8_RIGHT_SIDE_INJURY_FLAG AS Box8RightSideInjuryFlag,
                BOX10_SAIF_EVENT_DESCR AS Box10SaifEventDescr,
                INITIAL_SUBMIT_DATE AS InitialSubmitDate,
                S.UPDATE_NUM AS UpdateNum,
                S.UPDATE_USER AS UpdateUser,
                S.UPDATE_DATE AS UpdateDate,
                S.CREATE_USER AS CreateUser,
                S.CREATE_DATE AS CreateDate,
                NVL(EXT.PERSON_SEQ_NUM, 0) as CreatePersonId
           FROM CL_801_SUBMISSION S, EXT_USER EXT
          WHERE S.CL_801_SUBMISSION_SEQ_NUM = SubSeqNum
          AND S.CREATE_USER = EXT.ACTIVE_DIRECTORY_ID (+);
   END submission_select;

   PROCEDURE submission_claim_select (
      ClaimID   IN     CL_801_SUBMISSION.CLAIM_ID%TYPE,
      recordset OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
         SELECT CL_801_SUBMISSION_SEQ_NUM AS SubSeqNum,
                CLAIM_ID AS ClaimID,
                POLICY_NUM as PolicyNum,
                SUBMISSION_STATUS_CODE AS SubmissionStatusCode,
                BOX8_INJURY_DESCR AS Box8InjuryDescr,
                BOX8_LEFT_SIDE_INJURY_FLAG AS Box8LeftSideInjuryFlag,
                BOX8_RIGHT_SIDE_INJURY_FLAG AS Box8RightSideInjuryFlag,
                BOX10_SAIF_EVENT_DESCR AS Box10SaifEventDescr,
                INITIAL_SUBMIT_DATE AS InitialSubmitDate,
                S.UPDATE_NUM AS UpdateNum,
                S.UPDATE_USER AS UpdateUser,
                S.UPDATE_DATE AS UpdateDate,
                S.CREATE_USER AS CreateUser,
                S.CREATE_DATE AS CreateDate,
                NVL(EXT.PERSON_SEQ_NUM, 0) as CreatePersonId
           FROM CL_801_SUBMISSION  S, EXT_USER EXT
          WHERE CLAIM_ID = ClaimID
          AND S.CREATE_USER = EXT.ACTIVE_DIRECTORY_ID (+);
   END submission_claim_select;

   PROCEDURE submission_policy_select (
      PolicyNum   IN     CL_801_SUBMISSION.POLICY_NUM%TYPE,
      recordset   OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
         SELECT CL_801_SUBMISSION_SEQ_NUM AS SubSeqNum,
                CLAIM_ID AS ClaimID,
                POLICY_NUM as PolicyNum,
                SUBMISSION_STATUS_CODE AS SubmissionStatusCode,
                BOX8_INJURY_DESCR AS Box8InjuryDescr,
                BOX8_LEFT_SIDE_INJURY_FLAG AS Box8LeftSideInjuryFlag,
                BOX8_RIGHT_SIDE_INJURY_FLAG AS Box8RightSideInjuryFlag,
                BOX10_SAIF_EVENT_DESCR AS Box10SaifEventDescr,
                INITIAL_SUBMIT_DATE AS InitialSubmitDate,
                UPDATE_NUM AS UpdateNum,
                UPDATE_USER AS UpdateUser,
                UPDATE_DATE AS UpdateDate,
                CREATE_USER AS CreateUser,
                CREATE_DATE AS CreateDate
           FROM CL_801_SUBMISSION
          WHERE POLICY_NUM = PolicyNum;
   END submission_policy_select;

 PROCEDURE submission_cleanup_select (
      RetentionDate   IN     Date,
      recordset       OUT ref_cursor
      )
   IS
   BEGIN
      OPEN recordset FOR
         SELECT s.CL_801_SUBMISSION_SEQ_NUM AS SubSeqNum,
                CLAIM_ID AS ClaimID,
                POLICY_NUM as PolicyNum,
                SUBMISSION_STATUS_CODE AS SubmissionStatusCode,
                BOX8_INJURY_DESCR AS Box8InjuryDescr,
                BOX8_LEFT_SIDE_INJURY_FLAG AS Box8LeftSideInjuryFlag,
                BOX8_RIGHT_SIDE_INJURY_FLAG AS Box8RightSideInjuryFlag,
                BOX10_SAIF_EVENT_DESCR AS Box10SaifEventDescr,
                INITIAL_SUBMIT_DATE AS InitialSubmitDate,
                s.UPDATE_NUM AS UpdateNum,
                s.UPDATE_USER AS UpdateUser,
                s.UPDATE_DATE AS UpdateDate,
                s.CREATE_USER AS CreateUser,
                s.CREATE_DATE AS CreateDate
           FROM CL_801_SUBMISSION s , CL_801_SUBMISSION_DATA d
          WHERE s.CL_801_SUBMISSION_SEQ_NUM = d.CL_801_SUBMISSION_SEQ_NUM
            AND D.UPDATE_DATE < RetentionDate;
   END submission_cleanup_select;

PROCEDURE Incomplete_Submission_select (
      RetentionDate   IN     Date,
      recordset       OUT ref_cursor
      )
   IS
   BEGIN
      OPEN recordset FOR
         SELECT CL_801_SUBMISSION_SEQ_NUM AS SubSeqNum,
                CLAIM_ID AS ClaimID,
                POLICY_NUM as PolicyNum,
                SUBMISSION_STATUS_CODE AS SubmissionStatusCode,
                BOX8_INJURY_DESCR AS Box8InjuryDescr,
                BOX8_LEFT_SIDE_INJURY_FLAG AS Box8LeftSideInjuryFlag,
                BOX8_RIGHT_SIDE_INJURY_FLAG AS Box8RightSideInjuryFlag,
                BOX10_SAIF_EVENT_DESCR AS Box10SaifEventDescr,
                INITIAL_SUBMIT_DATE AS InitialSubmitDate,
                S.UPDATE_NUM AS UpdateNum,
                S.UPDATE_USER AS UpdateUser,
                S.UPDATE_DATE AS UpdateDate,
                S.CREATE_USER AS CreateUser,
                trunc(S.CREATE_DATE) AS CreateDate,
                NVL(EXT.PERSON_SEQ_NUM, 0) as CreatePersonId
           FROM CL_801_SUBMISSION S, EXT_USER EXT
          WHERE trunc(S.CREATE_DATE) <= RetentionDate
            AND S.CREATE_USER = EXT.ACTIVE_DIRECTORY_ID
            AND SUBMISSION_STATUS_CODE = 'INC'
            Order by S.POLICY_NUM, S.CREATE_DATE;
   END Incomplete_Submission_select;

   FUNCTION submission_insert (
      SubSeqNum                 IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      ClaimID                   IN CL_801_SUBMISSION.CLAIM_ID%TYPE,
      PolicyNum                    IN CL_801_SUBMISSION.POLICY_NUM%TYPE,
      SubmissionStatusCode      IN CL_801_SUBMISSION.SUBMISSION_STATUS_CODE%TYPE,
      Box8InjuryDescr           IN CL_801_SUBMISSION.BOX8_INJURY_DESCR%TYPE,
      Box8LeftSideInjuryFlag    IN CL_801_SUBMISSION.BOX8_LEFT_SIDE_INJURY_FLAG%TYPE,
      Box8RightSideInjuryFlag   IN CL_801_SUBMISSION.BOX8_RIGHT_SIDE_INJURY_FLAG%TYPE,
      Box10SaifEventDescr        IN CL_801_SUBMISSION.BOX10_SAIF_EVENT_DESCR%TYPE,
      InitialSubmitDate         IN CL_801_SUBMISSION.INITIAL_SUBMIT_DATE%TYPE,
      CreateUser                IN CL_801_SUBMISSION.CREATE_USER%TYPE,
      CreateDate                IN CL_801_SUBMISSION.CREATE_DATE%TYPE)
      RETURN NUMBER
   AS
   BEGIN
      INSERT INTO CL_801_SUBMISSION (CL_801_SUBMISSION_SEQ_NUM,
                                     CLAIM_ID,
                                     POLICY_NUM,
                                     SUBMISSION_STATUS_CODE,
                                     BOX8_INJURY_DESCR,
                                     BOX8_LEFT_SIDE_INJURY_FLAG,
                                     BOX8_RIGHT_SIDE_INJURY_FLAG,
                                     BOX10_SAIF_EVENT_DESCR,
                                     INITIAL_SUBMIT_DATE,
                                     UPDATE_NUM,
                                     UPDATE_USER,
                                     UPDATE_DATE,
                                     CREATE_USER,
                                     CREATE_DATE)
           VALUES (SubSeqNum,
                   ClaimID,
                   PolicyNum,
                   SubmissionStatusCode,
                   Box8InjuryDescr,
                   Box8LeftSideInjuryFlag,
                   Box8RightSideInjuryFlag,
                   Box10SaifEventDescr,
                   InitialSubmitDate,
                   0,
                   null,
                   null,
                   CreateUser,
                   CreateDate);

      RETURN SQL%ROWCOUNT;
   END submission_insert;

   FUNCTION submission_update (
      SubSeqNum                 IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      ClaimID                   IN CL_801_SUBMISSION.CLAIM_ID%TYPE,
      PolicyNum                    IN CL_801_SUBMISSION.POLICY_NUM%TYPE,
      SubmissionStatusCode      IN CL_801_SUBMISSION.SUBMISSION_STATUS_CODE%TYPE,
      Box8InjuryDescr           IN CL_801_SUBMISSION.BOX8_INJURY_DESCR%TYPE,
      Box8LeftSideInjuryFlag    IN CL_801_SUBMISSION.BOX8_LEFT_SIDE_INJURY_FLAG%TYPE,
      Box8RightSideInjuryFlag   IN CL_801_SUBMISSION.BOX8_RIGHT_SIDE_INJURY_FLAG%TYPE,
      Box10SaifEventDescr        IN CL_801_SUBMISSION.BOX10_SAIF_EVENT_DESCR%TYPE,
      InitialSubmitDate         IN CL_801_SUBMISSION.INITIAL_SUBMIT_DATE%TYPE,
      UpdateNum                 IN CL_801_SUBMISSION.UPDATE_NUM%TYPE,
      UpdateUser                IN CL_801_SUBMISSION.UPDATE_USER%TYPE,
      UpdateDate                IN CL_801_SUBMISSION.UPDATE_DATE%TYPE,
      LastUpdateNum             IN CL_801_SUBMISSION.UPDATE_NUM%TYPE)
      RETURN NUMBER
   AS
   BEGIN
      UPDATE CL_801_SUBMISSION
         SET CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM = SubSeqNum,
             CL_801_SUBMISSION.CLAIM_ID = ClaimID,
             CL_801_SUBMISSION.POLICY_NUM = PolicyNum,
             CL_801_SUBMISSION.SUBMISSION_STATUS_CODE = SubmissionStatusCode,
             CL_801_SUBMISSION.BOX8_INJURY_DESCR = Box8InjuryDescr,
             CL_801_SUBMISSION.BOX8_LEFT_SIDE_INJURY_FLAG = Box8LeftSideInjuryFlag,
             CL_801_SUBMISSION.BOX8_RIGHT_SIDE_INJURY_FLAG = Box8RightSideInjuryFlag,
             CL_801_SUBMISSION.BOX10_SAIF_EVENT_DESCR = Box10SaifEventDescr,
             CL_801_SUBMISSION.INITIAL_SUBMIT_DATE = InitialSubmitDate,
             CL_801_SUBMISSION.UPDATE_NUM = UpdateNum,
             CL_801_SUBMISSION.UPDATE_USER = UpdateUser,
             CL_801_SUBMISSION.UPDATE_DATE = UpdateDate
       WHERE     CL_801_SUBMISSION_SEQ_NUM = SubSeqNum
             AND UPDATE_NUM = LastUpdateNum;

      RETURN SQL%ROWCOUNT;
   END submission_update;

   FUNCTION submission_delete (
      SubSeqNum IN CL_801_SUBMISSION.CL_801_SUBMISSION_SEQ_NUM%TYPE,
      LastUpdateNum IN CL_801_SUBMISSION.UPDATE_NUM%TYPE)
      RETURN NUMBER
   AS
   BEGIN
      DELETE FROM CL_801_SUBMISSION
            WHERE CL_801_SUBMISSION_SEQ_NUM = SubSeqNum and update_num = LastUpdateNum;

      RETURN SQL%ROWCOUNT;
   END submission_delete;


    PROCEDURE submission_data_select (
        SubSeqNum   IN     CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        recordset  OUT ref_cursor)
        IS
        BEGIN
            open recordset for
                select
                    CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM      AS SubSeqNum,
                    CL_801_SUBMISSION_DATA.WORKER_ID                                                AS WorkerID,
                    CL_801_SUBMISSION_DATA.WORKER_PERSON_SEQ_NUM             AS WorkerPersonID,
                    CL_801_SUBMISSION_DATA.SUBMISSION_PERSON_SEQ_NUM       AS SubmissionPersonID,
                    CL_801_SUBMISSION_DATA.PROVIDER_PERSON_SEQ_NUM         AS ProviderPersonID,
                    CL_801_SUBMISSION_DATA.INSURANCE_PERSON_SEQ_NUM        AS InsurancePersonID,
                    CL_801_SUBMISSION_DATA.SUPERVISED_PERSON_SEQ_NUM      AS SupervisedPersonID,
                    CL_801_SUBMISSION_DATA.EVENT_PERSON_SEQ_NUM               AS EventPersonID,
                    CL_801_SUBMISSION_DATA.BOX1_INJURY_DATE               AS Box1InjuryDate,
                    CL_801_SUBMISSION_DATA.BOX2_LEAVE_WORK_DATE           AS Box2LeaveWorkDate,
                    CL_801_SUBMISSION_DATA.BOX3_BEGAN_WORK_TIME           AS Box3BeganWorkTime,
                    CL_801_SUBMISSION_DATA.BOX4_MONDAY_OFF_FLAG           AS Box4MondayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_TUESDAY_OFF_FLAG          AS Box4TuesdayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_WEDNESDAY_OFF_FLAG        AS Box4WednesdayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_THURSDAY_OFF_FLAG         AS Box4ThursdayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_FRIDAY_OFF_FLAG           AS Box4FridayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_SATURDAY_OFF_FLAG         AS Box4SaturdayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX4_SUNDAY_OFF_FLAG           AS Box4SundayOffFlag,
                    CL_801_SUBMISSION_DATA.BOX5_INJURY_TIME               AS Box5InjuryTime,
                    CL_801_SUBMISSION_DATA.BOX6_LEAVE_WORK_TIME           AS Box6LeaveWorkTime,
                    CL_801_SUBMISSION_DATA.BOX7_SHIFT_START_TIME          AS Box7ShiftStartTime,
                    CL_801_SUBMISSION_DATA.BOX7_SHIFT_END_TIME            AS Box7ShiftEndTime,
                    CL_801_SUBMISSION_DATA.BOX9_MULTIPLE_EMPR_FLAG        AS Box9MultipleEmployerFlag,
                    CL_801_SUBMISSION_DATA.BOX11_PRIMARY_FIRST_NAME       AS Box11PrimaryFirstName,
                    CL_801_SUBMISSION_DATA.BOX11_PRIMARY_LAST_NAME        AS Box11PrimaryLastName,
                    CL_801_SUBMISSION_DATA.BOX11_PRIMARY_MIDDLE_NAME      AS Box11PrimaryMiddleName,
                    CL_801_SUBMISSION_DATA.BOX11_PRIMARY_EXTENSION_NAME      AS Box11PrimaryExtensionName,
                    CL_801_SUBMISSION_DATA.BOX12_LANGUAGE_PREF_CODE      AS Box12LanguagePrefCode,
                    CL_801_SUBMISSION_DATA.BOX12_WORKER_LANG_OTHER_TEXT   AS Box12WorkerLanguageOtherText,
                    CL_801_SUBMISSION_DATA.BOX13_BIRTH_DATE               AS Box13BirthDate,
                    CL_801_SUBMISSION_DATA.BOX14_GENDER_CODE              AS Box14GenderCode,
                    CL_801_SUBMISSION_DATA.BOX17_PRIMARY_TAX_ID           AS Box17PrimaryTaxID,
                    CL_801_SUBMISSION_DATA.BOX18_WORKER_OCCUPATION_DESCR  AS Box18WorkerOccupationDescr,
                    CL_801_SUBMISSION_DATA.BOX20_WITNESS_NAMES            AS Box20WitnessNames,
                    CL_801_SUBMISSION_DATA.BOX23_PRIOR_BDY_PART_FLAG_CODE     AS Box23PriorBodyPartFlagCode,
                    CL_801_SUBMISSION_DATA.BOX23_PRIOR_BODY_PART_TEXT     AS Box23PriorBodyPartText,
                    CL_801_SUBMISSION_DATA.BOX24_INPATIENT_FLAG_CODE      AS Box24InpatientFlagCode,
                    CL_801_SUBMISSION_DATA.BOX24_OVERNIGHT_INPATIENT_TEXT AS Box24OvernightInpatientText,
                    CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_FLAG_CODE        AS Box25ERTreatmentFlagCode,
                    CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_TEXT        AS Box25ERTreatmentText,
                    CL_801_SUBMISSION_DATA.BOX30_EMPLOYER_LEGAL_NAME      AS Box30EmployerLegalName,
                    CL_801_SUBMISSION_DATA.BOX32_FEIN_TEXT                AS Box32FEINText,
                    CL_801_SUBMISSION_DATA.BOX33_WL_CLIENT_BUS_NAME       AS Box33WLClientBusinessName,
                    CL_801_SUBMISSION_DATA.BOX34_CLIENT_FEIN_TEXT         AS Box34ClientFEINText,
                    CL_801_SUBMISSION_DATA.BOX36_INS_POLICY_NUM           AS Box36InsurancePolicyNum,
                    CL_801_SUBMISSION_DATA.BOX38_NATURE_OF_BUS_TXT        AS Box38NatureOfBusiness,
                    CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSE_FLAG_CODE AS Box40HardwareCauseFlagCode,
                    CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSED_TEXT     AS Box40HardwareCausedText,
                    CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_TEXT       AS Box40PersonCausedText,
                    CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_FLAG_CODE  AS Box40PersonCausedFlagCode,
                    CL_801_SUBMISSION_DATA.BOX41_SAIF_PROV_PAY_CLASS_CODE AS Box41SAIFProviderPayrollClass,
                    CL_801_SUBMISSION_DATA.BOX42_MULTI_WORKER_FLAG_CODE   AS Box42MultiWorkerInjFlagCode,
                    CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_CODE   AS Box43EmploymentInjuryCode,
                    CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_TEXT       AS Box43EmploymentInjuryText,
                    CL_801_SUBMISSION_DATA.BOX44_OSHA_CASE_NUM            AS Box44OSHACaseNum,
                    CL_801_SUBMISSION_DATA.BOX45_KNOWLEDGE_DATE           AS Box45KnowledgeDate,
                    CL_801_SUBMISSION_DATA.BOX46_WAGE_AMT                 AS Box46WageAmount,
                    CL_801_SUBMISSION_DATA.BOX46_WAGE_UNIT_CODE           AS Box46WageUnitCode,
                    CL_801_SUBMISSION_DATA.BOX47_WORKER_HIRE_DATE         AS Box47WorkerHireDate,
                    CL_801_SUBMISSION_DATA.BOX48_DEATH_DATE               AS Box48DeathDate,
                    CL_801_SUBMISSION_DATA.BOX49_RTW_NOT_RETURNED_FLAG          AS Box49RTWNotReturnedFlag,
                    CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_DATE         AS Box49RTWRegularDate,
                    CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_DATE        AS Box49RTWModifiedDate,
                    CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_FLAG         AS Box49RTWRegularWorkFlag,
                    CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_FLAG        AS Box49RTWModifiedWorkFlag,
                    CL_801_SUBMISSION_DATA.BOX50_RTW_REGULAR_HW_FLAG_CODE      AS Box50RTWRegularHWFlagCode,
                    CL_801_SUBMISSION_DATA.EMPR_PROVIDED_EXTRA_TEXT     AS EmployerProvidedExtraText,
                    CL_801_SUBMISSION_DATA.EMPR_PROVIDED_WORKER_AREA_TXT  AS EmployerProvidedWorkerAreaText,
                    CL_801_SUBMISSION_DATA.EMPR_PROV_WORKER_DEPT_DESCR    AS EmployerProvidedWorkerDeptDesc,
                    CL_801_SUBMISSION_DATA.EMPR_PROVIDED_SHIFT_CODE       AS EmployerProvidedShiftCode,
                    CL_801_SUBMISSION_DATA.EMPR_PROV_PAYROLL_CLASS_CODE   AS EmployerProvidedPayClassCode,
                    CL_801_SUBMISSION_DATA.CLAIM_NOTES_TEXT               AS ClaimNotesText,
                    CL_801_SUBMISSION_DATA.USE_801_SSN_FLAG             AS Use801SSNFlag,
                    CL_801_SUBMISSION_DATA.USE_801_NAME_FLAG            AS Use801NameFlag,
                    CL_801_SUBMISSION_DATA.WORKER_DATA_VERIFIED_FLAG AS WorkerDataVerifiedFlag,
                    CL_801_SUBMISSION_DATA.CREATE_DATE                    AS CreateDate,
                    CL_801_SUBMISSION_DATA.CREATE_USER                    AS CreateUser,
                    CL_801_SUBMISSION_DATA.UPDATE_DATE                    AS UpdateDate,
                    CL_801_SUBMISSION_DATA.UPDATE_USER                    AS UpdateUser,
                    CL_801_SUBMISSION_DATA.UPDATE_NUM                     AS UpdateNum,
                    CL_801_SUBMISSION_DATA.ALLOW_DATA_REPLACEMENT_FLAG AS AllowDataReplacementFlag
                from    cl_801_submission_data
                where   cl_801_submission_seq_num = SubSeqNum;
        END submission_data_select;

    FUNCTION submission_data_insert (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        WorkerID IN CL_801_SUBMISSION_DATA.WORKER_ID%TYPE,
        WorkerPersonID IN CL_801_SUBMISSION_DATA.WORKER_PERSON_SEQ_NUM%TYPE,
        SubmissionPersonID IN CL_801_SUBMISSION_DATA.SUBMISSION_PERSON_SEQ_NUM%TYPE,
        ProviderPersonID IN CL_801_SUBMISSION_DATA.PROVIDER_PERSON_SEQ_NUM%TYPE,
        InsurancePersonID IN CL_801_SUBMISSION_DATA.INSURANCE_PERSON_SEQ_NUM%TYPE,
        SupervisedPersonID IN CL_801_SUBMISSION_DATA.SUPERVISED_PERSON_SEQ_NUM%TYPE,
        EventPersonID IN CL_801_SUBMISSION_DATA.EVENT_PERSON_SEQ_NUM%TYPE,
        Box1InjuryDate IN CL_801_SUBMISSION_DATA.BOX1_INJURY_DATE%TYPE,
        Box2LeaveWorkDate IN CL_801_SUBMISSION_DATA.BOX2_LEAVE_WORK_DATE%TYPE,
        Box3BeganWorkTime IN CL_801_SUBMISSION_DATA.BOX3_BEGAN_WORK_TIME%TYPE,
        Box4MondayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_MONDAY_OFF_FLAG%TYPE,
        Box4TuesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_TUESDAY_OFF_FLAG%TYPE,
        Box4WednesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_WEDNESDAY_OFF_FLAG%TYPE,
        Box4ThursdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_THURSDAY_OFF_FLAG%TYPE,
        Box4FridayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_FRIDAY_OFF_FLAG%TYPE,
        Box4SaturdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SATURDAY_OFF_FLAG%TYPE,
        Box4SundayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SUNDAY_OFF_FLAG%TYPE,
        Box5InjuryTime IN CL_801_SUBMISSION_DATA.BOX5_INJURY_TIME%TYPE,
        Box6LeaveWorkTime IN CL_801_SUBMISSION_DATA.BOX6_LEAVE_WORK_TIME%TYPE,
        Box7ShiftStartTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_START_TIME%TYPE,
        Box7ShiftEndTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_END_TIME%TYPE,
        Box9MultipleEmployerFlag IN CL_801_SUBMISSION_DATA.BOX9_MULTIPLE_EMPR_FLAG%TYPE,
        Box11PrimaryFirstName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_FIRST_NAME%TYPE,
        Box11PrimaryLastName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_LAST_NAME%TYPE,
        Box11PrimaryMiddleName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_MIDDLE_NAME%TYPE,
        Box11PrimaryNameExtension IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_EXTENSION_NAME%TYPE,
        Box12LanguagePrefCode IN CL_801_SUBMISSION_DATA.BOX12_LANGUAGE_PREF_CODE%TYPE,
        Box12WorkerLanguageOtherText IN CL_801_SUBMISSION_DATA.BOX12_WORKER_LANG_OTHER_TEXT%TYPE,
        Box13BirthDate IN CL_801_SUBMISSION_DATA.BOX13_BIRTH_DATE%TYPE,
        Box14GenderCode IN CL_801_SUBMISSION_DATA.BOX14_GENDER_CODE%TYPE,
        Box17PrimaryTaxID IN CL_801_SUBMISSION_DATA.BOX17_PRIMARY_TAX_ID%TYPE,
        Box18WorkerOccupationDescr IN CL_801_SUBMISSION_DATA.BOX18_WORKER_OCCUPATION_DESCR%TYPE,
        Box20WitnessNames IN CL_801_SUBMISSION_DATA.BOX20_WITNESS_NAMES%TYPE,
        Box23PriorBodyPartFlagCode IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BDY_PART_FLAG_CODE%TYPE,
        Box23PriorBodyPartText IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BODY_PART_TEXT%TYPE,
        Box24InpatientFlagCode IN CL_801_SUBMISSION_DATA.BOX24_INPATIENT_FLAG_CODE%TYPE,
        Box24OvernightInpatientText IN CL_801_SUBMISSION_DATA.BOX24_OVERNIGHT_INPATIENT_TEXT%TYPE,
        Box25ERTreatmentFlagCode IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_FLAG_CODE%TYPE,
        Box25ERTreatmentText IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_TEXT%TYPE,
        Box30EmployerLegalName IN CL_801_SUBMISSION_DATA.BOX30_EMPLOYER_LEGAL_NAME%TYPE,
        Box32FEINText IN CL_801_SUBMISSION_DATA.BOX32_FEIN_TEXT%TYPE,
        Box33WLClientBusinessName IN CL_801_SUBMISSION_DATA.BOX33_WL_CLIENT_BUS_NAME%TYPE,
        Box34ClientFEINText IN CL_801_SUBMISSION_DATA.BOX34_CLIENT_FEIN_TEXT%TYPE,
        Box36InsurancePolicyNum IN CL_801_SUBMISSION_DATA.BOX36_INS_POLICY_NUM%TYPE,
        Box38NatureOfBusiness IN CL_801_SUBMISSION_DATA.BOX38_NATURE_OF_BUS_TXT%TYPE,
        Box40HardwareCauseFlagCode IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSE_FLAG_CODE%TYPE,
        Box40HardwareCausedText IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSED_TEXT%TYPE,
        Box40PersonCausedText IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_TEXT%TYPE,
        Box40PersonCausedFlagCode IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_FLAG_CODE%TYPE,
        Box41SAIFProviderPayrollClass IN CL_801_SUBMISSION_DATA.BOX41_SAIF_PROV_PAY_CLASS_CODE%TYPE,
        Box42MultiWorkerInjFlagCode IN CL_801_SUBMISSION_DATA.BOX42_MULTI_WORKER_FLAG_CODE%TYPE,
        Box43EmploymentInjuryCode IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_CODE%TYPE,
        Box43EmploymentInjuryText IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_TEXT%TYPE,
        Box44OSHACaseNum IN CL_801_SUBMISSION_DATA.BOX44_OSHA_CASE_NUM%TYPE,
        Box45KnowledgeDate IN CL_801_SUBMISSION_DATA.BOX45_KNOWLEDGE_DATE%TYPE,
        Box46WageAmount IN CL_801_SUBMISSION_DATA.BOX46_WAGE_AMT%TYPE,
        Box46WageUnitCode IN CL_801_SUBMISSION_DATA.BOX46_WAGE_UNIT_CODE%TYPE,
        Box47WorkerHireDate IN CL_801_SUBMISSION_DATA.BOX47_WORKER_HIRE_DATE%TYPE,
        Box48DeathDate IN CL_801_SUBMISSION_DATA.BOX48_DEATH_DATE%TYPE,
        Box49RTWNotReturnedFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_NOT_RETURNED_FLAG%TYPE,
        Box49RTWRegularDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_DATE%TYPE,
        Box49RTWModifiedDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_DATE%TYPE,
        Box49RTWRegularWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_FLAG%TYPE,
        Box49RTWModifiedWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_FLAG%TYPE,
        Box50RTWRegularHWFlagCode IN CL_801_SUBMISSION_DATA.BOX50_RTW_REGULAR_HW_FLAG_CODE%TYPE,
        EmployerProvidedExtraText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_EXTRA_TEXT%TYPE,
        EmployerProvidedWorkerAreaText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_WORKER_AREA_TXT%TYPE,
        EmployerProvidedWorkerDeptDesc IN CL_801_SUBMISSION_DATA.EMPR_PROV_WORKER_DEPT_DESCR%TYPE,
        EmployerProvidedShiftCode IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_SHIFT_CODE%TYPE,
        EmployerProvidedPayClassCode IN CL_801_SUBMISSION_DATA.EMPR_PROV_PAYROLL_CLASS_CODE%TYPE,
        ClaimNotesText IN CL_801_SUBMISSION_DATA.CLAIM_NOTES_TEXT%TYPE,
        Use801SSNFlag IN  CL_801_SUBMISSION_DATA.USE_801_SSN_FLAG%TYPE,
        Use801NameFlag IN CL_801_SUBMISSION_DATA.USE_801_NAME_FLAG%TYPE,
        WorkerDataVerifiedFlag IN CL_801_SUBMISSION_DATA.WORKER_DATA_VERIFIED_FLAG%TYPE,
        CreateDate IN CL_801_SUBMISSION_DATA.CREATE_DATE%TYPE,
        CreateUser IN CL_801_SUBMISSION_DATA.CREATE_USER%TYPE,
        AllowDataReplacementFlag IN CL_801_SUBMISSION_DATA.ALLOW_DATA_REPLACEMENT_FLAG%TYPE)
        RETURN NUMBER
    AS
    BEGIN
        insert into cl_801_submission_data (
            CL_801_SUBMISSION_SEQ_NUM,
            WORKER_ID,
            WORKER_PERSON_SEQ_NUM,
            SUBMISSION_PERSON_SEQ_NUM,
            PROVIDER_PERSON_SEQ_NUM,
            INSURANCE_PERSON_SEQ_NUM,
            SUPERVISED_PERSON_SEQ_NUM,
            EVENT_PERSON_SEQ_NUM,
            BOX1_INJURY_DATE,
            BOX2_LEAVE_WORK_DATE,
            BOX3_BEGAN_WORK_TIME,
            BOX4_MONDAY_OFF_FLAG,
            BOX4_TUESDAY_OFF_FLAG,
            BOX4_WEDNESDAY_OFF_FLAG,
            BOX4_THURSDAY_OFF_FLAG,
            BOX4_FRIDAY_OFF_FLAG,
            BOX4_SATURDAY_OFF_FLAG,
            BOX4_SUNDAY_OFF_FLAG,
            BOX5_INJURY_TIME,
            BOX6_LEAVE_WORK_TIME,
            BOX7_SHIFT_START_TIME,
            BOX7_SHIFT_END_TIME,
            BOX9_MULTIPLE_EMPR_FLAG,
            BOX11_PRIMARY_FIRST_NAME,
            BOX11_PRIMARY_LAST_NAME,
            BOX11_PRIMARY_MIDDLE_NAME,
            BOX11_PRIMARY_EXTENSION_NAME,
            BOX12_LANGUAGE_PREF_CODE,
            BOX12_WORKER_LANG_OTHER_TEXT,
            BOX13_BIRTH_DATE,
            BOX14_GENDER_CODE,
            BOX17_PRIMARY_TAX_ID,
            BOX18_WORKER_OCCUPATION_DESCR,
            BOX20_WITNESS_NAMES,
            BOX23_PRIOR_BDY_PART_FLAG_CODE,
            BOX23_PRIOR_BODY_PART_TEXT,
            BOX24_INPATIENT_FLAG_CODE,
            BOX24_OVERNIGHT_INPATIENT_TEXT,
            BOX25_ER_TREATMENT_FLAG_CODE,
            BOX25_ER_TREATMENT_TEXT,
            BOX30_EMPLOYER_LEGAL_NAME,
            BOX32_FEIN_TEXT,
            BOX33_WL_CLIENT_BUS_NAME,
            BOX34_CLIENT_FEIN_TEXT,
            BOX36_INS_POLICY_NUM,
            BOX38_NATURE_OF_BUS_TXT,
            BOX40_HARDWARE_CAUSE_FLAG_CODE,
            BOX40_HARDWARE_CAUSED_TEXT,
            BOX40_PERSON_CAUSED_TEXT,
            BOX40_PERSON_CAUSED_FLAG_CODE,
            BOX41_SAIF_PROV_PAY_CLASS_CODE,
            BOX42_MULTI_WORKER_FLAG_CODE,
            BOX43_EMPLOYMENT_INJURY_CODE,
            BOX43_EMPLOYMENT_INJURY_TEXT,
            BOX44_OSHA_CASE_NUM,
            BOX45_KNOWLEDGE_DATE,
            BOX46_WAGE_AMT,
            BOX46_WAGE_UNIT_CODE,
            BOX47_WORKER_HIRE_DATE,
            BOX48_DEATH_DATE,
            BOX49_RTW_NOT_RETURNED_FLAG,
            BOX49_RTW_REGULAR_DATE,
            BOX49_RTW_MODIFIED_DATE,
            BOX49_RTW_REGULAR_FLAG,
            BOX49_RTW_MODIFIED_FLAG,
            BOX50_RTW_REGULAR_HW_FLAG_CODE,
            EMPR_PROVIDED_EXTRA_TEXT,
            EMPR_PROVIDED_WORKER_AREA_TXT,
            EMPR_PROV_WORKER_DEPT_DESCR,
            EMPR_PROVIDED_SHIFT_CODE,
            EMPR_PROV_PAYROLL_CLASS_CODE,
            CLAIM_NOTES_TEXT,
            USE_801_SSN_FLAG,
            USE_801_NAME_FLAG,
            WORKER_DATA_VERIFIED_FLAG,
            CREATE_DATE,
            CREATE_USER,
            UPDATE_NUM,
            ALLOW_DATA_REPLACEMENT_FLAG
            )
            Values (
            SubSeqNum,
            WorkerID,
            WorkerPersonID,
            SubmissionPersonID,
            ProviderPersonID,
            InsurancePersonID,
            SupervisedPersonID,
            EventPersonID,
            Box1InjuryDate,
            Box2LeaveWorkDate,
            Box3BeganWorkTime,
            Box4MondayOffFlag,
            Box4TuesdayOffFlag,
            Box4WednesdayOffFlag,
            Box4ThursdayOffFlag,
            Box4FridayOffFlag,
            Box4SaturdayOffFlag,
            Box4SundayOffFlag,
            Box5InjuryTime,
            Box6LeaveWorkTime,
            Box7ShiftStartTime,
            Box7ShiftEndTime,
            Box9MultipleEmployerFlag,
            Box11PrimaryFirstName,
            Box11PrimaryLastName,
            Box11PrimaryMiddleName,
            Box11PrimaryNameExtension,
            Box12LanguagePrefCode,
            Box12WorkerLanguageOtherText,
            Box13BirthDate,
            Box14GenderCode,
            Box17PrimaryTaxID,
            Box18WorkerOccupationDescr,
            Box20WitnessNames,
            Box23PriorBodyPartFlagCode,
            Box23PriorBodyPartText,
            Box24InpatientFlagCode,
            Box24OvernightInpatientText,
            Box25ERTreatmentFlagCode,
            Box25ERTreatmentText,
            Box30EmployerLegalName,
            Box32FEINText,
            Box33WLClientBusinessName,
            Box34ClientFEINText,
            Box36InsurancePolicyNum,
            Box38NatureOfBusiness,
            Box40HardwareCauseFlagCode,
            Box40HardwareCausedText,
            Box40PersonCausedText,
            Box40PersonCausedFlagCode,
            Box41SAIFProviderPayrollClass,
            Box42MultiWorkerInjFlagCode,
            Box43EmploymentInjuryCode,
            Box43EmploymentInjuryText,
            Box44OSHACaseNum,
            Box45KnowledgeDate,
            Box46WageAmount,
            Box46WageUnitCode,
            Box47WorkerHireDate,
            Box48DeathDate,
            Box49RTWNotReturnedFlag,
            Box49RTWRegularDate,
            Box49RTWModifiedDate,
            Box49RTWRegularWorkFlag,
            Box49RTWModifiedWorkFlag,
            Box50RTWRegularHWFlagCode,
            EmployerProvidedExtraText,
            EmployerProvidedWorkerAreaText,
            EmployerProvidedWorkerDeptDesc,
            EmployerProvidedShiftCode,
            EmployerProvidedPayClassCode,
            ClaimNotesText,
            Use801SSNFlag,
            Use801NameFlag,
            WorkerDataVerifiedFlag,
            CreateDate,
            CreateUser,
            0,
            AllowDataReplacementFlag
            );

        return SQL%ROWCOUNT;
    END submission_data_insert;

    FUNCTION submission_data_update (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        WorkerID IN CL_801_SUBMISSION_DATA.WORKER_ID%TYPE,
        WorkerPersonID IN CL_801_SUBMISSION_DATA.WORKER_PERSON_SEQ_NUM%TYPE,
        SubmissionPersonID IN CL_801_SUBMISSION_DATA.SUBMISSION_PERSON_SEQ_NUM%TYPE,
        ProviderPersonID IN CL_801_SUBMISSION_DATA.PROVIDER_PERSON_SEQ_NUM%TYPE,
        InsurancePersonID IN CL_801_SUBMISSION_DATA.INSURANCE_PERSON_SEQ_NUM%TYPE,
        SupervisedPersonID IN CL_801_SUBMISSION_DATA.SUPERVISED_PERSON_SEQ_NUM%TYPE,
        EventPersonID IN CL_801_SUBMISSION_DATA.EVENT_PERSON_SEQ_NUM%TYPE,
        Box1InjuryDate IN CL_801_SUBMISSION_DATA.BOX1_INJURY_DATE%TYPE,
        Box2LeaveWorkDate IN CL_801_SUBMISSION_DATA.BOX2_LEAVE_WORK_DATE%TYPE,
        Box3BeganWorkTime IN CL_801_SUBMISSION_DATA.BOX3_BEGAN_WORK_TIME%TYPE,
        Box4MondayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_MONDAY_OFF_FLAG%TYPE,
        Box4TuesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_TUESDAY_OFF_FLAG%TYPE,
        Box4WednesdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_WEDNESDAY_OFF_FLAG%TYPE,
        Box4ThursdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_THURSDAY_OFF_FLAG%TYPE,
        Box4FridayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_FRIDAY_OFF_FLAG%TYPE,
        Box4SaturdayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SATURDAY_OFF_FLAG%TYPE,
        Box4SundayOffFlag IN CL_801_SUBMISSION_DATA.BOX4_SUNDAY_OFF_FLAG%TYPE,
        Box5InjuryTime IN CL_801_SUBMISSION_DATA.BOX5_INJURY_TIME%TYPE,
        Box6LeaveWorkTime IN CL_801_SUBMISSION_DATA.BOX6_LEAVE_WORK_TIME%TYPE,
        Box7ShiftStartTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_START_TIME%TYPE,
        Box7ShiftEndTime IN CL_801_SUBMISSION_DATA.BOX7_SHIFT_END_TIME%TYPE,
        Box9MultipleEmployerFlag IN CL_801_SUBMISSION_DATA.BOX9_MULTIPLE_EMPR_FLAG%TYPE,
        Box11PrimaryFirstName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_FIRST_NAME%TYPE,
        Box11PrimaryLastName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_LAST_NAME%TYPE,
        Box11PrimaryMiddleName IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_MIDDLE_NAME%TYPE,
        Box11PrimaryNameExtension IN CL_801_SUBMISSION_DATA.BOX11_PRIMARY_EXTENSION_NAME%TYPE,
        Box12LanguagePrefCode IN CL_801_SUBMISSION_DATA.BOX12_LANGUAGE_PREF_CODE%TYPE,
        Box12WorkerLanguageOtherText IN CL_801_SUBMISSION_DATA.BOX12_WORKER_LANG_OTHER_TEXT%TYPE,
        Box13BirthDate IN CL_801_SUBMISSION_DATA.BOX13_BIRTH_DATE%TYPE,
        Box14GenderCode IN CL_801_SUBMISSION_DATA.BOX14_GENDER_CODE%TYPE,
        Box17PrimaryTaxID IN CL_801_SUBMISSION_DATA.BOX17_PRIMARY_TAX_ID%TYPE,
        Box18WorkerOccupationDescr IN CL_801_SUBMISSION_DATA.BOX18_WORKER_OCCUPATION_DESCR%TYPE,
        Box20WitnessNames IN CL_801_SUBMISSION_DATA.BOX20_WITNESS_NAMES%TYPE,
        Box23PriorBodyPartFlagCode IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BDY_PART_FLAG_CODE%TYPE,
        Box23PriorBodyPartText IN CL_801_SUBMISSION_DATA.BOX23_PRIOR_BODY_PART_TEXT%TYPE,
        Box24InpatientFlagCode IN CL_801_SUBMISSION_DATA.BOX24_INPATIENT_FLAG_CODE%TYPE,
        Box24OvernightInpatientText IN CL_801_SUBMISSION_DATA.BOX24_OVERNIGHT_INPATIENT_TEXT%TYPE,
        Box25ERTreatmentFlagCode IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_FLAG_CODE%TYPE,
        Box25ERTreatmentText IN CL_801_SUBMISSION_DATA.BOX25_ER_TREATMENT_TEXT%TYPE,
        Box30EmployerLegalName IN CL_801_SUBMISSION_DATA.BOX30_EMPLOYER_LEGAL_NAME%TYPE,
        Box32FEINText IN CL_801_SUBMISSION_DATA.BOX32_FEIN_TEXT%TYPE,
        Box33WLClientBusinessName IN CL_801_SUBMISSION_DATA.BOX33_WL_CLIENT_BUS_NAME%TYPE,
        Box34ClientFEINText IN CL_801_SUBMISSION_DATA.BOX34_CLIENT_FEIN_TEXT%TYPE,
        Box36InsurancePolicyNum IN CL_801_SUBMISSION_DATA.BOX36_INS_POLICY_NUM%TYPE,
        Box38NatureOfBusiness IN CL_801_SUBMISSION_DATA.BOX38_NATURE_OF_BUS_TXT%TYPE,
        Box40HardwareCauseFlagCode IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSE_FLAG_CODE%TYPE,
        Box40HardwareCausedText IN CL_801_SUBMISSION_DATA.BOX40_HARDWARE_CAUSED_TEXT%TYPE,
        Box40PersonCausedText IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_TEXT%TYPE,
        Box40PersonCausedFlagCode IN CL_801_SUBMISSION_DATA.BOX40_PERSON_CAUSED_FLAG_CODE%TYPE,
        Box41SAIFProviderPayrollClass IN CL_801_SUBMISSION_DATA.BOX41_SAIF_PROV_PAY_CLASS_CODE%TYPE,
        Box42MultiWorkerInjFlagCode IN CL_801_SUBMISSION_DATA.BOX42_MULTI_WORKER_FLAG_CODE%TYPE,
        Box43EmploymentInjuryCode IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_CODE%TYPE,
        Box43EmploymentInjuryText IN CL_801_SUBMISSION_DATA.BOX43_EMPLOYMENT_INJURY_TEXT%TYPE,
        Box44OSHACaseNum IN CL_801_SUBMISSION_DATA.BOX44_OSHA_CASE_NUM%TYPE,
        Box45KnowledgeDate IN CL_801_SUBMISSION_DATA.BOX45_KNOWLEDGE_DATE%TYPE,
        Box46WageAmount IN CL_801_SUBMISSION_DATA.BOX46_WAGE_AMT%TYPE,
        Box46WageUnitCode IN CL_801_SUBMISSION_DATA.BOX46_WAGE_UNIT_CODE%TYPE,
        Box47WorkerHireDate IN CL_801_SUBMISSION_DATA.BOX47_WORKER_HIRE_DATE%TYPE,
        Box48DeathDate IN CL_801_SUBMISSION_DATA.BOX48_DEATH_DATE%TYPE,
        Box49RTWNotReturnedFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_NOT_RETURNED_FLAG%TYPE,
        Box49RTWRegularDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_DATE%TYPE,
        Box49RTWModifiedDate IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_DATE%TYPE,
        Box49RTWRegularWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_REGULAR_FLAG%TYPE,
        Box49RTWModifiedWorkFlag IN CL_801_SUBMISSION_DATA.BOX49_RTW_MODIFIED_FLAG%TYPE,
        Box50RTWRegularHWFlagCode IN CL_801_SUBMISSION_DATA.BOX50_RTW_REGULAR_HW_FLAG_CODE%TYPE,
        EmployerProvidedExtraText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_EXTRA_TEXT%TYPE,
        EmployerProvidedWorkerAreaText IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_WORKER_AREA_TXT%TYPE,
        EmployerProvidedWorkerDeptDesc IN CL_801_SUBMISSION_DATA.EMPR_PROV_WORKER_DEPT_DESCR%TYPE,
        EmployerProvidedShiftCode IN CL_801_SUBMISSION_DATA.EMPR_PROVIDED_SHIFT_CODE%TYPE,
        EmployerProvidedPayClassCode IN CL_801_SUBMISSION_DATA.EMPR_PROV_PAYROLL_CLASS_CODE%TYPE,
        ClaimNotesText IN CL_801_SUBMISSION_DATA.CLAIM_NOTES_TEXT%TYPE,
        Use801SSNFlag IN  CL_801_SUBMISSION_DATA.USE_801_SSN_FLAG%TYPE,
        Use801NameFlag IN CL_801_SUBMISSION_DATA.USE_801_NAME_FLAG%TYPE,
        WorkerDataVerifiedFlag IN CL_801_SUBMISSION_DATA.WORKER_DATA_VERIFIED_FLAG%TYPE,
        LastUpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE,
        UpdateDate IN CL_801_SUBMISSION_DATA.UPDATE_DATE%TYPE,
        UpdateUser IN CL_801_SUBMISSION_DATA.UPDATE_USER%TYPE,
        UpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE,
        AllowDataReplacementFlag IN CL_801_SUBMISSION_DATA.ALLOW_DATA_REPLACEMENT_FLAG%TYPE)
        RETURN NUMBER
    AS
    BEGIN
        update cl_801_submission_data
        set
            CL_801_SUBMISSION_SEQ_NUM = SubSeqNum,
            WORKER_ID = WorkerID,
            WORKER_PERSON_SEQ_NUM = WorkerPersonID,
            SUBMISSION_PERSON_SEQ_NUM = SubmissionPersonID,
            PROVIDER_PERSON_SEQ_NUM = ProviderPersonID,
            INSURANCE_PERSON_SEQ_NUM = InsurancePersonID,
            SUPERVISED_PERSON_SEQ_NUM = SupervisedPersonID,
            EVENT_PERSON_SEQ_NUM = EventPersonID,
            BOX1_INJURY_DATE = Box1InjuryDate,
            BOX2_LEAVE_WORK_DATE = Box2LeaveWorkDate,
            BOX3_BEGAN_WORK_TIME = Box3BeganWorkTime,
            BOX4_MONDAY_OFF_FLAG = Box4MondayOffFlag,
            BOX4_TUESDAY_OFF_FLAG = Box4TuesdayOffFlag,
            BOX4_WEDNESDAY_OFF_FLAG = Box4WednesdayOffFlag,
            BOX4_THURSDAY_OFF_FLAG = Box4ThursdayOffFlag,
            BOX4_FRIDAY_OFF_FLAG = Box4FridayOffFlag,
            BOX4_SATURDAY_OFF_FLAG = Box4SaturdayOffFlag,
            BOX4_SUNDAY_OFF_FLAG = Box4SundayOffFlag,
            BOX5_INJURY_TIME = Box5InjuryTime,
            BOX6_LEAVE_WORK_TIME = Box6LeaveWorkTime,
            BOX7_SHIFT_START_TIME = Box7ShiftStartTime,
            BOX7_SHIFT_END_TIME = Box7ShiftEndTime,
            BOX9_MULTIPLE_EMPR_FLAG = Box9MultipleEmployerFlag,
            BOX11_PRIMARY_FIRST_NAME = Box11PrimaryFirstName,
            BOX11_PRIMARY_LAST_NAME = Box11PrimaryLastName,
            BOX11_PRIMARY_MIDDLE_NAME = Box11PrimaryMiddleName,
            BOX11_PRIMARY_EXTENSION_NAME = Box11PrimaryNameExtension,
            BOX12_LANGUAGE_PREF_CODE = Box12LanguagePrefCode,
            BOX12_WORKER_LANG_OTHER_TEXT = Box12WorkerLanguageOtherText,
            BOX13_BIRTH_DATE = Box13BirthDate,
            BOX14_GENDER_CODE = Box14GenderCode,
            BOX17_PRIMARY_TAX_ID = Box17PrimaryTaxID,
            BOX18_WORKER_OCCUPATION_DESCR = Box18WorkerOccupationDescr,
            BOX20_WITNESS_NAMES = Box20WitnessNames,
            BOX23_PRIOR_BDY_PART_FLAG_CODE = Box23PriorBodyPartFlagCode,
            BOX23_PRIOR_BODY_PART_TEXT = Box23PriorBodyPartText,
            BOX24_INPATIENT_FLAG_CODE = Box24InpatientFlagCode,
            BOX24_OVERNIGHT_INPATIENT_TEXT = Box24OvernightInpatientText,
            BOX25_ER_TREATMENT_FLAG_CODE = Box25ERTreatmentFlagCode,
            BOX25_ER_TREATMENT_TEXT = Box25ERTreatmentText,
            BOX30_EMPLOYER_LEGAL_NAME = Box30EmployerLegalName,
            BOX32_FEIN_TEXT = Box32FEINText,
            BOX33_WL_CLIENT_BUS_NAME = Box33WLClientBusinessName,
            BOX34_CLIENT_FEIN_TEXT = Box34ClientFEINText,
            BOX36_INS_POLICY_NUM = Box36InsurancePolicyNum,
            BOX38_NATURE_OF_BUS_TXT = Box38NatureOfBusiness,
            BOX40_HARDWARE_CAUSE_FLAG_CODE = Box40HardwareCauseFlagCode,
            BOX40_HARDWARE_CAUSED_TEXT = Box40HardwareCausedText,
            BOX40_PERSON_CAUSED_TEXT = Box40PersonCausedText,
            BOX40_PERSON_CAUSED_FLAG_CODE = Box40PersonCausedFlagCode,
            BOX41_SAIF_PROV_PAY_CLASS_CODE = Box41SAIFProviderPayrollClass,
            BOX42_MULTI_WORKER_FLAG_CODE = Box42MultiWorkerInjFlagCode,
            BOX43_EMPLOYMENT_INJURY_CODE = Box43EmploymentInjuryCode,
            BOX43_EMPLOYMENT_INJURY_TEXT = Box43EmploymentInjuryText,
            BOX44_OSHA_CASE_NUM = Box44OSHACaseNum,
            BOX45_KNOWLEDGE_DATE = Box45KnowledgeDate,
            BOX46_WAGE_AMT = Box46WageAmount,
            BOX46_WAGE_UNIT_CODE = Box46WageUnitCode,
            BOX47_WORKER_HIRE_DATE = Box47WorkerHireDate,
            BOX48_DEATH_DATE = Box48DeathDate,
            BOX49_RTW_NOT_RETURNED_FLAG = Box49RTWNotReturnedFlag,
            BOX49_RTW_REGULAR_DATE = Box49RTWRegularDate,
            BOX49_RTW_MODIFIED_DATE = Box49RTWModifiedDate,
            BOX49_RTW_REGULAR_FLAG = Box49RTWRegularWorkFlag,
            BOX49_RTW_MODIFIED_FLAG = Box49RTWModifiedWorkFlag,
            BOX50_RTW_REGULAR_HW_FLAG_CODE = Box50RTWRegularHWFlagCode,
            EMPR_PROVIDED_EXTRA_TEXT = EmployerProvidedExtraText,
            EMPR_PROVIDED_WORKER_AREA_TXT = EmployerProvidedWorkerAreaText,
            EMPR_PROV_WORKER_DEPT_DESCR = EmployerProvidedWorkerDeptDesc,
            EMPR_PROVIDED_SHIFT_CODE = EmployerProvidedShiftCode,
            EMPR_PROV_PAYROLL_CLASS_CODE = EmployerProvidedPayClassCode,
            CLAIM_NOTES_TEXT = ClaimNotesText,
            USE_801_SSN_FLAG               = Use801SSNFlag,
            USE_801_NAME_FLAG              = Use801NameFlag,
            WORKER_DATA_VERIFIED_FLAG      = WorkerDataVerifiedFlag,
            UPDATE_DATE                    = UpdateDate,
            UPDATE_USER                    = UpdateUser,
            UPDATE_NUM                     = UpdateNum,
            ALLOW_DATA_REPLACEMENT_FLAG = AllowDataReplacementFlag
        where cl_801_submission_seq_num = SubSeqNum and update_num = LastUpdateNum;

        return SQL%ROWCOUNT;
    END submission_data_update;

    FUNCTION submission_data_delete (
        SubSeqNum IN CL_801_SUBMISSION_DATA.CL_801_SUBMISSION_SEQ_NUM%TYPE,
        LastUpdateNum IN CL_801_SUBMISSION_DATA.UPDATE_NUM%TYPE)
        RETURN NUMBER
    AS
    BEGIN
        delete from cl_801_submission_data
        where cl_801_submission_seq_num = SubSeqNum and update_num = LastUpdateNum;

        return SQL%ROWCOUNT;
    END submission_data_delete;


   PROCEDURE document_select (
      ClaimID   IN     CL_801_DOCUMENT.CLAIM_ID%TYPE,
      recordset  OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
         SELECT D.CL_801_DOCUMENT_SEQ_NUM AS DocSeqNum,
                D.CLAIM_ID AS ClaimID,
                D.IMAGE_SEQ_NUM AS ImageSeqNum,
                D.FILE_NAME AS FileName,
                D.CL_801_DOCUMENT_TYPE_CODE AS DocTypeCode,
                DTC.CODE_DESCR as DocTypeCodeDescr,
                D.CREATE_USER AS CreateUser,
                D.CREATE_DATE AS CreateDate,
                D.UPDATE_NUM AS UpdateNum,
                D.UPDATE_USER AS UpdateUser,
                D.UPDATE_DATE AS UpdateDate,
                0 as StoredImageSeqNum,
                0 as ErrorImageSeqNum
           FROM  CL_801_DOCUMENT d
                LEFT JOIN CL_801_DOCUMENT_TYPE_CODE dtc
                   ON D.CL_801_DOCUMENT_TYPE_CODE = dtc.CODE
          WHERE d.CLAIM_ID = ClaimID;
   END document_select;

   PROCEDURE document_select_by_imageseqnum (
      ImageSeqNum   IN     CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      recordset  OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
         SELECT D.CL_801_DOCUMENT_SEQ_NUM AS DocSeqNum,
                D.CLAIM_ID AS ClaimID,
                D.IMAGE_SEQ_NUM AS ImageSeqNum,
                D.FILE_NAME AS FileName,
                D.CL_801_DOCUMENT_TYPE_CODE AS DocTypeCode,
                DTC.CODE_DESCR as DocTypeCodeDescr,
                D.CREATE_USER AS CreateUser,
                D.CREATE_DATE AS CreateDate,
                D.UPDATE_NUM AS UpdateNum,
                D.UPDATE_USER AS UpdateUser,
                D.UPDATE_DATE AS UpdateDate,
                0 as StoredImageSeqNum,
                0 as ErrorImageSeqNum
           FROM  CL_801_DOCUMENT d
                LEFT JOIN CL_801_DOCUMENT_TYPE_CODE dtc
                   ON D.CL_801_DOCUMENT_TYPE_CODE = dtc.CODE
          WHERE d.IMAGE_SEQ_NUM = ImageSeqNum;
   END document_select_by_imageseqnum;

   FUNCTION document_insert (
      DocSeqNum     IN CL_801_DOCUMENT.CL_801_DOCUMENT_SEQ_NUM%TYPE,
      ClaimID     IN CL_801_DOCUMENT.CLAIM_ID%TYPE,
      FileName      IN CL_801_DOCUMENT.FILE_NAME%TYPE,
      DocTypeCode   IN CL_801_DOCUMENT.CL_801_DOCUMENT_TYPE_CODE%TYPE,
      ImageSeqNum   IN CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      UpdateNum     IN CL_801_DOCUMENT.UPDATE_NUM%TYPE,
      UpdateUser    IN CL_801_DOCUMENT.UPDATE_USER%TYPE,
      UpdateDate    IN CL_801_DOCUMENT.UPDATE_DATE%TYPE,
      CreateUser    IN CL_801_DOCUMENT.CREATE_USER%TYPE,
      CreateDate    IN CL_801_DOCUMENT.CREATE_DATE%TYPE)
      RETURN NUMBER
   IS
      v_update_count   NUMBER;
   BEGIN
      INSERT INTO CL_801_DOCUMENT (CL_801_DOCUMENT_SEQ_NUM,
                                   CLAIM_ID,
                                   FILE_NAME,
                                   CL_801_DOCUMENT_TYPE_CODE,
                                   IMAGE_SEQ_NUM,
                                   UPDATE_NUM,
                                   UPDATE_USER,
                                   UPDATE_DATE,
                                   CREATE_USER,
                                   CREATE_DATE)
           VALUES (DocSeqNum,
                   ClaimID,
                   FileName,
                   DocTypeCode,
                   ImageSeqNum,
                   UpdateNum,
                   UpdateUser,
                   UpdateDate,
                   CreateUser,
                   CreateDate);

      v_update_count := SQL%ROWCOUNT;
      RETURN v_update_count;
   END document_insert;

   FUNCTION document_update (
      DocSeqNum     IN CL_801_DOCUMENT.CL_801_DOCUMENT_SEQ_NUM%TYPE,
      ClaimID     IN CL_801_DOCUMENT.CLAIM_ID%TYPE,
      FileName      IN CL_801_DOCUMENT.FILE_NAME%TYPE,
      DocTypeCode   IN CL_801_DOCUMENT.CL_801_DOCUMENT_TYPE_CODE%TYPE,
      ImageSeqNum   IN CL_801_DOCUMENT.IMAGE_SEQ_NUM%TYPE,
      UpdateNum IN CL_801_DOCUMENT.UPDATE_NUM%TYPE,
      UpdateUser IN CL_801_DOCUMENT.UPDATE_USER%TYPE,
      UpdateDate IN CL_801_DOCUMENT.UPDATE_DATE%TYPE,
      LastUpdateNum IN CL_801_DOCUMENT.UPDATE_NUM%TYPE)
        RETURN NUMBER
   AS
   BEGIN
        UPDATE CL_801_DOCUMENT
        SET
            CLAIM_ID = ClaimID,
            FILE_NAME = FileName,
            CL_801_DOCUMENT_TYPE_CODE = DocTypeCode,
            IMAGE_SEQ_NUM = ImageSeqNum,
            UPDATE_NUM = UpdateNum,
            UPDATE_USER = UpdateUser,
            UPDATE_DATE = UpdateDate
        WHERE CL_801_DOCUMENT_SEQ_NUM = DocSeqNum and update_num = LastUpdateNum;

        RETURN SQL%ROWCOUNT;
   END document_update;

END;

Note: Statements marked with ** could not be confirmed; treat them as unknown until verified.

Sources

  • Business\SAIF.Business.Claim.Entry\Online801\Submission\cls801Document.vb
  • Business\SAIF.Business.Claim.Entry\Online801\Submission\cls801Submission.vb
  • Business\SAIF.Business.Claim.Entry\Online801\Submission\cls801SubmissionData.vb