CMR_R_RCV_TRANSACTIONS_V

Details

• Schema: FUSION

• Object owner: CMR

• Object type: VIEW

Columns

Name

CMR_RCV_TRANSACTION_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID TXN_GROUP_ID TRANSACTION_TYPE TRANSACTION_DATE PARENT_TRANSACTION_ID SHIP_TO_ORGANIZATION_ID PO_LINE_LOCATION_ID PO_DISTRIBUTION_ID RECEIPT_NUMBER PRIMARY_QUANTITY PRIMARY_UOM_CODE TRANSACTION_QUANTITY TRANSACTION_UOM_CODE INVENTORY_ITEM_ID SOURCE_DOCUMENT_CODE SOURCE_DOC_QUANTITY SOURCE_DOC_UOM_CODE TRANSACTION_AMOUNT DESTINATION_TYPE_CODE CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_RATE PREPROCESSED_STATUS PJC_CONTEXT_CATEGORY PJC_PROJECT_ID PJC_TASK_ID PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_BILLABLE_FLAG PJC_WORK_TYPE_ID PJC_FUNDING_ALLOCATION_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_USER_DEF_ATTRIBUTE1 PJC_USER_DEF_ATTRIBUTE2 PJC_USER_DEF_ATTRIBUTE3 PJC_USER_DEF_ATTRIBUTE4 PJC_USER_DEF_ATTRIBUTE5 PJC_USER_DEF_ATTRIBUTE6 PJC_USER_DEF_ATTRIBUTE7 PJC_USER_DEF_ATTRIBUTE8 PJC_USER_DEF_ATTRIBUTE9 PJC_USER_DEF_ATTRIBUTE10 PJC_CAPITALIZABLE_FLAG PJC_RESERVED_ATTRIBUTE1 PJC_RESERVED_ATTRIBUTE2 PJC_RESERVED_ATTRIBUTE3 PJC_RESERVED_ATTRIBUTE4 PJC_RESERVED_ATTRIBUTE5 PJC_RESERVED_ATTRIBUTE6 PJC_RESERVED_ATTRIBUTE7 PJC_RESERVED_ATTRIBUTE8 PJC_RESERVED_ATTRIBUTE9 PJC_RESERVED_ATTRIBUTE10 PJC_INTERFACED_STATUS ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 INTERFACE_BATCH_NUMBER INTERFACE_BATCH_NAME ROOT_RECEIVE_TXN_ID ROOT_DELIVER_TXN_ID CMR_ROOT_RECEIVE_TXN_ID CMR_ROOT_DELIVER_TXN_ID CMR_PO_DISTRIBUTION_ID TXN_FLOW_HEADER_ID TAX_PROCESSED_FLAG ALLOCATED_FLAG CMR_PO_LINE_LOCATION_ID DEFAULT_TAXATION_COUNTRY FIRST_PTY_REG_ID THIRD_PTY_REG_ID FINAL_DISCHARGE_LOCATION_ID DOCUMENT_FISCAL_CLASSIFICATION TRX_BUSINESS_CATEGORY USER_DEFINED_FISC_CLASS TAX_INVOICE_NUMBER TAX_INVOICE_DATE PRODUCT_FISC_CLASS_ID PRODUCT_CATEGORY INTENDED_USE_CLASSIF_ID PRODUCT_TYPE ASSESSABLE_VALUE TAX_CLASSIFICATION_CODE SHIP_FROM_LOCATION_ID INV_SHIPPING_TRANSACTION_ID SHIPMENT_NUMBER SHIP_FROM_INV_ORG_ID SHIPMENT_LINE_NUMBER TRANSFER_ORDER_HEADER_ID TRANSFER_ORDER_LINE_ID TRANSFER_ORDER_DIST_ID PHYSICAL_RETURN_REQD_FLAG ORIG_TF_ORD_INV_SHIP_TXN_ID CONSIGNED_FLAG RECEIPT_LINE_NUMBER PRIOR_TRADE_INV_ORG_ID BILL_TO_BUSINESS_UNIT_ID FTR_ID ERROR_CODE PJC_TXN_STATUS_CODE CMR_SUB_LEDGER_ID ACCOUNTING_EVENT_ID

