View: XNC_CSI_SUBSCRIPTION_IB_V

View Text

SELECT HZCA.PARTY_ID PARTY_ID
, CIARGV.ACCOUNT_ID CUST_ACCOUNT_ID
, CIARGV.ACCOUNT_NUMBER ACCOUNT_NUMBER
, MRIA.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.INVENTORY_ITEM_ID RELATED_ITEM_ID
, MSI.DESCRIPTION RELATED_ITEM_DESC
, LTRIM(RTRIM(MSI.PADDED_CONCATENATED_SEGMENTS)) RELATED_ITEM_NAME
, CSVL.SYSTEM_ID SYSTEM_ID
, CSVL.START_DATE_ACTIVE START_DATE_ACTIVE
, CSVL.END_DATE_ACTIVE END_DATE_ACTIVE
, CSVL.NAME SUBSCRIPTION
, MSI.ORGANIZATION_ID
FROM CSI_INSTANCE_ACCTS_RG_V CIARGV
, CSI_SYSTEMS_VL CSVL
, CSI_ITEM_INSTANCES CII
, HZ_CUST_ACCOUNTS HZCA
, MTL_SYSTEM_ITEMS_KFV MSI
, XNC_ITEM_ACTION_PARS_V XIAPV
, MTL_RELATED_ITEMS_ALL_V MRIA
WHERE CIARGV.SYSTEM_ID = CSVL.SYSTEM_ID
AND CII.INSTANCE_ID = CIARGV.CUSTOMER_PRODUCT_ID
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CII.INV_MASTER_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MRIA.RELATED_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MRIA.RELATIONSHIP_TYPE_ID = 6
AND SYSDATE BETWEEN CII.ACTIVE_START_DATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE)
AND HZCA.CUST_ACCOUNT_ID = CIARGV.ACCOUNT_ID
AND CII.INVENTORY_ITEM_ID = XIAPV.INVENTORY_ITEM_ID
AND XIAPV.SUB_ACTION_TYPE = 'SUBSCRIPTION'
AND XIAPV.SUBSCRIPTION_FLAG = 'Y'

Columns