{"id": 41, "name": "E and M Duplicate Charges for Encounter", "steps": [{"id": "phYPGt8TgiGJznZkVr5Kk", "name": "duplicatecharges", "type": "SOURCE", "query": "select\n  inn.FIN,\n  inn.name_full_formatted as patient_name,\n  inn.charge_description,\n  inn.item_extended_price,\n  inn.charge_item_id\nfrom (\n  select c.charge_item_id,\n         c.charge_description,\n         c.item_extended_price,\n         ea.alias as FIN,\n         p.name_full_formatted,\n         row_number() over (partition by c.encntr_id order by c.activity_dt_tm) num\n  from charge c\n  join charge_mod cm on cm.charge_item_id = c.charge_item_id\n                    and cm.field6 in ('99202','99203','99204','99205','99206','99207','99208','99209','99210','99211','99212','99213',\n                                      '99214','99215','99241','99242','99243','99244','99245','99381','99382','99383','99384','99385',\n                                      '99386','99387','99388','99389','99390','99391','99392','99393','99394','99395','99396','99397',\n                                      '92002','92004','92012','92014')\n                    and cm.field1_id in (select cv2.code_value from code_value cv2 where cv2.code_set = 14002 and cv2.cdf_meaning = 'CPT4')\n                    and cm.active_ind = 1\n  join encntr_alias ea on ea.encntr_id = c.encntr_id\n                      and ea.active_ind = 1\n                      and ea.encntr_alias_type_cd = 1077\n                      and ea.beg_effective_dt_tm <= sysdate\n                      and ea.end_effective_dt_tm >= sysdate\n  join person p on p.person_id = c.person_id  \n  where c.charge_type_cd = (select cv.code_value from code_value cv where cv.cki = 'CKI.CODEVALUE!3776')\n    and c.active_ind = 1\n    and c.offset_charge_item_id = 0\n    and c.activity_dt_tm >= sysdate-5\n    and exists(select 1 from charge c2, charge_mod cm2\n            where cm2.charge_item_id = c2.charge_item_id\n              and cm2.field6 = cm.field6\n              and cm2.field1_id = cm.field1_id\n              and cm2.active_ind = 1\n              and trunc(FROM_TZ(CAST(c.service_dt_tm AS TIMESTAMP), 'America/Chicago'), 'DD') = trunc(FROM_TZ(CAST(c2.service_dt_tm AS TIMESTAMP), 'America/Chicago'), 'DD')\n              and c2.active_ind = 1\n              and c2.offset_charge_item_id = 0\n              and c2.ord_phys_id = c.ord_phys_id\n              and c2.verify_phys_id = c.verify_phys_id\n              and c2.charge_item_id != c.charge_item_id\n              and c2.charge_type_cd = (select cv.code_value from code_value cv where cv.cki = 'CKI.CODEVALUE!3776')\n              and exists(select 1 from charge_mod cm5, charge_mod cm6    --Check [Primary] CDM code\n                          where cm5.charge_item_id = c2.charge_item_id\n                          and cm5.field1_id in (select cv3.code_value from code_value cv3 where cv3.code_set = 14002 and cv3.cdf_meaning = 'CDM_SCHED')\n                          and cm5.field2_id  = 1\n                          and cm5.active_ind = 1\n                          and cm6.field6     = cm5.field6\n                          and cm6.active_ind = 1\n                          and cm6.field2_id  = 1\n                          and cm6.field1_id  = cm5.field1_id\n                          and cm6.charge_item_id = c.charge_item_id)\n              and c2.encntr_id = c.encntr_id)\n  order by c.encntr_id, c.activity_dt_tm) inn\nwhere inn.num != 1      -- Keep only duplicates to be credited", "params": [], "source": {}, "target": {}, "subType": "QUERY", "sequence": 1, "lastRunStepId": 71794, "enrichmentStep": "", "datasourceMnemonic": "millenniumOracleDB", "enrichmentSourceFields": []}, {"id": "X5N1u8M0Uws5hj-Gy5HaI", "name": "duplicatechargenotification", "type": "TARGET", "query": "select fin, patient_name, charge_description, item_extended_price,charge_item_id from pipeline.duplicatecharges", "params": [], "source": {}, "target": {}, "subType": "NOTIFICATION", "sequence": 2, "lastRunStepId": 71795, "enrichmentStep": "", "notificationType": ["IN-APP"], "notificationUsers": [3, 2, 8], "datasourceMnemonic": "desisivioDatafabric", "notificationSubject": "Duplicate E&M Charges for Same Enounter", "enrichmentSourceFields": [], "notificationsPriorityValue": "HIGH"}], "location": "e_and_m_duplicate_charges_for_encounter_c8b2d2aa_094e_4336_a9f2_1ce0b1542e9d", "lastRunId": 35601, "variables": []}