eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: XNC_MY_PRODUCTS_V

Product:	XNC - Sales for Communications (Obsolete)
Description:	This view retrieves the Items we are currently displaying in the My Products section in iStore for Communications.
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT HZP.STATE
, HZP.POSTAL_CODE
, HZP.PERSON_LAST_NAME
, MTS.DESCRIPTION
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_ID
, CII.QUANTITY
, MMU.UNIT_OF_MEASURE
, CII.SYSTEM_ID
, HZP.PERSON_FIRST_NAME
, HZP.ADDRESS1
, HZP.CITY
, QPH.NAME PRICE_LIST
, CII.QUANTITY*DECODE(XIT.ITEM_TYPE
, 'NON-RECURRING'
, OEO.UNIT_LIST_PRICE
, 0) +CII.QUANTITY*DECODE(XIT.ITEM_TYPE
, 'RECURRING'
, OEO.UNIT_LIST_PRICE
, 0) EPRICE
, DECODE(XIT.ITEM_TYPE
, 'NON-RECURRING'
, OEO.UNIT_LIST_PRICE) ONE_TIME_CHARGE
, DECODE(XIT.ITEM_TYPE
, 'RECURRING'
, OEO.UNIT_LIST_PRICE
, 0) RECURRING_PRICE
, OEH.FLOW_STATUS_CODE
, MSI.SEGMENT1 NAME
, OEH.TRANSACTIONAL_CURR_CODE
, CIA.PARTY_ACCOUNT_ID
, MSI.ORGANIZATION_ID
, CSS.NAME SUBSCRIPTION
, OEO.OPEN_FLAG
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZC
, CSI_IP_ACCOUNTS CIA
, CSI_I_PARTIES CIP
, CSI_ITEM_INSTANCES CII
, OE_ORDER_LINES_ALL OEO
, OE_ORDER_HEADERS_ALL OEH
, QP_PRICE_LISTS_V QPH
, XNC_ITEM_TYPE_V XIT
, MTL_SYSTEM_ITEMS_VL MTS
, CSI_SYSTEMS_VL CSS
, MTL_SYSTEM_ITEMS_B MSI
, MTL_UNITS_OF_MEASURE_VL MMU
WHERE HZP.PARTY_ID=HZC.PARTY_ID
AND CIA.PARTY_ACCOUNT_ID=HZC.CUST_ACCOUNT_ID
AND CIA.INSTANCE_PARTY_ID=CIP.INSTANCE_PARTY_ID
AND CII.INSTANCE_ID=CIP.INSTANCE_ID
AND OEO.LINE_ID=CII.LAST_OE_ORDER_LINE_ID
AND OEO.HEADER_ID=OEH.HEADER_ID
AND NVL(CII.CUSTOMER_VIEW_FLAG
, 'Y') = 'Y'
AND OEO.PRICE_LIST_ID=QPH.PRICE_LIST_ID
AND OEO.INVENTORY_ITEM_ID=CII.INVENTORY_ITEM_ID
AND XIT.ORGANIZATION_ID=OEO.ORG_ID
AND XIT.INVENTORY_ITEM_ID=OEO.INVENTORY_ITEM_ID
AND MTS.INVENTORY_ITEM_ID=CII.INVENTORY_ITEM_ID
AND MTS.ORGANIZATION_ID=XIT.ORGANIZATION_ID
AND CSS.SYSTEM_ID(+)=CII.SYSTEM_ID
AND MTS.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MTS.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND CIA.RELATIONSHIP_TYPE_CODE='OWNER'
AND CII.UNIT_OF_MEASURE=MMU.UOM_CODE
AND CIA.ACTIVE_END_DATE IS NULL

Columns

Name
STATE
POSTAL_CODE
PERSON_LAST_NAME
DESCRIPTION
INVENTORY_ITEM_ID
INSTANCE_ID
QUANTITY
UNIT_OF_MEASURE
SYSTEM_ID
PERSON_FIRST_NAME
ADDRESS1
CITY
PRICE_LIST
EPRICE
ONE_TIME_CHARGE
RECURRING_PRICE
FLOW_STATUS_CODE
NAME
TRANSACTIONAL_CURR_CODE
PARTY_ACCOUNT_ID
ORGANIZATION_ID
SUBSCRIPTION
OPEN_FLAG