Adding User History IDs to SetID and Business Unit History
If you add new user-defined history IDs, they do not automatically appear on history inquiry pages. You need to perform three tasks for them to appear.
This topic discusses how to:
Modify view text for history IDs.
Modify views for the Customer History page.
Modify views for the Unit History page.
To modify the view text:
Open the record that you want to modify in Application Designer.
In the SQL object window that appears, select the row for the CUST_HIST_ID field.
Right-click and select View PeopleCode.
Modify the PeopleCode.
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
New history IDs do not appear on the Unit History page until you modify these views:
BU_SUM_DVW
BU_SUM_LP_DVW
For example, to add a new history ID called WIDGET_SALES, change the view text on each of the views as shown here.
BU_SUM_DVW
Change the view text to:
SELECT BUSINESS_UNIT,
CUST_HIST_ID,
FISCAL_YEAR,
ACCOUNTING_PERIOD,
SUM(HIST_AMT),
CURRENCY_CD
FROM PS_CUST_HISTORY
WHERE CUST_HIST_ID IN ('SALES', 'WIDGET_SALES')
GROUP BY BUSINESS_UNIT, CUST_HIST_ID, FISCAL_YEAR, ACCOUNTING_PERIOD, CURRENCY_CD
BU_SUM_LP_DVW
Change the view text to:
SELECT H.BUSINESS_UNIT,
H.CUST_HIST_ID,
H.FISCAL_YEAR,
H.ACCOUNTING_PERIOD,
SUM(H.HIST_AMT),
H.CURRENCY_CD
FROM PS_CUST_HISTORY 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_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_HIST_ID = H.CUST_HIST_ID
AND FISCAL_YEAR = H.FISCAL_YEAR)
GROUP BY H.BUSINESS_UNIT,
H.CUST_HIST_ID,
H.FISCAL_YEAR,
H.ACCOUNTING_PERIOD,
H.CURRENCY_CD