IN_ONHAND_KB_VW(SQL View) |
Index Back |
|---|---|
On Hand Qty View for Insights |
| SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , D.CM_UNIT_COST , %Round(%DecMult(A.CM_ONHAND_QTY,D.CM_UNIT_COST ),6) , D.CURRENCY_CD FROM PS_CM_ONHAND_VW A , PS_CM_ITEM_METH_VW C , PS_CM_ACTUAL_COSTB D , PS_CM_ELEMENT E , PS_SET_CNTRL_REC F WHERE A.CONSIGNED_FLAG ='N' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.CM_BOOK = C.CM_BOOK AND C.CM_METHOD IN ('1' , '7' ) AND A.BUSINESS_UNIT=D.BUSINESS_UNIT AND A.INV_ITEM_ID =D.INV_ITEM_ID AND A.CM_DT_TIMESTAMP_A=D.DT_TIMESTAMP AND A.CM_SEQ_NBR_A=D.SEQ_NBR AND A.CM_BOOK = D.CM_BOOK AND D.CM_DTTM_STAMP = ( SELECT MAX(ACT.CM_DTTM_STAMP) FROM PS_CM_ACTUAL_COSTB ACT WHERE ACT.BUSINESS_UNIT = D.BUSINESS_UNIT AND ACT.INV_ITEM_ID = D.INV_ITEM_ID AND ACT.CM_BOOK = D.CM_BOOK AND ACT.DT_TIMESTAMP = D.DT_TIMESTAMP AND ACT.SEQ_NBR = D.SEQ_NBR AND ACT.COST_ELEMENT = D.COST_ELEMENT AND ACT.CM_SEQ_COST = ( SELECT MAX(ACT2.CM_SEQ_COST) FROM PS_CM_ACTUAL_COSTB ACT2 WHERE ACT2.BUSINESS_UNIT = ACT.BUSINESS_UNIT AND ACT2.INV_ITEM_ID = ACT.INV_ITEM_ID AND ACT2.CM_BOOK = ACT.CM_BOOK AND ACT2.DT_TIMESTAMP = ACT.DT_TIMESTAMP AND ACT2.SEQ_NBR = ACT.SEQ_NBR AND ACT2.COST_ELEMENT = ACT.COST_ELEMENT AND ACT2.CM_DTTM_STAMP = ACT.CM_DTTM_STAMP)) AND F.SETCNTRLVALUE = D.BUSINESS_UNIT AND F.RECNAME = 'CM_ELEMENT' AND F.SETID = E.SETID AND E.COST_ELEMENT = D.COST_ELEMENT AND E.COST_CATEGORY <> 'OUB' UNION ALL SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , D.CM_UNIT_COST , %Round(%DecMult(A.CM_ONHAND_QTY,D.CM_UNIT_COST ),6) , D.CURRENCY_CD FROM PS_CM_ONHAND_VW A , PS_CM_ITEM_METH_VW C , PS_CM_PAV2_COST_VW D WHERE A.CONSIGNED_FLAG ='N' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.CM_BOOK = C.CM_BOOK AND C.CM_METHOD = '6' AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND C.INV_ITEM_ID =D.INV_ITEM_ID AND C.CM_BOOK = D.CM_BOOK UNION ALL SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , D.CM_UNIT_COST , %Round(%DecMult(A.CM_ONHAND_QTY,D.CM_UNIT_COST ),6) , D.CURRENCY_CD FROM PS_CM_ONHAND_VW A , PS_CM_ITEM_METH_VW C , PS_CM_PER2_COST_VW D WHERE A.CONSIGNED_FLAG ='N' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.CM_BOOK = C.CM_BOOK AND C.CM_METHOD = '9' AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND C.INV_ITEM_ID =D.INV_ITEM_ID AND C.CM_BOOK = D.CM_BOOK UNION ALL SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , SUM(D.TL_COST + D.LL_COST) , %Round(SUM(%DecMult(A.CM_ONHAND_QTY,(D.TL_COST + D.LL_COST)) ),6) , G.BASE_CURRENCY FROM PS_CM_ONHAND_VW A , PS_CM_ITEM_METH_VW C , PS_CM_PRODCOST D , PS_CM_ELEMENT E , PS_SET_CNTRL_REC F , PS_BUS_UNIT_TBL_IN G WHERE A.CONSIGNED_FLAG ='N' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.CM_BOOK = C.CM_BOOK AND C.CM_METHOD = '8' AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND C.INV_ITEM_ID =D.INV_ITEM_ID AND D.CONFIG_CODE = ' ' AND D.EFFDT = ( SELECT MAX(S.EFFDT) FROM PS_CM_PRODCOST S WHERE S.BUSINESS_UNIT = D.BUSINESS_UNIT AND S.INV_ITEM_ID = D.INV_ITEM_ID AND S.CONFIG_CODE = D.CONFIG_CODE AND S.EFFDT <= %CURRENTDATEIN ) AND F.SETCNTRLVALUE = D.BUSINESS_UNIT AND F.RECNAME = 'CM_ELEMENT' AND F.SETID = E.SETID AND E.COST_ELEMENT = D.COST_ELEMENT AND E.COST_CATEGORY <> 'OUB' AND A.BUSINESS_UNIT=G.BUSINESS_UNIT GROUP BY A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A, A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , G.BASE_CURRENCY UNION ALL SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , SUM(D.TL_COST + D.LL_COST) , %Round(SUM(%DecMult(A.CM_ONHAND_QTY,(D.TL_COST + D.LL_COST)) ),6) , G.BASE_CURRENCY FROM PS_CM_ONHAND_VW A , PS_CM_ITEM_METH_VW C , PS_CM_PRODCOST D , PS_CM_ELEMENT E , PS_SET_CNTRL_REC F , PS_BUS_UNIT_TBL_IN G , PS_LOT_CONTROL_INV H WHERE A.CONSIGNED_FLAG ='N' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.CM_BOOK = C.CM_BOOK AND C.CM_METHOD = '8' AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND C.INV_ITEM_ID =D.INV_ITEM_ID AND D.CONFIG_CODE <>' ' AND D.EFFDT = ( SELECT MAX(S.EFFDT) FROM PS_CM_PRODCOST S WHERE S.BUSINESS_UNIT = D.BUSINESS_UNIT AND S.INV_ITEM_ID = D.INV_ITEM_ID AND S.CONFIG_CODE = D.CONFIG_CODE AND S.EFFDT <= %CURRENTDATEIN ) AND F.SETCNTRLVALUE = D.BUSINESS_UNIT AND F.RECNAME = 'CM_ELEMENT' AND F.SETID = E.SETID AND E.COST_ELEMENT = D.COST_ELEMENT AND E.COST_CATEGORY <> 'OUB' AND D.BUSINESS_UNIT=H.BUSINESS_UNIT AND D.INV_ITEM_ID = H.INV_ITEM_ID AND A.INV_LOT_ID = H.INV_LOT_ID AND D.CONFIG_CODE =H.CONFIG_CODE AND A.BUSINESS_UNIT=G.BUSINESS_UNIT GROUP BY A.BUSINESS_UNIT , A.INV_ITEM_ID , A.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A, A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , C.CM_METHOD , A.CM_ONHAND_QTY , G.BASE_CURRENCY |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BUIN_NONVW |
| 2 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 3 | CM_BOOK |
Character(10) | VARCHAR2(10) NOT NULL | Cost Book |
| 4 | DT_TIMESTAMP |
DateTime(26) | TIMESTAMP | Date Timestamp |
| 5 | SEQ_NBR |
Number(15,0) | DECIMAL(15) NOT NULL | Sequence Number |
| 6 | CM_DT_TIMESTAMP_A |
DateTime(26) | TIMESTAMP | 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd. |
| 7 | CM_SEQ_NBR_A |
Number(15,0) | DECIMAL(15) NOT NULL | 07/99 GMG CN800-9.0: |
| 8 | CONSIGNED_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Consigned Flag |
| 9 | STORAGE_AREA | Character(5) | VARCHAR2(5) NOT NULL | Storage Area |
| 10 | INV_LOT_ID | Character(15) | VARCHAR2(15) NOT NULL | Lot ID |
| 11 | CM_METHOD | Character(2) | VARCHAR2(2) NOT NULL |
8/20/02 lee: added xlate 9 for retroactive perp average
01/18/99 RML Created for CN800-9.0 FIFO/LIFO Enhancements
0=Non Cost 1=Actual Cost 6=Perpetual Weighted Average 7=Periodic Weighted Average 8=Value at Current Standard 9=Retroactive Perpetual Average |
| 12 | CM_ONHAND_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | 08/30/99 RML Changed from "onhand" to "on hand" |
| 13 | CM_UNIT_COST | Signed Number(27,15) | DECIMAL(25,15) NOT NULL | 07/99 GMG CN800-9.0: |
| 14 | CM_ONHAND_VALUE | Number(19,6) | DECIMAL(18,6) NOT NULL | 08/30/99 RML Changed from "onhand" to "on hand" |
| 15 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |