I was asked to implement the following business rule any ideas on a logic that I can use .

 

1. Based on our conversations initially we are expecting that if there is a need to correct a claim, we get a voided record that completely negates the original. Then we get another original record to replace.
   1. Void/Replacement
   2.       A void is a claim that reverses or backs out a previously paid one. All financials and quantities are negated on the void record. A replacement record that contains the corrected information generally follows it.
   3.        The original, void and replacement need not appear in the same file.
   4.       Example: After adjudication, a paid claim with a $25 Copay and $50 Net Pay, a correction was necessary. The correction contains a $10 Copay and $65 Net Pay.

Thanks

Record Type	Service Count	Charge Submitted	Copay	Deductible	Net Payment
Original	1	75.00	25.00	0.00	50.00
Void	-1	-75.00	-25.00	0.00	-50.00
Replacement	1	75.00	10.00	0.00	65.00

Yes. its not difficult.

But we need more details on how claims are currently stored in the system. Can you post the relevant table structures please?

CLCL_ID            | char(12)
MEME_CK            | int
GRGR_CK            | int
SBSB_CK            | int
SGSG_CK            | int
CLCL_CL_TYPE       | char(1)
CLCL_CL_SUB_TYPE   | char(1)
CLCL_PRE_PRICE_IND | char(1)
CLCL_CUR_STS       | char(2)----------field of interest
CLST_MCTR_REAS     | char(4)
CLST_SEQ_NO        | smallint
CLCL_SITE          | char(2)
CLCL_LAST_ACT_DTM  | datetime
CLCL_INPUT_DT      | datetime
CLCL_RECD_DT       | datetime
CLCL_ACPT_DTM      | datetime
CLCL_PAID_DT       | datetime
CLCL_NEXT_REV_DT   | datetime
CLCL_LOW_SVC_DT    | datetime
CLCL_HIGH_SVC_DT   | datetime
CLCL_ID_ADJ_TO     | char(12)
CLCL_ID_ADJ_FROM   | char(12)
CLCL_ID_CRTE_FROM  | char(12)
CSPD_CAT           | char(1)
PZAP_ID            | char(4)
CSCS_ID            | char(4)
CSPI_ID            | char(8)
PDPD_ID            | char(8)
MEPE_FI            | char(1)
MEPE_PLAN_ENTRY_DT | datetime
CLCL_COBRA_IND     | char(1)
CLCL_ME_AGE        | smallint
MEME_REL           | char(1)
MEME_SEX           | char(1)
MEME_RECORD_NO     | char(11)
MEME_HICN          | char(12)
NWPE_PFX           | char(4)
NWPR_PFX           | char(4)
NWCR_PFX           | char(4)
PDBC_PFX_NPPR      | char(4)
PDBC_PFX_SEDF      | char(4)
PRAC_PFX           | char(4)
PCAG_PFX           | char(4)
MDPC_PFX           | char(4)
NWNW_ID            | char(12)
AGAG_ID            | char(12)
CLCL_MED_ASSGN_IND | char(1)
CLCL_PAY_PR_IND    | char(1)
CLCL_PAYEE_PR_ID   | char(12)
CLCL_REL_INFO_IND  | char(1)
CLCL_OTHER_BN_IND  | char(1)
CLCL_ACD_IND       | char(1)
CLCL_ACD_STATE     | char(2)
CLCL_ACD_DT        | datetime
CLCL_ACD_AMT       | money
CLCL_CURR_ILL_DT   | datetime
CLCL_SIMI_ILL_DT   | datetime
PRPR_ID            | char(12)
CLCL_PR_SS_EIN_IND | char(1)
CLCL_NTWK_IND      | char(1)
CLCL_PCP_IND       | char(1)
CLCL_PRPR_ID_PCP   | char(12)
CLCL_PRPR_ID_REF   | char(12)
CLCL_PA_ACCT_NO    | varchar(35)
CLCL_EXT_AUTH_NO   | char(18)
CLCL_PA_PAID_AMT   | money
CLCL_TOT_CHG       | money
CLCL_TOT_PAYABLE   | money
CLCL_DRAG_OR_IND   | char(1)
CLCL_DRAG_DT       | datetime
CLCL_INPUT_METH    | char(1)
CLCL_AIAI_EOB_IND  | char(1)
CLCL_EOB_EXCD_ID   | char(3)
CLCL_BATCH_ID      | char(8)
CLCL_BATCH_ACTION  | char(1)
CLCL_CE_IND        | char(1)
CLCL_CAP_IND       | char(1)
CLCL_AG_SOURCE     | char(1)
PDDS_PROD_TYPE     | char(1)
PDDS_MCTR_BCAT     | char(4)
CLCL_MICRO_ID      | char(18)
CLCL_UNABL_FROM_DT | datetime
CLCL_UNABL_TO_DT   | datetime
CLCL_RELHP_FROM_DT | datetime
CLCL_RELHP_TO_DT   | datetime
CLCL_OUT_LAB_IND   | char(1)
CLCL_MECD_RESUB_NO | char(15)
PRAD_TYPE          | char(3)
CLCL_PCA_IND       | char(1)
CLCL_OOA_IND       | char(1)
CLCL_EXT_REF_IND   | char(1)
CLCL_RAD_ENC_IND   | char(1)
CLCL_REC_ENC_IND   | char(1)
CLCL_COB_EOB_IND   | char(1)
CLCL_PAP_EOB_IND   | char(1)
CLCL_HSA_IND       | char(1)
CLCL_REL_CLCL_ID   | varchar(12)
USUS_ID            | char(10)
CLCL_LOCK_TOKEN    | smallint
ATXR_SOURCE_ID     | datetime
CLCL_SEC_ADJUD_NVL | char(1)

Hi,

In my opinion, there are 2 possible methods to implement

1. Insert 3 rows (original, void and replacement) in table and then the aggregate or manipulate data either using view or SP to extract accurate information. This method ensures to catch all relevant transactions in the table and the subsequent process will take care of the data interpretation.

2. Only one record at a time exists in table ie., insert original row in table and then update with void/replaced so that only one record with uptodate info available.

Again it depends on your requirements and come up with best solution. All the best!

Method 1:
Insert into table
select *...

method 2:
Insert into table
select *

update table set details...

They only want the void and replace. Since the original has been received initially