|
SELECT h.business_unit, h.cust_id, SUM(HIST_AMT), COUNT(*), H.CURRENCY_CD
FROM PS_CUST_HISTORY H, PS_CAL_CUR_PER_VW C
WHERE C.SETID =
(SELECT SETID FROM PS_SET_CNTRL_REC
WHERE SETCNTRLVALUE = H.BUSINESS_UNIT AND RECNAME = 'CAL_DEFN_TBL')
AND C.CALENDAR_ID =
(SELECT CALENDAR_ID FROM PS_BUS_UNIT_OPT_AR O
WHERE O.SETID =
(SELECT SETID FROM PS_SET_CNTRL_REC
WHERE SETCNTRLVALUE = H.BUSINESS_UNIT AND RECNAME = 'BUS_UNIT_OPT_AR'))
AND H.CUST_HIST_ID = 'SALES'
AND (H.FISCAL_YEAR * 1000 + H.ACCOUNTING_PERIOD BETWEEN C.FISCAL_YEAR * 1000 + C.ACCOUNTING_PERIOD - 3 AND C.FISCAL_YEAR * 1000 + C.ACCOUNTING_PERIOD -1
OR (C.ACCOUNTING_PERIOD - 3 < 1 AND H.FISCAL_YEAR * 1000 + H.ACCOUNTING_PERIOD BETWEEN (C.FISCAL_YEAR - 1) * 1000 + NUMBER_OF_PERIODS - 3 - C.ACCOUNTING_PERIOD
AND (C.FISCAL_YEAR - 1) * 1000 + NUMBER_OF_PERIODS))
group by H.business_unit, H.cust_id, H.CURRENCY_CD
|
BUSINESS_UNIT