select
T7.LF_ID,T7.inv_id,T7.INV_TOTAL_NET_DUE,T7.INV_NEXT_STEP,T7.INV_STATUS,T7.INV_FEE_TOT,T7.INV_EXP_TOT,
T7.INV_CUR_FEE_TOT,T7.INV_CUR_EXP_TOT,T7.INV_ORIG_NET_AMT,T7.INV_SUB_DT,T7.CL_MATTER_ID, T7.MATTER_DIVISION,
T7.LF_NAME, T7.CLAIM_NO,Y2.LVL_DATE,Y2.LVL_EMP_ID, Y2.LVL_EMP_NAME,T7.INV_DT, T7.INV_DUE_DATE,
T7.INV_START_DATE,T7.INV_END_DATE,T7.UPDATE_DT, T7.INV_UNDO_DT,T7.INV_AP_DT,T7.inv_id_esc,T7.inv_id_adj, T7.Flag,
case when Y2.LVL_DATE is null then 'N' else case when DatePart(wk,(Y2.LVL_DATE))=DatePart(wk,getdate())and
DatePart(Mm,(Y2.LVL_DATE)) = DatePart(Mm,getdate())
and DatePart(Yy,(Y2.LVL_DATE)) = DatePart(Yy,getdate()) then 'W' else
case when DatePart(Mm,(Y2.LVL_DATE)) = DatePart(Mm,getdate()) and
DatePart(Yy,(Y2.LVL_DATE)) = DatePart(Yy,getdate()) then 'M' else
case when DatePart(Yy,(Y2.LVL_DATE))=DatePart(Yy,getdate()) then 'Y' else 'P' end end end end APPROVAL_IND
from (select
t5.LF_ID,T5.inv_id,T5.INV_TOTAL_NET_DUE,T5.INV_NEXT_STEP,T5.INV_STATUS,T5.INV_FEE_TOT,T5.INV_EXP_TOT,
T5.INV_CUR_FEE_TOT,T5.INV_CUR_EXP_TOT,T5.INV_ORIG_NET_AMT,T5.INV_SUB_DT,T5.CL_MATTER_ID, T5.MATTER_DIVISION,
T5.LF_NAME, T5.CLAIM_NO,T5.INV_DT, T5.INV_DUE_DATE,
T5.INV_START_DATE,T5.INV_END_DATE,T5.UPDATE_DT, T5.INV_UNDO_DT,T5.INV_AP_DT,T5.inv_id_esc,T5.inv_id_adj, case when T6.rule_data = 'Y' then 'Y' else 'N' end Flag
from (select
t4.LF_ID,t4.inv_id,t4.INV_TOTAL_NET_DUE,t4.INV_NEXT_STEP,t4.INV_STATUS,t4.INV_FEE_TOT,t4.INV_EXP_TOT,
t4.INV_CUR_FEE_TOT,t4.INV_CUR_EXP_TOT,t4.INV_ORIG_NET_AMT,t4.INV_SUB_DT,t4.CL_MATTER_ID, t4.MATTER_DIVISION,
t4.LF_NAME, t4.CLAIM_NO,T4.INV_DT, T4.INV_DUE_DATE,T4.INV_START_DATE,T4.INV_END_DATE,
T4.UPDATE_DT, T4.INV_UNDO_DT,T4.INV_AP_DT,t4.inv_id_esc,t3.inv_id as
inv_id_adj
from(select
t1.LF_ID,t1.inv_id,t1.INV_TOTAL_NET_DUE,t1.INV_NEXT_STEP,t1.INV_STATUS,t1.INV_FEE_TOT,t1.INV_EXP_TOT,
t1.INV_CUR_FEE_TOT,t1.INV_CUR_EXP_TOT,t1.INV_ORIG_NET_AMT,t1.INV_SUB_DT, t1.CL_MATTER_ID, t1.MATTER_DIVISION,
t1.LF_NAME,t1.CLAIM_NO, T1.INV_DT, T1.INV_DUE_DATE,T1.INV_START_DATE,
T1.INV_END_DATE,T1.UPDATE_DT, T1.INV_UNDO_DT, T1.INV_AP_DT, t2.inv_id as
inv_id_esc
from(SELECT INVOICE.LF_ID,
INVOICE.INV_ID,INVOICE.INV_TOTAL_NET_DUE,INVOICE.INV_NEXT_STEP,INVOICE.INV_STATUS,INVOICE.INV_FEE_TOT,INVOICE.INV_EXP_TOT,INVOICE.INV_CUR_FEE_TOT,INVOICE.INV_CUR_EXP_TOT,INVOICE.INV_ORIG_NET_AMT,
INVOICE.INV_SUB_DT,MATTER.CL_MATTER_ID, MATTER.MATTER_DIVISION, LAW_FIRM.LF_NAME,
USER_DEFINED_FIELD.CLAIM_NO,INVOICE.INV_DT, INVOICE.INV_DUE_DATE,INVOICE.INV_START_DATE,INVOICE.INV_END_DATE,
INVOICE.UPDATE_DT, INVOICE.INV_UNDO_DT,INVOICE.INV_AP_DT
FROM INVOICE, MATTER, LAW_FIRM, USER_DEFINED_FIELD
WHERE INVOICE.LF_ID = MATTER.LF_ID
AND INVOICE.INV_ID = MATTER.INV_ID
AND INVOICE.LF_ID = LAW_FIRM.LF_ID
AND INVOICE.LF_ID = USER_DEFINED_FIELD.LF_ID
AND INVOICE.INV_ID = USER_DEFINED_FIELD.INV_ID
AND USER_DEFINED_FIELD.CLAIM_NO not in('#410-59361')
AND INVOICE.LF_ID <> ''
AND INVOICE.LF_ID <> ' '
group by
INVOICE.LF_ID,
INVOICE.INV_ID,INVOICE.INV_TOTAL_NET_DUE,INVOICE.INV_NEXT_STEP,INVOICE.INV_STATUS,INVOICE.INV_FEE_TOT,INVOICE.INV_EXP_TOT,INVOICE.INV_CUR_FEE_TOT,INVOICE.INV_CUR_EXP_TOT,INVOICE.INV_ORIG_NET_AMT,
INVOICE.INV_SUB_DT,MATTER.CL_MATTER_ID, MATTER.MATTER_DIVISION, LAW_FIRM.LF_NAME,
USER_DEFINED_FIELD.CLAIM_NO, INVOICE.INV_DT, INVOICE.INV_DUE_DATE,INVOICE.INV_START_DATE,INVOICE.INV_END_DATE,
INVOICE.UPDATE_DT, INVOICE.INV_UNDO_DT,INVOICE.INV_AP_DT
)
as T1 LEFT JOIN (select distinct(inv_id) from escalation) as T2 on T1.inv_id = T2.inv_id )
as T4 LEFT JOIN (select distinct(inv_id) from invoice_adjustment ) as T3 on T4.INV_ID = T3.INV_ID )
as T5 LEFT JOIN (select distinct INV_ID,CL_MATTER_ID,LF_ID,'Y' rule_data from rules
where rule_action <> 0 and rule_type='invoice' and rule_action is not null ) as T6 on
T5.INV_ID = T6.INV_ID and
T5.LF_ID = T6.LF_ID and
T5.CL_MATTER_ID = T6.CL_MATTER_ID)
as T7 LEFT JOIN (select APPROVAL_LEVEL.INV_ID,APPROVAL_LEVEL.LF_ID,APPROVAL_LEVEL.CL_MATTER_ID,APPROVAL_LEVEL.LVL_DATE,APPROVAL_LEVEL.LVL_EMP_ID,APPROVAL_LEVEL.LVL_EMP_NAME
from APPROVAL_LEVEL, (SELECT INV_ID,LF_ID,CL_MATTER_ID,MAX(LVL_DATE) AS MAX_LVL_DATE
from APPROVAL_LEVEL
where APPROVAL_LEVEL.GRP_NUMBER = 1
AND APPROVAL_LEVEL.LVL_NUMBER = 1
AND APPROVAL_LEVEL.APP_TYPE = 'invoice'
AND APPROVAL_LEVEL.LI_LINUM = '0' group by INV_ID,LF_ID,CL_MATTER_ID ) AS Y1
where APPROVAL_LEVEL.INV_ID = Y1.INV_ID
AND APPROVAL_LEVEL.LF_ID = Y1.LF_ID
AND APPROVAL_LEVEL.CL_MATTER_ID = Y1.CL_MATTER_ID
AND APPROVAL_LEVEL.LVL_DATE = Y1.MAX_LVL_DATE
AND APPROVAL_LEVEL.GRP_NUMBER = 1
AND APPROVAL_LEVEL.LVL_NUMBER = 1
AND APPROVAL_LEVEL.APP_TYPE = 'invoice'
AND APPROVAL_LEVEL.LI_LINUM = '0') AS Y2
ON T7.INV_ID = Y2.INV_ID
AND T7.LF_ID = Y2.LF_ID
AND T7.CL_MATTER_ID = Y2.CL_MATTER_ID
Thanks
Shivan
ArndW wrote:Shivan,
could you post the SQL statement please?