CM_OH1_INQ_VW

(SQL View)
 Index Back

On hand per receipt - Actual

08/24/00 RML Detail view to show receipts for actual and period average cost items 08/02/99 RML Removed cm_seq_receipt due to record change in CM_RECEIPTS. 07/19/99 RML Changed sql - removed check for cost method. Created on 06/08/99 RML CM800-9.0 View to show quantity on hand, unit cost, on hand value, and total depletion per receipt line. This view is used for on hand inquiry for specific business unit and item (CM_RCPT_LINEDET_SP). 06/07/06 9.0 Ice 1513691000

SELECT a.oprid , a.business_unit , a.inv_item_id , a.cm_book , a.dt_timestamp , a.seq_nbr , b.cost_element , a.storage_area , a.inv_lot_id , a.serial_id , a.transaction_date , a.cm_receipt_qty , a.cm_deplete_qty , a.cm_onhand_qty , b.cm_unit_cost , %Round(%DecMult(a.cm_onhand_qty,b.cm_unit_cost),6) FROM PS_CM_OH_INQ_TMP1 a , PS_CM_ACTUAL_COSTB b , PS_CM_ITEM_METH_VW c , PS_CM_ELEMENT d , PS_SET_CNTRL_REC e 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.cm_dt_timestamp_a = b.dt_timestamp AND a.cm_seq_nbr_a = b.seq_nbr AND ((c.CM_WRITEOFF_VAR = 'Y' AND b.CM_COST_SOURCE IN ('1', '2', '3', '5', '6', '7', '8', '9', 'C', 'U') AND c.CM_METHOD IN ('1', '7') AND b.CM_DTTM_STAMP = ( SELECT MAX(ACT.CM_DTTM_STAMP) FROM PS_CM_ACTUAL_COSTB ACT WHERE ACT.BUSINESS_UNIT = a.BUSINESS_UNIT AND ACT.INV_ITEM_ID = a.INV_ITEM_ID AND ACT.CM_BOOK = a.CM_BOOK AND ACT.DT_TIMESTAMP = a.CM_DT_TIMESTAMP_A AND ACT.SEQ_NBR = a.CM_SEQ_NBR_A AND ACT.COST_ELEMENT = B.COST_ELEMENT AND %DatePart(ACT.CM_DTTM_IN_EFFECT) <= a.system_date 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))) OR (c.CM_WRITEOFF_VAR = 'N' AND c.CM_METHOD IN ('1', '7') AND b.CM_DTTM_STAMP = ( SELECT MAX(ACT.CM_DTTM_STAMP) FROM PS_CM_ACTUAL_COSTB ACT WHERE ACT.BUSINESS_UNIT = a.BUSINESS_UNIT AND ACT.INV_ITEM_ID = a.INV_ITEM_ID AND ACT.CM_BOOK = a.CM_BOOK AND ACT.DT_TIMESTAMP = a.CM_DT_TIMESTAMP_A AND ACT.SEQ_NBR = a.CM_SEQ_NBR_A AND ACT.COST_ELEMENT = B.COST_ELEMENT AND %DatePart(ACT.CM_DTTM_IN_EFFECT) <= a.system_date 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 a.CM_ONHAND_QTY <> 0 AND a.business_unit = c.business_unit AND a.inv_item_id = c.inv_item_id AND a.cm_book = c.cm_book AND a.consigned_flag = 'N' AND b.business_unit = e.setcntrlvalue AND e.RECNAME = 'CM_ELEMENT' AND e.setid = d.setid AND d.cost_element = b.cost_element AND d.cost_category <> 'OUB'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
4 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
5 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
6 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
7 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
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 TRANSACTION_DATE Date(10) DATE Transaction Date
12 CM_RECEIPT_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 07/99 GMG CN800-9.0:
13 CM_DEPLETE_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity Depleted
14 CM_ONHAND_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 08/30/99 RML Changed from "onhand" to "on hand"
15 CM_UNIT_COST Signed Number(27,15) DECIMAL(25,15) NOT NULL 07/99 GMG CN800-9.0:
16 CM_ONHAND_VALUE Number(19,6) DECIMAL(18,6) NOT NULL 08/30/99 RML Changed from "onhand" to "on hand"