RE_LS_EXCPT_VW(SQL View) |
Index Back |
|---|
| SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , TRM.TXN_GRP , TRM.TERM_ID , TRM.EFFDT_FROM , TRM.EFFDT_TO , 'IOE' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM TRM , PS_RE_LS_OPEX_CTG TRM1 , PS_RE_EXP_CLS CTG1 , PS_RE_LS_OPEX_CTG TRM2 , PS_RE_EXP_CLS CTG2 WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = TRM.LS_KEY AND A.LS_KEY = TRM1.LS_KEY AND A.LS_KEY = TRM2.LS_KEY AND TRM.TERM_ID = TRM1.TERM_ID AND TRM1.TERM_ID = TRM2.TERM_ID AND TRM1.CHG_GRP_CD = CTG1.CHG_GRP_CD AND TRM2.CHG_GRP_CD = CTG2.CHG_GRP_CD AND CTG1.SETID = CTG2.SETID AND CTG1.CAPITALIZE_SW = 'Y' AND CTG2.CAPITALIZE_SW = 'N' AND CTG1.EFF_STATUS = 'A' AND CTG2.EFF_STATUS = 'A' AND CTG1.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_RE_EXP_CLS B WHERE B.SETID = CTG1.SETID AND B.CHG_GRP_CD = CTG1.CHG_GRP_CD AND B.EFFDT <= %CurrentDateIn) AND CTG2.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_RE_EXP_CLS C WHERE C.SETID = CTG2.SETID AND C.CHG_GRP_CD = CTG2.CHG_GRP_CD AND C.EFFDT <= %CurrentDateIn) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T1.TXN_GRP , T1.TERM_ID , T1.EFFDT_FROM , T1.EFFDT_TO , 'OVL' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T1 , PS_RE_LS_TRM T2 WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T1.LS_KEY AND A.LS_KEY = T2.LS_KEY AND T1.TERM_ID <> T2.TERM_ID AND T1.TXN_GRP = '1' AND (T1.TERM_RENT_TYPE = '01' OR (T1.TERM_RENT_TYPE = '07' AND T1.CAPITALIZE_SW = 'Y')) AND (T2.TERM_RENT_TYPE = '01' OR (T2.TERM_RENT_TYPE = '07' AND T2.CAPITALIZE_SW = 'Y')) AND T1.TXN_GRP = T2.TXN_GRP AND T1.PAYMENT_GROUP = T2.PAYMENT_GROUP AND ((T1.EFFDT_FROM <= T2.EFFDT_FROM AND T1.EFFDT_TO >= T2.EFFDT_FROM) OR (T2.EFFDT_FROM <= T1.EFFDT_FROM AND T2.EFFDT_TO >= T1.EFFDT_FROM)) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T1.TXN_GRP , T1.TERM_ID , T1.EFFDT_FROM , T1.EFFDT_TO , 'OVL' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T1 , PS_RE_LS_TRM T2 , PS_RE_MISC_FEES M1 , PS_RE_MISC_FEES M2 WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T1.LS_KEY AND A.LS_KEY = T2.LS_KEY AND T1.TERM_ID <> T2.TERM_ID AND T1.TXN_GRP = '6' AND T1.TERM_RENT_TYPE = '03' AND T1.TXN_GRP = T2.TXN_GRP AND T1.TERM_RENT_TYPE = T2.TERM_RENT_TYPE AND T1.CHG_GRP_CD = M1.CHG_GRP_CD AND T2.CHG_GRP_CD = M2.CHG_GRP_CD AND M1.CAPITALIZE_SW = 'Y' AND M2.CAPITALIZE_SW = 'Y' AND M1.EFF_STATUS = 'A' AND M2.EFF_STATUS = 'A' AND M1.EFFDT = ( SELECT MAX(M11.EFFDT) FROM PS_RE_MISC_FEES M11 WHERE M1.SETID = M11.SETID AND M1.CHG_GRP_CD = M11.CHG_GRP_CD AND M11.EFFDT <= %CurrentDateIn) AND M2.EFFDT = ( SELECT MAX(M22.EFFDT) FROM PS_RE_MISC_FEES M22 WHERE M2.SETID = M22.SETID AND M2.CHG_GRP_CD = M22.CHG_GRP_CD AND M22.EFFDT <= %CurrentDateIn) AND T1.PAYMENT_GROUP = T2.PAYMENT_GROUP AND ((T1.EFFDT_FROM <= T2.EFFDT_FROM AND T1.EFFDT_TO >= T2.EFFDT_FROM) OR (T2.EFFDT_FROM <= T1.EFFDT_FROM AND T2.EFFDT_TO >= T1.EFFDT_FROM)) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T1.TXN_GRP , T1.TERM_ID , T1.EFFDT_FROM , T1.EFFDT_TO , 'OVL' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T1 , PS_RE_LS_TRM T2 WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T1.LS_KEY AND A.LS_KEY = T2.LS_KEY AND T1.TERM_ID <> T2.TERM_ID AND T1.TXN_GRP = '4' AND T1.TXN_GRP = T2.TXN_GRP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG E , PS_RE_EXP_CLS CTG WHERE E.LS_KEY = T1.LS_KEY AND E.TERM_ID = T1.TERM_ID AND E.CHG_GRP_CD = CTG.CHG_GRP_CD AND CTG.CAPITALIZE_SW = 'N' AND CTG.EFF_STATUS = 'A' AND CTG.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_RE_EXP_CLS B WHERE B.SETID = CTG.SETID AND B.CHG_GRP_CD = CTG.CHG_GRP_CD AND B.EFFDT <= %CurrentDateIn)) AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG E2 , PS_RE_EXP_CLS CTG2 WHERE E2.LS_KEY = T2.LS_KEY AND E2.TERM_ID = T2.TERM_ID AND E2.CHG_GRP_CD = CTG2.CHG_GRP_CD AND CTG2.CAPITALIZE_SW = 'N' AND CTG2.EFF_STATUS = 'A' AND CTG2.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_RE_EXP_CLS B2 WHERE B2.SETID = CTG2.SETID AND B2.CHG_GRP_CD = CTG2.CHG_GRP_CD AND B2.EFFDT <= %CurrentDateIn)) AND T1.PAYMENT_GROUP = T2.PAYMENT_GROUP AND ((T1.EFFDT_FROM <= T2.EFFDT_FROM AND T1.EFFDT_TO >= T2.EFFDT_FROM) OR (T2.EFFDT_FROM <= T1.EFFDT_FROM AND T2.EFFDT_TO >= T1.EFFDT_FROM)) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '1' AND (T.TERM_RENT_TYPE = '01' OR (T.TERM_RENT_TYPE = '07' AND T.CAPITALIZE_SW = 'Y')) AND (( SELECT %DateAdd(T1.EFFDT_TO, 1) FROM PS_RE_LS_TRM T1 WHERE T.LS_KEY = T1.LS_KEY AND T.TERM_ID = T1.TERM_ID AND T.TXN_GRP = T1.TXN_GRP AND (T1.TERM_RENT_TYPE = '01' OR (T1.TERM_RENT_TYPE = '07' AND T1.CAPITALIZE_SW = 'Y')) AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP) < ( SELECT MIN(T2.EFFDT_FROM) FROM PS_RE_LS_TRM T2 WHERE T.LS_KEY = T2.LS_KEY AND T.TERM_ID <> T2.TERM_ID AND T.TXN_GRP = T2.TXN_GRP AND (T2.TERM_RENT_TYPE = '01' OR (T2.TERM_RENT_TYPE = '07' AND T2.CAPITALIZE_SW = 'Y')) AND T.PAYMENT_GROUP = T2.PAYMENT_GROUP AND T2.EFFDT_FROM > ( SELECT T22.EFFDT_TO FROM PS_RE_LS_TRM T22 WHERE T.LS_KEY = T22.LS_KEY AND T.TERM_ID = T22.TERM_ID AND T.TXN_GRP = T22.TXN_GRP AND (T22.TERM_RENT_TYPE = '01' OR (T22.TERM_RENT_TYPE = '07' AND T22.CAPITALIZE_SW = 'Y')) AND T.PAYMENT_GROUP = T22.PAYMENT_GROUP))) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '1' AND (T.TERM_RENT_TYPE = '01' OR (T.TERM_RENT_TYPE = '07' AND T.CAPITALIZE_SW = 'Y')) AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T.LS_KEY = T1.LS_KEY AND T.TXN_GRP = T1.TXN_GRP AND (T1.TERM_RENT_TYPE = '01' OR (T1.TERM_RENT_TYPE = '07' AND T1.CAPITALIZE_SW = 'Y')) AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP AND T1.EFFDT_FROM > ( SELECT MAX(TF.EFFDT_FROM) FROM PS_RE_LS AF , PS_RE_LS_TRM TF WHERE AF.LS_KEY = A.LS_KEY AND AF.LS_KEY = TF.LS_KEY AND TF.TXN_GRP = '1' AND (TF.TERM_RENT_TYPE = '01' OR (TF.TERM_RENT_TYPE = '07' AND TF.CAPITALIZE_SW = 'Y')) AND (( SELECT %DateAdd(TF1.EFFDT_TO, 1) FROM PS_RE_LS_TRM TF1 WHERE TF.LS_KEY = TF1.LS_KEY AND TF.TERM_ID = TF1.TERM_ID AND TF.TXN_GRP = TF1.TXN_GRP AND (TF1.TERM_RENT_TYPE = '01' OR (TF1.TERM_RENT_TYPE = '07' AND TF1.CAPITALIZE_SW = 'Y')) AND TF.PAYMENT_GROUP = TF1.PAYMENT_GROUP) < ( SELECT MIN(TF2.EFFDT_FROM) FROM PS_RE_LS_TRM TF2 WHERE TF.LS_KEY = TF2.LS_KEY AND TF.TERM_ID <> TF2.TERM_ID AND TF.TXN_GRP = TF2.TXN_GRP AND (TF2.TERM_RENT_TYPE = '01' OR (TF2.TERM_RENT_TYPE = '07' AND TF2.CAPITALIZE_SW = 'Y')) AND TF.PAYMENT_GROUP = TF2.PAYMENT_GROUP AND TF2.EFFDT_FROM > ( SELECT TF22.EFFDT_TO FROM PS_RE_LS_TRM TF22 WHERE TF.LS_KEY = TF22.LS_KEY AND TF.TERM_ID = TF22.TERM_ID AND TF.TXN_GRP = TF22.TXN_GRP AND (TF22.TERM_RENT_TYPE = '01' OR (TF22.TERM_RENT_TYPE = '07' AND TF22.CAPITALIZE_SW = 'Y')) AND TF.PAYMENT_GROUP = TF22.PAYMENT_GROUP))))) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '1' AND (T.TERM_RENT_TYPE = '01' OR (T.TERM_RENT_TYPE = '07' AND T.CAPITALIZE_SW = 'Y')) AND(( SELECT MIN(T3.EFFDT_FROM) FROM PS_RE_LS_TRM T3 WHERE T.LS_KEY = T3.LS_KEY AND T.TXN_GRP = T3.TXN_GRP AND (T3.TERM_RENT_TYPE = '01' OR (T3.TERM_RENT_TYPE = '07' AND T3.CAPITALIZE_SW = 'Y')) AND T3.PAYMENT_GROUP = 1) > ( SELECT L3.LEASE_START_DT FROM PS_RE_LS L3 WHERE L3.LS_KEY = A.LS_KEY)) AND T.EFFDT_FROM = ( SELECT MIN(TM.EFFDT_FROM) FROM PS_RE_LS_TRM TM WHERE T.LS_KEY = TM.LS_KEY AND T.TXN_GRP = TM.TXN_GRP AND (TM.TERM_RENT_TYPE = '01' OR (TM.TERM_RENT_TYPE = '07' AND TM.CAPITALIZE_SW = 'Y')) AND TM.PAYMENT_GROUP = 1) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '1' AND (T.TERM_RENT_TYPE = '01' OR (T.TERM_RENT_TYPE = '07' AND T.CAPITALIZE_SW = 'Y')) AND(( SELECT MAX(T4.EFFDT_TO) FROM PS_RE_LS_TRM T4 WHERE T.LS_KEY = T4.LS_KEY AND T.TXN_GRP = T4.TXN_GRP AND (T4.TERM_RENT_TYPE = '01' OR (T4.TERM_RENT_TYPE = '07' AND T4.CAPITALIZE_SW = 'Y')) AND T4.PAYMENT_GROUP = 1) < ( SELECT L4.LEASE_END_DT FROM PS_RE_LS L4 WHERE L4.LS_KEY = A.LS_KEY)) AND T.EFFDT_TO = ( SELECT MAX(TX.EFFDT_TO) FROM PS_RE_LS_TRM TX WHERE T.LS_KEY = TX.LS_KEY AND T.TXN_GRP = TX.TXN_GRP AND (TX.TERM_RENT_TYPE = '01' OR (TX.TERM_RENT_TYPE = '07' AND TX.CAPITALIZE_SW = 'Y')) AND TX.PAYMENT_GROUP = 1) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID ,A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '6' AND T.TERM_RENT_TYPE = '03' AND ( SELECT %DateAdd(T1.EFFDT_TO, 1) FROM PS_RE_LS_TRM T1 , PS_RE_MISC_FEES M1 WHERE T.LS_KEY = T1.LS_KEY AND T.TERM_ID = T1.TERM_ID AND T.TXN_GRP = T1.TXN_GRP AND T.TERM_RENT_TYPE = T1.TERM_RENT_TYPE AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP AND T1.CHG_GRP_CD = M1.CHG_GRP_CD AND M1.CAPITALIZE_SW = 'Y' AND M1.EFF_STATUS = 'A' AND M1.EFFDT = ( SELECT MAX(M11.EFFDT) FROM PS_RE_MISC_FEES M11 WHERE M1.SETID = M11.SETID AND M1.CHG_GRP_CD = M11.CHG_GRP_CD AND M11.EFFDT <= %CurrentDateIn)) < ( SELECT MIN(T2.EFFDT_FROM) FROM PS_RE_LS_TRM T2 , PS_RE_MISC_FEES M2 WHERE T.LS_KEY = T2.LS_KEY AND T.TERM_ID <> T2.TERM_ID AND T.TXN_GRP = T2.TXN_GRP AND T.TERM_RENT_TYPE = T2.TERM_RENT_TYPE AND T.PAYMENT_GROUP = T2.PAYMENT_GROUP AND T2.CHG_GRP_CD = M2.CHG_GRP_CD AND M2.CAPITALIZE_SW = 'Y' AND M2.EFF_STATUS = 'A' AND M2.EFFDT = ( SELECT MAX(M22.EFFDT) FROM PS_RE_MISC_FEES M22 WHERE M2.SETID = M22.SETID AND M2.CHG_GRP_CD = M22.CHG_GRP_CD AND M22.EFFDT <= %CurrentDateIn) AND T2.EFFDT_FROM > ( SELECT T22.EFFDT_TO FROM PS_RE_LS_TRM T22 , PS_RE_MISC_FEES M3 WHERE T.LS_KEY = T22.LS_KEY AND T.TERM_ID = T22.TERM_ID AND T.TXN_GRP = T22.TXN_GRP AND T.TERM_RENT_TYPE = T22.TERM_RENT_TYPE AND T.PAYMENT_GROUP = T22.PAYMENT_GROUP AND T22.CHG_GRP_CD = M3.CHG_GRP_CD AND M3.CAPITALIZE_SW = 'Y' AND M3.EFF_STATUS = 'A' AND M3.EFFDT = ( SELECT MAX(M33.EFFDT) FROM PS_RE_MISC_FEES M33 WHERE M3.SETID = M33.SETID AND M3.CHG_GRP_CD = M33.CHG_GRP_CD AND M33.EFFDT <= %CurrentDateIn))) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID ,A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '6' AND T.TERM_RENT_TYPE = '03' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T.LS_KEY = T1.LS_KEY AND T.TXN_GRP = T1.TXN_GRP AND T.TERM_RENT_TYPE = T1.TERM_RENT_TYPE AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP AND T1.EFFDT_FROM > ( SELECT MAX(TD.EFFDT_FROM) FROM PS_RE_LS AD , PS_RE_LS_TRM TD WHERE AD.LS_KEY = A.LS_KEY AND AD.LS_KEY = TD.LS_KEY AND TD.TXN_GRP = '6' AND TD.TERM_RENT_TYPE = '03' AND ( SELECT %DateAdd(TD1.EFFDT_TO, 1) FROM PS_RE_LS_TRM TD1 , PS_RE_MISC_FEES MD1 WHERE TD.LS_KEY = TD1.LS_KEY AND TD.TERM_ID = TD1.TERM_ID AND TD.TXN_GRP = TD1.TXN_GRP AND TD.TERM_RENT_TYPE = TD1.TERM_RENT_TYPE AND TD.PAYMENT_GROUP = TD1.PAYMENT_GROUP AND TD1.CHG_GRP_CD = MD1.CHG_GRP_CD AND MD1.CAPITALIZE_SW = 'Y' AND MD1.EFF_STATUS = 'A' AND MD1.EFFDT = ( SELECT MAX(MD11.EFFDT) FROM PS_RE_MISC_FEES MD11 WHERE MD1.SETID = MD11.SETID AND MD1.CHG_GRP_CD = MD11.CHG_GRP_CD AND MD11.EFFDT <= %CurrentDateIn)) < ( SELECT MIN(TD2.EFFDT_FROM) FROM PS_RE_LS_TRM TD2 , PS_RE_MISC_FEES MD2 WHERE TD.LS_KEY = TD2.LS_KEY AND TD.TERM_ID <> TD2.TERM_ID AND TD.TXN_GRP = TD2.TXN_GRP AND TD.TERM_RENT_TYPE = TD2.TERM_RENT_TYPE AND TD.PAYMENT_GROUP = TD2.PAYMENT_GROUP AND TD2.CHG_GRP_CD = MD2.CHG_GRP_CD AND MD2.CAPITALIZE_SW = 'Y' AND MD2.EFF_STATUS = 'A' AND MD2.EFFDT = ( SELECT MAX(MD22.EFFDT) FROM PS_RE_MISC_FEES MD22 WHERE MD2.SETID = MD22.SETID AND MD2.CHG_GRP_CD = MD22.CHG_GRP_CD AND MD22.EFFDT <= %CurrentDateIn) AND TD2.EFFDT_FROM > ( SELECT TD22.EFFDT_TO FROM PS_RE_LS_TRM TD22 , PS_RE_MISC_FEES MD3 WHERE TD.LS_KEY = TD22.LS_KEY AND TD.TERM_ID = TD22.TERM_ID AND TD.TXN_GRP = TD22.TXN_GRP AND TD.TERM_RENT_TYPE = TD22.TERM_RENT_TYPE AND TD.PAYMENT_GROUP = TD22.PAYMENT_GROUP AND TD22.CHG_GRP_CD = MD3.CHG_GRP_CD AND MD3.CAPITALIZE_SW = 'Y' AND MD3.E FF_STATUS = 'A' AND MD3.EFFDT = ( SELECT MAX(MD33.EFFDT) FROM PS_RE_MISC_FEES MD33 WHERE MD3.SETID = MD33.SETID AND MD3.CHG_GRP_CD = MD33.CHG_GRP_CD AND MD33.EFFDT <= %CurrentDateIn))))) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '4' AND (( SELECT %DateAdd(T1.EFFDT_TO, 1) FROM PS_RE_LS_TRM T1 WHERE T.LS_KEY = T1.LS_KEY AND T.TERM_ID = T1.TERM_ID AND T.TXN_GRP = T1.TXN_GRP AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG E , PS_RE_EXP_CLS CTG WHERE E.LS_KEY = T1.LS_KEY AND E.TERM_ID = T1.TERM_ID AND E.CHG_GRP_CD = CTG.CHG_GRP_CD AND CTG.CAPITALIZE_SW = 'N' AND CTG.EFF_STATUS = 'A' AND CTG.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_RE_EXP_CLS B WHERE B.SETID = CTG.SETID AND B.CHG_GRP_CD = CTG.CHG_GRP_CD AND B.EFFDT <= %CurrentDateIn))) < ( SELECT MIN(T2.EFFDT_FROM) FROM PS_RE_LS_TRM T2 WHERE T.LS_KEY = T2.LS_KEY AND T.TERM_ID <> T2.TERM_ID AND T.TXN_GRP = T2.TXN_GRP AND T.PAYMENT_GROUP = T2.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG E2 , PS_RE_EXP_CLS CTG2 WHERE E2.LS_KEY = T2.LS_KEY AND E2.TERM_ID = T2.TERM_ID AND E2.CHG_GRP_CD = CTG2.CHG_GRP_CD AND CTG2.CAPITALIZE_SW = 'N' AND CTG2.EFF_STATUS = 'A' AND CTG2.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_RE_EXP_CLS B2 WHERE B2.SETID = CTG2.SETID AND B2.CHG_GRP_CD = CTG2.CHG_GRP_CD AND B2.EFFDT <= %CurrentDateIn)) AND T2.EFFDT_FROM > ( SELECT T22.EFFDT_TO FROM PS_RE_LS_TRM T22 WHERE T.LS_KEY = T22.LS_KEY AND T.TERM_ID = T22.TERM_ID AND T.TXN_GRP = T22.TXN_GRP AND T.PAYMENT_GROUP = T22.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG E22 , PS_RE_EXP_CLS CTG22 WHERE E22.LS_KEY = T22.LS_KEY AND E22.TERM_ID = T22.TERM_ID AND E22.CHG_GRP_CD = CTG22.CHG_GRP_CD AND CTG22.CAPITALIZE_SW = 'N' AND CTG22.EFF_STATUS = 'A' AND CTG22.EFFDT = ( SELECT MAX(B22.EFFDT) FROM PS_RE_EXP_CLS B22 WHERE B22.SETID = CTG22.SETID AND B22.CHG_GRP_CD = CTG22.CHG_GRP_CD AND B22.EFFDT <= %CurrentDateIn))))) UNION SELECT A.BUSINESS_UNIT , A.LS_KEY , A.LS_NBR , A.LEASE_NAME , A.LSE_TYPE_CD , A.PRINCIPAL_ID , A.LS_ADMIN , A.LEASE_START_DT , A.LEASE_END_DT , T.TXN_GRP , T.TERM_ID , T.EFFDT_FROM , T.EFFDT_TO , 'GAP' AS TYPE FROM PS_RE_LS A , PS_RE_LS_TRM T WHERE A.LS_CLASSIFICATION = 'O' AND A.LEASE_OBLG = '1' AND A.LEASE_STATUS IN ('2', '6') AND A.RECOGNITION_EXEMPT = 'N' AND A.LS_KEY = T.LS_KEY AND T.TXN_GRP = '4' AND T.EFFDT_FROM = ( SELECT MIN(T1.EFFDT_FROM) FROM PS_RE_LS_TRM T1 WHERE T.LS_KEY = T1.LS_KEY AND T.TXN_GRP = T1.TXN_GRP AND T.PAYMENT_GROUP = T1.PAYMENT_GROUP AND T1.EFFDT_FROM > ( SELECT MAX(TE.EFFDT_FROM) FROM PS_RE_LS AE , PS_RE_LS_TRM TE WHERE AE.LS_KEY = A.LS_KEY AND AE.LS_KEY = TE.LS_KEY AND TE.TXN_GRP = '4' AND (( SELECT %DateAdd(TE1.EFFDT_TO, 1) FROM PS_RE_LS_TRM TE1 WHERE TE.LS_KEY = TE1.LS_KEY AND TE.TERM_ID = TE1.TERM_ID AND TE.TXN_GRP = TE1.TXN_GRP AND TE.PAYMENT_GROUP = TE1.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG EE , PS_RE_EXP_CLS CTGE WHERE EE.LS_KEY = TE1.LS_KEY AND EE.TERM_ID = TE1.TERM_ID AND EE.CHG_GRP_CD = CTGE.CHG_GRP_CD AND CTGE.CAPITALIZE_SW = 'N' AND CTGE.EFF_STATUS = 'A' AND CTGE.EFFDT = ( SELECT MAX(BE.EFFDT) FROM PS_RE_EXP_CLS BE WHERE BE.SETID = CTGE.SETID AND BE.CHG_GRP_CD = CTGE.CHG_GRP_CD AND BE.EFFDT <= %CurrentDateIn))) < ( SELECT MIN(TE2.EFFDT_FROM) FROM PS_RE_LS_TRM TE2 WHERE TE.LS_KEY = TE2.LS_KEY AND TE.TERM_ID <> TE2.TERM_ID AND TE.TXN_GRP = TE2.TXN_GRP AND TE.PAYMENT_GROUP = TE2.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG EE2 , PS_RE_EXP_CLS CTGE2 WHERE EE2.LS_KEY = TE2.LS_KEY AND EE2.TERM_ID = TE2.TERM_ID AND EE2.CHG_GRP_CD = CTGE2.CHG_GRP_CD AND CTGE2.CAPITALIZE_SW = 'N' AND CTGE2.EFF_STATUS = 'A' AND CTGE2.EFFDT = ( SELECT MAX(BE2.EFFDT) FROM PS_RE_EXP_CLS BE2 WHERE BE2.SETID = CTGE2.SETID AND BE2.CHG_GRP_CD = CTGE2.CHG_GRP_CD AND BE2.EFFDT <= %CurrentDateIn)) AND TE2.EFFDT_FROM > ( SELECT TE22.EFFDT_TO FROM PS_RE_LS_TRM TE22 WHERE TE.LS_KEY = TE22.LS_KEY AND TE.TERM_ID = TE22.TERM_ID AND TE.TXN_GRP = TE22.TXN_GRP AND TE.PAYMENT_GROUP = TE22.PAYMENT_GROUP AND NOT EXISTS( SELECT 'X' FROM PS_RE_LS_OPEX_CTG EE22 , PS_RE_EXP_CLS CTGE22 WHERE EE22.LS_KEY = TE22.LS_KEY AND EE22.TERM_ID = TE22.TERM_ID AND EE22.CHG_GRP_CD = CTGE22.CHG_GRP_CD AND CTGE22.CAPITALIZE_SW = 'N' AND CTGE22.EFF_STATUS = 'A' AND CTGE22.EFFDT = ( SELECT MAX(BE22.EFFDT) FROM PS_RE_EXP_CLS BE22 WHERE BE22.SETID = CTGE22.SETID AND BE22.CHG_GRP_CD = CTGE22.CHG_GRP_CD AND BE22.EFFDT <= %CurrentDateIn))))))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 3 | LS_NBR | Character(10) | VARCHAR2(10) NOT NULL | Lease Number, User Enterable or Generated Sequence Number |
| 4 | LEASE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Lease Name |
| 5 | LSE_TYPE_CD | Character(10) | VARCHAR2(10) NOT NULL | This field contains the code for the Lease Types |
| 6 | PRINCIPAL_ID | Character(15) | VARCHAR2(15) NOT NULL | Landlord/Tenant |
| 7 | LS_ADMIN | Number(10,0) | DECIMAL(10) NOT NULL | Lease Administrator |
| 8 | LEASE_START_DT | Date(10) | DATE | Lease Start Date |
| 9 | LEASE_END_DT | Date(10) | DATE | Lease End Date |
| 10 | TXN_GRP | Character(1) | VARCHAR2(1) NOT NULL |
Transaction Group
1=Base Rent 2=Security Deposit 3=Straightline Accounting 4=Operating Expense 5=Percent Rent 6=Miscellaneous Rent 7=Manual Fee 8=Lease Obligation 9=Lease Interest Expense A=Prior Period Lease Obligation B=Prior Period Lease Interest C=Rent Expense D=Lease Incentive |
| 11 | TERM_ID | Number(10,0) | DECIMAL(10) NOT NULL | Financial Term ID |
| 12 | EFFDT_FROM | Date(10) | DATE | Effective Date From |
| 13 | EFFDT_TO | Date(10) | DATE | Effective Date To |
| 14 | TYPE_DESCR | Character(10) | VARCHAR2(10) NOT NULL | 09/19/11 EGS: Created |