CST_TRANS_ONHANDS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
TRANSACTION_ID REC_TRXN_ID DEP_TRXN_ID COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID EFF_DATE BASE_TXN_TYPE_ID CT_COST_DATE COST_TRANSACTION_TYPE ADDITIONAL_PROCESSING_CODE TRANS_QUANTITY VU_RUN_TOTAL REC_TOTAL UOM_CODE

Query

SQL_Statement

SELECT DISTINCT CT.TRANSACTION_ID , CT.REC_TRXN_ID , CT.DEP_TRXN_ID , CT.COST_ORG_ID , CT.COST_BOOK_ID , CT.INVENTORY_ITEM_ID , CT.VAL_UNIT_ID , CTC.EFF_DATE, CTC.BASE_TXN_TYPE_ID, DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', CTC.COST_DATE, CT.COST_DATE) CT_COST_DATE, NVL(ACTN.COST_TRANSACTION_TYPE, DECODE(SIGN(CT.QUANTITY),1,'RECEIPT','ISSUE')) COST_TRANSACTION_TYPE, ACTN.ADDITIONAL_PROCESSING_CODE, DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', 0, CT.QUANTITY) TRANS_QUANTITY, SUM(DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', 0, CT.QUANTITY)) OVER (PARTITION BY CT.COST_ORG_ID, CT.COST_BOOK_ID, CT.VAL_UNIT_ID, CT.INVENTORY_ITEM_ID ORDER BY CT.INVENTORY_ITEM_ID, DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', CTC.COST_DATE, CT.COST_DATE), CT.DEP_TRXN_ID, CT.TRANSACTION_ID, CT.REC_TRXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) VU_RUN_TOTAL , SUM(DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', 0, CT.QUANTITY)) OVER (PARTITION BY CT.REC_TRXN_ID ORDER BY CT.INVENTORY_ITEM_ID,DECODE(ACTN.COST_TRANSACTION_TYPE, 'ADJUST', CTC.COST_DATE, CT.COST_DATE), CT.DEP_TRXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) REC_TOTAL , CT.UOM_CODE FROM CST_TRANSACTION_LAYERS CT, (SELECT DISTINCT TRANSACTION_ID, BASE_TXN_TYPE_ID, BASE_TXN_SOURCE_TYPE_ID, BASE_TXN_ACTION_ID, EFF_DATE, COST_DATE FROM CST_TRANSACTION_COSTS) CTC, CST_TXN_SOURCE_ACTIONS ACTN WHERE CT.TRANSACTION_ID = CTC.TRANSACTION_ID (+) AND CTC.BASE_TXN_SOURCE_TYPE_ID = ACTN.BASE_TXN_SOURCE_TYPE_ID (+) AND CTC.BASE_TXN_ACTION_ID = ACTN.BASE_TXN_ACTION_ID (+)AND CT.POSTED_FLAG NOT IN ( 'X','W')