SQL Split Opportunity Revenue by Calendaristic Months
SELECT OPPORTUNITY_REVENUE.OPTY_ID,
OPPORTUNITY_REVENUE.OPTY_NUMBER,
OPPORTUNITY_REVENUE.NAME,
OPPORTUNITY_REVENUE.LOCATIONS,
OPPORTUNITY_REVENUE.REVENUE_DRIVER,
OPPORTUNITY_REVENUE.CETA,
(
CASE
WHEN OPPORTUNITY_REVENUE.ACCOUNT_PARTY_ID IS NULL THEN 'NO ACCOUNT'
ELSE (
SELECT HZ_PARTIES.PARTY_NAME
FROM FUSION.HZ_PARTIES
WHERE HZ_PARTIES.PARTY_ID = OPPORTUNITY_REVENUE.ACCOUNT_PARTY_ID
)
END
) AS ACCOUNT_NAME,
(
CASE
WHEN OPPORTUNITY_REVENUE.CONTACT_PARTY_ID IS NULL THEN 'NO CONTACT'
ELSE (
SELECT HZ_PARTIES.PARTY_NAME
FROM FUSION.HZ_PARTIES
WHERE HZ_PARTIES.PARTY_ID = OPPORTUNITY_REVENUE.CONTACT_PARTY_ID
)
END
) AS CONTACT_NAME,
OPPORTUNITY_REVENUE.OPTY_STATUS_CD,
OPPORTUNITY_REVENUE.OPTY_REASON_WON_LOST_CD,
OPPORTUNITY_REVENUE.REVN_REVN_ID,
OPPORTUNITY_REVENUE.REVN_NUMBER,
OPPORTUNITY_REVENUE.REVN_EFFECTIVE_DATE,
OPPORTUNITY_REVENUE.REVN_CREATION_DATE,
OPPORTUNITY_REVENUE.REVN_ACTUAL_CLOSE_DATE,
OPPORTUNITY_REVENUE.REVN_UNIT_PRICE,
OPPORTUNITY_REVENUE.REVN_QTY,
OPTY_TOTAL_REV.OPPORTUNITY_TOTAL,
OPPORTUNITY_REVENUE.YEAR,
OPPORTUNITY_REVENUE.JANUARY,
OPPORTUNITY_REVENUE.FEBRUARY,
OPPORTUNITY_REVENUE.MARCH,
OPPORTUNITY_REVENUE.APRIL,
OPPORTUNITY_REVENUE.MAY,
OPPORTUNITY_REVENUE.JUNE,
OPPORTUNITY_REVENUE.JULY,
OPPORTUNITY_REVENUE.AUGUST,
OPPORTUNITY_REVENUE.SEPTEMBER,
OPPORTUNITY_REVENUE.OCTOBER,
OPPORTUNITY_REVENUE.NOVEMBER,
OPPORTUNITY_REVENUE.DECEMBER,
(
CASE
WHEN OPPORTUNITY_REVENUE.REVN_INVENTORY_ITEM_ID IS NULL THEN 'NO PRODUCT'
ELSE (
SELECT DESCRIPTION
FROM FUSION.QSC_PRODUCTS_TL
WHERE INVENTORY_ITEM_ID = OPPORTUNITY_REVENUE.REVN_INVENTORY_ITEM_ID
AND LANGUAGE = 'US'
AND rownum = 1
)
END
) AS REVN_PRODUCT,
OPPORTUNITY_REVENUE.REVN_INVENTORY_ORG_ID,
(
CASE
WHEN OPPORTUNITY_REVENUE.REVN_PROD_GROUP_ID IS NULL THEN 'NO PRODUCT GROUP'
ELSE (
SELECT PRODUCT_NAME
FROM FUSION.ZSP_PRODUCT_GROUPS
WHERE PROD_GROUP_ID = OPPORTUNITY_REVENUE.REVN_PROD_GROUP_ID
)
END
) AS REVN_PROD_GROUP,
OPPORTUNITY_REVENUE.REVN_WIN_PROB,
OPPORTUNITY_REVENUE.REVN_STATUS_CODE,
OPPORTUNITY_REVENUE.REVN_PRIMARY_FLAG
FROM (
select opty.OPTY_ID as OPTY_ID,
opty.OPTY_NUMBER AS OPTY_NUMBER,
opty.NAME AS NAME,
opty.CUST_PARTY_ID as ACCOUNT_PARTY_ID,
opty.PR_CON_PARTY_ID AS CONTACT_PARTY_ID,
opty.STATUS_CD AS OPTY_STATUS_CD,
opty.REASON_WON_LOST_CD AS OPTY_REASON_WON_LOST_CD,
opty.EXTN_ATTRIBUTE_CHAR073 AS LOCATIONS,
opty.EXTN_ATTRIBUTE_CHAR075 AS REVENUE_DRIVER,
opty.EXTN_ATTRIBUTE_CHAR074 AS CETA,
REVENUE_TABLE.REVN_ID AS REVN_REVN_ID,
REVENUE_TABLE.REVN_NUMBER AS REVN_NUMBER,
REVENUE_TABLE.EFFECTIVE_DATE AS REVN_EFFECTIVE_DATE,
REVENUE_TABLE.CREATION_DATE AS REVN_CREATION_DATE,
REVENUE_TABLE.ACTUAL_CLOSE_DATE AS REVN_ACTUAL_CLOSE_DATE,
REVENUE_TABLE.UNIT_PRICE AS REVN_UNIT_PRICE,
REVENUE_TABLE.QTY AS REVN_QTY,
REVENUE_TABLE.YEAR,
REVENUE_TABLE.JANUARY,
REVENUE_TABLE.FEBRUARY,
REVENUE_TABLE.MARCH,
REVENUE_TABLE.APRIL,
REVENUE_TABLE.MAY,
REVENUE_TABLE.JUNE,
REVENUE_TABLE.JULY,
REVENUE_TABLE.AUGUST,
REVENUE_TABLE.SEPTEMBER,
REVENUE_TABLE.OCTOBER,
REVENUE_TABLE.NOVEMBER,
REVENUE_TABLE.DECEMBER,
REVENUE_TABLE.INVENTORY_ITEM_ID AS REVN_INVENTORY_ITEM_ID,
REVENUE_TABLE.INVENTORY_ORG_ID AS REVN_INVENTORY_ORG_ID,
REVENUE_TABLE.PROD_GROUP_ID AS REVN_PROD_GROUP_ID,
REVENUE_TABLE.WIN_PROB AS REVN_WIN_PROB,
REVENUE_TABLE.STATUS_CODE AS REVN_STATUS_CODE,
REVENUE_TABLE.PRIMARY_FLAG AS REVN_PRIMARY_FLAG
from FUSION.MOO_OPTY opty
INNER JOIN (
SELECT rev.REVN_ID,
rev.REVN_NUMBER,
rev.OPTY_ID,
rev.CUST_PARTY_ID,
rev.OWNER_RESOURCE_ID,
rev.EFFECTIVE_DATE,
rev.CREATION_DATE,
rev.ACTUAL_CLOSE_DATE,
rev.UNIT_PRICE,
rev.QTY,
rev.EXPECT_AMT,
EXTRACT(
YEAR
FROM rev.CREATION_DATE
) AS YEAR,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '1'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '1'
)
END
) as JANUARY,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '2'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '2'
)
END
) as FEBRUARY,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '3'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '3'
)
END
) as MARCH,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '4'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '4'
)
END
) as APRIL,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '5'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '5'
)
END
) as MAY,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '6'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '6'
)
END
) as JUNE,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '7'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '7'
)
END
) as JULY,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '8'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '8'
)
END
) as AUGUST,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '9'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '9'
)
END
) as SEPTEMBER,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '10'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '10'
)
END
) as OCTOBER,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '11'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '11'
)
END
) as NOVEMBER,
(
CASE
WHEN (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '12'
) IS NULL THEN 0
ELSE (
SELECT EXPECT_AMT
FROM FUSION.MOO_REVN
WHERE REVN_ID = rev.REVN_ID
AND EXTRACT(
MONTH
FROM CREATION_DATE
) = '12'
)
END
) as DECEMBER,
rev.INVENTORY_ITEM_ID,
rev.INVENTORY_ORG_ID,
rev.PROD_GROUP_ID,
rev.WIN_PROB,
rev.STATUS_CODE,
rev.PRIMARY_FLAG
FROM FUSION.MOO_REVN rev
WHERE rev.PRIMARY_FLAG <> 'Y'
AND EXTRACT(
YEAR
FROM rev.CREATION_DATE
) IN ('2018')
) REVENUE_TABLE ON (opty.OPTY_ID = REVENUE_TABLE.OPTY_ID)
) OPPORTUNITY_REVENUE
INNER JOIN (
SELECT rev.OPTY_ID AS OPTY_ID,
SUM(rev.EXPECT_AMT) as OPPORTUNITY_TOTAL
FROM FUSION.MOO_REVN rev
WHERE rev.PRIMARY_FLAG <> 'Y'
GROUP BY rev.OPTY_ID
) OPTY_TOTAL_REV ON (
OPPORTUNITY_REVENUE.OPTY_ID = OPTY_TOTAL_REV.OPTY_ID
)
WHERE OPPORTUNITY_REVENUE.LOCATIONS IS NOT NULL
ORDER BY OPPORTUNITY_REVENUE.OPTY_ID