Query

SQL_Statement

select CRT.CMR_RCV_TRANSACTION_ID , CRT.EXTERNAL_SYSTEM_REFERENCE , CRT.EXTERNAL_SYSTEM_REF_ID , CRT.TXN_GROUP_ID , CRT.TRANSACTION_TYPE , CRT.TRANSACTION_DATE , CRT.PARENT_TRANSACTION_ID , CRT.SHIP_TO_ORGANIZATION_ID , CRT.PO_LINE_LOCATION_ID , CRT.PO_DISTRIBUTION_ID , CRT.RECEIPT_NUMBER , CRT.PRIMARY_QUANTITY , CRT.PRIMARY_UOM_CODE , CRT.TRANSACTION_QUANTITY , CRT.TRANSACTION_UOM_CODE , CRT.INVENTORY_ITEM_ID , CRT.SOURCE_DOCUMENT_CODE , CRT.SOURCE_DOC_QUANTITY , CRT.SOURCE_DOC_UOM_CODE , CRT.TRANSACTION_AMOUNT , CRT.DESTINATION_TYPE_CODE , CRT.CURRENCY_CONVERSION_DATE , CRT.CURRENCY_CONVERSION_RATE , CRT.PREPROCESSED_STATUS , CRT.PJC_CONTEXT_CATEGORY , CRT.PJC_PROJECT_ID , CRT.PJC_TASK_ID , CRT.PJC_EXPENDITURE_TYPE_ID , CRT.PJC_EXPENDITURE_ITEM_DATE , CRT.PJC_ORGANIZATION_ID , CRT.PJC_BILLABLE_FLAG , CRT.PJC_WORK_TYPE_ID , CRT.PJC_FUNDING_ALLOCATION_ID , CRT.PJC_CONTRACT_ID , CRT.PJC_CONTRACT_LINE_ID , CRT.PJC_USER_DEF_ATTRIBUTE1 , CRT.PJC_USER_DEF_ATTRIBUTE2 , CRT.PJC_USER_DEF_ATTRIBUTE3 , CRT.PJC_USER_DEF_ATTRIBUTE4 , CRT.PJC_USER_DEF_ATTRIBUTE5 , CRT.PJC_USER_DEF_ATTRIBUTE6 , CRT.PJC_USER_DEF_ATTRIBUTE7 , CRT.PJC_USER_DEF_ATTRIBUTE8 , CRT.PJC_USER_DEF_ATTRIBUTE9 , CRT.PJC_USER_DEF_ATTRIBUTE10 , CRT.PJC_CAPITALIZABLE_FLAG , CRT.PJC_RESERVED_ATTRIBUTE1 , CRT.PJC_RESERVED_ATTRIBUTE2 , CRT.PJC_RESERVED_ATTRIBUTE3 , CRT.PJC_RESERVED_ATTRIBUTE4 , CRT.PJC_RESERVED_ATTRIBUTE5 , CRT.PJC_RESERVED_ATTRIBUTE6 , CRT.PJC_RESERVED_ATTRIBUTE7 , CRT.PJC_RESERVED_ATTRIBUTE8 , CRT.PJC_RESERVED_ATTRIBUTE9 , CRT.PJC_RESERVED_ATTRIBUTE10 , CRT.PJC_INTERFACED_STATUS , CRT.ATTRIBUTE_CATEGORY , CRT.ATTRIBUTE1 , CRT.ATTRIBUTE2 , CRT.ATTRIBUTE3 , CRT.ATTRIBUTE4 , CRT.ATTRIBUTE5 , CRT.ATTRIBUTE6 , CRT.ATTRIBUTE7 , CRT.ATTRIBUTE8 , CRT.ATTRIBUTE9 , CRT.ATTRIBUTE10 , CRT.ATTRIBUTE11 , CRT.ATTRIBUTE12 , CRT.ATTRIBUTE13 , CRT.ATTRIBUTE14 , CRT.ATTRIBUTE15 , CRT.INTERFACE_BATCH_NUMBER , CRT.INTERFACE_BATCH_NAME , CRT.ROOT_RECEIVE_TXN_ID , CRT.ROOT_DELIVER_TXN_ID , CRT.CMR_ROOT_RECEIVE_TXN_ID , CRT.CMR_ROOT_DELIVER_TXN_ID , CRT.CMR_PO_DISTRIBUTION_ID , CRT.TXN_FLOW_HEADER_ID , CRT.TAX_PROCESSED_FLAG , CRT.ALLOCATED_FLAG , CRT.CMR_PO_LINE_LOCATION_ID , CRT.DEFAULT_TAXATION_COUNTRY , CRT.FIRST_PTY_REG_ID , CRT.THIRD_PTY_REG_ID , CRT.FINAL_DISCHARGE_LOCATION_ID , CRT.DOCUMENT_FISCAL_CLASSIFICATION , CRT.TRX_BUSINESS_CATEGORY , CRT.USER_DEFINED_FISC_CLASS , CRT.TAX_INVOICE_NUMBER , CRT.TAX_INVOICE_DATE , CRT.PRODUCT_FISC_CLASS_ID , CRT.PRODUCT_CATEGORY , CRT.INTENDED_USE_CLASSIF_ID , CRT.PRODUCT_TYPE , CRT.ASSESSABLE_VALUE , CRT.TAX_CLASSIFICATION_CODE , CRT.SHIP_FROM_LOCATION_ID , CRT.INV_SHIPPING_TRANSACTION_ID , CRT.SHIPMENT_NUMBER , CRT.SHIP_FROM_INV_ORG_ID , CRT.SHIPMENT_LINE_NUMBER , CRT.TRANSFER_ORDER_HEADER_ID , CRT.TRANSFER_ORDER_LINE_ID , CRT.TRANSFER_ORDER_DIST_ID , CRT.PHYSICAL_RETURN_REQD_FLAG , CRT.ORIG_TF_ORD_INV_SHIP_TXN_ID , CRT.CONSIGNED_FLAG , CRT.RECEIPT_LINE_NUMBER , CRT.PRIOR_TRADE_INV_ORG_ID , CRT.BILL_TO_BUSINESS_UNIT_ID , CRT.FTR_ID , CRT.ERROR_CODE , CRD.PJC_TXN_STATUS_CODE , CRD.CMR_SUB_LEDGER_ID , CRD.ACCOUNTING_EVENT_ID FROM cmr_rcv_events cre, cmr_rcv_distributions crd, cmr_purchase_order_dtls cpod, cmr_rcv_transactions crt WHERE crd.accounting_event_id= cre.accounting_event_id AND cre.cmr_po_distribution_id= crt.cmr_po_distribution_id AND cpod.active_flag= 'Y' AND cpod.cmr_po_distribution_id= cre.cmr_po_distribution_id AND ((cre.event_type_code='EXPENSE_ADJUSTMENT' AND crd.accounting_line_type= 'EXPENSE' AND crt.cmr_rcv_transaction_id=(SELECT MAX(crt1.cmr_rcv_transaction_id) FROM cmr_rcv_transactions crt1 WHERE crt1.po_distribution_id=crt.po_distribution_id )) OR (cre.event_type_code in ('CORRECTION_TO_DELIVERY', 'RETURN_TO_RECEIVING', 'DELIVERY_TO_EXPENSE') and cre.accounting_event_id = crd.accounting_event_id and cre.cmr_po_distribution_id = cpod.cmr_po_distribution_id and cre.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id))