[Home] [Help]
70: || sarakshi 12-Nov-2001 Bug no.2103692:Person Interface DLD
71: || Added the DFF validation before insert/update to the oss table, also in
72: || the call to insert_row/update_row to the oss table adding the dff columns
73: || kumma 23-OCT-2002 Added the parameters for DFF columns to the calls of insert_row and update_row on
74: || igs_pe_hlth_ins_pkg and igs_pe_immu_dtls_pkg, #2608360
75: || kumma 28-OCT-2002 Replaced MILITARY_TYPE_ID with MILITARY_TYPE_CD in validate_military procedure, #2608360
76: || Changed the data type of parameter p_MILITARY_TYPE_CD to VARCHAR2 in procedure CHK_DUP_MILIT
77: || kumma 30-OCT-2002 Added the call to igs_ad_imp_018.validate_desc_flex for new flex fields added in health
78: || insurance and immunization details
1371: END IF;
1372:
1373: l_org_id := igs_ge_gen_003.get_org_id;
1374:
1375: IGS_PE_HLTH_INS_PKG.INSERT_ROW (
1376: x_ROWID => l_rowid,
1377: x_HEALTH_INS_ID => l_hlth_id ,
1378: x_PERSON_ID => HLTH_INS_REC.PERSON_ID ,
1379: x_INSURANCE_CD => HLTH_INS_REC.INSURANCE_CD ,
1494: p_attribute17 =>HLTH_INS_REC.attribute17 ,
1495: p_attribute18 =>HLTH_INS_REC.attribute18 ,
1496: p_attribute19 =>HLTH_INS_REC.attribute19 ,
1497: p_attribute20 =>HLTH_INS_REC.attribute20 ,
1498: p_desc_flex_name =>'IGS_PE_HLTH_INS_ALL_FLEX' ) THEN
1499:
1500: p_error_code := 'E255';
1501: RAISE NO_DATA_FOUND;
1502: END IF;
1558: match_ind = cst_mi_val_19
1559: WHERE mi.interface_run_id = l_interface_run_id
1560: AND mi.status = cst_stat_val_2
1561: AND EXISTS ( SELECT '1'
1562: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1563: WHERE ii.interface_run_id = l_interface_run_id
1564: AND ii.interface_id = mi.interface_id
1565: AND ii.person_id = pe.person_id
1566: AND pe.insurance_cd = UPPER(mi.insurance_cd)
1595: WHERE mi.interface_run_id = l_interface_run_id
1596: AND mi.match_ind IS NULL
1597: AND mi.status = cst_stat_val_2
1598: AND EXISTS ( SELECT '1'
1599: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1600: WHERE ii.interface_run_id = l_interface_run_id
1601: AND ii.interface_id = mi.interface_id
1602: AND ii.person_id = pe.person_id
1603: AND pe.insurance_cd = UPPER(mi.insurance_cd)
1633: UPDATE igs_ad_hlth_ins_int_all mi
1634: SET status = cst_stat_val_3,
1635: match_ind = cst_mi_val_20,
1636: dup_hlth_ins_id = (SELECT health_ins_id
1637: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1638: WHERE ii.interface_run_id = l_interface_run_id
1639: AND ii.interface_id = mi.interface_id
1640: AND ii.person_id = pe.person_id
1641: AND pe.insurance_cd = UPPER(mi.insurance_cd)
1643: WHERE mi.interface_run_id = l_interface_run_id
1644: AND mi.match_ind IS NULL
1645: AND mi.status = cst_stat_val_2
1646: AND EXISTS (SELECT '1'
1647: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1648: WHERE ii.interface_run_id = l_interface_run_id
1649: AND ii.interface_id = mi.interface_id
1650: AND ii.person_id = pe.person_id
1651: AND pe.insurance_cd = UPPER(mi.insurance_cd)
1668: CURSOR chk_dup_pe_hlthins(cp_insurance_cd VARCHAR2,
1669: cp_person_id NUMBER,
1670: cp_start_date IGS_AD_HLTH_INS_INT.START_DATE%TYPE) IS
1671: SELECT rowid, hi.*
1672: FROM igs_pe_hlth_ins hi
1673: WHERE hi.person_id = cp_person_id
1674: AND UPPER(hi.insurance_cd) = UPPER(cp_insurance_cd)
1675: AND TRUNC(hi.start_date) = TRUNC(cp_start_date);
1676: dup_pe_hlthins_rec chk_dup_pe_hlthins%ROWTYPE;
1683: CLOSE chk_dup_pe_hlthins;
1684: IF dup_pe_hlthins_rec.insurance_cd IS NOT NULL THEN
1685: IF l_rule = 'I' THEN
1686: BEGIN
1687: igs_pe_hlth_ins_pkg.update_row(
1688: x_rowid=>dup_pe_hlthins_rec.rowid,
1689: x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
1690: x_person_id=>dup_pe_hlthins_rec.person_id,
1691: x_insurance_provider=> NVL(hlth_ins_rec.insurance_provider,dup_pe_hlthins_rec.insurance_provider),
1753: END;
1754: ELSIF l_rule = 'R' THEN
1755: IF hlth_ins_rec.MATCH_IND = '21' THEN
1756: BEGIN
1757: igs_pe_hlth_ins_pkg.update_row(
1758: x_rowid=>dup_pe_hlthins_rec.rowid,
1759: x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
1760: x_person_id=>dup_pe_hlthins_rec.person_id,
1761: x_insurance_cd=> NVL(hlth_ins_rec.insurance_cd,dup_pe_hlthins_rec.insurance_cd),