RSF_PROD_RCR_VW(SQL View) |
Index Back |
|---|---|
View of recurring productsView of products to provide recurring price, frequency and duration |
| SELECT A.SETID , A.PRODUCT_ID , A.UNIT_OF_MEASURE , A.CURRENCY_CD , A.RECUR_PRICE , A.PW_FREQUENCY , B.DURATION , B.DURATION_FREQ FROM PS_PROD_PRICE A , PS_PROD_ITEM B WHERE A.SETID = B.SETID AND A.PRODUCT_ID = B.PRODUCT_ID AND B.EFF_STATUS = 'A' AND A.RECUR_PRICE <> 0 AND B.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PROD_PRICE C WHERE A.SETID = C.SETID AND A.PRODUCT_ID = C.PRODUCT_ID AND A.UNIT_OF_MEASURE = C.UNIT_OF_MEASURE AND A.CURRENCY_CD = C.CURRENCY_CD AND C.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 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL | This field stores the Currency Code value. |
| 5 | RECUR_PRICE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Recurring Price |
| 6 | PW_FREQUENCY | Character(2) | VARCHAR2(2) NOT NULL |
Recurring Frequency
00=Daily 05=Weekly 10=Monthly 15=Quarterly 20=Annually |
| 7 | DURATION | Number(5,0) | INTEGER NOT NULL | Field for IBM development. Lead Time used in SM |
| 8 | DURATION_FREQ | Character(1) | VARCHAR2(1) NOT NULL |
Duration Frequency Units, usually paired with the DURATION, to form a 2 part entry field - ie 12 Months.
D=Days M=Months Q=Quarters W=Weeks Y=Years |