[Home] [Help]
| Product: | OPI - Operations Intelligence (Obsolete) |
|---|---|
| Description: | Product Sales Revenue report source view |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ORG.ALL_ALL_PK_KEY ALL_ORGS_ID
, ORG.BGRP_BUSINESS_GROUP_PK_KEY BGRP_ID
, ORG.LGET_LEGAL_ENTITY_PK_KEY LE_ID
, ORG.OPER_OPERATING_UNIT_PK_KEY OU_ID
, ORG.ORGA_ORGANIZATION_PK_KEY ORG_ID
, MARGIN1.ALL_ALL_PK_KEY ALL_TIME_ID
, MARGIN1.CPER_CAL_PERIOD_PK_KEY CAL_PERIOD_ID
, MARGIN1.CQTR_CAL_QTR_PK_KEY CAL_QTR_ID
, MARGIN1.CYR_CAL_YEAR_PK_KEY CAL_YEAR_ID
, IT.ALL_ALL_PK_KEY ALL_ITEMS_ID
, IT.CO16_CATEGORY_PK_KEY ITEM_CAT_ID
, IT.IREV_ITEM_REVISION_PK_KEY ITEM_ID
, SCH.A_ALL_PK_KEY ALL_SCH_ID
, SCH.SCHN_SLSCHNL_PK_KEY SCH_ID
, GEO.ALL_ALL_PK_KEY ALL_LOCS_ID
, GEO.GLOC_LOCATION_PK_KEY LOC_ID
, GEO.CITY_CITY_PK_KEY CITY_ID
, GEO.STAT_STATE_PK_KEY STATE_ID
, GEO.COUN_COUNTRY_PK_KEY COUNTRY_ID
, GEO.REGN_REGION_PK_KEY REGION_ID
, GEO.ARE1_AREA1_PK_KEY AREA_ID
, MARGIN1.CPER_END_DATE PERD_END_DATE
, ADD_MONTHS(MARGIN1.CPER_END_DATE
, -12) LAST_YEAR_PERD_END_DATE
, MARGIN1.PROD_AMT_G SALES_REV
, NVL(MARGIN2.PROD_AMT_G
, 0) PREV_YR_SALES
FROM (SELECT *
FROM OPI_EDW_PERD_MARGIN_F
, EDW_TIME_M
WHERE MARGIN_PERIOD_FK_KEY = CDAY_CAL_DAY_PK_KEY ) MARGIN1
, (SELECT *
FROM OPI_EDW_PERD_MARGIN_F
, EDW_TIME_M
WHERE MARGIN_PERIOD_FK_KEY = CDAY_CAL_DAY_PK_KEY ) MARGIN2
, EDW_ORGANIZATION_M ORG
, EDW_OE_SLCHNL_M SCH
, EDW_ITEMS_M IT
, EDW_GEOGRAPHY_M GEO
WHERE ADD_MONTHS(MARGIN1.CPER_END_DATE
, -12) = MARGIN2.CPER_END_DATE (+)
AND MARGIN1.ITEM_ORG_FK_KEY = MARGIN2.ITEM_ORG_FK_KEY (+)
AND MARGIN1.OPERATING_UNIT_FK_KEY = MARGIN2.OPERATING_UNIT_FK_KEY (+)
AND MARGIN1.CUSTOMER_FK_KEY = MARGIN2.CUSTOMER_FK_KEY (+)
AND MARGIN1.SALES_CHANNEL_FK_KEY = MARGIN2.SALES_CHANNEL_FK_KEY (+)
AND MARGIN1.PRIM_SALES_REP_FK_KEY = MARGIN2.PRIM_SALES_REP_FK_KEY (+)
AND MARGIN1.BILL_TO_LOC_FK_KEY = MARGIN2.BILL_TO_LOC_FK_KEY (+)
AND MARGIN1.SHIP_TO_LOC_FK_KEY = MARGIN2.SHIP_TO_LOC_FK_KEY (+)
AND MARGIN1.PROJECT_FK_KEY = MARGIN2.PROJECT_FK_KEY (+)
AND MARGIN1.SHIP_LOCATION_FK_KEY = MARGIN2.SHIP_LOCATION_FK_KEY (+)
AND MARGIN1.OPERATING_UNIT_FK_KEY = ORG.ORGA_ORGANIZATION_PK_KEY
AND MARGIN1.SALES_CHANNEL_FK_KEY = SCH.SCHN_SLSCHNL_PK_KEY
AND MARGIN1.ITEM_ORG_FK_KEY = IT.IREV_ITEM_REVISION_PK_KEY
AND MARGIN1.SHIP_TO_LOC_FK_KEY = GEO.GLOC_LOCATION_PK_KEY