VIEW: APPS.OKL_LA_PAYMENTS_SUMMARY_UV

Source

View Text - Preformatted

SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, NULL SERVICE_FEE_NAME, TO_NUMBER(NULL) SERVICE_FEE_ID, NULL ASSET_NAME, NULL ASSET_DESC, TO_NUMBER(NULL) ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, TO_NUMBER(NULL) TOP_LINE_LSE_ID, NULL TOP_LINE_LTY_CODE, NULL TOP_LINE_LSE_NAME, 6 SEQUENCE_NUM FROM OKC_K_HEADERS_B K,OKC_RULE_GROUPS_B RG,OKC_RULES_B RUL,OKL_STRMTYP_SOURCE_V STRM,OKC_STATUSES_B STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID = K.ID AND RG.CLE_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND STS.CODE = K.STS_CODE AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, NULL SERVICE_FEE_NAME, TO_NUMBER(NULL) SERVICE_FEE_ID, KL.NAME ASSET_NAME, KL.ITEM_DESCRIPTION ASSET_DESC, KL.ID ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, LS.ID TOP_LINE_LSE_ID, LS.LTY_CODE TOP_LINE_LTY_CODE, LS.NAME TOP_LINE_LSE_NAME, 1 SEQUENCE_NUM FROM OKC_K_HEADERS_B K, OKC_RULE_GROUPS_B RG, OKC_RULES_B RUL, OKL_STRMTYP_SOURCE_V STRM, OKC_K_LINES_V KL, OKC_LINE_STYLES_V LS, OKC_STATUSES_B STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RG.CLE_ID = KL.ID AND KL.LSE_ID = LS.ID AND LS.LTY_CODE = 'FREE_FORM1' AND KL.CHR_ID = K.ID AND KL.DNZ_CHR_ID = KL.CHR_ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND STS.CODE = KL.STS_CODE AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, SERVICE.NAME SERVICE_FEE_NAME, KL.ID SERVICE_FEE_ID, NULL ASSET_NAME, NULL ASSET_DESC, TO_NUMBER(NULL) ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, LS.ID TOP_LINE_LSE_ID, LS.LTY_CODE TOP_LINE_LTY_CODE, LS.NAME TOP_LINE_LSE_NAME, 2 SEQUENCE_NUM FROM OKC_K_HEADERS_B K, OKC_RULE_GROUPS_B RG, OKC_RULES_B RUL, OKL_STRMTYP_SOURCE_V STRM, OKX_SYSTEM_ITEMS_V SERVICE, OKC_K_LINES_V KL, OKC_LINE_STYLES_V LS, OKC_K_ITEMS KITMS, OKC_STATUSES_B STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RG.CLE_ID = KL.ID AND KL.LSE_ID = LS.ID AND LS.LTY_CODE = 'SOLD_SERVICE' AND SERVICE.ID1 = KITMS.OBJECT1_ID1 AND SERVICE.ID2 = KITMS.OBJECT1_ID2 AND KITMS.JTOT_OBJECT1_CODE = 'OKX_SERVICE' AND KITMS.CLE_ID = KL.ID AND KL.CHR_ID = K.ID AND KL.DNZ_CHR_ID = KL.CHR_ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND STS.CODE = KL.STS_CODE AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, FEE.NAME SERVICE_FEE_NAME, KL.ID SERVICE_FEE_ID, NULL ASSET_NAME, NULL ASSET_DESC, TO_NUMBER(NULL) ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, LS.ID TOP_LINE_LSE_ID, LS.LTY_CODE TOP_LINE_LTY_CODE, LS.NAME TOP_LINE_LSE_NAME, 4 SEQUENCE_NUM FROM OKC_K_HEADERS_B K, OKC_RULE_GROUPS_B RG, OKC_RULES_B RUL, OKL_STRMTYP_SOURCE_V STRM, OKL_STRMTYP_SOURCE_V FEE, OKC_K_LINES_V KL, OKC_LINE_STYLES_V LS, OKC_K_ITEMS KITMS, OKC_STATUSES_B STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RG.CLE_ID = KL.ID AND KL.LSE_ID = LS.ID AND LS.LTY_CODE = 'FEE' AND FEE.ID1 = KITMS.OBJECT1_ID1 AND FEE.ID2 = KITMS.OBJECT1_ID2 AND KITMS.JTOT_OBJECT1_CODE = 'OKL_STRMTYP' AND KITMS.CLE_ID = KL.ID AND KL.CHR_ID = K.ID AND KL.DNZ_CHR_ID = KL.CHR_ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND STS.CODE = KL.STS_CODE AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, SERVICE.NAME SERVICE_FEE_NAME, KL.ID SERVICE_FEE_ID, ASTLINE.NAME ASSET_NAME, ASTLINE.ITEM_DESCRIPTION ASSET_DESC, ASTLINE.ID ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, LS.ID TOP_LINE_LSE_ID, LS.LTY_CODE TOP_LINE_LTY_CODE, LS.NAME TOP_LINE_LSE_NAME, 3 SEQUENCE_NUM FROM OKC_K_HEADERS_B K, OKC_RULE_GROUPS_B RG, OKC_RULES_B RUL, OKL_STRMTYP_SOURCE_V STRM, OKX_SYSTEM_ITEMS_V SERVICE, OKC_K_LINES_V KL, OKC_K_LINES_B SUBLINE, OKC_LINE_STYLES_B SUBLS, OKC_LINE_STYLES_V LS, OKC_K_ITEMS KITMS, OKC_K_ITEMS SUBITEM, OKC_K_LINES_V ASTLINE, OKC_LINE_STYLES_B ASTLS, OKC_STATUSES_B SRV_STS, OKC_STATUSES_B AST_STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND RG.CLE_ID = SUBLINE.ID AND KL.LSE_ID = LS.ID AND LS.LTY_CODE = 'SOLD_SERVICE' AND SERVICE.ID1 = KITMS.OBJECT1_ID1 AND SERVICE.ID2 = KITMS.OBJECT1_ID2 AND KITMS.JTOT_OBJECT1_CODE = 'OKX_SERVICE' AND KITMS.CLE_ID = KL.ID AND KL.CHR_ID = K.ID AND KL.DNZ_CHR_ID = KL.CHR_ID AND SUBLINE.CLE_ID = KL.ID AND SUBLINE.DNZ_CHR_ID = KL.DNZ_CHR_ID AND SUBLINE.LSE_ID = SUBLS.ID AND SUBLS.LTY_CODE = 'LINK_SERV_ASSET' AND SUBITEM.JTOT_OBJECT1_CODE ='OKX_COVASST' AND SUBITEM.CLE_ID = SUBLINE.ID AND SUBITEM.DNZ_CHR_ID = SUBLINE.DNZ_CHR_ID AND ASTLINE.ID = SUBITEM.OBJECT1_ID1 AND ASTLINE.LSE_ID = ASTLS.ID AND ASTLS.LTY_CODE = 'FREE_FORM1' AND ASTLINE.DNZ_CHR_ID = K.ID AND SRV_STS.CODE = KL.STS_CODE AND SRV_STS.STE_CODE <> 'CANCELLED' AND AST_STS.CODE = ASTLINE.STS_CODE AND AST_STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID, K.CONTRACT_NUMBER CONTRACT_NUMBER, FEE.NAME SERVICE_FEE_NAME, KL.ID SERVICE_FEE_ID, ASTLINE.NAME ASSET_NAME, ASTLINE.ITEM_DESCRIPTION ASSET_DESC, ASTLINE.ID ASSET_ID, STRM.NAME STREAM_NAME, RUL.OBJECT1_ID1 STREAM_ID, STRM.CODE STREAM_CODE, RG.ID RULE_GROUP_ID, RUL.ID SLH_ID, LS.ID TOP_LINE_LSE_ID, LS.LTY_CODE TOP_LINE_LTY_CODE, LS.NAME TOP_LINE_LSE_NAME, 5 SEQUENCE_NUM FROM OKC_K_HEADERS_B K, OKC_RULE_GROUPS_B RG, OKC_RULES_B RUL, OKL_STRMTYP_SOURCE_V STRM, OKL_STRMTYP_SOURCE_V FEE, OKC_K_LINES_V KL, OKC_K_LINES_B SUBLINE, OKC_LINE_STYLES_B SUBLS, OKC_LINE_STYLES_V LS, OKC_K_ITEMS KITMS, OKC_K_ITEMS SUBITEM, OKC_K_LINES_V ASTLINE, OKC_LINE_STYLES_B ASTLS, OKC_STATUSES_B FEE_STS, OKC_STATUSES_B AST_STS WHERE RG.DNZ_CHR_ID = K.ID AND RG.CHR_ID IS NULL AND RG.RGD_CODE = 'LALEVL' AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH' AND RUL.RGP_ID = RG.ID AND RUL.OBJECT1_ID1 = STRM.ID1 AND RG.CLE_ID = SUBLINE.ID AND KL.LSE_ID = LS.ID AND LS.LTY_CODE = 'FEE' AND FEE.ID1 = KITMS.OBJECT1_ID1 AND FEE.ID2 = KITMS.OBJECT1_ID2 AND KITMS.JTOT_OBJECT1_CODE = 'OKL_STRMTYP' AND KITMS.CLE_ID = KL.ID AND KL.CHR_ID = K.ID AND KL.DNZ_CHR_ID = KL.CHR_ID AND SUBLINE.CLE_ID = KL.ID AND SUBLINE.DNZ_CHR_ID = KL.DNZ_CHR_ID AND SUBLINE.LSE_ID = SUBLS.ID AND SUBLS.LTY_CODE = 'LINK_FEE_ASSET' AND SUBITEM.JTOT_OBJECT1_CODE ='OKX_COVASST' AND SUBITEM.CLE_ID = SUBLINE.ID AND SUBITEM.DNZ_CHR_ID = SUBLINE.DNZ_CHR_ID AND ASTLINE.ID = SUBITEM.OBJECT1_ID1 AND ASTLINE.LSE_ID = ASTLS.ID AND ASTLS.LTY_CODE = 'FREE_FORM1' AND ASTLINE.DNZ_CHR_ID = K.ID AND FEE_STS.CODE = KL.STS_CODE AND FEE_STS.STE_CODE <> 'CANCELLED' AND AST_STS.CODE = ASTLINE.STS_CODE AND AST_STS.STE_CODE <> 'CANCELLED'

