SELECT DISTINCT
decode (GLE.SENSE_C, '2', -GLE.AMOUNT,GLE.AMOUNT) GL_AMT,
EAP.PROC_CODE,
EAP.PROC_NAME,
EAP_REV.PROC_CODE REV_CODE,
EAP_REV.PROC_NAME REV_CODE_NAME,
GLE_SENSE.NAME,
CLM_CF.NAME,
CLM.CLM_LOB_ID,
nvl(LOBCL.LOB_NAME,'UNKNOWN'),
d_PRV_SPEC.SPECIALTY_C,
ZC_SPEC.NAME,
RKP.RISK_PANEL_NAME,
CLM_MAP_1.INTERNAL_ID,
CLM_ION.NAME,
CLD_REF.EXTERNAL_ID_NUM,
GLE.GL_DATE,
CLM.SERVICE_START_DATE,
VENCLM.VENDOR_NAME,
VENCLM.VENDOR_EXTERNAL_ID,
"d_ID"."IDENTITY_ID",
CLM.SERVICE_END_DATE,
CLD_MAP.LINE,
/* CASE WHEN substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,1)+1,1) = '*' THEN ' '
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,1)+1, instr(GLE.GL_CODE,'*',1,2)-instr(GLE.GL_CODE,'*',1,1)-1)
END,
CASE WHEN substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,2)+1,1) = '*' THEN ' '
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,2)+1, instr(GLE.GL_CODE, '*',1,3)-instr(GLE.GL_CODE, '*',1,2)-1)
END ,
CASE
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
WHEN length(GLE.GL_CODE) - Length ( replace (GLE.GL_CODE,'*','')) =3 THEN trim(substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,3)+1, 10))
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,3)+1, instr(GLE.GL_CODE, '*',1,4)-instr(GLE.GL_CODE, '*',1,3)-1)
END,*/
ltrim(regexp_substr(GLE.GL_CODE ,'[^*]+',1,1)),
TXG_SVC.SERVICE_AMOUNT,
PAT.PAT_NAME,
SER_PCP_MAP.INTERNAL_ID,
d_SER_REN_MAP.INTERNAL_ID,
CLMPOS.NAME,
decode(ZC_TXG_SWT.ABBR,null,ZC_TXG_WFT.ABBR,ZC_TXG_WFT.ABBR||'-'||ZC_TXG_SWT.ABBR),
CLM_MAP_REV.INTERNAL_ID,
CLM_MAP_ORIG.INTERNAL_ID,
CKR.CHECK_DATE,
CKR.CHECK_NUMBER,
GLE.GL_TX_ID,
decode(ROP_ROLLUP.ROLLUP_CATEGORY,'UNKNOWN','Undefined',ROP_ROLLUP.ROLLUP_CATEGORY),
decode(ROP_ROLLUP.ROP_SCHEDULE,0,null,ROP_ROLLUP.ROP_SCHEDULE),
COALESCE(PAYOR_STATE.NAME,ZC_EPP_GRP_6.NAME,'') ,
trim(GLE.GL_CODE),
case when upper(CHK_ACCT.PAYER_NAME) like '%INSURANCE%' then 'KPIC'
when
upper(CHK_ACCT.PAYER_NAME) like '%FOUNDATION%'
then 'KFHP' else NULL end,
ZC_RFL_CAT.NAME,
CLM.TYPE_OF_BILL,
d_VTN5.TAX_ID,
d_PGA_IDS.SUBGROUP_ID,
d_PGA_IDS.GROUP_ID,
GLE.REVERS_OF_GL_TX_ID,
GLE.REVERS_BY_GL_TX_ID,
nvl(d_VSTNG_VNDR2.VM_VENDOR_YN,'N'),
d_FIRST_TOUCH_DATE.FIRST_TOUCH_DATE,
CLM_TRAIT_4.NAME
FROM
HCCLNC.AP_CHECK_ACCT CHK_ACCT RIGHT OUTER JOIN HCCLNC.AP_CHECK CKR ON (CKR.CHECKING_ACCT_ID=CHK_ACCT.ACCT_ID)
RIGHT OUTER JOIN (
SELECT *
FROM HCCLNC.AP_CLAIM_CHECK WHERE CHECK_ID IS NOT NULL
) DOFR ON (DOFR.CHECK_ID=CKR.CHECK_ID)
RIGHT OUTER JOIN HCCLNC.AP_CLAIM CLM ON (CLM.CLAIM_ID=DOFR.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.CL_GL_TRANSACTION GLE ON (GLE.CLAIM_ID=CLM.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.ZC_SENSE GLE_SENSE ON (GLE.SENSE_C=GLE_SENSE.SENSE_C)
LEFT OUTER JOIN HCCLNC.TXG_SERVICES TXG_SVC ON (GLE.GL_TX_ID+0=TXG_SVC.TRANSACTION_ID+0)
LEFT OUTER JOIN HCCLNC.ZC_TXG_SUB_WORKFLOW_TYPE ZC_TXG_SWT ON (GLE.SUB_WORKFLOW_TYPE_C=ZC_TXG_SWT.TXG_SUB_WORKFLOW_TYPE_C)
LEFT OUTER JOIN HCCLNC.ZC_TXG_WORKFLOW_TYPE ZC_TXG_WFT ON (GLE.WORKFLOW_TYPE_C=ZC_TXG_WFT.TXG_WORKFLOW_TYPE_C)
LEFT OUTER JOIN HCCLNC.AP_CHECK GLCHK ON (GLE.AP_CHECK_ID=GLCHK.CHECK_ID)
LEFT OUTER JOIN HCCLNC.DIM_GL_CODE ROP_ROLLUP ON (GLE.GL_CODE=ROP_ROLLUP.GL_CODE)
LEFT OUTER JOIN HCCLNC.CLARITY_VENDOR VENCLM ON (CLM.VENDOR_ID=VENCLM.VENDOR_ID)
LEFT OUTER JOIN (
select t1.vendor_id,t1.line,t1.TAX_ID,t1.TAX_ID_EFF_DATE
from HCCLNC.vendor_tax_id t1
inner join
(
select vendor_id,
max(line) line
from HCCLNC.vendor_tax_id
group by vendor_id) t2
on t1.vendor_id=t2.vendor_id
and t1.line=t2.line
group by
t1.vendor_id,t1.line,t1.TAX_ID,t1.TAX_ID_EFF_DATE
) d_VTN5 ON (VENCLM.VENDOR_ID=d_VTN5.VENDOR_ID)
LEFT OUTER JOIN (
select VPOS.VENDOR_ID
,'Y' as VM_VENDOR_YN
from HCCLNC.VENDOR_POS VPOS
inner join (
select POS_ID
from HCCLNC.clarity_POS
where POS_NAME like upper('%VISITING MEMBER%')
group by POS_ID) CPOS
on VPOS.PLACE_OF_SERVICE_ID=CPOS.POS_ID
group by VPOS.VENDOR_ID,'Y'
UNION ALL
SELECT
t1.vendor_id
,'Y' as VM_VENDOR_YN
from
HCCLNC.vendor_tax_id t1 inner join
(select vendor_id,
max(line) as line
from HCCLNC.vendor_tax_id
group by vendor_id
) t2 on t1.vendor_id=t2.vendor_id
and t1.line=t2.line
WHERE
t1.TAX_ID = '811559375'
group by t1.vendor_id,'Y'
) d_VSTNG_VNDR2 ON (VENCLM.VENDOR_ID=d_VSTNG_VNDR2.VENDOR_ID)
LEFT OUTER JOIN HCCLNC.CLM_MAP CLM_MAP_1 ON (CLM.CLAIM_ID=CLM_MAP_1.CID AND CLM.CM_LOG_OWNER_ID=CLM_MAP_1.CM_LOG_OWNER_ID)
LEFT OUTER JOIN HCCLNC.COVERAGE COVCL ON (CLM.COVERAGE_ID=COVCL.COVERAGE_ID)
LEFT OUTER JOIN HCCLNC.PLAN_GRP GRP ON (COVCL.PLAN_GRP_ID=GRP.PLAN_GRP_ID)
LEFT OUTER JOIN (
select
PLAN_GRP_ID
,ALTERNATE_ID
,case
when instr(Alternate_id,'-') >=1 then REGEXP_SUBSTR(alternate_id,'[^-]+',1,1)
when instr(Alternate_id,'*') >=1 then REGEXP_SUBSTR(alternate_id,'[^*]+',1,1)
else null
end as GROUP_ID
,case
when instr(Alternate_id,'-') >=1 then REGEXP_SUBSTR(alternate_id,'[^-]+',1,2)
when instr(Alternate_id,'*') >=1 then REGEXP_SUBSTR(alternate_id,'[^*]+',1,2)
else null
end as SUBGROUP_ID
,(case
when instr(Alternate_id,'-') >=1 then REGEXP_SUBSTR(alternate_id,'[^-]+',1,1)
when instr(Alternate_id,'*') >=1 then REGEXP_SUBSTR(alternate_id,'[^*]+',1,1)
else null
end || '-' || case
when instr(Alternate_id,'-') >=1 then REGEXP_SUBSTR(alternate_id,'[^-]+',1,2)
when instr(Alternate_id,'*') >=1 then REGEXP_SUBSTR(alternate_id,'[^*]+',1,2)
else null
end ) as GROUP_ID_SUBGROUP_ID
from HCCLNC.PLAN_GRP_ALT_ID
where line=1
) d_PGA_IDS ON (GRP.PLAN_GRP_ID=d_PGA_IDS.PLAN_GRP_ID)
LEFT OUTER JOIN HCCLNC.CLARITY_LOB LOBCL ON (CLM.CLM_LOB_ID=LOBCL.LOB_ID)
LEFT OUTER JOIN HCCLNC.CLARITY_EPP EPPCL ON (CLM.BENEFIT_PLAN_ID=EPPCL.BENEFIT_PLAN_ID)
LEFT OUTER JOIN HCCLNC.ZC_EPP_RPT_GRP_6 ZC_EPP_GRP_6 ON (EPPCL.RPT_GRP_SIX=ZC_EPP_GRP_6.RPT_GRP_SIX)
LEFT OUTER JOIN HCCLNC.CLARITY_SER SERREN ON (CLM.PROV_ID=SERREN.PROV_ID)
LEFT OUTER JOIN (
SELECT
PRV_SPEC.PROV_ID PROV_ID,
PRV_SPEC.SPECIALTY_C SPECIALTY_C
FROM
HCCLNC.CLARITY_SER_SPEC PRV_SPEC
WHERE LINE=1
group by
PROV_ID,
SPECIALTY_C
) d_PRV_SPEC ON (SERREN.PROV_ID=d_PRV_SPEC.PROV_ID)
LEFT OUTER JOIN HCCLNC.ZC_SPECIALTY ZC_SPEC ON (d_PRV_SPEC.SPECIALTY_C=ZC_SPEC.SPECIALTY_C)
LEFT OUTER JOIN (
Select CID
,INTERNAL_ID
from HCCLNC.SER_MAP
where
CM_LOG_OWNER_ID in (1217,120190,1216,120170,120130,120200,120140)
) d_SER_REN_MAP ON (SERREN.PROV_ID=d_SER_REN_MAP.CID)
LEFT OUTER JOIN HCCLNC.ZC_CLAIM_FORMAT CLM_CF ON (CLM.CLAIM_FORMAT_C=CLM_CF.CLAIM_FORMAT_C)
LEFT OUTER JOIN HCCLNC.PATIENT PAT ON (CLM.PAT_ID=PAT.PAT_ID)
LEFT OUTER JOIN (
SELECT DISTINCT PAT_ID, IDENTITY_ID FROM HCCLNC.IDENTITY_ID WHERE IDENTITY_TYPE_ID IN ('12150151','12150120','12150140','12150170','12150130','12150100','12150110')
) "d_ID" ON (PAT.PAT_ID="d_ID"."PAT_ID")
LEFT OUTER JOIN HCCLNC.CLARITY_RKP RKP ON (CLM.RKP_ID=RKP.RKP_ID)
LEFT OUTER JOIN HCCLNC.ZC_IN_OUT_NET CLM_ION ON (CLM.IN_OUT_NET_C=CLM_ION.IN_OUT_NET_C)
LEFT OUTER JOIN HCCLNC.AP_CLAIM_2 CLM2 ON (CLM2.CLAIM_ID=CLM.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.ZC_CLM_TRAIT_4 CLM_TRAIT_4 ON (CLM2.CLM_TRAIT_4_C=CLM_TRAIT_4.CLM_TRAIT_4_C)
LEFT OUTER JOIN HCCLNC.AP_CLAIM_PROC_IDS CLD_MAP ON (CLM.CLAIM_ID=CLD_MAP.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.AP_CLAIM_PROC CLD ON (CLD_MAP.TX_ID=CLD.TX_ID)
LEFT OUTER JOIN HCCLNC.ZC_POS_TYPE CLMPOS ON (CLD.POS_TYPE_C=CLMPOS.POS_TYPE_C)
LEFT OUTER JOIN HCCLNC.CLARITY_EAP EAP ON (CLD.PROC_ID=EAP.PROC_ID)
LEFT OUTER JOIN HCCLNC.CLARITY_EAP EAP_REV ON (CLD.UBC_REVENUE_CODE_ID = EAP_REV.PROC_ID)
LEFT OUTER JOIN HCCLNC.AP_CLAIM_PROC_2 CLD2 ON (CLD_MAP.TX_ID=CLD2.TX_ID)
LEFT OUTER JOIN HCCLNC.REFERRAL CLD_REF ON (CLD2.PAT_PAY_RFL=CLD_REF.REFERRAL_ID)
LEFT OUTER JOIN HCCLNC.REFERRAL_2 CLD_REF_2 ON (CLD_REF.REFERRAL_ID=CLD_REF_2.REFERRAL_ID)
LEFT OUTER JOIN HCCLNC.ZC_GEN_RFL_CAT_2 ZC_RFL_CAT ON (CLD_REF_2.GEN_RFL_CAT_2_C=ZC_RFL_CAT.GEN_RFL_CAT_2_C)
LEFT OUTER JOIN HCCLNC.V_AP_CLAIM_COVERAGE CLM_CVG ON (CLM.CLAIM_ID=CLM_CVG.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.CLARITY_EPM PAYOR ON (CLM_CVG.PAYOR_ID=PAYOR.PAYOR_ID)
LEFT OUTER JOIN HCCLNC.ZC_STATE PAYOR_STATE ON (PAYOR.STATE_C=PAYOR_STATE.STATE_C AND PAYOR_STATE.INTERNAL_ID >= 0 AND PAYOR_STATE.INTERNAL_ID <= 51)
LEFT OUTER JOIN (
select
CLAIM_ID,
min(first_pay_date) as FIRST_TOUCH_DATE
from
(select Claim_ID as CLAIM_ID,
min(apc.check_date) as first_pay_date
from HCCLNC.ap_claim_void_chks vdofr
left join HCCLNC.ap_check apc on vdofr.void_chks_id=apc.check_id
where nvl(vdofr.VOID_CHKS_SERVICE_CNT,1)>0
group by Claim_ID
union
select Claim_ID as CLAIM_ID,
min(apc.check_date) as first_pay_date
from HCCLNC.ap_claim_check dofr
left join HCCLNC.ap_check apc on dofr.CHECK_ID=apc.check_id
where nvl(SERVICE_COUNT,1) >0
group by Claim_ID
) one_22_2019
group by CLAIM_ID
/*West: 10_8_18 EPIC 2018 upgrade, nvl on service counts, vdofr svc cnt
West: 4_6_17 added service count for KPIC/KFHP sequence claims
+added compatibility for legacy Tapestry Claims*/
) d_FIRST_TOUCH_DATE ON (CLM.CLAIM_ID=d_FIRST_TOUCH_DATE.CLAIM_ID)
LEFT OUTER JOIN (
select CLAIM_ID,MIN(PAT_SVC_START_PCP_PROV_ID) PAT_PCP_ID
from HCCLNC.V_AP_CLAIM_PROVS_AND_LOCS
GROUP BY CLAIM_ID
) d_PAT_PCP_ID ON (CLM.CLAIM_ID=d_PAT_PCP_ID.CLAIM_ID)
LEFT OUTER JOIN HCCLNC.CLARITY_SER SERPCP ON (d_PAT_PCP_ID.PAT_PCP_ID=SERPCP.PROV_ID)
LEFT OUTER JOIN HCCLNC.SER_MAP SER_PCP_MAP ON (SERPCP.PROV_ID=SER_PCP_MAP.CID AND SERPCP.CM_LOG_OWNER_ID=SER_PCP_MAP.CM_LOG_OWNER_ID)
LEFT OUTER JOIN HCCLNC.CLM_MAP CLM_MAP_ORIG ON (CLM.ORIG_ADJST_CLM_ID=CLM_MAP_ORIG.CID AND CLM.CM_LOG_OWNER_ID=CLM_MAP_ORIG.CM_LOG_OWNER_ID)
LEFT OUTER JOIN HCCLNC.CLM_MAP CLM_MAP_REV ON (CLM.ORIG_REV_CLM_ID=CLM_MAP_REV.CID AND CLM.CM_LOG_OWNER_ID=CLM_MAP_REV.CM_LOG_OWNER_ID)
WHERE
(
GLE.GL_TX_CODE_TYPE_C IN ( 5,23,25 )
AND
CKR.CHECK_DATE Is Not Null
AND
CKR.CHECK_DATE NOT BETWEEN (CASE
WHEN 'wb' ='t' THEN trunc(sysdate)
WHEN 'wb' ='t-1' THEN trunc(sysdate)-1
WHEN 'wb' ='wb' THEN TRUNC(sysdate, 'IW')-1
WHEN 'wb' ='wb-1' THEN TRUNC(sysdate, 'IW')-8
WHEN 'wb' ='we' THEN NEXT_DAY(TRUNC(sysdate,'IW'),'SATURDAY')
WHEN 'wb' ='we-1' THEN NEXT_DAY(TRUNC(sysdate,'IW')-8,'SATURDAY')
WHEN 'wb' ='mb' THEN trunc(sysdate,'MM')
WHEN 'wb' ='mb-1' THEN trunc(trunc(sysdate, 'MM') - 1, 'MM')
WHEN 'wb' ='me' THEN trunc(last_day(sysdate))
WHEN 'wb' ='me-1' THEN (trunc(sysdate, 'MM') - 1)
WHEN 'wb' ='yb' THEN trunc(sysdate,'YY')
WHEN 'wb' ='yb-1' THEN trunc(trunc(sysdate, 'YY') - 1, 'YY')
WHEN 'wb' ='ye' THEN (last_day(add_months(trunc(sysdate,'Year'),11)))
WHEN 'wb' ='ye-1' THEN (last_day((trunc(sysdate,'Year')-1)))
WHEN 'wb' ='dmb' THEN trunc(trunc(sysdate)-1, 'MM')
ELSE
TO_DATE('wb' ,'MM/dd/yyyy')
END
) AND (CASE
WHEN 't' ='t' THEN trunc(sysdate)
WHEN 't' ='t-1' THEN trunc(sysdate)-1
WHEN 't' ='wb' THEN TRUNC(sysdate, 'IW')-1
WHEN 't' ='wb-1' THEN TRUNC(sysdate, 'IW')-8
WHEN 't' ='we' THEN NEXT_DAY(TRUNC(sysdate,'IW'),'SATURDAY')
WHEN 't' ='we-1' THEN NEXT_DAY(TRUNC(sysdate,'IW')-8,'SATURDAY')
WHEN 't' ='mb' THEN trunc(sysdate,'MM')
WHEN 't' ='mb-1' THEN trunc(trunc(sysdate, 'MM') - 1, 'MM')
WHEN 't' ='me' THEN trunc(last_day(sysdate))
WHEN 't' ='me-1' THEN (trunc(sysdate, 'MM') - 1)
WHEN 't' ='yb' THEN trunc(sysdate,'YY')
WHEN 't' ='yb-1' THEN trunc(trunc(sysdate, 'YY') - 1, 'YY')
WHEN 't' ='ye' THEN (last_day(add_months(trunc(sysdate,'Year'),11)))
WHEN 't' ='ye-1' THEN (last_day((trunc(sysdate,'Year')-1)))
WHEN 't' ='dmb' THEN trunc(trunc(sysdate)-1, 'MM')
ELSE
TO_DATE('t' ,'MM/dd/yyyy')
END
)
AND
CLD.TX_ID = TXG_SVC.SERVICE_ID
AND
GLE.CLAIM_ID Is Not Null
AND
GLE.GL_DATE BETWEEN (CASE
WHEN 'wb' ='t' THEN trunc(sysdate)
WHEN 'wb' ='t-1' THEN trunc(sysdate)-1
WHEN 'wb' ='wb' THEN TRUNC(sysdate, 'IW')-1
WHEN 'wb' ='wb-1' THEN TRUNC(sysdate, 'IW')-8
WHEN 'wb' ='we' THEN NEXT_DAY(TRUNC(sysdate,'IW'),'SATURDAY')
WHEN 'wb' ='we-1' THEN NEXT_DAY(TRUNC(sysdate,'IW')-8,'SATURDAY')
WHEN 'wb' ='mb' THEN trunc(sysdate,'MM')
WHEN 'wb' ='mb-1' THEN trunc(trunc(sysdate, 'MM') - 1, 'MM')
WHEN 'wb' ='me' THEN trunc(last_day(sysdate))
WHEN 'wb' ='me-1' THEN (trunc(sysdate, 'MM') - 1)
WHEN 'wb' ='yb' THEN trunc(sysdate,'YY')
WHEN 'wb' ='yb-1' THEN trunc(trunc(sysdate, 'YY') - 1, 'YY')
WHEN 'wb' ='ye' THEN (last_day(add_months(trunc(sysdate,'Year'),11)))
WHEN 'wb' ='ye-1' THEN (last_day((trunc(sysdate,'Year')-1)))
WHEN 'wb' ='dmb' THEN trunc(trunc(sysdate)-1, 'MM')
ELSE
TO_DATE('wb' ,'MM/dd/yyyy')
END
) AND (CASE
WHEN 't' ='t' THEN trunc(sysdate)
WHEN 't' ='t-1' THEN trunc(sysdate)-1
WHEN 't' ='wb' THEN TRUNC(sysdate, 'IW')-1
WHEN 't' ='wb-1' THEN TRUNC(sysdate, 'IW')-8
WHEN 't' ='we' THEN NEXT_DAY(TRUNC(sysdate,'IW'),'SATURDAY')
WHEN 't' ='we-1' THEN NEXT_DAY(TRUNC(sysdate,'IW')-8,'SATURDAY')
WHEN 't' ='mb' THEN trunc(sysdate,'MM')
WHEN 't' ='mb-1' THEN trunc(trunc(sysdate, 'MM') - 1, 'MM')
WHEN 't' ='me' THEN trunc(last_day(sysdate))
WHEN 't' ='me-1' THEN (trunc(sysdate, 'MM') - 1)
WHEN 't' ='yb' THEN trunc(sysdate,'YY')
WHEN 't' ='yb-1' THEN trunc(trunc(sysdate, 'YY') - 1, 'YY')
WHEN 't' ='ye' THEN (last_day(add_months(trunc(sysdate,'Year'),11)))
WHEN 't' ='ye-1' THEN (last_day((trunc(sysdate,'Year')-1)))
WHEN 't' ='dmb' THEN trunc(trunc(sysdate)-1, 'MM')
ELSE
TO_DATE('t' ,'MM/dd/yyyy')
END
)
AND
(
GLCHK.CHECK_DATE <= CKR.CHECK_DATE
OR
GLCHK.CHECK_DATE Is Null
)
AND
decode(ZC_TXG_SWT.ABBR,null,ZC_TXG_WFT.ABBR,ZC_TXG_WFT.ABBR||'-'||ZC_TXG_SWT.ABBR) <> 'RFND'
)
GROUP BY
decode (GLE.SENSE_C, '2', -GLE.AMOUNT,GLE.AMOUNT),
EAP.PROC_CODE,
EAP.PROC_NAME,
EAP_REV.PROC_CODE ,
EAP_REV.PROC_NAME ,
GLE_SENSE.NAME,
CLM_CF.NAME,
CLM.CLM_LOB_ID,
nvl(LOBCL.LOB_NAME,'UNKNOWN'),
d_PRV_SPEC.SPECIALTY_C,
ZC_SPEC.NAME,
RKP.RISK_PANEL_NAME,
CLM_MAP_1.INTERNAL_ID,
CLM_ION.NAME,
CLD_REF.EXTERNAL_ID_NUM,
GLE.GL_DATE,
CLM.SERVICE_START_DATE,
VENCLM.VENDOR_NAME,
VENCLM.VENDOR_EXTERNAL_ID,
"d_ID"."IDENTITY_ID",
CLM.SERVICE_END_DATE,
CLD_MAP.LINE,
/* CASE WHEN substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,1)+1,1) = '*' THEN ' '
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,1)+1, instr(GLE.GL_CODE,'*',1,2)-instr(GLE.GL_CODE,'*',1,1)-1)
END,
CASE WHEN substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,2)+1,1) = '*' THEN ' '
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,2)+1, instr(GLE.GL_CODE, '*',1,3)-instr(GLE.GL_CODE, '*',1,2)-1)
END ,
CASE
WHEN instr(GLE.GL_CODE, '*',1,3) < 2 THEN 'BAD GL STRING'
WHEN length(GLE.GL_CODE) - Length ( replace (GLE.GL_CODE,'*','')) =3 THEN trim(substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,3)+1, 10))
ELSE substr(GLE.GL_CODE, instr(GLE.GL_CODE, '*',1,3)+1, instr(GLE.GL_CODE, '*',1,4)-instr(GLE.GL_CODE, '*',1,3)-1)
END, */
ltrim(regexp_substr(GLE.GL_CODE ,'[^*]+',1,1)),
TXG_SVC.SERVICE_AMOUNT,
PAT.PAT_NAME,
SER_PCP_MAP.INTERNAL_ID,
d_SER_REN_MAP.INTERNAL_ID,
CLMPOS.NAME,
decode(ZC_TXG_SWT.ABBR,null,ZC_TXG_WFT.ABBR,ZC_TXG_WFT.ABBR||'-'||ZC_TXG_SWT.ABBR) ,
CLM_MAP_REV.INTERNAL_ID,
CLM_MAP_ORIG.INTERNAL_ID,
CKR.CHECK_DATE,
CKR.CHECK_NUMBER,
GLE.GL_TX_ID,
decode(ROP_ROLLUP.ROLLUP_CATEGORY,'UNKNOWN','Undefined',ROP_ROLLUP.ROLLUP_CATEGORY),
decode(ROP_ROLLUP.ROP_SCHEDULE,0,null,ROP_ROLLUP.ROP_SCHEDULE),
COALESCE(PAYOR_STATE.NAME,ZC_EPP_GRP_6.NAME,'') ,
trim(GLE.GL_CODE),
case when upper(CHK_ACCT.PAYER_NAME) like '%INSURANCE%' then 'KPIC'
when
upper(CHK_ACCT.PAYER_NAME) like '%FOUNDATION%'
then 'KFHP' else NULL end,
ZC_RFL_CAT.NAME,
CLM.TYPE_OF_BILL,
d_VTN5.TAX_ID,
d_PGA_IDS.SUBGROUP_ID,
d_PGA_IDS.GROUP_ID,
GLE.REVERS_OF_GL_TX_ID,
GLE.REVERS_BY_GL_TX_ID,
nvl(d_VSTNG_VNDR2.VM_VENDOR_YN,'N'),
d_FIRST_TOUCH_DATE.FIRST_TOUCH_DATE,
CLM_TRAIT_4.NAME
HAVING
max(GLE.GL_DATE) > CKR.CHECK_DATE
/*'User'='S394562';DocumentName =''CO_NAT_FIN025c_IBNR_Posted_WithProcCode'; DPName =''OC1'; DPTYPE =''DPUNIVERS';UNVNAME =''CO_Clarity_FIN_2018'*/
Telegram: DB developers channel