Modifying Views for the Customer History Page

If you create queries that use a business unit, the new history ID appears immediately on the Customer History page. If the business unit field is left blank, the new history ID does not appear until these views are modified:

• HIST_SI_SUM_DVW

• HIST_SI_LSM_DVW

• SUBC_SI_SUM_DVW

• SUBC_SI_LSM_DVW

For example, to add a new history ID called WIDGET_SALES, change the view text on each of the views as shown here.

HIST_SI_SUM_DVW

Change the view text to:

SELECT SETID,
    CUST_ID,
    CUST_HIST_ID,
    FISCAL_YEAR,
    ACCOUNTING_PERIOD,
    SUM(HIST_AMT),
    SUM(HIST_WEIGHT),
    SUM(HIST_COUNT),
    CURRENCY_CD
FROM PS_CUST_HIST_SI_VW
WHERE CUST_HIST_ID IN ('SALES', 'WIDGET_SALES')
GROUP BY SETID, CUST_ID, CUST_HIST_ID, FISCAL_YEAR, ACCOUNTING_PERIOD, CURRENCY_CD

HIST_SI_LSM_DVW

Change the view text to:

SELECT H.SETID,
    H.CUST_ID,
    H.CUST_HIST_ID,
    H.FISCAL_YEAR,
    H.ACCOUNTING_PERIOD,
    SUM(H.HIST_AMT),
    SUM(H.HIST_WEIGHT),
    SUM(H.HIST_COUNT),
    CURRENCY_CD
FROM PS_CUST_HIST_SI_VW H
WHERE H. CUST_HIST_ID IN ('SALES', 'WIDGET_SALES')
 AND H.FISCAL_YEAR = 
     (SELECT MAX(FISCAL_YEAR)
       FROM PS_CUST_HISTORY
      WHERE BUSINESS_UNIT
             = H.BUSINESS_UNIT
      AND CUST_ID = H.CUST_ID
      AND CUST_HIST_ID = H.CUST_HIST_ID)
 AND H.ACCOUNTING_PERIOD =
     (SELECT MAX(ACCOUNTING_PERIOD)
       FROM PS_CUST_HISTORY
      WHERE BUSINESS_UNIT
              = H.BUSINESS_UNIT
      AND CUST_ID = H.CUST_ID
      AND CUST_HIST_ID = H.CUST_HIST_ID
      AND FISCAL_YEAR = H.FISCAL_YEAR)
GROUP BY H.SETID,
    H.CUST_ID,
    H.CUST_HIST_ID,
    H.FISCAL_YEAR,
    H.ACCOUNTING_PERIOD,
    H.CURRENCY_CD

SUBC_SI_SUM_DVW

Change the view text to:

SELECT SETID,
    CUST_ID,
    SUBCUST_QUAL1,
    SUBCUST_QUAL2,
    CUST_HIST_ID,
    FISCAL_YEAR,
    ACCOUNTING_PERIOD,
    SUM(HIST_AMT),
    SUM(HIST_WEIGHT),
    SUM(HIST_COUNT),
    CURRENCY_CD
FROM PS_SUBC_HIST_SI_VW
WHERE CUST_HIST_ID IN ('SALES', 'WIDGET_SALES')
GROUP BY SETID,
    CUST_ID,
    SUBCUST_QUAL1,
    SUBCUST_QUAL2,
    CUST_HIST_ID,
    FISCAL_YEAR,
    ACCOUNTING_PERIOD,
    CURRENCY_CD

SUBC_SI_LSM_DVW

Change the view text to:

SELECT H.SETID,
    H.CUST_ID,
    H.SUBCUST_QUAL1,
    H.SUBCUST_QUAL2,
    H.CUST_HIST_ID,
    H.FISCAL_YEAR,
    H.ACCOUNTING_PERIOD,
    SUM(H.HIST_AMT),
    SUM(H.HIST_WEIGHT),
    SUM(H.HIST_COUNT),
    H.CURRENCY_CD
FROM PS_SUBC_HIST_SI_VW H
WHERE H. CUST_HIST_ID IN ('SALES', 'WIDGET_SALES')
 AND H.FISCAL_YEAR = 
     (SELECT MAX(FISCAL_YEAR)
       FROM PS_CUST_HISTORY
      WHERE BUSINESS_UNIT
             = H.BUSINESS_UNIT
      AND CUST_ID = H.CUST_ID
      AND CUST_HIST_ID = H.CUST_HIST_ID)
 AND H.ACCOUNTING_PERIOD =
     (SELECT MAX(ACCOUNTING_PERIOD)
       FROM PS_CUST_HISTORY
      WHERE BUSINESS_UNIT
              = H.BUSINESS_UNIT
      AND CUST_ID = H.CUST_ID
      AND CUST_HIST_ID = H.CUST_HIST_ID
      AND FISCAL_YEAR = H.FISCAL_YEAR)
GROUP BY H.SETID,
    H.CUST_ID,
    H.SUBCUST_QUAL1,
    H.SUBCUST_QUAL2,
    H.CUST_HIST_ID, 
    H.FISCAL_YEAR, 
    H.ACCOUNTING_PERIOD,
    H.CURRENCY_CD