1. Tables and Views for SCM
  2. FOS_IC_AP_RECEIPT_STATUS_V

FOS_IC_AP_RECEIPT_STATUS_V

Details

  • Schema: FUSION

  • Object owner: FOS

  • Object type: VIEW

Columns

Name

INVOICE_ID

RECEIPT_STATUS

Query

SQL_Statement

SELECT

target_document_header_id invoice_id,

DECODE(MIN((

CASE

WHEN (event.event_Type != 'INTERORG_SHIPMENT') OR (event.event_Type = 'INTERORG_SHIPMENT' AND doc.transfer_order_flag != 'Y')

THEN '1'

WHEN (event.event_type='INTERORG_SHIPMENT' AND doc.transfer_order_flag ='Y')

AND ((SELECT status_lookup

FROM INV_TRANSFER_ORDER_HEADERS header,

inv_transfer_order_lines line,

fos_source_event_doc_info doc

WHERE header.header_id = line.header_id

AND line.line_number = doc.document_line_number

AND header.header_number = doc.document_number

AND doc.transfer_order_flag ='Y'

AND doc.source_document_id =event.source_document_id ) = 'CLOSED' )

THEN '1'

ELSE '0'

END)),1,'CLOSED','OPEN') receipt_status

FROM fos_source_Txn_Events event,

fos_flow_instances fi,

fos_source_event_doc_info doc

WHERE event.event_data_id = fi.event_data_id

AND event.source_document_id = doc.source_document_id

AND fi.task_type ='AP'

AND target_document_header_id is not null

GROUP BY target_document_header_id