CUSTV_HIST_P_VW

(SQL View)
 Index Back

Customer History Previous


SELECT R.SETID , H.BUSINESS_UNIT , H.CUST_ID , H.FISCAL_YEAR , H.CURRENCY_CD , SUM(CASE WHEN H.CUST_HIST_ID = 'SALES' THEN H.HIST_AMT ELSE 0 END) AS HI_SALES , SUM(CASE WHEN H.CUST_HIST_ID = 'HI_BAL_AMT' THEN H.HIST_AMT ELSE 0 END) AS HI_BAL_AMT , SUM(CASE WHEN H.CUST_HIST_ID = 'AVGDAYS' THEN H.HIST_AMT ELSE 0 END) AS HI_AVGDAYS , SUM(CASE WHEN H.CUST_HIST_ID = 'CURRENTDUE' THEN H.HIST_AMT ELSE 0 END) AS HI_CURRENTDUE , SUM(CASE WHEN H.CUST_HIST_ID = 'DSO30' THEN H.HIST_AMT ELSE 0 END) AS HI_DSO30 , SUM(CASE WHEN H.CUST_HIST_ID = 'DSO90' THEN H.HIST_AMT ELSE 0 END) AS HI_DSO90 , SUM(CASE WHEN H.CUST_HIST_ID = 'HI_FUTURE' THEN H.HIST_AMT ELSE 0 END) AS HI_FUTURE , SUM(CASE WHEN H.CUST_HIST_ID = 'HI_CURRENT' THEN H.HIST_AMT ELSE 0 END) AS HI_CURRENT , SUM(CASE WHEN H.CUST_HIST_ID = 'OTHERDUE' THEN H.HIST_AMT ELSE 0 END) AS HI_OTHERDUE , SUM(CASE WHEN H.CUST_HIST_ID = 'HI_PAST' THEN H.HIST_AMT ELSE 0 END) AS HI_PAST , SUM(CASE WHEN H.CUST_HIST_ID = 'HI_OTHER' THEN H.HIST_AMT ELSE 0 END) AS HI_OTHER , SUM(CASE WHEN H.CUST_HIST_ID = 'PASTDUE' THEN H.HIST_AMT ELSE 0 END) AS HI_PASTDUE , SUM(CASE WHEN H.CUST_HIST_ID = 'WTAVGDAYS' THEN H.HIST_AMT ELSE 0 END) AS HI_WTAVGDAYS , SUM(CASE WHEN H.CUST_HIST_ID = 'WTAVGPAID' THEN H.HIST_AMT ELSE 0 END) AS HI_WTAVGPAID , SUM(CASE WHEN H.CUST_HIST_ID = 'WTAVGTERMS' THEN H.HIST_AMT ELSE 0 END) AS HI_WTAVGTERMS FROM PS_CUST_HISTORY H , PS_SET_CNTRL_REC R , ps_cal_detp_tbl c , PS_BUS_UNIT_OPT_AR B , PS_SET_CNTRL_REC R1 , PS_SET_CNTRL_REC R2 WHERE R.SETCNTRLVALUE = H.BUSINESS_UNIT AND R.RECNAME = 'CUSTOMER' AND R1.SETCNTRLVALUE = H.BUSINESS_UNIT AND R1.RECNAME = 'BUS_UNIT_OPT_AR' AND B.SETID = R1.SETID AND R2.SETCNTRLVALUE = H.business_unit AND R2.RECNAME = 'CAL_DEFN_TBL' AND c.setid = R2.SETID AND c.calendar_id = B.calendar_id AND (c.fiscal_year-1) = H.FISCAL_YEAR AND %CurrentDateIn BETWEEN c.begin_dt AND c.end_dt GROUP BY R.SETID, H.BUSINESS_UNIT, H.CUST_ID, H.FISCAL_YEAR, H.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Prompt Table: SP_SETID_NONVW

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BUARDS_NONVW

3 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
4 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
6 HI_SALES Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Sales
7 HI_BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Balance
8 HI_AVGDAYS Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Average Days
9 HI_CURRENTDUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Current Due
10 HI_DS030 Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi DS030
11 HI_DS090 Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi DS090
12 HI_FUTURE Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Future
13 HI_CURRENT Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Current
14 HI_OTHERDUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Other Due
15 HI_PAST Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Past
16 HI_OTHER Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Other
17 HI_PASTDUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi Past Due
18 HI_WTAVGDAYS Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi WTA Avg Days
19 HI_WTAVGPAID Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi WTA Avg Paid
20 HI_WTAVGTERMS Signed Number(28,3) DECIMAL(26,3) NOT NULL Hi WTA Avg Terms