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:

  1. Open the record that you want to modify in Application Designer.

  2. In the SQL object window that appears, select the row for the CUST_HIST_ID field.

  3. Right-click and select View PeopleCode.

  4. 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