1. Tables and Views for SCM
  2. FOS_DELIVERY_DETAIL_ID_V

FOS_DELIVERY_DETAIL_ID_V

Details

  • Schema: FUSION

  • Object owner: FOS

  • Object type: VIEW

Columns

Name

FLOW_INSTANCE_ID

DELIVERY_DETAIL_ID

Query

SQL_Statement

SELECT fi.flow_instance_id,

DECODE (te.event_definition_id,

1800, (

SELECT pte.event_line_number

FROM fos_txn_events pte

WHERE pte.source_document_id = te.source_document_id

and pte.event_definition_id = 1700

and pte.system_id = te.system_id

),

te.event_line_number

) delivery_detail_id

FROM fos_flow_instances fi,

fos_txn_events te,

fos_event_doc_info edi,

fos_agreement_ftr_f ftr

WHERE fi.event_data_id = te.event_data_id

AND fi.event_definition_id = te.event_definition_id

AND fi.system_id = te.system_id

AND te.source_document_id = edi.source_document_id

AND te.document_type = edi.document_type

AND te.document_source_system_id = edi.document_source_system_id

AND (te.event_definition_id = 2100 OR (te.event_definition_id IN (1700, 1800) AND edi.transfer_order_flag = 'Y'))

AND fi.agreement_ftr_id = ftr.agreement_ftr_id

AND ftr.sequence_number=1

AND edi.document_date BETWEEN ftr.effective_start_date and ftr.effective_end_date