[Home] [Help]
SELECT all_orgs_id, bgrp_id, le_id, ou_id, org_id, all_time_id, cal_period_id, cal_qtr_id, cal_year_id, all_items_id, item_cat_id, item_id, ITEM_NUMBER_ID, ITEM_ORG_ID, all_sch_id, sch_id, all_locs_id, loc_id, city_id, state_id, country_id, region_id, area_id, perd_end_date, last_year_perd_end_date, sales_rev, prev_yr_sales FROM ( 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, margin.all_all_pk_key all_time_id, margin.cper_cal_period_pk_key cal_period_id, margin.cqtr_cal_qtr_pk_key cal_qtr_id, margin.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, it.ITEM_ITEM_NUMBER_PK_KEY ITEM_NUMBER_ID, it.IORG_ITEM_ORG_PK_KEY ITEM_ORG_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, margin.cper_end_date perd_end_date, ADD_MONTHS(cper_end_date, -12) last_year_perd_end_date, margin.sales_rev sales_rev, LAG(margin.sales_rev,12) OVER ( PARTITION BY margin.cnam_cal_name_pk_key, margin.item_org_fk_key, margin.operating_unit_fk_key, margin.sales_channel_fk_key, margin.ship_to_loc_fk_key ORDER BY margin.cper_end_date ) prev_yr_sales FROM ( SELECT /* One Margin row for every Period - even if Zero */ item_org_fk_key, operating_unit_fk_key, sales_channel_fk_key, ship_to_loc_fk_key, margin_period_fk_key, cnam_cal_name_pk_key, all_all_pk_key, cper_cal_period_pk_key, cqtr_cal_qtr_pk_key, cyr_cal_year_pk_key, cper_end_date, SUM(prod_amt_g) sales_rev FROM ( SELECT item_org_fk_key, operating_unit_fk_key, sales_channel_fk_key, ship_to_loc_fk_key, margin_period_fk_key, 0 prod_amt_g, cnam_cal_name_pk_key, all_all_pk_key, cper_cal_period_pk_key, cqtr_cal_qtr_pk_key, cyr_cal_year_pk_key, cper_end_date FROM ( SELECT DISTINCT operating_unit_fk_key, sales_channel_fk_key, item_org_fk_key, ship_to_loc_fk_key FROM opi_edw_perd_margin_f ) fks, ( SELECT t.cday_cal_day_pk_key margin_period_fk_key, t.cnam_cal_name_pk_key, t.all_all_pk_key, t.cper_cal_period_pk_key, t.cqtr_cal_qtr_pk_key, t.cyr_cal_year_pk_key, t.cper_end_date FROM edw_time_m t, ( SELECT cnam_cal_name_pk_key, MIN(cper_end_date) min_date, MAX(cper_end_date) max_date FROM edw_time_m t, opi_edw_perd_margin_f m WHERE t.cday_cal_day_pk_key = m.margin_period_fk_key GROUP BY cnam_cal_name_pk_key ) cals WHERE t.cday_cal_day_pk LIKE '%CPER' AND t.cnam_cal_name_pk_key = cals.cnam_cal_name_pk_key AND t.cper_end_date BETWEEN add_months(cals.min_date,-12) AND add_months(cals.max_date,12) ) periods /* no WHERE clause -- this is an intentional cartesian product */ UNION ALL SELECT /* Net_Margin -- rows only where non-zero revenue or cost */ m.item_org_fk_key, m.operating_unit_fk_key, m.sales_channel_fk_key, m.ship_to_loc_fk_key, m.margin_period_fk_key, m.prod_amt_g, t.cnam_cal_name_pk_key, t.all_all_pk_key, t.cper_cal_period_pk_key, t.cqtr_cal_qtr_pk_key, t.cyr_cal_year_pk_key, t.cper_end_date FROM opi_edw_perd_margin_f m, edw_time_m t WHERE t.cday_cal_day_pk_key = m.margin_period_fk_key ) /* FROM Zero_Margin_plus_Net_Margin */ GROUP BY item_org_fk_key, operating_unit_fk_key, sales_channel_fk_key, ship_to_loc_fk_key, margin_period_fk_key, cnam_cal_name_pk_key, all_all_pk_key, cper_cal_period_pk_key, cqtr_cal_qtr_pk_key, cyr_cal_year_pk_key, cper_end_date ) margin, edw_organization_m org, edw_oe_slchnl_m sch, edw_items_m it, edw_geography_m geo WHERE margin.operating_unit_fk_key = org.orga_organization_pk_key AND margin.sales_channel_fk_key = sch.schn_slschnl_pk_key AND margin.item_org_fk_key = it.irev_item_revision_pk_key AND margin.ship_to_loc_fk_key = geo.gloc_location_pk_key ) WHERE sales_rev <> 0 OR prev_yr_sales <> 0
SELECT ALL_ORGS_ID
, BGRP_ID
, LE_ID
, OU_ID
, ORG_ID
, ALL_TIME_ID
, CAL_PERIOD_ID
, CAL_QTR_ID
, CAL_YEAR_ID
, ALL_ITEMS_ID
, ITEM_CAT_ID
, ITEM_ID
, ITEM_NUMBER_ID
, ITEM_ORG_ID
, ALL_SCH_ID
, SCH_ID
, ALL_LOCS_ID
, LOC_ID
, CITY_ID
, STATE_ID
, COUNTRY_ID
, REGION_ID
, AREA_ID
, PERD_END_DATE
, LAST_YEAR_PERD_END_DATE
, SALES_REV
, PREV_YR_SALES
FROM ( 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
, MARGIN.ALL_ALL_PK_KEY ALL_TIME_ID
, MARGIN.CPER_CAL_PERIOD_PK_KEY CAL_PERIOD_ID
, MARGIN.CQTR_CAL_QTR_PK_KEY CAL_QTR_ID
, MARGIN.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
, IT.ITEM_ITEM_NUMBER_PK_KEY ITEM_NUMBER_ID
, IT.IORG_ITEM_ORG_PK_KEY ITEM_ORG_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
, MARGIN.CPER_END_DATE PERD_END_DATE
, ADD_MONTHS(CPER_END_DATE
, -12) LAST_YEAR_PERD_END_DATE
, MARGIN.SALES_REV SALES_REV
, LAG(MARGIN.SALES_REV
, 12) OVER ( PARTITION BY MARGIN.CNAM_CAL_NAME_PK_KEY
, MARGIN.ITEM_ORG_FK_KEY
, MARGIN.OPERATING_UNIT_FK_KEY
, MARGIN.SALES_CHANNEL_FK_KEY
, MARGIN.SHIP_TO_LOC_FK_KEY ORDER BY MARGIN.CPER_END_DATE ) PREV_YR_SALES
FROM ( SELECT /* ONE MARGIN ROW FOR EVERY PERIOD - EVEN IF ZERO */ ITEM_ORG_FK_KEY
, OPERATING_UNIT_FK_KEY
, SALES_CHANNEL_FK_KEY
, SHIP_TO_LOC_FK_KEY
, MARGIN_PERIOD_FK_KEY
, CNAM_CAL_NAME_PK_KEY
, ALL_ALL_PK_KEY
, CPER_CAL_PERIOD_PK_KEY
, CQTR_CAL_QTR_PK_KEY
, CYR_CAL_YEAR_PK_KEY
, CPER_END_DATE
, SUM(PROD_AMT_G) SALES_REV
FROM ( SELECT ITEM_ORG_FK_KEY
, OPERATING_UNIT_FK_KEY
, SALES_CHANNEL_FK_KEY
, SHIP_TO_LOC_FK_KEY
, MARGIN_PERIOD_FK_KEY
, 0 PROD_AMT_G
, CNAM_CAL_NAME_PK_KEY
, ALL_ALL_PK_KEY
, CPER_CAL_PERIOD_PK_KEY
, CQTR_CAL_QTR_PK_KEY
, CYR_CAL_YEAR_PK_KEY
, CPER_END_DATE
FROM ( SELECT DISTINCT OPERATING_UNIT_FK_KEY
, SALES_CHANNEL_FK_KEY
, ITEM_ORG_FK_KEY
, SHIP_TO_LOC_FK_KEY
FROM OPI_EDW_PERD_MARGIN_F ) FKS
, ( SELECT T.CDAY_CAL_DAY_PK_KEY MARGIN_PERIOD_FK_KEY
, T.CNAM_CAL_NAME_PK_KEY
, T.ALL_ALL_PK_KEY
, T.CPER_CAL_PERIOD_PK_KEY
, T.CQTR_CAL_QTR_PK_KEY
, T.CYR_CAL_YEAR_PK_KEY
, T.CPER_END_DATE
FROM EDW_TIME_M T
, ( SELECT CNAM_CAL_NAME_PK_KEY
, MIN(CPER_END_DATE) MIN_DATE
, MAX(CPER_END_DATE) MAX_DATE
FROM EDW_TIME_M T
, OPI_EDW_PERD_MARGIN_F M
WHERE T.CDAY_CAL_DAY_PK_KEY = M.MARGIN_PERIOD_FK_KEY GROUP BY CNAM_CAL_NAME_PK_KEY ) CALS
WHERE T.CDAY_CAL_DAY_PK LIKE '%CPER'
AND T.CNAM_CAL_NAME_PK_KEY = CALS.CNAM_CAL_NAME_PK_KEY
AND T.CPER_END_DATE BETWEEN ADD_MONTHS(CALS.MIN_DATE
, -12)
AND ADD_MONTHS(CALS.MAX_DATE
, 12) ) PERIODS /* NO
WHERE CLAUSE -- THIS IS AN INTENTIONAL CARTESIAN PRODUCT */ UNION ALL SELECT /* NET_MARGIN -- ROWS ONLY
WHERE NON-ZERO REVENUE OR COST */ M.ITEM_ORG_FK_KEY
, M.OPERATING_UNIT_FK_KEY
, M.SALES_CHANNEL_FK_KEY
, M.SHIP_TO_LOC_FK_KEY
, M.MARGIN_PERIOD_FK_KEY
, M.PROD_AMT_G
, T.CNAM_CAL_NAME_PK_KEY
, T.ALL_ALL_PK_KEY
, T.CPER_CAL_PERIOD_PK_KEY
, T.CQTR_CAL_QTR_PK_KEY
, T.CYR_CAL_YEAR_PK_KEY
, T.CPER_END_DATE
FROM OPI_EDW_PERD_MARGIN_F M
, EDW_TIME_M T
WHERE T.CDAY_CAL_DAY_PK_KEY = M.MARGIN_PERIOD_FK_KEY ) /*
FROM ZERO_MARGIN_PLUS_NET_MARGIN */ GROUP BY ITEM_ORG_FK_KEY
, OPERATING_UNIT_FK_KEY
, SALES_CHANNEL_FK_KEY
, SHIP_TO_LOC_FK_KEY
, MARGIN_PERIOD_FK_KEY
, CNAM_CAL_NAME_PK_KEY
, ALL_ALL_PK_KEY
, CPER_CAL_PERIOD_PK_KEY
, CQTR_CAL_QTR_PK_KEY
, CYR_CAL_YEAR_PK_KEY
, CPER_END_DATE ) MARGIN
, EDW_ORGANIZATION_M ORG
, EDW_OE_SLCHNL_M SCH
, EDW_ITEMS_M IT
, EDW_GEOGRAPHY_M GEO
WHERE MARGIN.OPERATING_UNIT_FK_KEY = ORG.ORGA_ORGANIZATION_PK_KEY
AND MARGIN.SALES_CHANNEL_FK_KEY = SCH.SCHN_SLSCHNL_PK_KEY
AND MARGIN.ITEM_ORG_FK_KEY = IT.IREV_ITEM_REVISION_PK_KEY
AND MARGIN.SHIP_TO_LOC_FK_KEY = GEO.GLOC_LOCATION_PK_KEY )
WHERE SALES_REV <> 0 OR PREV_YR_SALES <> 0
|
|
|
|