HWB_AMTHLD_VW

(SQL View)
 Index Back

Order Amounts by Hold Code


SELECT H.BUSINESS_UNIT , H.HOLD_CD , S.CURRENCY_CD_BASE , COUNT(DISTINCT H.ORDER_NO) , %Round(SUM(%DecMult(S.NET_UNIT_PRICE_BSE, S.QTY_SCHEDULED)), 4) FROM PS_ORD_HOLD H , PS_ORD_HEADER O , PS_ORD_SCHEDULE S WHERE H.BUSINESS_UNIT = O.BUSINESS_UNIT AND H.ORDER_NO = O.ORDER_NO AND H.BUSINESS_UNIT = S.BUSINESS_UNIT AND H.ORDER_NO = S.ORDER_NO AND ((H.ORDER_NO = S.ORDER_NO AND H.ORDER_INT_LINE_NO = 0 AND H.SCHED_LINE_NBR = 0) OR (H.ORDER_INT_LINE_NO <> 0 AND H.ORDER_INT_LINE_NO = S.ORDER_INT_LINE_NO AND H.SCHED_LINE_NBR = 0) OR (H.ORDER_INT_LINE_NO <> 0 AND H.SCHED_LINE_NBR <> 0 AND H.ORDER_INT_LINE_NO = S.ORDER_INT_LINE_NO AND H.SCHED_LINE_NBR = S.SCHED_LINE_NBR)) AND H.EFF_STATUS = 'A' AND H.HOLD_LEVEL <> 'W' AND O.ORDER_TYPE_CD = 'SO' AND S.ORD_SCH_STATUS IN ('O','P') GROUP BY H.BUSINESS_UNIT , H.HOLD_CD, S.CURRENCY_CD_BASE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 HOLD_CD Character(6) VARCHAR2(6) NOT NULL Hold Code
3 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
4 ORDER_HOLD_COUNT Number(10,0) DECIMAL(10) NOT NULL Order Count
5 HOLD_AMT Number(32,4) DECIMAL(31,4) NOT NULL Hold Amount