CM_UNDEPL_WO_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.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 , PS_TRANSACTION_INV D 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 %Join(COMMON_KEYS, TRANSACTION_INV D, CM_DEPLETE A) AND NOT EXISTS ( SELECT 'X' FROM PS_CM_DEPLETION C , PS_TRANSACTION_INV E 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 %Join(COMMON_KEYS, CM_DEPLETION C, TRANSACTION_INV E) 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.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 CM_ORIG_TRANS_DATE Date(10) DATE 07/99 GMG CN800-9.0:
12 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
13 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
14 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
15 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
16 CM_DEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Depleted
17 CM_UNDEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Undepleted
18 CM_ONHAND_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 08/30/99 RML Changed from "onhand" to "on hand"