FOS_TRANSFER_PRICE_FDC

Details

Schema: FUSION
Object owner: FOS
Object type: VIEW

Columns

Name
DOCUMENT_ID DOCUMENT_TYPE SOURCE_SYSTEM_ID PRICE_TYPE BASIS AGREEMENT_FTR_ID AS_ON_EFFECTIVE_DATE UNIT_PRICE UNIT_OF_MEASURE CURRENCY_CODE CONVERSION_TYPE CHARGE_ID PRICE_ELEMENT_CODE FLOW_INSTANCE_ID EVENT_DATA_ID EVENT_DEFINITION_ID TASK_TYPE TRANSACTION_TYPE TASK_STATUS

Name

DOCUMENT_ID

DOCUMENT_TYPE

SOURCE_SYSTEM_ID

PRICE_TYPE

BASIS

AGREEMENT_FTR_ID

AS_ON_EFFECTIVE_DATE

UNIT_PRICE

UNIT_OF_MEASURE

CURRENCY_CODE

CONVERSION_TYPE

CHARGE_ID

PRICE_ELEMENT_CODE

FLOW_INSTANCE_ID

EVENT_DATA_ID

EVENT_DEFINITION_ID

TASK_TYPE

TRANSACTION_TYPE

TASK_STATUS

Query

SQL_Statement
SELECT pd.document_id, pd.document_type, pd.source_system_id, pd.price_type, pd.basis, pd.agreement_ftr_id, pd.as_on_effective_date, pd.unit_price, pd.unit_of_measure, pd.currency_code, pd.conversion_type, pd.charge_id, pd.price_element_code, fi.flow_instance_id, fi.event_data_id, fi.event_definition_id, fi.task_type, fi.transaction_type, fi.status as task_status FROM fos_pricing_details pd, fos_flow_instances fi, fos_doc_flow_assignment dfa, fos_agreement_ftr_f ftr WHERE pd.agreement_ftr_id = fi.agreement_ftr_id AND pd.price_type = 'ACCOUNTING' AND pd.price_element_code = 'NetTransferPrice' AND pd.document_id = dfa.source_document_id AND pd.event_data_id = fi.event_data_id AND pd.event_definition_id = fi.event_definition_id AND pd.agreement_ftr_id = ftr.agreement_ftr_id AND pd.as_on_effective_date BETWEEN ftr.effective_start_date AND ftr.effective_end_date AND fi.document_flow_assignment_id = dfa.document_flow_assignment_id AND fi.task_type = 'CST' AND fi.transaction_type = 'INTRANSIT_RECEIPT' AND ftr.sequence_number = ( SELECT MAX (ftr1.sequence_number) FROM fos_agreement_ftr_f ftr1 WHERE ftr1.agreement_ptr_id = ftr.agreement_ptr_id AND pd.as_on_effective_date BETWEEN ftr1.effective_start_date AND ftr1.effective_end_date )

SQL_Statement

SELECT pd.document_id,

pd.document_type,

pd.source_system_id,

pd.price_type,

pd.basis,

pd.agreement_ftr_id,

pd.as_on_effective_date,

pd.unit_price,

pd.unit_of_measure,

pd.currency_code,

pd.conversion_type,

pd.charge_id,

pd.price_element_code,

fi.flow_instance_id,

fi.event_data_id,

fi.event_definition_id,

fi.task_type,

fi.transaction_type,

fi.status as task_status

FROM fos_pricing_details pd,

fos_flow_instances fi,

fos_doc_flow_assignment dfa,

fos_agreement_ftr_f ftr

WHERE pd.agreement_ftr_id = fi.agreement_ftr_id

AND pd.price_type = 'ACCOUNTING'

AND pd.price_element_code = 'NetTransferPrice'

AND pd.document_id = dfa.source_document_id

AND pd.event_data_id = fi.event_data_id

AND pd.event_definition_id = fi.event_definition_id

AND pd.agreement_ftr_id = ftr.agreement_ftr_id

AND pd.as_on_effective_date BETWEEN ftr.effective_start_date AND ftr.effective_end_date

AND fi.document_flow_assignment_id = dfa.document_flow_assignment_id

AND fi.task_type = 'CST'

AND fi.transaction_type = 'INTRANSIT_RECEIPT'

AND ftr.sequence_number = (

SELECT MAX (ftr1.sequence_number)

FROM fos_agreement_ftr_f ftr1

WHERE ftr1.agreement_ptr_id = ftr.agreement_ptr_id

AND pd.as_on_effective_date BETWEEN ftr1.effective_start_date AND ftr1.effective_end_date

)