FOS_TRADE_AGR_INFO_V

Details

• Schema: FUSION

• Object owner: FOS

• Object type: VIEW

Columns

Name
AGREEMENT_PTR_ID DOCUMENT_DATE SOURCE_DOCUMENT_ID DOCUMENT_SOURCE_SYSTEM_ID DOCUMENT_TYPE STATUS TO_BU_ID TO_TRADE_ORG_ID FROM_TRADE_ORG_ID EVENT_TYPE EVENT_STATUS

Query

SQL_Statement

WITH doc_info AS ( SELECT edi.agreement_ptr_id, edi.document_date, edi.source_document_id, edi.document_source_system_id, edi.document_type, 'ACTIVE' STATUS, ftr.to_bu_id, ftr.to_trade_org, ftr.from_trade_org FROM fos_event_doc_info edi, fos_agreement_ftr_f ftr WHERE (edi.agreement_ptr_id = ftr.agreement_ptr_id AND edi.document_date BETWEEN ftr.effective_start_date AND ftr.effective_end_date) AND ftr.sequence_number = 1 AND edi.document_type in ('IOT','OO') AND edi.DOCUMENT_SOURCE_SYSTEM_ID='-1' ) SELECT di.agreement_ptr_id AGREEMENT_PTR_ID, di.document_date DOCUMENT_DATE, event.source_document_id SOURCE_DOCUMENT_ID, di.document_source_system_id DOCUMENT_SOURCE_SYSTEM_ID, 'IOT' DOCUMENT_TYPE, di.status STATUS, di.to_bu_id TO_BU_ID, di.to_trade_org TO_TRADE_ORG_ID, di.from_trade_org FROM_TRADE_ORG_ID, event.event_type EVENT_TYPE, event.status EVENT_STATUS FROM fos_source_txn_events event, doc_info di WHERE event.source_document_id = di.source_document_id(+) and event.event_type in ('INTERORG_SHIPMENT') union all SELECT di.agreement_ptr_id AGREEMENT_PTR_ID, di.document_date DOCUMENT_DATE, di.source_document_id SOURCE_DOCUMENT_ID, di.document_source_system_id DOCUMENT_SOURCE_SYSTEM_ID, 'OO' DOCUMENT_TYPE, di.status STATUS, di.to_bu_id TO_BU_ID, di.to_trade_org TO_TRADE_ORG_ID, di.from_trade_org FROM_TRADE_ORG_ID, null EVENT_TYPE, null EVENT_STATUS FROM doc_info di WHERE di.document_type='OO' union all SELECT di.agreement_ptr_id AGREEMENT_PTR_ID, di.document_date DOCUMENT_DATE, event.event_data_id SOURCE_DOCUMENT_ID, di.document_source_system_id DOCUMENT_SOURCE_SYSTEM_ID, 'IOT' DOCUMENT_TYPE, di.status STATUS, di.to_bu_id TO_BU_ID, di.to_trade_org TO_TRADE_ORG_ID, di.from_trade_org FROM_TRADE_ORG_ID, event.event_type EVENT_TYPE, event.status EVENT_STATUS FROM fos_source_txn_events event, doc_info di WHERE event.source_document_id||event.transaction_org_code = di.source_document_id(+) and event.event_type in ('TRANSFER_TO_OWNED')