SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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'*/
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear