VIEW: APPS.OKL_BPD_CNTR_LVLNG_LNS_UV

Source

View Text - Preformatted

SELECT HA.CUST_ACCOUNT_ID , HC.SITE_USE_ID, CN.COUNTER_ID, CN.NAME, PL.ID1, KHR.CONTRACT_NUMBER, ASSET.NAME FROM HZ_CUST_SITE_USES HC,OKX_LIST_HEADERS_V PL,OKC_K_LINES_V ASSET,OKC_K_LINES_B USAGE,OKC_LINE_STYLES_B LTY,OKC_K_ITEMS UITEM,OKC_K_LINES_B OKS,OKC_K_LINES_B OKSU,OKC_K_ITEMS OKSITEM,CS_COUNTERS CN,OKC_RULES_B PLRULE,OKC_RULE_GROUPS_B LPRGRP,OKC_K_ITEMS ASSET_ITEM,OKC_K_REL_OBJS REL,OKC_K_HEADERS_B KHR,OKC_K_LINES_B USUB_LINE,HZ_CUST_ACCOUNTS HA WHERE KHR.BILL_TO_SITE_USE_ID = TO_char(HC.SITE_USE_ID) AND KHR.CUST_ACCT_ID = TO_char(HA.CUST_ACCOUNT_ID) AND USAGE.LSE_ID = LTY.ID AND LTY.LTY_CODE = 'USAGE' AND USAGE.ID = UITEM.CLE_ID AND USAGE.DNZ_CHR_ID = KHR.ID AND KHR.STS_CODE = 'BOOKED' AND KHR.ID = USAGE.CHR_ID AND USUB_LINE.DNZ_CHR_ID = USAGE.CHR_ID AND USAGE.ID = USUB_LINE.CLE_ID AND USUB_LINE.ID = ASSET_ITEM.CLE_ID AND LPRGRP.DNZ_CHR_ID = USAGE.CHR_ID AND LPRGRP.RGD_CODE = 'LAUSBB' AND PLRULE.RGP_ID = LPRGRP.ID AND PLRULE.RULE_INFORMATION_CATEGORY = 'LAUSBB' AND PLRULE.OBJECT2_ID1 = PL.ID1 AND PLRULE.OBJECT2_ID2 = PL.ID2 AND PLRULE.RULE_INFORMATION6 <> 'FRT' AND OKS.ID = UITEM.OBJECT1_ID1 AND OKS.DNZ_CHR_ID = REL.OBJECT1_ID1 AND OKSU.dnz_chr_id = REL.OBJECT1_ID1 AND OKS.ID = OKSU.CLE_ID AND OKS.DNZ_CHR_ID = OKSU.DNZ_CHR_ID AND OKSU.ID = OKSITEM.CLE_ID AND OKSITEM.OBJECT1_ID1 = CN.COUNTER_ID AND ASSET_ITEM.OBJECT1_ID1 = ASSET.ID AND REL.RTY_CODE = 'OKLUBB' AND REL.JTOT_OBJECT1_CODE = 'OKL_SERVICE' AND REL.CHR_ID = USAGE.DNZ_CHR_ID AND NOT EXISTS (SELECT '1' FROM OKL_CNTR_LVLNG_LNS_V CNTR_LVLNG, OKL_CNTR_LVLNG_GRPS_V CNTR_GRPS WHERE CNTR_LVLNG.KLE_ID = CN.COUNTER_ID AND CNTR_LVLNG.CLG_ID = CNTR_GRPS.ID AND SYSDATE BETWEEN CNTR_GRPS.EFFECTIVE_DATE_FROM AND CNTR_GRPS.EFFECTIVE_DATE_TO)

View Text - HTML Formatted

SELECT HA.CUST_ACCOUNT_ID
, HC.SITE_USE_ID
, CN.COUNTER_ID
, CN.NAME
, PL.ID1
, KHR.CONTRACT_NUMBER
, ASSET.NAME
FROM HZ_CUST_SITE_USES HC
, OKX_LIST_HEADERS_V PL
, OKC_K_LINES_V ASSET
, OKC_K_LINES_B USAGE
, OKC_LINE_STYLES_B LTY
, OKC_K_ITEMS UITEM
, OKC_K_LINES_B OKS
, OKC_K_LINES_B OKSU
, OKC_K_ITEMS OKSITEM
, CS_COUNTERS CN
, OKC_RULES_B PLRULE
, OKC_RULE_GROUPS_B LPRGRP
, OKC_K_ITEMS ASSET_ITEM
, OKC_K_REL_OBJS REL
, OKC_K_HEADERS_B KHR
, OKC_K_LINES_B USUB_LINE
, HZ_CUST_ACCOUNTS HA
WHERE KHR.BILL_TO_SITE_USE_ID = TO_CHAR(HC.SITE_USE_ID)
AND KHR.CUST_ACCT_ID = TO_CHAR(HA.CUST_ACCOUNT_ID)
AND USAGE.LSE_ID = LTY.ID
AND LTY.LTY_CODE = 'USAGE'
AND USAGE.ID = UITEM.CLE_ID
AND USAGE.DNZ_CHR_ID = KHR.ID
AND KHR.STS_CODE = 'BOOKED'
AND KHR.ID = USAGE.CHR_ID
AND USUB_LINE.DNZ_CHR_ID = USAGE.CHR_ID
AND USAGE.ID = USUB_LINE.CLE_ID
AND USUB_LINE.ID = ASSET_ITEM.CLE_ID
AND LPRGRP.DNZ_CHR_ID = USAGE.CHR_ID
AND LPRGRP.RGD_CODE = 'LAUSBB'
AND PLRULE.RGP_ID = LPRGRP.ID
AND PLRULE.RULE_INFORMATION_CATEGORY = 'LAUSBB'
AND PLRULE.OBJECT2_ID1 = PL.ID1
AND PLRULE.OBJECT2_ID2 = PL.ID2
AND PLRULE.RULE_INFORMATION6 <> 'FRT'
AND OKS.ID = UITEM.OBJECT1_ID1
AND OKS.DNZ_CHR_ID = REL.OBJECT1_ID1
AND OKSU.DNZ_CHR_ID = REL.OBJECT1_ID1
AND OKS.ID = OKSU.CLE_ID
AND OKS.DNZ_CHR_ID = OKSU.DNZ_CHR_ID
AND OKSU.ID = OKSITEM.CLE_ID
AND OKSITEM.OBJECT1_ID1 = CN.COUNTER_ID
AND ASSET_ITEM.OBJECT1_ID1 = ASSET.ID
AND REL.RTY_CODE = 'OKLUBB'
AND REL.JTOT_OBJECT1_CODE = 'OKL_SERVICE'
AND REL.CHR_ID = USAGE.DNZ_CHR_ID
AND NOT EXISTS (SELECT '1'
FROM OKL_CNTR_LVLNG_LNS_V CNTR_LVLNG
, OKL_CNTR_LVLNG_GRPS_V CNTR_GRPS
WHERE CNTR_LVLNG.KLE_ID = CN.COUNTER_ID
AND CNTR_LVLNG.CLG_ID = CNTR_GRPS.ID
AND SYSDATE BETWEEN CNTR_GRPS.EFFECTIVE_DATE_FROM
AND CNTR_GRPS.EFFECTIVE_DATE_TO)