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: BIM_FCTV_REV_COGS

Product:	BIM - Marketing Intelligence(Obsolete)
Description:	Used by the concurrent programs to load the summary tables.
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT NVL(CST.SALES_CHANNEL_CODE
, '-999' ) SALES_CHANNEL_CODE
, CST.CUSTOMER_ID ACCOUNT_ID
, ODH.PARTY_ID CUSTOMER_ID
, NVL(CST.PARENT_INVENTORY_ITEM_ID
, -999) INVENTORY_ITEM_ID
, NVL(CST.PARENT_ORGANIZATION_ID
, -999) ITEM_ORGANIZATION_ID
, CST.ORG_ID ORG_ID
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(CST.SHIP_TO_SITE_USE_ID
, CST.ORG_ID) SHIP_TO_GEOGRAPHY_CODE
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(CST.INVOICE_TO_SITE_USE_ID
, CST.ORG_ID ) BILL_TO_GEOGRAPHY_CODE
, CST.GL_DATE TRANSACTION_DATE
, CST.PERIOD_NAME_MONTH MONTH_PERIOD_NAME
, NVL(SRCD.CAMPAIGN_ID
, -999) CAMPAIGN_ID
, NVL(SRCD.MEDIA_ID
, -999) MEDIA_ID
, NVL(SRCD.CHANNEL_ID
, -999) CHANNEL_ID
, NVL(SRCD.EVENT_OFFER_ID
, -999) EVENT_OFFER_ID
, NVL(SRCD.EVENT_ID
, -999) EVENT_ID
, NVL(PMKT.MARKET_SEGMENT_ID
, -999) MARKET_SEGMENT_ID
, BIM_TARGET_SEGMENT_PKG.TARGET_SEGMENT_FK(ODH.PARTY_ID
, SRCD.SOURCE_CODE) TARGET_SEGMENT_ID
, NVL(SLIST.IMPORT_LIST_HEADER_ID
, -999) SOURCE_LIST_ID
, ODH.MARKETING_SOURCE_CODE_ID SOURCE_CODE_ID
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( GSOB.CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, CST.GL_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, NVL(CST.INVOICED_AMOUNT
, 0)) INITIATED_REVENUE
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( GSOB.CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, CST.GL_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, NVL(CST.COGS_AMOUNT
, 0)) COGS
, 0 REGISTRATION_REVENUE
FROM CST_BIS_MARGIN_SUMMARY CST
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
, ASO_I_ORDER_HEADERS_V ODH
, BIM_SOURCE_CODE_DETAILS SRCD
, AMS_PARTY_MARKET_SEGMENTS PMKT
, AMS_PARTY_SOURCES SLIST
WHERE CST.LEGAL_ENTITY_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'LEGAL ENTITY ACCOUNTING'
AND HOI.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID
AND CST.HEADER_ID = ODH.HEADER_ID
AND ODH.MARKETING_SOURCE_CODE_ID = SRCD.SOURCE_CODE_ID (+)
AND ODH.PARTY_ID = PMKT.PARTY_ID (+)
AND ODH.ORDERED_DATE >= PMKT.START_DATE_ACTIVE (+)
AND ODH.ORDERED_DATE <= NVL( PMKT.END_DATE_ACTIVE
, ODH.ORDERED_DATE )
AND PMKT.MARKET_SEGMENT_FLAG (+) = 'Y'
AND ODH.PARTY_ID = SLIST.PARTY_ID (+)
AND CST.PARENT_INVENTORY_ITEM_ID NOT IN ( SELECT INVENTORY_ITEM_ID
FROM AMS_EVENT_OFFERS_ALL_B ) UNION SELECT NVL( CST.SALES_CHANNEL_CODE
, '-999' ) SALES_CHANNEL_CODE
, CST.CUSTOMER_ID ACCOUNT_ID
, ODH.PARTY_ID CUSTOMER_ID
, NVL(CST.PARENT_INVENTORY_ITEM_ID
, -999) INVENTORY_ITEM_ID
, NVL(CST.PARENT_ORGANIZATION_ID
, -999) ITEM_ORGANIZATION_ID
, CST.ORG_ID ORG_ID
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(CST.SHIP_TO_SITE_USE_ID
, CST.ORG_ID ) SHIP_TO_GEOGRAPHY_CODE
, BIM_GEO_PKG.GET_GEOGRAPHY_CODE(CST.INVOICE_TO_SITE_USE_ID
, CST.ORG_ID ) BILL_TO_GEOGRAPHY_CODE
, CST.GL_DATE TRANSACTION_DATE
, CST.PERIOD_NAME_MONTH MONTH_PERIOD_NAME
, NVL(SRCD.CAMPAIGN_ID
, -999) CAMPAIGN_ID
, NVL(SRCD.MEDIA_ID
, -999) MEDIA_ID
, NVL(SRCD.CHANNEL_ID
, -999) CHANNEL_ID
, DECODE( NVL(EVTO.EVENT_OFFER_ID
, '-999')
, '-999'
, '-999'
, 'EVEO' || EVTO.EVENT_OFFER_ID ) EVENT_OFFER_ID
, DECODE( NVL(EVTO.EVENT_HEADER_ID
, '-999' )
, '-999'
, '-999'
, 'EVEH' || EVTO.EVENT_HEADER_ID ) EVENT_ID
, NVL(PMKT.MARKET_SEGMENT_ID
, -999) MARKET_SEGMENT_ID
, BIM_TARGET_SEGMENT_PKG.TARGET_SEGMENT_FK(ODH.PARTY_ID
, SRCD.SOURCE_CODE) TARGET_SEGMENT_ID
, NVL(SLIST.IMPORT_LIST_HEADER_ID
, -999) SOURCE_LIST_ID
, ODH.MARKETING_SOURCE_CODE_ID SOURCE_CODE_ID
, 0 INITIATED_REVENUE
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( GSOB.CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, CST.GL_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, NVL(CST.COGS_AMOUNT
, 0)) COGS
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( GSOB.CURRENCY_CODE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:CURRENCY_CODE')
, CST.GL_DATE
, JTF_BIS_UTIL.PROFILEVALUE('CRMBIS:GL_CONVERSION_TYPE')
, NVL(CST.INVOICED_AMOUNT
, 0)) REGISTRATION_REVENUE
FROM CST_BIS_MARGIN_SUMMARY CST
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
, ASO_I_ORDER_HEADERS_V ODH
, AMS_EVENT_OFFERS_ALL_B EVTO
, BIM_SOURCE_CODE_DETAILS SRCD
, AMS_PARTY_MARKET_SEGMENTS PMKT
, AMS_PARTY_SOURCES SLIST
WHERE CST.LEGAL_ENTITY_ID = HOI.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'LEGAL ENTITY ACCOUNTING'
AND HOI.ORG_INFORMATION1 = GSOB.SET_OF_BOOKS_ID
AND CST.PARENT_INVENTORY_ITEM_ID = EVTO.INVENTORY_ITEM_ID
AND CST.HEADER_ID = ODH.HEADER_ID
AND ODH.MARKETING_SOURCE_CODE_ID = SRCD.SOURCE_CODE_ID (+)
AND ODH.PARTY_ID = PMKT.PARTY_ID (+)
AND ODH.ORDERED_DATE >= PMKT.START_DATE_ACTIVE (+)
AND ODH.ORDERED_DATE <= NVL( PMKT.END_DATE_ACTIVE
, ODH.ORDERED_DATE )
AND PMKT.MARKET_SEGMENT_FLAG (+) = 'Y'
AND ODH.PARTY_ID = SLIST.PARTY_ID (+) WITH READ ONLY

Columns

Name
SALES_CHANNEL_CODE
ACCOUNT_ID
CUSTOMER_ID
INVENTORY_ITEM_ID
ITEM_ORGANIZATION_ID
ORG_ID
SHIP_TO_GEOGRAPHY_CODE
BILL_TO_GEOGRAPHY_CODE
TRANSACTION_DATE
MONTH_PERIOD_NAME
CAMPAIGN_ID
MEDIA_ID
CHANNEL_ID
EVENT_OFFER_ID
EVENT_ID
MARKET_SEGMENT_ID
TARGET_SEGMENT_ID
SOURCE_LIST_ID
SOURCE_CODE_ID
INITIATED_REVENUE
COGS
REGISTRATION_REVENUE