WITH tmp_tab AS

(

SELECT DISTINCT EQP_LOCAL_EQP,F.EQP_MODEL_EQP, S.FLAG FROM EQP_BASE F

INNER JOIN POD_ROUTER S ON F.EQP_MODEL_EQP = S.EQP_MODEL_EQP

)

SELECT * FROM

(

SELECT A.SYS_ROIT AS SYS, A.PRIN_ROIT AS PRIN, A.AGNT_ROIT AS AGNT, A.SUB_ACCT_NO_ROIT AS SUB_ACCT_NO, A.ORDER_NO_ROIT AS ORDER_NO, E1.FLAG, 'R' AS FLAG1

FROM OIT_ITEMS A

LEFT JOIN OIT_ITEMS B ON A.SYS_ROIT = B.SYS_ROIT AND A.SUB_ACCT_NO_ROIT = B.SUB_ACCT_NO_ROIT AND A.ORDER_NO_ROIT = B.ORDER_NO_ROIT AND B.SERV_CDE_ROIT = 'INS30' AND B.ITEM_STATUS_ROIT != 'X' AND B.AFT_QTY_ROIT - B.BEF_QTY_ROIT > 0

LEFT JOIN OIT_ITEMS B1 ON A.SYS_ROIT = B1.SYS_ROIT AND A.SUB_ACCT_NO_ROIT = B1.SUB_ACCT_NO_ROIT AND A.ORDER_NO_ROIT = B1.ORDER_NO_ROIT AND B1.SERV_CDE_ROIT IN ('INS28','INS27') AND B1.ITEM_STATUS_ROIT != 'X' AND B1.AFT_QTY_ROIT - B1.BEF_QTY_ROIT > 0

LEFT JOIN tmp_tab E ON A.SUB_ACCT_NO_ROIT = E.EQP_LOCAL_EQP AND E.FLAG = 'SC'

LEFT JOIN tmp_tab E1 ON A.SUB_ACCT_NO_ROIT = E1.EQP_LOCAL_EQP AND E1.FLAG = 'AW'

WHERE A.ITEM_STATUS_ROIT != 'X' AND A.SERV_CDE_ROIT IN ('INS60', 'INS61') AND A.AFT_QTY_ROIT - A.BEF_QTY_ROIT > 0

AND

(

(B.SERV_CDE_ROIT IS NULL AND E.EQP_LOCAL_EQP IS NULL)

OR

((B.SERV_CDE_ROIT IS NULL OR B1.SERV_CDE_ROIT IS NULL) AND E.EQP_LOCAL_EQP IS NULL AND E1.EQP_LOCAL_EQP IS NOT NULL)

)

)

WITH tmp_tab 
     AS (SELECT DISTINCT eqp_local_eqp, 
                         F.eqp_model_eqp, 
                         S.flag 
         FROM   eqp_base F 
                INNER JOIN pod_router S 
                        ON F.eqp_model_eqp = S.eqp_model_eqp) 
SELECT * 
FROM   (SELECT A.sys_roit         AS SYS, 
               A.prin_roit        AS PRIN, 
               A.agnt_roit        AS AGNT, 
               A.sub_acct_no_roit AS SUB_ACCT_NO, 
               A.order_no_roit    AS ORDER_NO, 
               E1.flag, 
               'R'                AS FLAG1 
        FROM   oit_items A 
               LEFT JOIN oit_items B 
                      ON A.sys_roit = B.sys_roit 
                         AND A.sub_acct_no_roit = B.sub_acct_no_roit 
                         AND A.order_no_roit = B.order_no_roit 
                         AND B.serv_cde_roit = 'INS30' 
                         AND B.item_status_roit != 'X' 
                         AND B.aft_qty_roit - B.bef_qty_roit > 0 
               LEFT JOIN oit_items B1 
                      ON A.sys_roit = B1.sys_roit 
                         AND A.sub_acct_no_roit = B1.sub_acct_no_roit 
                         AND A.order_no_roit = B1.order_no_roit 
                         AND B1.serv_cde_roit IN ( 'INS28', 'INS27' ) 
                         AND B1.item_status_roit != 'X' 
                         AND B1.aft_qty_roit - B1.bef_qty_roit > 0 
               LEFT JOIN tmp_tab E 
                      ON A.sub_acct_no_roit = E.eqp_local_eqp 
                         AND E.flag = 'SC' 
               LEFT JOIN tmp_tab E1 
                      ON A.sub_acct_no_roit = E1.eqp_local_eqp 
                         AND E1.flag = 'AW' 
        WHERE  A.item_status_roit != 'X' 
               AND A.serv_cde_roit IN ( 'INS60', 'INS61' ) 
               AND A.aft_qty_roit - A.bef_qty_roit > 0 
               AND ( ( B.serv_cde_roit IS NULL 
                       AND E.eqp_local_eqp IS NULL ) 
                      OR ( ( B.serv_cde_roit IS NULL 
                              OR B1.serv_cde_roit IS NULL ) 
                           AND E.eqp_local_eqp IS NULL 
                           AND E1.eqp_local_eqp IS NOT NULL ) )) 

AND ( ( B.serv_cde_roit IS NULL 
                       AND E.eqp_local_eqp IS NULL ) 
                      OR ( ( B.serv_cde_roit IS NULL 
                              OR B1.serv_cde_roit IS NULL ) 
                           AND E.eqp_local_eqp IS NULL 
                           AND E1.eqp_local_eqp IS NOT NULL ) ))