SRBT_ORDERI_VW2

(SQL View)
 Index Back

OMSRBT temp table

This is a temporary working table for SQR OMSRBT. It totals orders to be used for the rebate calculation.

SELECT H.BUSINESS_UNIT , H.SOLD_TO_CUST_ID , L.PRODUCT_ID , L.UNIT_OF_MEASURE , S.QTY_SCHEDULED , H.ORDER_DATE , S.REQ_ARRIVAL_DTTM , S.SCHED_SHIP_DTTM , H.CURRENCY_CD_BASE , H.RT_TYPE , %Round(%DecMult(S.NET_UNIT_PRICE_BSE, %DecMult(S.QTY_SCHEDULED, %DecDiv(100 + SUM(A.OM_ADJST_AMT), 100))), 3) FROM PS_ORD_HEADER H , PS_ORD_LINE L , PS_ORD_SCHEDULE S , PS_ORD_PRICE_ADJST A , PS_BI_LINE B WHERE H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.BUSINESS_UNIT = S.BUSINESS_UNIT AND H.BUSINESS_UNIT = A.BUSINESS_UNIT AND H.ORDER_TYPE_CD = 'SO' AND H.ORDER_NO = L.ORDER_NO AND H.ORDER_NO = S.ORDER_NO AND H.ORDER_NO = A.ORDER_NO AND L.ORDER_INT_LINE_NO = S.ORDER_INT_LINE_NO AND S.ORD_SCH_STATUS <> 'X' AND S.ORD_SCH_STATUS <> 'P' AND B.BUSINESS_UNIT_OM = H.BUSINESS_UNIT AND B.ORDER_NO = H.ORDER_NO AND B.LINE_TYPE = 'REV' AND A.ORDER_INT_LINE_NO = 0 AND A.OM_ADJST_FLG = '1' GROUP BY H.BUSINESS_UNIT , H.SOLD_TO_CUST_ID , L.PRODUCT_ID , L.UNIT_OF_MEASURE , S.NET_UNIT_PRICE_BSE, S.QTY_SCHEDULED , H.ORDER_DATE , S.REQ_ARRIVAL_DTTM , S.SCHED_SHIP_DTTM, H.CURRENCY_CD_BASE, H.RT_TYPE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
3 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
4 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
5 QTY_SCHEDULED Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity Scheduled
6 ORDER_DATE Date(10) DATE Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
7 REQ_ARRIVAL_DTTM DateTime(26) TIMESTAMP Requested Arrival
8 SCHED_SHIP_DTTM DateTime(26) TIMESTAMP Schedule Ship Datetime
9 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
10 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
11 NET_EXTENDED_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Extended Amount