[Home] [Help]
| Product: | BIL - Sales Intelligence (Obsolete) |
|---|---|
| Description: | Quantitative and descriptive information related to sales orders and sales margin |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CBMS.ORDER_NUMBER
, CBMS.HEADER_ID
, CBMS.LINE_ID
, CBMS.ORDER_TYPE_ID
, NVL(HZCA.PARTY_ID
, -999)
, JRS.RESOURCE_ID
, CBMS.SALES_CHANNEL_CODE
, CBMS.INVENTORY_ITEM_ID
, CBMS.TERRITORY_ID
, NULL
, GSOB.CURRENCY_CODE
, CBMS.ORIGINAL_GL_DATE
, CBMS.GL_DATE
, NULL
, SUM(CBMS.INVOICE_LINE_QUANTITY)
, SUM(CBMS.INVOICE_QUANTITY)
, SUM(CBMS.INVOICED_AMOUNT)
, SUM(CBMS.COGS_AMOUNT)
, SUM(CBMS.MARGIN)
, AVG(CBMS.MARGIN_PCT)
, CBMS.PERIOD_NAME_QTR
, CBMS.PERIOD_NUM_QTR
, CBMS.PERIOD_NAME_YEAR
, CBMS.PERIOD_NUM_YEAR
, CBMS.PERIOD_NAME_MONTH
, CBMS.PERIOD_NUM_MONTH
FROM CST_BIS_MARGIN_SUMMARY CBMS
, JTF_RS_SALESREPS JRS
, HZ_CUST_ACCOUNTS HZCA
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
WHERE JRS.SALESREP_ID = CBMS.PRIMARY_SALESREP_ID
AND NVL(CBMS.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND HZCA.CUST_ACCOUNT_ID(+) = CBMS.CUSTOMER_ID
AND CBMS.LEGAL_ENTITY_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'LEGAL ENTITY ACCOUNTING'
AND HOI.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID
AND HZCA.STATUS IN ('A'
, 'I') GROUP BY CBMS.ORDER_NUMBER
, CBMS.HEADER_ID
, CBMS.LINE_ID
, CBMS.ORDER_TYPE_ID
, NVL(HZCA.PARTY_ID
, -999)
, JRS.RESOURCE_ID
, CBMS.SALES_CHANNEL_CODE
, CBMS.INVENTORY_ITEM_ID
, CBMS.TERRITORY_ID
, CBMS.ORG_ID
, GSOB.CURRENCY_CODE
, CBMS.ORIGINAL_GL_DATE
, CBMS.GL_DATE
, CBMS.PERIOD_NAME_QTR
, CBMS.PERIOD_NUM_QTR
, CBMS.PERIOD_NAME_YEAR
, CBMS.PERIOD_NUM_YEAR
, CBMS.PERIOD_NAME_MONTH
, CBMS.PERIOD_NUM_MONTH WITH READ ONLY