PROD_PRICE_EFF

(SQL View)
Index Back

Product Prices by IBU Eff

This record stores the list price by Unit of Measure for each Inventory Business Unit. It is a child of the PROD_PRICE_BU record. This is used for PRODUCT_FULLSYNC_EFF message.

SELECT SETID ,PRODUCT_ID ,UNIT_OF_MEASURE ,BUSINESS_UNIT_IN ,CURRENCY_CD ,EFFDT ,EFF_STATUS ,LIST_PRICE ,UNIT_COST ,SERVICE_COST ,REPAIR_COST ,MFG_SUG_RTL_PRC ,PRICING_FLAG ,DATETIME_ADDED ,LASTUPDDTTM ,LAST_MAINT_OPRID FROM PS_PROD_PRICE X WHERE X.EFFDT = ( SELECT MAX(Y.EFFDT) FROM PS_PROD_PRICE Y WHERE Y.SETID = X.SETID AND Y.PRODUCT_ID = X.PRODUCT_ID AND Y.UNIT_OF_MEASURE = X.UNIT_OF_MEASURE AND Y.BUSINESS_UNIT_IN = X.BUSINESS_UNIT_IN AND Y.CURRENCY_CD = X.CURRENCY_CD AND Y.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
2 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

Prompt Table: PROD_ITEM

3 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
CS=Case
EA=Each
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: PROD_UOM

4 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
6 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

7 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

8 LIST_PRICE Signed Number(17,4) DECIMAL(15,4) NOT NULL List Price
9 UNIT_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Unit Cost
10 SERVICE_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Service Cost
11 REPAIR_COST Signed Number(16,4) DECIMAL(14,4) NOT NULL Repair Cost
12 MFG_SUG_RTL_PRC Number(16,4) DECIMAL(15,4) NOT NULL Manufactures's Suggested Retai
13 PRICING_FLAG Character(1) VARCHAR2(1) NOT NULL Pricing Flag
C=Cost
L=List Price
14 DATETIME_ADDED DateTime(26) TIMESTAMP DateTime Added
15 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
16 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL This field determines the last user or operator that last changed this item.