ECC Cost Management, Margin Summary

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: cst-mrg-smry
Query Procedure: CST_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: CST_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
select
x.*
from
(
SELECT * FROM (
 SELECT cms.simulation_code
||'-'||cms.historical_or_expected
||'-'||cms.simulation_or_Actual
||'-'||cms.org_type
||'-'||NVL(cms.CUSTOMER_ID,-1)
||'-'||NVL(cms.SHIP_TO_SITE_USE_ID, -1)
||'-'||NVL(cms.INVOICE_TO_SITE_USE_ID, -1)
||'-'||cms.day_id
||'-'||cms.cal_day_id
||'-'||cms.PRIMARY_SALESREP_ID
||'-'||cms.sales_channel_code
||'-'||cms.parent_organization_id
||'-'||cms.parent_inventory_item_id ECC_SPEC_ID,
 cms.simulation_code,
 cms.historical_or_expected,
 cms.simulation_or_Actual,
 cms.org_type,
 cms.LEGAL_ENTITY_ID                           ,
 cms.BUILD_ID                       ,
cms.ORG_ID                OPERATING_UNIT_ID                     ,
cms.CUSTOMER_ID                                   ,
cms.PRIMARY_SALESREP_ID                            ,
nvl(schnllkup.meaning, 'UNSPECIFIED')  SALES_CHANNEL_CODE     ,
cms.PARENT_INVENTORY_ITEM_ID                       ,
cms.PARENT_ORGANIZATION_ID                         ,
cms.SHIP_TO_SITE_USE_ID                            ,
cms.INVOICE_TO_SITE_USE_ID                         ,
cms.GL_DATE                                          ,
cms.INVOICE_LINE_QUANTITY            ,
decode(cms.INVOICE_QUANTITY, 0, 0.00001, cms.INVOICE_QUANTITY) INVOICE_QUANTITY ,
cms.ORDER_LINE_QUANTITY                      ,
decode(cms.SHIP_QUANTITY, 0, 0.00001, cms.SHIP_QUANTITY) SHIP_QUANTITY ,
decode(cms.INVOICED_AMOUNT , 0, 0.00001, cms.INVOICED_AMOUNT)      INVOICED_AMOUNT ,
cms.COGS_AMOUNT                            ,
cms.TERRITORY_ID                                   ,
cms.CUSTOMER_CLASS_CODE                  ,
cms.MARGIN,
(-1*COGS_AMOUNT) NEG_COGS_AMOUNT,
(-1*INVOICED_AMOUNT) NEG_INVOICED_AMOUNT,
(-1*MARGIN) NEG_MARGIN,
ftd.FISCAL_YEAR_NAME,
ftd.FISCAL_YEAR_START_DATE,
ftd.FISCAL_QUARTER_NAME,
ftd.FISCAL_QUARTER_START_DATE,
ftd.FISCAL_PERIOD_NAME,
ftd.FISCAL_PERIOD_NUM,
trunc(ftd.FISCAL_PERIOD_START_DATE) FISCAL_PERIOD_START_DATE,
ftd.FISCAL_PERIOD_END_DATE,
ftd.FISCAL_QUARTER_NUM,
ftd.FISCAL_PERIOD_NUM_NAME,
ftd.DAY_NAME,
cms.CAL_DATE ,
cms.DELETE_FLAG,
cms.ECC_LAST_UPDATE_DATE,
btcd.PARTY_NAME bill_to_customer_name,
nvl(btcindlkup.MEANING, 'UNSPECIFIED') bill_to_customer_class,
nvl(btcd.NAME , 'Unassigned Territory') BTC_TERRITORY,
nvl(btcd.LEVEL_1_TERRITORY, 'UNASSIGNED' ) BTC_LEVEL_1_TERRITORY,
nvl(btcd.LEVEL_2_TERRITORY, '-NA-') BTC_LEVEL_2_TERRITORY,
nvl(btcd.LEVEL_3_TERRITORY, '-NA-') BTC_LEVEL_3_TERRITORY,
nvl(btcd.LEVEL_4_TERRITORY, '-NA-') BTC_LEVEL_4_TERRITORY,
nvl(btcd.LEVEL_5_TERRITORY, '-NA-') BTC_LEVEL_5_TERRITORY,
nvl(btcd.LEVEL_6_TERRITORY, '-NA-') BTC_LEVEL_6_TERRITORY,
nvl(btcd.LEVEL_7_TERRITORY, '-NA-') BTC_LEVEL_7_TERRITORY,
nvl(btcd.LEVEL_8_TERRITORY, '-NA-') BTC_LEVEL_8_TERRITORY,
nvl(btcd.LEVEL_9_TERRITORY, '-NA-') BTC_LEVEL_9_TERRITORY,
nvl(btcd.LEVEL_10_TERRITORY, '-NA-') BTC_LEVEL_10_TERRITORY,
nvl(btcd.LEVEL_11_TERRITORY, '-NA-') BTC_LEVEL_11_TERRITORY,
nvl(btcd.LEVEL_12_TERRITORY, '-NA-') BTC_LEVEL_12_TERRITORY,
nvl(btcd.LEVEL_13_TERRITORY, '-NA-') BTC_LEVEL_13_TERRITORY,
nvl(btcd.LEVEL_14_TERRITORY, '-NA-') BTC_LEVEL_14_TERRITORY,
nvl(btcd.LEVEL_15_TERRITORY, '-NA-') BTC_LEVEL_15_TERRITORY,
nvl(btcd.LEVEL_16_TERRITORY, '-NA-') BTC_LEVEL_16_TERRITORY,
nvl(btcd.LEVEL_17_TERRITORY, '-NA-') BTC_LEVEL_17_TERRITORY,
nvl(btcd.LEVEL_18_TERRITORY, '-NA-') BTC_LEVEL_18_TERRITORY,
nvl(btcd.LEVEL_19_TERRITORY, '-NA-') BTC_LEVEL_19_TERRITORY,
nvl(btcd.LEVEL_20_TERRITORY, '-NA-') BTC_LEVEL_20_TERRITORY,
shtcd.PARTY_NAME ship_to_customer_name,
nvl(shtcindlkup.MEANING, 'UNSPECIFIED') ship_to_customer_class,
nvl(shtcd.NAME , 'Unassigned Territory') SHTC_TERRITORY,
nvl(shtcd.LEVEL_1_TERRITORY, 'UNASSIGNED') SHTC_LEVEL_1_TERRITORY,
nvl(shtcd.LEVEL_2_TERRITORY, '-NA-') SHTC_LEVEL_2_TERRITORY,
nvl(shtcd.LEVEL_3_TERRITORY, '-NA-') SHTC_LEVEL_3_TERRITORY,
nvl(shtcd.LEVEL_4_TERRITORY, '-NA-') SHTC_LEVEL_4_TERRITORY,
nvl(shtcd.LEVEL_5_TERRITORY, '-NA-') SHTC_LEVEL_5_TERRITORY,
nvl(shtcd.LEVEL_6_TERRITORY, '-NA-') SHTC_LEVEL_6_TERRITORY,
nvl(shtcd.LEVEL_7_TERRITORY, '-NA-') SHTC_LEVEL_7_TERRITORY,
nvl(shtcd.LEVEL_8_TERRITORY, '-NA-') SHTC_LEVEL_8_TERRITORY,
nvl(shtcd.LEVEL_9_TERRITORY, '-NA-') SHTC_LEVEL_9_TERRITORY,
nvl(shtcd.LEVEL_10_TERRITORY, '-NA-') SHTC_LEVEL_10_TERRITORY,
nvl(shtcd.LEVEL_11_TERRITORY, '-NA-') SHTC_LEVEL_11_TERRITORY,
nvl(shtcd.LEVEL_12_TERRITORY, '-NA-') SHTC_LEVEL_12_TERRITORY,
nvl(shtcd.LEVEL_13_TERRITORY, '-NA-') SHTC_LEVEL_13_TERRITORY,
nvl(shtcd.LEVEL_14_TERRITORY, '-NA-') SHTC_LEVEL_14_TERRITORY,
nvl(shtcd.LEVEL_15_TERRITORY, '-NA-') SHTC_LEVEL_15_TERRITORY,
nvl(shtcd.LEVEL_16_TERRITORY, '-NA-') SHTC_LEVEL_16_TERRITORY,
nvl(shtcd.LEVEL_17_TERRITORY, '-NA-') SHTC_LEVEL_17_TERRITORY,
nvl(shtcd.LEVEL_18_TERRITORY, '-NA-') SHTC_LEVEL_18_TERRITORY,
nvl(shtcd.LEVEL_19_TERRITORY, '-NA-') SHTC_LEVEL_19_TERRITORY,
nvl(shtcd.LEVEL_20_TERRITORY, '-NA-') SHTC_LEVEL_20_TERRITORY,
soldcd.PARTY_NAME sold_to_customer_name,
nvl(indlkup.MEANING, 'UNSPECIFIED') sold_to_customer_class,
nvl(slsrep.NAME , 'UNSPECIFIED') SALES_REP_NAME,
msi.concatenated_segments PRODUCT,
msi.primary_uom_code PRIMARY_UOM_CODE,
msi_tl.description PRODUCT_DESCRIPTION,
nvl(proditmd.CATEGORY_CONCAT_SEG, 'UNASSIGNED')   PROD_CATEGORY,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL1, 'UNASSIGNED') PROD_CAT_SEG_LEVEL1,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL2, '-NA-') PROD_CAT_SEG_LEVEL2,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL3, '-NA-') PROD_CAT_SEG_LEVEL3,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL4, '-NA-') PROD_CAT_SEG_LEVEL4,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL5, '-NA-') PROD_CAT_SEG_LEVEL5,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL6, '-NA-') PROD_CAT_SEG_LEVEL6,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL7, '-NA-') PROD_CAT_SEG_LEVEL7,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL8, '-NA-') PROD_CAT_SEG_LEVEL8,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL9, '-NA-') PROD_CAT_SEG_LEVEL9,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL10, '-NA-') PROD_CAT_SEG_LEVEL10,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL11, '-NA-') PROD_CAT_SEG_LEVEL11,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL12, '-NA-') PROD_CAT_SEG_LEVEL12,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL13, '-NA-') PROD_CAT_SEG_LEVEL13,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL14, '-NA-') PROD_CAT_SEG_LEVEL14,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL15, '-NA-') PROD_CAT_SEG_LEVEL15,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL16, '-NA-') PROD_CAT_SEG_LEVEL16,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL17, '-NA-') PROD_CAT_SEG_LEVEL17,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL18, '-NA-') PROD_CAT_SEG_LEVEL18,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL19, '-NA-') PROD_CAT_SEG_LEVEL19,
nvl(proditmd.CATEGORY_SEGMENT_LEVEL20, '-NA-') PROD_CAT_SEG_LEVEL20,
orgd.CURRENCY_CODE,
orgd.SOB_NAME LEDGER_NAME,
orgd.LEDGER_ID,
orgd.LEGAL_ENTITY ,
orgd.OPERATING_UNIT,
orgd.ORGANIZATION_NAME,
orgd.ORGANIZATION_CODE,
orgd.LANGUAGE ,
orgtyplkup.MEANING org_type_desc
FROM CST_ECC_MRG_SMRY_STG cms,
CST_ECC_CUSTOMER_DIM btcd,
CST_ECC_CUSTOMER_DIM shtcd,
(SELECT hca.cust_account_id SOLD_TO_CUSTOMER_ID,
nvl(hca.CUSTOMER_CLASS_CODE,'UNSPECIFIED' ) CUSTOMER_CLASS_CODE,
hp.PARTY_NAME PARTY_NAME,
hp.last_update_date HP_LAST_UPDATE_DATE,
hca.last_update_date HCA_LAST_UPDATE_DATE
FROM  hz_cust_accounts hca,
hz_parties hp
WHERE  hp.party_id = hca.party_id
union all
select -1 SOLD_TO_CUSTOMER_ID,
'UNSPECIFIED'	CUSTOMER_CLASS_CODE,
'UNSPECIFIED'	PARTY_NAME,
sysdate HP_LAST_UPDATE_DATE,
sysdate HCA_LAST_UPDATE_DATE
from dual) soldcd,
(SELECT
to_number(to_char(rs.SALESREP_ID)||to_char(abs(rs.ORG_ID))) SALESREP_ID,
res.resource_name name,
rs.SALESREP_NUMBER,
rs.EMAIL_ADDRESS,
rs.LAST_UPDATE_DATE
from jtf_RS_SALESREPS rs,
JTF_RS_RESOURCE_EXTNS_VL res,
hr_organization_units hou
WHERE  hou.organization_id = rs.org_id
AND  rs.resource_id = res.resource_id
) slsrep,
CST_ECC_PRODUCT_DIM proditmd,
CST_ECC_TIME_DIM ftd,
CST_ECC_ORG_STRUCT_TL_GT orgd,
MTL_SYSTEM_ITEMS_KFV msi,
MTL_SYSTEM_ITEMS_TL msi_tl,
FND_LOOKUP_VALUES schnllkup,
FND_LOOKUP_VALUES indlkup,
FND_LOOKUP_VALUES btcindlkup,
FND_LOOKUP_VALUES shtcindlkup,
FND_LOOKUP_VALUES orgtyplkup,
CST_ECC_MRG_PRF_SNPSHT prfsn
WHERE
cms.DAY_ID = ftd.DAY_ID
AND cms.GL_DATE = ftd.DAY_NAME
AND cms.parent_organization_id = orgd.organization_id
AND cms.SHIP_TO_SITE_USE_ID = shtcd.SITE_USE_ID
AND cms.INVOICE_TO_SITE_USE_ID = btcd.SITE_USE_ID
AND cms.CUSTOMER_ID = soldcd.SOLD_TO_CUSTOMER_ID
AND cms.PARENT_INVENTORY_ITEM_ID  = msi.INVENTORY_ITEM_ID
AND cms.PARENT_ORGANIZATION_ID  = msi.ORGANIZATION_ID
AND msi_tl.INVENTORY_ITEM_ID  = msi.INVENTORY_ITEM_ID
AND msi_tl.ORGANIZATION_ID  = msi.ORGANIZATION_ID
AND msi_tl.language = orgd.language
AND cms.PARENT_INVENTORY_ITEM_ID  = proditmd.INVENTORY_ITEM_ID (+)
AND cms.PARENT_ORGANIZATION_ID  = proditmd.ORGANIZATION_ID (+)
AND cms.PRIMARY_SALESREP_ID = slsrep.SALESREP_ID (+)
AND decode(soldcd.customer_class_code, 'UNSPECIFIED', 'UNSPECIFIED', soldcd.customer_class_code) = indlkup.lookup_code (+)
AND orgd.language = nvl(indlkup.language, orgd.language)
AND indlkup.lookup_type (+) = 'CUSTOMER CLASS'
AND indlkup.view_application_id (+) = 222
AND indlkup.security_group_id (+) = 0
AND decode(btcd.CUSTOMER_CLASS_CODE, 'UNSPECIFIED', 'UNSPECIFIED', btcd.CUSTOMER_CLASS_CODE) = btcindlkup.lookup_code (+)
AND orgd.language = nvl(btcindlkup.language, orgd.language)
AND btcindlkup.lookup_type (+) = 'CUSTOMER CLASS'
AND btcindlkup.view_application_id (+) = 222
AND btcindlkup.security_group_id (+) = 0
AND decode(shtcd.CUSTOMER_CLASS_CODE, NULL, 'UNSPECIFIED', shtcd.CUSTOMER_CLASS_CODE) = shtcindlkup.lookup_code (+)
AND orgd.language = nvl(shtcindlkup.language, orgd.language)
AND shtcindlkup.lookup_type (+) = 'CUSTOMER CLASS'
AND shtcindlkup.view_application_id (+) = 222
AND shtcindlkup.security_group_id (+) = 0
AND decode(cms.sales_channel_code, NULL, 'UNSPECIFIED', cms.sales_channel_code) = schnllkup.lookup_code (+)
AND orgd.language = nvl(schnllkup.language, orgd.language)
AND schnllkup.lookup_type (+) = 'SALES_CHANNEL'
AND schnllkup.view_application_id (+) = 660
AND schnllkup.security_group_id (+) = 0
AND orgtyplkup.lookup_type = 'CST_ECC_ORG_TYPE'
AND orgtyplkup.lookup_code = DECODE(cms.org_type, 'P', 'PROCESS', 'DISCRETE')
AND orgd.language = orgtyplkup.language
AND orgtyplkup.view_application_id = 0
AND orgtyplkup.security_group_id = 0
AND
orgd.language in ('US') ) PIVOT (
                                              max(PRODUCT_DESCRIPTION) as PRODUCT_DESCRIPTION,
                                              max(ORGANIZATION_NAME) as ORGANIZATION_NAME ,
					                          max(OPERATING_UNIT) as OPERATING_UNIT ,
						                      max(bill_to_customer_class) as bill_to_customer_class,
						                      max(ship_to_customer_class) as ship_to_customer_class,
					                          max(sold_to_customer_class) as sold_to_customer_class,
					                          max(SALES_CHANNEL_CODE) as SALES_CHANNEL_CODE	,
								  max(org_type_desc) as ORG_TYPE_DESC
                                              for LANGUAGE in ('US' "US"))
) x
where
2=2
Parameter NameSQL textValidation
Organization Code
x.organization_code=:organization_code
LOV
Download
Blitz Report™