CM_UNDEPL2_VW

(SQL View)
 Index Back

Undepletable Quantities

Undepletable Quantities Based on Deplete Qty Less Undeplete Qty. - CM_DT_TIMESTAMP_D = component consumption/kit deplete key. - CM_DT_TIMESTAMP = cm_receipts foreign key. - CM_DT_TIMESTAMP_A = cm_actual_cost foreign key.

SELECT B.BUSINESS_UNIT , B.INV_ITEM_ID , B.CM_BOOK , B.CM_DT_TIMESTAMP_D , B.CM_SEQ_NBR_D , A.CM_DT_TIMESTAMP , A.CM_SEQ_NBR , A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A , A.TRANSACTION_GROUP , A.PRODUCTION_ID , A.OP_SEQUENCE , A.CM_ORIG_TRANS_DATE , A.CONSIGNED_FLAG , A.STORAGE_AREA , A.INV_LOT_ID , A.SERIAL_ID , A.QTY_BASE , SUM(B.QTY_BASE) , A.QTY_BASE - SUM(B.QTY_BASE) FROM PS_CM_DEPLETE A , PS_CM_UNDEPLETE B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.CM_BOOK = B.CM_BOOK AND A.DT_TIMESTAMP = B.CM_DT_TIMESTAMP_D AND A.SEQ_NBR = B.CM_SEQ_NBR_D AND A.CM_DT_TIMESTAMP = B.CM_DT_TIMESTAMP AND A.CM_SEQ_NBR = B.CM_SEQ_NBR AND A.CM_DT_TIMESTAMP_A = B.CM_DT_TIMESTAMP_A AND A.CM_SEQ_NBR_A = B.CM_SEQ_NBR_A AND NOT EXISTS ( SELECT 'X' FROM PS_CM_DEPLETION C WHERE C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.INV_ITEM_ID = B.INV_ITEM_ID AND C.CM_BOOK = B.CM_BOOK AND C.INV_LOT_ID = C.INV_LOT_ID AND C.SERIAL_ID = B.SERIAL_ID AND C.TRANSACTION_GROUP = B.TRANSACTION_GROUP AND C.PRODUCTION_ID = B.PRODUCTION_ID AND C.OP_SEQUENCE = B.OP_SEQUENCE AND C.MESSAGE_NBR <> 0 AND C.POSTED_FLAG = 'N' ) GROUP BY B.BUSINESS_UNIT, B.INV_ITEM_ID, B.CM_BOOK, B.CM_DT_TIMESTAMP_D, B.CM_SEQ_NBR_D, A.CM_DT_TIMESTAMP, A.CM_SEQ_NBR, A.CM_DT_TIMESTAMP_A , A.CM_SEQ_NBR_A, A.TRANSACTION_GROUP, A.PRODUCTION_ID, A.OP_SEQUENCE, A.CM_ORIG_TRANS_DATE, A.CONSIGNED_FLAG, A.STORAGE_AREA, A.INV_LOT_ID, A.SERIAL_ID, A.QTY_BASE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
4 CM_DT_TIMESTAMP_D DateTime(26) TIMESTAMP Deplete Date Timestamp
5 CM_SEQ_NBR_D Number(15,0) DECIMAL(15) NOT NULL Sequence Number
6 CM_DT_TIMESTAMP DateTime(26) TIMESTAMP Receipt dttm.
7 CM_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
8 CM_DT_TIMESTAMP_A DateTime(26) TIMESTAMP 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd.
9 CM_SEQ_NBR_A Number(15,0) DECIMAL(15) NOT NULL 07/99 GMG CN800-9.0:
10 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
11 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
12 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
13 CM_ORIG_TRANS_DATE Date(10) DATE 07/99 GMG CN800-9.0:
14 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
15 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
16 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
17 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
18 CM_DEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Depleted
19 CM_UNDEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Undepleted
20 CM_ONHAND_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 08/30/99 RML Changed from "onhand" to "on hand"