CM_ONHAND_CD_VW

(SQL View)
 Index Back

On Hand Qty, Costed Depletions

08/99 GMG CN800-9.0 Created On Hand Quantities Based on Receipt Quantity Less Costed Deplete Quantity. This is similar to the view CM_ONHAND_VW except this view is the onhand quantity including receipts depleted but not deplete costed in the onhand quantity (ie: where... AND DEPLETE.POSTED_FLAG IN ('Y','X') ). This is used to compute periodic weighted average after quantities have been depleted but before the depleted quantities are costed with the new periodic weighted average.

SELECT PS_CM_DEPLETE.BUSINESS_UNIT , PS_CM_DEPLETE.INV_ITEM_ID , PS_CM_DEPLETE.CM_BOOK , PS_CM_DEPLETE.CM_DT_TIMESTAMP , PS_CM_DEPLETE.CM_SEQ_NBR , PS_CM_RECEIPTS.CM_DT_TIMESTAMP_A , PS_CM_RECEIPTS.CM_SEQ_NBR_A , PS_CM_RECEIPTS.QTY_BASE , SUM(PS_CM_DEPLETE.QTY_BASE) , PS_CM_RECEIPTS.QTY_BASE - SUM(PS_CM_DEPLETE.QTY_BASE) FROM PS_CM_RECEIPTS , PS_CM_DEPLETE WHERE PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_DEPLETE.BUSINESS_UNIT AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_DEPLETE.INV_ITEM_ID AND PS_CM_RECEIPTS.CM_BOOK = PS_CM_DEPLETE.CM_BOOK AND PS_CM_RECEIPTS.DT_TIMESTAMP = PS_CM_DEPLETE.CM_DT_TIMESTAMP AND PS_CM_RECEIPTS.SEQ_NBR = PS_CM_DEPLETE.CM_SEQ_NBR AND PS_CM_RECEIPTS.CM_DT_TIMESTAMP_A = PS_CM_DEPLETE.CM_DT_TIMESTAMP_A AND PS_CM_RECEIPTS.CM_SEQ_NBR_A = PS_CM_DEPLETE.CM_SEQ_NBR_A AND EXISTS ( SELECT 'X' FROM PS_CM_RECEIPT_COST WHERE PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_RECEIPT_COST.BUSINESS_UNIT AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_RECEIPT_COST.INV_ITEM_ID AND PS_CM_RECEIPTS.CM_BOOK = PS_CM_RECEIPT_COST.CM_BOOK AND PS_CM_RECEIPTS.DT_TIMESTAMP = PS_CM_RECEIPT_COST.DT_TIMESTAMP AND PS_CM_RECEIPTS.SEQ_NBR = PS_CM_RECEIPT_COST.SEQ_NBR AND PS_CM_RECEIPTS.CM_DT_TIMESTAMP_A = PS_CM_RECEIPT_COST.CM_DT_TIMESTAMP_A AND PS_CM_RECEIPTS.CM_SEQ_NBR_A = PS_CM_RECEIPT_COST.CM_SEQ_NBR_A) AND PS_CM_RECEIPTS.CONSIGNED_FLAG = 'N' AND PS_CM_DEPLETE.CONSIGNED_FLAG = 'N' AND (PS_CM_DEPLETE.POSTED_FLAG IN ('Y','X') OR PS_CM_DEPLETE.TRANSACTION_GROUP = '060') GROUP BY PS_CM_DEPLETE.BUSINESS_UNIT, PS_CM_DEPLETE.INV_ITEM_ID, PS_CM_DEPLETE.CM_BOOK, PS_CM_DEPLETE.CM_DT_TIMESTAMP, PS_CM_DEPLETE.CM_SEQ_NBR, PS_CM_RECEIPTS.CM_DT_TIMESTAMP_A , PS_CM_RECEIPTS.CM_SEQ_NBR_A, PS_CM_RECEIPTS.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 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 CM_RECEIPT_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 07/99 GMG CN800-9.0:
9 CM_DEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Depleted
10 CM_ONHAND_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 08/30/99 RML Changed from "onhand" to "on hand"