View: XNC_ORD_IB_PRODUCT_RG_V

View Text

SELECT UNIQUE A.INSTANCE_ID||'
, '||TO_CHAR(HCA.CUST_ACCOUNT_ID)||'
, '||TO_CHAR(HCA.PARTY_ID) NODE_KEY
, A.INV_ORGANIZATION_ID ORG_ID
, A.INSTANCE_ID CUSTOMER_PRODUCT_ID
, A.QUANTITY
, RPAD(NVL(MSI.SEGMENT1
, ' ')
, 25) CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, QLH.NAME PRICE_LIST_NAME
, OOL.PRICE_LIST_ID
, QLL.COMMENTS
, QLH.CURRENCY_CODE
, OOL.UNIT_SELLING_PRICE * A.QUANTITY EXTENDED_PRICE
, A.UNIT_OF_MEASURE UNIT_OF_MEASURE_CODE
, A.CREATION_DATE ORIGINAL_ORDER_DATE
, B.DESCRIPTION CP_STATUS
, A.ACTIVE_START_DATE START_DATE_ACTIVE
, A.ACTIVE_END_DATE END_DATE_ACTIVE
, OOL.LINE_ID
, OOL.HEADER_ID
, A.INVENTORY_ITEM_ID
, CIPA.PARTY_ACCOUNT_ID CUSTOMER_ID
, 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
FROM CSI_ITEM_INSTANCES A
, CSI_INSTANCE_STATUSES B
, MTL_SYSTEM_ITEMS_VL MSI
, QP_PRICE_LIST_LINES_V QLL
, QP_PRICE_LISTS_V QLH
, OE_ORDER_LINES_ALL OOL
, HZ_CUST_ACCOUNTS HCA
, CSI_I_PARTIES CIP
, CSI_IP_ACCOUNTS CIPA
WHERE A.INSTANCE_STATUS_ID = B.INSTANCE_STATUS_ID
AND HCA.CUST_ACCOUNT_ID = CIPA.PARTY_ACCOUNT_ID
AND A.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CS_STD.GET_ITEM_VALDN_ORGZN_ID = MSI.ORGANIZATION_ID
AND OOL.LINE_ID = A.LAST_OE_ORDER_LINE_ID
AND OOL.PRICE_LIST_ID = QLH.PRICE_LIST_ID
AND OOL.PRICE_LIST_ID = QLL.PRICE_LIST_ID
AND A.INSTANCE_ID = CIP.INSTANCE_ID
AND CIP.INSTANCE_PARTY_ID = CIPA.INSTANCE_PARTY_ID

Columns