CM_TRANSPEND_VW

(SQL View)
 Index Back

Transactions Pending CM Proc

10/99 GMG CN800-9.0. Added. View into transaction_inv rows pending CM processing.

SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , B.CM_BOOK , A.DT_TIMESTAMP , A.SEQ_NBR , D.CM_COST_PROC_GROUP , A.TRANSACTION_GROUP , A.QTY_BASE , A.ADJUST_TYPE , E.LOC_ACCTG_REQ , C.CM_STOR_AREA_CNTRL , A.STORAGE_AREA , A.CONSIGNED_FLAG , A.TO_CONSIGNED_FLG , A.TRANSACTION_DATE , A.TRANSACTION_SOURCE , A.UNIT_MEASURE_STD , A.SERIAL_ID , A.INV_LOT_ID , A.TO_STORAGE_AREA , D.CM_SIGN , A.PROCESS_INSTANCE , A.COSTED_FLAG FROM PS_TRANSACTION_INV A , PS_CM_BU_BOOK_TBL B , PS_CM_ITEM_METH_VW C , PS_CM_ACCTG_GRP_D D , PS_CM_LOC_ACCTG_VW E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.CM_BOOK = C.CM_BOOK AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.TRANSACTION_GROUP = D.TRANSACTION_GROUP AND A.ADJUST_TYPE = D.ADJUST_TYPE AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND D.CM_COST_PROC_GROUP IN ('BINTOBIN','DEPLETES','RECEIPTS','RTV','TRANSFERIN','TRANSFEROUT','SHIPONBEHALF','IBUADJI','IBUADJD') AND B.CM_BOOK_STATUS = 'A' AND C.CM_TYPE IN ('1','2') AND A.DT_TIMESTAMP >= B.CM_BOOK_START AND (A.NON_OWN_FLAG = 'N' OR (A.NON_OWN_FLAG = 'Y' AND A.CONSIGNED_FLAG = 'Y')) AND (A.TRANSACTION_GROUP IN ( SELECT TRANSACTION_GROUP FROM PS_CM_ACCTG_GRP_D WHERE TRANSACTION_GROUP <> '060') OR (A.TRANSACTION_GROUP = '060' AND A.STORAGE_AREA <> A.TO_STORAGE_AREA AND (E.LOC_ACCTG_REQ = 'Y' OR C.CM_STOR_AREA_CNTRL = 'Y') ) ) AND A.COSTED_FLAG = 'N'

  • Parent record: TRANSACTION_INV

    • # 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_COST_PROC_GROUP Character(18) VARCHAR2(18) NOT NULL Cost Flow Record
    7 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
    8 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
    9 ADJUST_TYPE Character(1) VARCHAR2(1) NOT NULL Adjustment Type
    0=CM Only
    A=VMI Return
    D=Decrease
    I=Increase
    M=Misc Issue
    R=Misc Return
    S=Scrap
    V=Scrap RTV
    10 LOC_ACCTG_REQ Character(1) VARCHAR2(1) NOT NULL Location Accounting Required
    11 CM_STOR_AREA_CNTRL Character(1) VARCHAR2(1) NOT NULL CN# CM800-9.0 GMG Storage Area Control for Costing. If this flag is on (Y) depletions will only deplete receipts in the storage area from which the depletion was made. If it is off (N), depletions will not consider the storage area when selecting qualifying receipts to deplete.
    12 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
    13 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
    14 TO_CONSIGNED_FLG Character(1) VARCHAR2(1) NOT NULL To Consigned Flag
    15 TRANSACTION_DATE Date(10) DATE Transaction Date
    16 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
    17 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
    18 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
    19 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
    20 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
    21 CM_SIGN Signed Number(2,0) DECIMAL(1) NOT NULL This field is used in cm_acctg_grp_d and can be multiplied by the qty_base in transaction_inv to get a true movement direction (- for depletions of inventory and + for receipts of inventory). Join cm_acctg_grp_d by the transaction_group and adjust type.
    22 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    23 POSTED_FLAG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a Receivables item has been posted. This field is automatically updated by the Receivables Update program.
    N=Not Posted
    X=Don't Post
    Y=Posted