CM_ADJ_USER_VW(SQL View) |
Index Back |
|---|---|
CM User Adjustment Actual view |
| SELECT DISTINCT B.BUSINESS_UNIT , B.INV_ITEM_ID , B.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , A.COST_ELEMENT , A.RECEIVER_ID , B.STORAGE_AREA , B.INV_LOT_ID , B.SERIAL_ID , B.CM_ONHAND_QTY , A.CM_UNIT_COST , B.CONSIGNED_FLAG , A.CM_ADJ_TYPE , A.PRODUCTION_ID , A.CM_COST_SOURCE FROM PS_CM_ACTUAL_COSTB A , PS_CM_ONHAND_VW B , PS_CM_ITEM_METH_VW C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.CM_DT_TIMESTAMP_A AND A.SEQ_NBR = B.CM_SEQ_NBR_A AND A.CM_BOOK = B.CM_BOOK AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.INV_ITEM_ID = A.INV_ITEM_ID AND C.CM_BOOK = A.CM_BOOK AND C.CM_METHOD IN ('1', '7') AND ((C.CM_WRITEOFF_VAR = 'Y' AND A.CM_COST_SOURCE IN ('1', '2', '3', '5', '6', '7', '8', '9', 'C', 'U') AND A.CM_SEQ_COST = ( SELECT MAX(ACT.CM_SEQ_COST) FROM PS_CM_ACTUAL_COSTB ACT WHERE ACT.BUSINESS_UNIT = A.BUSINESS_UNIT AND ACT.INV_ITEM_ID = A.INV_ITEM_ID AND ACT.DT_TIMESTAMP = A.DT_TIMESTAMP AND ACT.SEQ_NBR = A.SEQ_NBR AND ACT.CM_BOOK = A.CM_BOOK AND ACT.COST_ELEMENT = A.COST_ELEMENT AND ACT.RECEIVER_ID = A.RECEIVER_ID AND ACT.CM_COST_SOURCE IN ('1', '2', '3', '5', '6', '7', '8', '9', 'C', 'U'))) OR (C.CM_WRITEOFF_VAR = 'N' AND A.CM_SEQ_COST = ( SELECT MAX(ACT2.CM_SEQ_COST) FROM PS_CM_ACTUAL_COSTB ACT2 WHERE ACT2.BUSINESS_UNIT = A.BUSINESS_UNIT AND ACT2.INV_ITEM_ID = A.INV_ITEM_ID AND ACT2.DT_TIMESTAMP = A.DT_TIMESTAMP AND ACT2.SEQ_NBR = A.SEQ_NBR AND ACT2.CM_BOOK = A.CM_BOOK AND ACT2.COST_ELEMENT = A.COST_ELEMENT AND ACT2.RECEIVER_ID = A.RECEIVER_ID))) |
| # | 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
Prompt Table: CM_BOOK_DEFN |
| 4 | DT_TIMESTAMP |
DateTime(26) | TIMESTAMP | Date Timestamp |
| 5 | SEQ_NBR |
Number(15,0) | DECIMAL(15) NOT NULL | Sequence Number |
| 6 | COST_ELEMENT |
Character(4) | VARCHAR2(4) NOT NULL | Cost Element |
| 7 | RECEIVER_ID | Character(10) | VARCHAR2(10) NOT NULL | Receiver number |
| 8 | STORAGE_AREA | Character(5) | VARCHAR2(5) NOT NULL | Storage Area |
| 9 | INV_LOT_ID | Character(15) | VARCHAR2(15) NOT NULL | Lot ID |
| 10 | SERIAL_ID | Character(20) | VARCHAR2(20) NOT NULL | Serial ID |
| 11 | CM_ONHAND_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | 08/30/99 RML Changed from "onhand" to "on hand" |
| 12 | CM_UNIT_COST_OLD | Number(26,15) | DECIMAL(25,15) NOT NULL | 07/99 GMG CN800-9.0: |
| 13 | CONSIGNED_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Consigned Flag |
| 14 | CM_ADJ_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
07/99 GMG CN800-9.0:
0=Original Cost 1=Cost Adjustment 2=Value Adjustment |
| 15 | PRODUCTION_ID | Character(10) | VARCHAR2(10) NOT NULL | Production ID |
| 16 | CM_COST_SOURCE | Character(1) | VARCHAR2(1) NOT NULL |
12/29/2005 dbe ICE 1379942000 - Added "U" Xlat.
0=Depletion 1=PO Receipt 2=Expr Ptway 3=IUTransfer 4=AP Matched 5=User Adj 6=Qty Adjs. 7=Bin to Bin 8=Prelim Cst 9=Prdn Compl C=NegQty R=Return S=Split from U=75 Upgrade |