View: XNC_PARTY_ACCT_INSTANCE

View Text

SELECT UNIQUE CP.INSTANCE_ID
, CP.PARTY_ID
, HCA.ACCOUNT_NUMBER ACCOUNT
, CII.SYSTEM_ID
, DECODE(CST.NAME
, NULL
, 'NO SYSTEM'
, CST.NAME) SYSTEM_NAME
, CII.INVENTORY_ITEM_ID
, NVL(CII.INV_ORGANIZATION_ID
, CII.INV_MASTER_ORGANIZATION_ID) ORGANIZATION_ID
, MSI.DESCRIPTION
, MSI.PADDED_CONCATENATED_SEGMENTS
, OL.CONFIG_HEADER_ID
, OL.CONFIG_REV_NBR CONFIG_REVISION_NUM
, DECODE(MSI.BOM_ITEM_TYPE
, 1
, 'MDL'
, 2
, 'OPT'
, 3
, 'PLN'
, 4
, DECODE( MSI.SERVICE_ITEM_FLAG
, 'Y'
, 'SRV'
, DECODE( MSI.SERVICEABLE_PRODUCT_FLAG
, 'Y'
, 'SVA'
, 'STD'))) ITEM_TYPE_CODE
, MSI.PRIMARY_UOM_CODE
FROM CSI_I_PARTIES CP
, CSI_IP_ACCOUNTS CIA
, CSI_ITEM_INSTANCES CII
, CSI_SYSTEMS_TL CST
, CSI_SYSTEMS_B CSB
, MTL_SYSTEM_ITEMS_VL MSI
, OE_ORDER_LINES_ALL OL
, HZ_CUST_ACCOUNTS HCA
WHERE CP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID
AND CP.INSTANCE_ID =CII.INSTANCE_ID
AND CSB.SYSTEM_ID (+)= CII.SYSTEM_ID
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CII.INV_MASTER_ORGANIZATION_ID =MSI.ORGANIZATION_ID
AND CSB.SYSTEM_ID = CST.SYSTEM_ID (+)
AND CIA.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CII.ACTIVE_END_DATE IS NULL
AND CIA.ACTIVE_END_DATE IS NULL
AND OL.LINE_ID = CII.LAST_OE_ORDER_LINE_ID
AND HCA.CUST_ACCOUNT_ID = CIA.PARTY_ACCOUNT_ID
AND CST.LANGUAGE (+)= USERENV('LANG')

Columns