RO_BULK_ROLLUP(SQL View) |
Index Back |
|---|---|
Rollup pricesView used to rollup prices while in bulk ordering. |
| SELECT A.CAPTURE_ID_REL , L.CURRENCY_CD , L.PRODUCT_ID , P.DESCR , L.UNIT_OF_MEASURE , SUM(L.QTY_ORDERED) , %Round(SUM(%DecMult(L.PRICE,L.QTY_ORDERED)),4) , %Round(SUM(%DecMult(L.LIST_PRICE,L.QTY_ORDERED)),4) FROM PS_RO_HEADER H , PS_PROD_ITEM P , PS_RO_LINE L , PS_RO_ASSOCIATION A , PS_SET_CNTRL_REC SC WHERE H.CAPTURE_ID = L.CAPTURE_ID AND H.CAPTURE_ID = A.CAPTURE_ID AND A.TYPE = 'BULK' AND P.PRODUCT_ID = L.PRODUCT_ID AND P.SETID = SC.SETID AND SC.SETCNTRLVALUE = H.BUSINESS_UNIT AND SC.RECNAME = 'PROD_ITEM' AND L.STATUS_CODE <> 1 AND L.PARENT_LINE_NBR = 0 AND H.CAPTURE_TYPE_CD <> 'COPY' GROUP BY A.CAPTURE_ID_REL, L.CURRENCY_CD, L.PRODUCT_ID, P.DESCR, L.UNIT_OF_MEASURE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CAPTURE_ID_REL | Character(20) | VARCHAR2(20) NOT NULL | This field stores relative capture IDs. |
| 2 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | This field stores the Currency Code value. |
| 3 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 4 | PRODUCT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Product Description |
| 5 | 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 |
| 6 | TOT_QTY | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Total Quantity |
| 7 | TOT_PRICE | Signed Number(19,4) | DECIMAL(17,4) NOT NULL | Total Price |
| 8 | TOT_LIST_PRICE | Signed Number(19,4) | DECIMAL(17,4) NOT NULL | Total List Price |