OM_PWC_PRC_EFF

(SQL View)
 Index Back

SO Price Adjustments

SO Price Adjustments

SELECT DISTINCT A.BUSINESS_UNIT , A.SETID , A.EOEP_RULE_ID , A.PRICE_PROGRAM , A.QTY_ORDERED , %DecMult(L.PRICE_BASE, L.QTY_ORDERED) , L.PRODUCT_ID , ' ' , H.SOLD_TO_CUST_ID , ' ' , '1' , 'Current Period' FROM PS_ORD_PRICE_ADJST A , PS_ORD_HEADER H , PS_ORD_LINE L , PS_OM_PWC_PRI_CAL C WHERE (A.ORDER_FLAG NOT IN ('Y','L') OR (A.ORDER_INT_LINE_NO = 0 AND A.SCHED_LINE_NBR = 0 AND A.PRC_SCHED_NBR = 0) ) AND (A.EOEP_RULE_ID <> ' ' OR A.PRICE_PROGRAM <> ' ') AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.ORDER_NO = H.ORDER_NO AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.ORDER_NO = L.ORDER_NO AND A.ORDER_INT_LINE_NO = L.ORDER_INT_LINE_NO AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND %DatePart(A.LASTUPDDTTM) BETWEEN C.BEGIN_DT AND C.END_DT UNION SELECT DISTINCT A.BUSINESS_UNIT , A.SETID , A.EOEP_RULE_ID , A.PRICE_PROGRAM , A.QTY_ORDERED , %DecMult(L.PRICE_BASE, L.QTY_ORDERED) , L.PRODUCT_ID , ' ' , H.SOLD_TO_CUST_ID , ' ' , '2' , 'Last Period' FROM PS_ORD_PRICE_ADJST A , PS_ORD_HEADER H , PS_ORD_LINE L , PS_OM_PWC_PRI_CAL1 C WHERE (A.ORDER_FLAG NOT IN ('Y','L') OR (A.ORDER_INT_LINE_NO = 0 AND A.SCHED_LINE_NBR = 0 AND A.PRC_SCHED_NBR = 0) ) AND (A.EOEP_RULE_ID <> ' ' OR A.PRICE_PROGRAM <> ' ') AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.ORDER_NO = H.ORDER_NO AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.ORDER_NO = L.ORDER_NO AND A.ORDER_INT_LINE_NO = L.ORDER_INT_LINE_NO AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND %DatePart(A.LASTUPDDTTM) BETWEEN C.BEGIN_DT AND C.END_DT UNION SELECT DISTINCT A.BUSINESS_UNIT , A.SETID , A.EOEP_RULE_ID , A.PRICE_PROGRAM , A.QTY_ORDERED , %DecMult(L.PRICE_BASE, L.QTY_ORDERED) , L.PRODUCT_ID , ' ' , H.SOLD_TO_CUST_ID , ' ' , '3' , 'Period Prior Year' FROM PS_ORD_PRICE_ADJST A , PS_ORD_HEADER H , PS_ORD_LINE L , PS_OM_PWC_PRI_CAL2 C WHERE (A.ORDER_FLAG NOT IN ('Y','L') OR (A.ORDER_INT_LINE_NO = 0 AND A.SCHED_LINE_NBR = 0 AND A.PRC_SCHED_NBR = 0) ) AND (A.EOEP_RULE_ID <> ' ' OR A.PRICE_PROGRAM <> ' ') AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.ORDER_NO = H.ORDER_NO AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND A.ORDER_NO = L.ORDER_NO AND A.ORDER_INT_LINE_NO = L.ORDER_INT_LINE_NO AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND %DatePart(A.LASTUPDDTTM) BETWEEN C.BEGIN_DT AND C.END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
3 EOEP_RULE_ID Character(30) VARCHAR2(30) NOT NULL Price Rule ID
4 PRICE_PROGRAM Character(10) VARCHAR2(10) NOT NULL Date Initials Issue Description 111701 dms F-CJORGENS-11 Add EXCL_PRICE_LBL
5 QTY_ORDERED Signed Number(16,4) DECIMAL(14,4) NOT NULL Order Qty
6 OM_AMOUNT_BSE Signed Number(19,4) DECIMAL(17,4) NOT NULL Amount
7 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
8 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL Product Group
9 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
10 CUSTOMER_GROUP Character(10) VARCHAR2(10) NOT NULL Customer Group
11 OM_PWC_DATE_RANGE Character(1) VARCHAR2(1) NOT NULL Date Range
1=Current Period
2=Current vs Last Period
3=Current vs Period Prior Year
12 DATE_RANGE30 Character(30) VARCHAR2(30) NOT NULL Sched Ship Date Range