CST_UPG_PENDING_ACCTG_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
CST_INV_TRANSACTION_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID PARENT_CST_INV_TXN_ID BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID BASE_TXN_ACTION_ID LOGICAL_FLAG INTRANSIT_FLAG INACTIVE_FLAG TRANSACTION_FLOW_TYPE PRICING_OPTION INTERCOMPANY_INVOICING_FLAG

Query

SQL_Statement

WITH FINDGPPARENTOFLOGICALS AS (SELECT CIT.CST_INV_TRANSACTION_ID LOGICAL_TXN, CASE WHEN (CIT.BASE_TXN_SOURCE_TYPE_ID, CIT.BASE_TXN_ACTION_ID) IN ((1,26),(13,9),(13,10)) AND UPPER(CRT.TRANSACTION_TYPE) = 'RECEIVE' THEN (SELECT X.CST_INV_TRANSACTION_ID FROM CST_INV_TRANSACTIONS X WHERE X.PO_DISTRIBUTION_ID = CIT.PO_DISTRIBUTION_ID AND (X.BASE_TXN_SOURCE_TYPE_ID,X.BASE_TXN_ACTION_ID) IN ((1,27)) AND (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = CIT.RCV_TRANSACTION_ID) = (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = X.RCV_TRANSACTION_ID) ) WHEN (CIT.BASE_TXN_SOURCE_TYPE_ID, CIT.BASE_TXN_ACTION_ID) IN ((1,7),(13,13),(13,14)) AND UPPER(CRT.TRANSACTION_TYPE) = 'RETURN TO VENDOR' THEN (SELECT X.CST_INV_TRANSACTION_ID FROM CST_INV_TRANSACTIONS X WHERE X.PO_DISTRIBUTION_ID = CIT.PO_DISTRIBUTION_ID AND (X.BASE_TXN_SOURCE_TYPE_ID,X.BASE_TXN_ACTION_ID) IN ((1,1)) AND (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = CIT.RCV_TRANSACTION_ID) = (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = X.RCV_TRANSACTION_ID) ) WHEN (CIT.BASE_TXN_SOURCE_TYPE_ID, CIT.BASE_TXN_ACTION_ID) IN ((1,11),(13,9),(13,10),(13,13),(13,14)) AND UPPER(CRT.TRANSACTION_TYPE) = 'CORRECT' THEN (SELECT X.CST_INV_TRANSACTION_ID FROM CST_INV_TRANSACTIONS X WHERE X.PO_DISTRIBUTION_ID = CIT.PO_DISTRIBUTION_ID AND (X.BASE_TXN_SOURCE_TYPE_ID,X.BASE_TXN_ACTION_ID) IN ((1,29)) AND (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = CIT.RCV_TRANSACTION_ID) = (SELECT RT.SHIPMENT_HEADER_ID FROM RCV_TRANSACTIONS RT WHERE RT.TRANSACTION_ID = X.RCV_TRANSACTION_ID) ) ELSE NULL END PARENT_TXN, CIT.BASE_TXN_SOURCE_TYPE_ID LOG_SOURCE, CIT.BASE_TXN_ACTION_ID LOG_ACTION, CIT.PO_DISTRIBUTION_ID, CRT.TRANSACTION_TYPE, 1 PARENT_SOURCE, CASE WHEN UPPER(CRT.TRANSACTION_TYPE) = 'RECEIVE' THEN 27 WHEN UPPER(CRT.TRANSACTION_TYPE) = 'RETURN TO VENDOR' THEN 1 WHEN UPPER(CRT.TRANSACTION_TYPE) = 'CORRECT' THEN 29 ELSE NULL END PARENT_ACTION, CIT.TRANSACTION_QTY FROM CST_INV_TRANSACTIONS CIT, CST_TXN_SOURCE_ACTIONS CTSA, CMR_RCV_TRANSACTIONS CRT WHERE CIT.BASE_TXN_SOURCE_TYPE_ID = CTSA.BASE_TXN_SOURCE_TYPE_ID AND CIT.BASE_TXN_ACTION_ID = CTSA.BASE_TXN_ACTION_ID AND CTSA.INACTIVE_FLAG = 'Y' AND 'FUSION' = CRT.EXTERNAL_SYSTEM_REFERENCE AND CIT.RCV_TRANSACTION_ID = CRT.EXTERNAL_SYSTEM_REF_ID AND CIT.TRANSACTION_FLOW_TYPE = 2 AND CIT.INTRANSIT_FLAG = 'Y') SELECT CIT.CST_INV_TRANSACTION_ID, CIT.EXTERNAL_SYSTEM_REFERENCE, CIT.EXTERNAL_SYSTEM_REF_ID, CASE WHEN CIT.TRANSACTION_FLOW_TYPE = 2 THEN GP.PARENT_TXN WHEN CIT.TRANSACTION_FLOW_TYPE = 1 THEN (SELECT DS.CST_INV_TRANSACTION_ID FROM CST_INV_TRANSACTIONS DS WHERE DS.EXTERNAL_SYSTEM_REF_ID = CIT.PARENT_TRANSACTION_ID AND DS.EXTERNAL_SYSTEM_REFERENCE = 'FUSION') ELSE CIT.PARENT_TRANSACTION_ID END PARENT_CST_INV_TXN_ID, CIT.BASE_TXN_TYPE_ID, CIT.BASE_TXN_SOURCE_TYPE_ID, CIT.BASE_TXN_ACTION_ID, CTSA.LOGICAL_FLAG, CIT.INTRANSIT_FLAG, NVL(CTSA.INACTIVE_FLAG,'N') INACTIVE_FLAG, CIT.TRANSACTION_FLOW_TYPE, CIT.PRICING_OPTION, CIT.INTERCOMPANY_INVOICING_FLAG FROM CST_INV_TRANSACTIONS CIT, CST_TXN_SOURCE_ACTIONS CTSA, FINDGPPARENTOFLOGICALS GP WHERE CIT.CST_INV_TRANSACTION_ID = GP.LOGICAL_TXN(+) AND CIT.BASE_TXN_SOURCE_TYPE_ID = CTSA.BASE_TXN_SOURCE_TYPE_ID AND CIT.BASE_TXN_ACTION_ID = CTSA.BASE_TXN_ACTION_ID AND ( CTSA.INACTIVE_FLAG = 'Y' OR ((CTSA.BASE_TXN_SOURCE_TYPE_ID,CTSA.BASE_TXN_ACTION_ID) IN ((13,3),(13,21),(13,12)/*,(2,28),(4,2),(9,2),(10,2),(13,2),(13,5),(13,58)*/) AND CIT.CREATION_DATE < (SELECT NVL(MIN(CTE.CREATION_DATE),SYSDATE) FROM CST_TRADE_EVENTS CTE) ) ) AND (CIT.BASE_TXN_SOURCE_TYPE_ID,CIT.BASE_TXN_ACTION_ID) NOT IN ((0,5),(0,6)) AND (CIT.BASE_TXN_SOURCE_TYPE_ID,CIT.BASE_TXN_ACTION_ID,NVL(CIT.FOB_POINT,-1)) NOT IN ((0,7,2),(0,8,1)) AND NOT EXISTS (SELECT 'X' FROM CST_TRANSACTIONS CT, CST_COST_ORG_BOOKS COB, CST_COST_DISTRIBUTIONS CCD WHERE CIT.CST_INV_TRANSACTION_ID = CT.CST_INV_TRANSACTION_ID AND CT.COST_ORG_ID = COB.COST_ORG_ID AND CT.COST_BOOK_ID = COB.COST_BOOK_ID AND COB.PRIMARY_BOOK_FLAG = 'Y' AND COB.MAINTAIN_TRANSACTIONS_FLAG = 'Y' AND CCD.TRANSACTION_ID = CT.TRANSACTION_ID) AND NOT EXISTS (SELECT 'X' FROM CST_UPG_INTERCO_TXNS CUIT WHERE CUIT.CST_INV_TRANSACTION_ID = CIT.CST_INV_TRANSACTION_ID)