View Text - HTML Formatted

SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, NULL SERVICE_FEE_NAME
, TO_NUMBER(NULL) SERVICE_FEE_ID
, NULL ASSET_NAME
, NULL ASSET_DESC
, TO_NUMBER(NULL) ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, TO_NUMBER(NULL) TOP_LINE_LSE_ID
, NULL TOP_LINE_LTY_CODE
, NULL TOP_LINE_LSE_NAME
, 6 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKC_STATUSES_B STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID = K.ID
AND RG.CLE_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND STS.CODE = K.STS_CODE
AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, NULL SERVICE_FEE_NAME
, TO_NUMBER(NULL) SERVICE_FEE_ID
, KL.NAME ASSET_NAME
, KL.ITEM_DESCRIPTION ASSET_DESC
, KL.ID ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, LS.ID TOP_LINE_LSE_ID
, LS.LTY_CODE TOP_LINE_LTY_CODE
, LS.NAME TOP_LINE_LSE_NAME
, 1 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKC_K_LINES_V KL
, OKC_LINE_STYLES_V LS
, OKC_STATUSES_B STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RG.CLE_ID = KL.ID
AND KL.LSE_ID = LS.ID
AND LS.LTY_CODE = 'FREE_FORM1'
AND KL.CHR_ID = K.ID
AND KL.DNZ_CHR_ID = KL.CHR_ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND STS.CODE = KL.STS_CODE
AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, SERVICE.NAME SERVICE_FEE_NAME
, KL.ID SERVICE_FEE_ID
, NULL ASSET_NAME
, NULL ASSET_DESC
, TO_NUMBER(NULL) ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, LS.ID TOP_LINE_LSE_ID
, LS.LTY_CODE TOP_LINE_LTY_CODE
, LS.NAME TOP_LINE_LSE_NAME
, 2 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKX_SYSTEM_ITEMS_V SERVICE
, OKC_K_LINES_V KL
, OKC_LINE_STYLES_V LS
, OKC_K_ITEMS KITMS
, OKC_STATUSES_B STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RG.CLE_ID = KL.ID
AND KL.LSE_ID = LS.ID
AND LS.LTY_CODE = 'SOLD_SERVICE'
AND SERVICE.ID1 = KITMS.OBJECT1_ID1
AND SERVICE.ID2 = KITMS.OBJECT1_ID2
AND KITMS.JTOT_OBJECT1_CODE = 'OKX_SERVICE'
AND KITMS.CLE_ID = KL.ID
AND KL.CHR_ID = K.ID
AND KL.DNZ_CHR_ID = KL.CHR_ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND STS.CODE = KL.STS_CODE
AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, FEE.NAME SERVICE_FEE_NAME
, KL.ID SERVICE_FEE_ID
, NULL ASSET_NAME
, NULL ASSET_DESC
, TO_NUMBER(NULL) ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, LS.ID TOP_LINE_LSE_ID
, LS.LTY_CODE TOP_LINE_LTY_CODE
, LS.NAME TOP_LINE_LSE_NAME
, 4 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKL_STRMTYP_SOURCE_V FEE
, OKC_K_LINES_V KL
, OKC_LINE_STYLES_V LS
, OKC_K_ITEMS KITMS
, OKC_STATUSES_B STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RG.CLE_ID = KL.ID
AND KL.LSE_ID = LS.ID
AND LS.LTY_CODE = 'FEE'
AND FEE.ID1 = KITMS.OBJECT1_ID1
AND FEE.ID2 = KITMS.OBJECT1_ID2
AND KITMS.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND KITMS.CLE_ID = KL.ID
AND KL.CHR_ID = K.ID
AND KL.DNZ_CHR_ID = KL.CHR_ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND STS.CODE = KL.STS_CODE
AND STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, SERVICE.NAME SERVICE_FEE_NAME
, KL.ID SERVICE_FEE_ID
, ASTLINE.NAME ASSET_NAME
, ASTLINE.ITEM_DESCRIPTION ASSET_DESC
, ASTLINE.ID ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, LS.ID TOP_LINE_LSE_ID
, LS.LTY_CODE TOP_LINE_LTY_CODE
, LS.NAME TOP_LINE_LSE_NAME
, 3 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKX_SYSTEM_ITEMS_V SERVICE
, OKC_K_LINES_V KL
, OKC_K_LINES_B SUBLINE
, OKC_LINE_STYLES_B SUBLS
, OKC_LINE_STYLES_V LS
, OKC_K_ITEMS KITMS
, OKC_K_ITEMS SUBITEM
, OKC_K_LINES_V ASTLINE
, OKC_LINE_STYLES_B ASTLS
, OKC_STATUSES_B SRV_STS
, OKC_STATUSES_B AST_STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND RG.CLE_ID = SUBLINE.ID
AND KL.LSE_ID = LS.ID
AND LS.LTY_CODE = 'SOLD_SERVICE'
AND SERVICE.ID1 = KITMS.OBJECT1_ID1
AND SERVICE.ID2 = KITMS.OBJECT1_ID2
AND KITMS.JTOT_OBJECT1_CODE = 'OKX_SERVICE'
AND KITMS.CLE_ID = KL.ID
AND KL.CHR_ID = K.ID
AND KL.DNZ_CHR_ID = KL.CHR_ID
AND SUBLINE.CLE_ID = KL.ID
AND SUBLINE.DNZ_CHR_ID = KL.DNZ_CHR_ID
AND SUBLINE.LSE_ID = SUBLS.ID
AND SUBLS.LTY_CODE = 'LINK_SERV_ASSET'
AND SUBITEM.JTOT_OBJECT1_CODE ='OKX_COVASST'
AND SUBITEM.CLE_ID = SUBLINE.ID
AND SUBITEM.DNZ_CHR_ID = SUBLINE.DNZ_CHR_ID
AND ASTLINE.ID = SUBITEM.OBJECT1_ID1
AND ASTLINE.LSE_ID = ASTLS.ID
AND ASTLS.LTY_CODE = 'FREE_FORM1'
AND ASTLINE.DNZ_CHR_ID = K.ID
AND SRV_STS.CODE = KL.STS_CODE
AND SRV_STS.STE_CODE <> 'CANCELLED'
AND AST_STS.CODE = ASTLINE.STS_CODE
AND AST_STS.STE_CODE <> 'CANCELLED' UNION ALL SELECT K.ID CHR_ID
, K.CONTRACT_NUMBER CONTRACT_NUMBER
, FEE.NAME SERVICE_FEE_NAME
, KL.ID SERVICE_FEE_ID
, ASTLINE.NAME ASSET_NAME
, ASTLINE.ITEM_DESCRIPTION ASSET_DESC
, ASTLINE.ID ASSET_ID
, STRM.NAME STREAM_NAME
, RUL.OBJECT1_ID1 STREAM_ID
, STRM.CODE STREAM_CODE
, RG.ID RULE_GROUP_ID
, RUL.ID SLH_ID
, LS.ID TOP_LINE_LSE_ID
, LS.LTY_CODE TOP_LINE_LTY_CODE
, LS.NAME TOP_LINE_LSE_NAME
, 5 SEQUENCE_NUM
FROM OKC_K_HEADERS_B K
, OKC_RULE_GROUPS_B RG
, OKC_RULES_B RUL
, OKL_STRMTYP_SOURCE_V STRM
, OKL_STRMTYP_SOURCE_V FEE
, OKC_K_LINES_V KL
, OKC_K_LINES_B SUBLINE
, OKC_LINE_STYLES_B SUBLS
, OKC_LINE_STYLES_V LS
, OKC_K_ITEMS KITMS
, OKC_K_ITEMS SUBITEM
, OKC_K_LINES_V ASTLINE
, OKC_LINE_STYLES_B ASTLS
, OKC_STATUSES_B FEE_STS
, OKC_STATUSES_B AST_STS
WHERE RG.DNZ_CHR_ID = K.ID
AND RG.CHR_ID IS NULL
AND RG.RGD_CODE = 'LALEVL'
AND RUL.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL.RGP_ID = RG.ID
AND RUL.OBJECT1_ID1 = STRM.ID1
AND RG.CLE_ID = SUBLINE.ID
AND KL.LSE_ID = LS.ID
AND LS.LTY_CODE = 'FEE'
AND FEE.ID1 = KITMS.OBJECT1_ID1
AND FEE.ID2 = KITMS.OBJECT1_ID2
AND KITMS.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND KITMS.CLE_ID = KL.ID
AND KL.CHR_ID = K.ID
AND KL.DNZ_CHR_ID = KL.CHR_ID
AND SUBLINE.CLE_ID = KL.ID
AND SUBLINE.DNZ_CHR_ID = KL.DNZ_CHR_ID
AND SUBLINE.LSE_ID = SUBLS.ID
AND SUBLS.LTY_CODE = 'LINK_FEE_ASSET'
AND SUBITEM.JTOT_OBJECT1_CODE ='OKX_COVASST'
AND SUBITEM.CLE_ID = SUBLINE.ID
AND SUBITEM.DNZ_CHR_ID = SUBLINE.DNZ_CHR_ID
AND ASTLINE.ID = SUBITEM.OBJECT1_ID1
AND ASTLINE.LSE_ID = ASTLS.ID
AND ASTLS.LTY_CODE = 'FREE_FORM1'
AND ASTLINE.DNZ_CHR_ID = K.ID
AND FEE_STS.CODE = KL.STS_CODE
AND FEE_STS.STE_CODE <> 'CANCELLED'
AND AST_STS.CODE = ASTLINE.STS_CODE
AND AST_STS.STE_CODE <> 'CANCELLED'