FOS_SOURCE_EVENT_INFORMATION_V

Details

• Schema: FUSION

• Object owner: FOS

• Object type: VIEW

Columns

Name

AGREEMENT_NUMBER AGREEMENT_TYPE LAST_UPDATE_DATE OBJECT_VERSION_NUMBER FROM_PRIMARY_BUSINESS_UNIT TO_PRIMARY_BUSINESS_UNIT TRANSACTION_LOG_ID EVENT_TYPE EVENT_DEFINITION_ID EVENT_DATA_ID EVENT_DATE EVENT_NUMBER TRANSACTION_ORG_CODE SOURCE_EVENT_STATUS EVENT_LINE_NUMBER SYSTEM_CODE SYSTEM_ID ORCHESTRATION_STATUS DOCUMENT_SOURCE_SYSTEM_CODE CORRECTION_FLAG PARENT_EVENT_SYSTEM_CODE PARENT_EVENT_TYPE PARENT_EVENT_DEFINITION_ID PARENT_EVENT_DATA_ID REFERENCE_EVENT_DATA_ID REFERENCE_EVENT_TYPE REFERENCE_EVENT_DEFINITION_ID REFERENCE_EVENT_SYSTEM_CODE UNREFERENCED_EVENT_FLAG QUANTITY UOM_CODE UOM ITEM_COST COST_CURRENCY_CODE SECONDARY_QTY SECONDARY_UOM RECEIPT_AMOUNT RECEIPT_CURRENCY_CODE PROCESS_BATCH_ID CONSUMPTION_ORG INTERORG_TRANSACTION DOC_INFO_LOG_ID SOURCE_DOCUMENT_ID DOCUMENT_TYPE DOCUMENT_NUMBER DOCUMENT_DATE DOCUMENT_LINE_NUMBER DOCUMENT_LINE_DETAIL_NUMBER SHIP_FROM_ORGANIZATION_CODE SHIP_TO_ORGANIZATION_CODE CONSIGNED_FLAG DROPSHIP_FLAG ITEM_NUMBER ITEM_DESCRIPTION SUPPLIER_NUMBER SUPPLIER_SITE_CODE PURCHASING_CATEGORY DESTINATION_TYPE ORDERED_QUANTITY ORDERED_UOM UNIT_PRICE PRICE_CURRENCY_CODE ORDERED_AMOUNT ORDERED_CURRENCY_CODE BASE_ITEM_NUMBER AUTO_CREATED_CONFIG_FLAG TRANSFER_ORDER_FLAG SHIP_TO_PARTY_NUMBER SOLD_TO_PARTY_NUMBER SHIP_TO_PARTY_SITE_NUMBER LINK_TO_DOCUMENT_TYPE LINK_TO_DOCUMENT_SYSTEM_CODE LINK_TO_DOCUMENT_ID REF_SALES_ORDER_NUMBER REF_SALES_ORDER_LINE_NUMBER REF_SALES_ORDER_SYSTEM_CODE REFERENCED_RMA_FLAG AGREEMENT_PTR_ID PTR_NUMBER TRANSFER_ORDER_NO SHIP_TO_PARTY_NAME FROM_BU_ID FROM_LE_ID TO_BU_ID TO_LE_ID FROM_BU_NAME TO_BU_NAME FROM_LEGAL_ENTITY TO_LEGAL_ENTITY DOCUMENT_FLOW_ASSIGNMENT_ID MESSAGE_TYPE PROCESSED_FLAG PRICING_UOM_TYPE PRICING_UOM_VALUE PRICING_UOM_TYPE_MEANING PRICING_UNIT_OF_MEASURE DSO_FLAG TRANSACTION_TYPE_ID COUNTRY_OF_ORIGIN_CODE COUNTRY_OF_ORIGIN PJC_PROJECT_ID PROJECT_NUMBER

Query

SQL_Statement

/* 1st Segment (Events before Starting of orchestration) */ SELECT null agreement_number, null agreement_type, sevent.last_update_date, sevent.object_version_number, null from_primary_business_unit, null to_primary_business_unit, sevent.transaction_log_id, sevent.event_type, null event_definition_id, sevent.event_data_id, sevent.event_date, sevent.event_number, sevent.transaction_org_code, sevent.status source_event_status, sevent.event_line_number, sevent.system_code, -1 system_id, DECODE(sevent.status,'EVENT_UNUSED',sevent.status,'NOT_STARTED') orchestration_status, sevent.document_source_system_code, sevent.correction_flag, sevent.parent_event_system_code, sevent.parent_event_type, null parent_event_definition_id, sevent.parent_event_data_id, sevent.reference_event_data_id, sevent.reference_event_type, null reference_event_definition_id, sevent.reference_event_system_code, sevent.unreferenced_event_flag, sevent.quantity, sevent.uom as uom_code, DECODE (sevent.uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sevent.uom)) uom, sevent.item_cost, sevent.cost_currency_code, sevent.secondary_qty, DECODE (sevent.secondary_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sevent.secondary_uom)) secondary_uom, sevent.receipt_amount, sevent.receipt_currency_code, sevent.process_batch_id, DECODE(sevent.event_type,'TRANSFER_TO_OWNED', sevent.transaction_org_code) consumption_org, DECODE(sevent.document_type,'IOT', sevent.source_document_id) interorg_transaction, sedi.doc_info_log_id, sedi.source_document_id, sedi.document_type, sedi.document_number, sedi.document_date, sedi.document_line_number, sedi.document_line_detail_number, sedi.ship_from_organization_code, sedi.ship_to_organization_code, sedi.consigned_flag, sedi.dropship_flag, DECODE (sedi.inventory_item_id, NULL, sedi.item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = sedi.inventory_item_id AND ROWNUM = 1)) item_number, sedi.item_description, sedi.supplier_number, sedi.supplier_site_code, sedi.purchasing_category, sedi.destination_type, DECODE(sedi.dropship_flag,'Y',sedi.ordered_quantity,(DECODE (sedi.document_type,'OO',DECODE (sevent.ordered_uom_quantity,NULL,sedi.ordered_quantity,sevent.ordered_uom_quantity),sedi.ordered_quantity))) as ordered_quantity, DECODE (sedi.ordered_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sedi.ordered_uom)) ordered_uom, DECODE(sedi.dropship_flag,'Y', sedi.unit_price, NVL (sevent.ordered_unit_price, sedi.unit_price)) as unit_price, sedi.price_currency_code, sedi.ordered_amount, sedi.ordered_currency_code, DECODE (sedi.base_inventory_item_id, NULL, sedi.base_item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = sedi.base_inventory_item_id AND ROWNUM = 1)) base_item_number, sedi.auto_created_config_flag, sedi.transfer_order_flag, sedi.ship_to_party_number, sedi.sold_to_party_number, sedi.ship_to_party_site_number, sedi.link_to_document_type, sedi.link_to_document_system_code, sedi.link_to_document_id, sedi.ref_sales_order_number, sedi.ref_sales_order_line_number, sedi.ref_sales_order_system_code, sedi.referenced_rma_flag, null agreement_ptr_id, null ptr_number, DECODE(sedi.transfer_order_flag,'Y', sedi.document_number) transfer_order_no, (SELECT party_name FROM hz_parties WHERE party_number = sedi.ship_to_party_number) AS ship_to_party_name, null FROM_BU_ID, null FROM_LE_ID, null TO_BU_ID, null TO_LE_ID, null from_bu_name, null to_bu_name, null from_legal_entity, null TO_LEGAL_ENTITY, null document_flow_assignment_id, sevent.message_type, sevent.processed_flag, null pricing_uom_type, null pricing_uom_value, null pricing_uom_type_meaning, null pricing_unit_of_measure, DECODE (sevent.transaction_type_id,6,(SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'N')) DSO_Flag, sevent.transaction_type_id, sevent.country_of_origin_code, DECODE (sevent.country_of_origin_code, NULL, NULL, (SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = sevent.country_of_origin_code)) country_of_origin, sevent.pjc_project_id, (SELECT SEGMENT1 FROM PJF_PROJECTS_ALL_VL where project_id =sevent.pjc_project_id) project_number FROM FOS_SOURCE_TXN_EVENTS sevent, FOS_SOURCE_EVENT_DOC_INFO sedi WHERE sevent.source_document_id = sedi.source_document_id AND sevent.document_source_system_code = sedi.document_source_system_code AND sevent.document_type = sedi.document_type AND NOT EXISTS (SELECT 1 FROM fos_event_doc_info a, fos_txn_events b WHERE a.source_document_id = b.source_document_id AND a.document_type=b.document_type AND a.document_source_system_id = b.document_source_system_id AND b.event_data_id = sevent.event_data_id AND (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = b.event_definition_id)= sevent.event_type AND b.system_id = -1 AND a.source_document_id = sevent.source_document_id AND a.document_type = sevent.document_type AND a.document_source_system_id = -1) UNION ALL /* 2nd Segment (Simple flows after agreement identification) */ SELECT agr.AGREEMENT_NUMBER, agr.AGREEMENT_TYPE, (select last_update_date from fos_source_txn_events where transaction_log_id = source_txn_event_id) as last_update_date, tevent.object_version_number, (SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = ptr.from_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date) AS from_primary_business_unit, (SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = ptr.to_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date) AS to_primary_business_unit, tevent.source_txn_event_id transaction_log_id, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.event_definition_id) event_type, tevent.event_definition_id, tevent.event_data_id, tevent.event_date, tevent.event_number, tevent.transaction_org_code, (select status from fos_source_txn_events where transaction_log_id = source_txn_event_id) as source_event_status, tevent.event_line_number, 'NATIVE' system_code, tevent.system_id, DECODE(REPLACE(tevent.dropship_flag || tevent.consigned_flag, 'N', ''), NULL, DECODE(tevent.status, 'COMPLETED', 'SUCCESS', fos_utilities_pkg.get_orchestration_txn_status(tevent.source_txn_event_id,docassign.document_flow_assignment_id,tevent.event_data_id,tevent.event_definition_id,tevent.system_id)), fos_utilities_pkg.get_orchestration_txn_status(tevent.source_txn_event_id,docassign.document_flow_assignment_id,tevent.event_data_id,tevent.event_definition_id,tevent.system_id)) as orchestration_status, 'NATIVE' document_source_system_code, tevent.correction_flag, DECODE(parent_event_system_id,-1,'NATIVE') parent_event_system_code, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.parent_event_definition_id) parent_event_type, tevent.parent_event_definition_id, tevent.parent_event_data_id, tevent.reference_event_data_id, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.reference_event_definition_id) reference_event_type, tevent.reference_event_definition_id, DECODE(reference_event_system_id,-1,'NATIVE') reference_event_system_code, tevent.unreferenced_event_flag, tevent.quantity, tevent.uom as uom_code, DECODE (tevent.uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = tevent.uom)) uom, tevent.item_cost, tevent.cost_currency_code, tevent.secondary_qty, DECODE (tevent.secondary_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = tevent.secondary_uom)) secondary_uom, tevent.receipt_amount, tevent.receipt_currency_code, null process_batch_id, DECODE(tevent.event_definition_id,400, tevent.transaction_org_code) consumption_org, DECODE(tevent.document_type,'IOT', tevent.source_document_id) interorg_transaction, null doc_info_log_id, edi.source_document_id, edi.document_type, edi.document_number, edi.document_date, edi.document_line_number, edi.document_line_detail_number, edi.ship_from_organization_code, edi.ship_to_organization_code, edi.consigned_flag, edi.dropship_flag, DECODE (edi.inventory_item_id, NULL, edi.item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = edi.inventory_item_id AND ROWNUM = 1)) item_number, edi.item_description, edi.supplier_number, edi.supplier_site_code, edi.purchasing_category, edi.destination_type, DECODE(edi.dropship_flag,'Y',edi.ordered_quantity,(DECODE (edi.document_type,'OO',DECODE (tevent.ordered_uom_quantity,NULL,edi.ordered_quantity,tevent.ordered_uom_quantity),edi.ordered_quantity))) as ordered_quantity, DECODE (edi.ordered_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = edi.ordered_uom)) ordered_uom, DECODE(edi.dropship_flag,'Y', edi.unit_price, NVL (tevent.ordered_unit_price, edi.unit_price)) as unit_price, edi.price_currency_code, edi.ordered_amount, edi.ordered_currency_code, DECODE (edi.base_inventory_item_id, NULL, edi.base_item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = edi.base_inventory_item_id AND ROWNUM = 1)) base_item_number, edi.auto_created_config_flag, edi.transfer_order_flag, edi.ship_to_party_number, edi.sold_to_party_number, edi.ship_to_party_site_number, edi.link_to_document_type, DECODE(edi.link_to_document_system_id,-1,'NATIVE') link_to_document_system_code, edi.link_to_document_id, edi.ref_sales_order_number, edi.ref_sales_order_line_number, edi.ref_sales_order_system_code, edi.referenced_rma_flag, tevent.agreement_ptr_id, ptr.ptr_number, DECODE(edi.transfer_order_flag,'Y', edi.document_number) transfer_order_no, (SELECT party_name FROM hz_parties WHERE party_number = edi.ship_to_party_number) AS ship_to_party_name, edi.FROM_BU_ID, edi.FROM_LE_ID, edi.TO_BU_ID, edi.to_le_id, DECODE(edi.from_bu_id, null, null,(SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = edi.from_bu_id AND nvl (docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date)) from_bu_name, DECODE(edi.to_bu_id, null, null,(SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = edi.to_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date)) to_bu_name, DECODE(edi.from_le_id, null, null,(SELECT name FROM xle_entity_profiles WHERE legal_entity_id = edi.from_le_id)) AS from_legal_entity, DECODE(edi.TO_LE_ID, null, null,(SELECT NAME FROM XLE_ENTITY_PROFILES WHERE legal_entity_id = edi.to_le_id)) AS to_legal_entity, docassign.document_flow_assignment_id, tevent.message_type, null processed_flag, edi.pricing_uom_type, edi.pricing_uom_value, DECODE (edi.pricing_uom_type, NULL, NULL, (SELECT meaning FROM fnd_lookup_values_vl WHERE lookup_code = edi.pricing_uom_type AND lookup_type = 'EGP_ITEM_PRICING')) pricing_uom_type_meaning, DECODE (edi.pricing_uom_value, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = edi.pricing_uom_value)) pricing_unit_of_measure, (SELECT DECODE (ste.transaction_type_id, 6, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'N')) FROM fos_source_txn_events ste WHERE ste.transaction_log_id =tevent.source_txn_event_id) DSO_Flag, (select transaction_type_id from fos_source_txn_events where transaction_log_id = tevent.source_txn_event_id) as transaction_type_id, tevent.country_of_origin_code, DECODE (tevent.country_of_origin_code, NULL, NULL, (SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = tevent.country_of_origin_code)) country_of_origin, tevent.pjc_project_id, (SELECT SEGMENT1 FROM PJF_PROJECTS_ALL_VL where project_id =tevent.pjc_project_id) project_number FROM FOS_TXN_EVENTS tevent, FOS_EVENT_DOC_INFO edi, FOS_DOC_FLOW_ASSIGNMENT docassign, FOS_AGREEMENT_PTR_F ptr, FOS_AGREEMENT_DEFINITION_B_F agr WHERE tevent.source_document_id = edi.source_document_id AND tevent.document_type = edi.document_type AND docassign.SOURCE_DOCUMENT_ID (+)= edi.SOURCE_DOCUMENT_ID AND docassign.DOCUMENT_TYPE (+)= edi.DOCUMENT_TYPE AND docassign.SYSTEM_ID(+) = edi.DOCUMENT_SOURCE_SYSTEM_ID AND docassign.EVENT_DATA_ID(+) = tevent.EVENT_DATA_ID AND docassign.EVENT_DEFINITION_ID(+) = tevent.EVENT_DEFINITION_ID AND tevent.AGREEMENT_PTR_ID = ptr.AGREEMENT_PTR_ID AND nvl(docassign.EFFECTIVE_DOCUMENT_DATE,edi.document_date) BETWEEN ptr.EFFECTIVE_START_DATE AND ptr.EFFECTIVE_END_DATE AND ptr.AGREEMENT_ID = agr.AGREEMENT_ID AND nvl(docassign.EFFECTIVE_DOCUMENT_DATE,edi.document_date) BETWEEN agr.EFFECTIVE_START_DATE AND agr.EFFECTIVE_END_DATE UNION ALL /* 3rd Segment (ORA_EVENT_ENRICHMENT_FAILED) */ SELECT null agreement_number, null agreement_type, sevent.last_update_date, sevent.object_version_number, null from_primary_business_unit, null to_primary_business_unit, sevent.transaction_log_id, sevent.event_type, null event_definition_id, sevent.event_data_id, sevent.event_date, sevent.event_number, sevent.transaction_org_code, sevent.status source_event_status, sevent.event_line_number, sevent.system_code, -1 system_id, DECODE(sevent.status,'EVENT_UNUSED',sevent.status,'NOT_STARTED') orchestration_status, sevent.document_source_system_code, sevent.correction_flag, sevent.parent_event_system_code, sevent.parent_event_type, null parent_event_definition_id, sevent.parent_event_data_id, sevent.reference_event_data_id, sevent.reference_event_type, null reference_event_definition_id, sevent.reference_event_system_code, sevent.unreferenced_event_flag, sevent.quantity, sevent.uom as uom_code, null uom, sevent.item_cost, sevent.cost_currency_code, sevent.secondary_qty, null secondary_uom, sevent.receipt_amount, sevent.receipt_currency_code, sevent.process_batch_id, DECODE(sevent.event_type,'TRANSFER_TO_OWNED', sevent.transaction_org_code) consumption_org, null interorg_transaction, NULL, null, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ordered_uom, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, null agreement_ptr_id, null ptr_number, null transfer_order_no, null ship_to_party_name, null FROM_BU_ID, null FROM_LE_ID, null TO_BU_ID, null TO_LE_ID, null from_bu_name, null to_bu_name, null from_legal_entity, null TO_LEGAL_ENTITY, null document_flow_assignment_id, sevent.message_type, sevent.processed_flag, null pricing_uom_type, null pricing_uom_value, null pricing_uom_type_meaning, null pricing_unit_of_measure, DECODE (sevent.transaction_type_id, 6, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'N')) DSO_Flag, sevent.transaction_type_id, sevent.country_of_origin_code, DECODE (sevent.country_of_origin_code, NULL, NULL, (SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = sevent.country_of_origin_code)) country_of_origin, sevent.pjc_project_id, (SELECT SEGMENT1 FROM PJF_PROJECTS_ALL_VL where project_id =sevent.pjc_project_id) project_number FROM FOS_SOURCE_TXN_EVENTS sevent WHERE sevent.status= 'ORA_EVENT_ENRICHMENT_FAILED' UNION ALL /* 4th Segment (For processing linked doc rows before orchetstration) */ SELECT null agreement_number, null agreement_type, sevent.last_update_date, sevent.object_version_number, null from_primary_business_unit, null to_primary_business_unit, sevent.transaction_log_id, sevent.event_type, null event_definition_id, sevent.event_data_id, sevent.event_date, sevent.event_number, sevent.transaction_org_code, sevent.status source_event_status, sevent.event_line_number, sevent.system_code, -1 system_id, DECODE(sevent.status,'EVENT_UNUSED',sevent.status,'NOT_STARTED') orchestration_status, sevent.document_source_system_code, sevent.correction_flag, sevent.parent_event_system_code, sevent.parent_event_type, null parent_event_definition_id, sevent.parent_event_data_id, sevent.reference_event_data_id, sevent.reference_event_type, null reference_event_definition_id, sevent.reference_event_system_code, sevent.unreferenced_event_flag, sevent.quantity, sevent.uom as uom_code, DECODE (sevent.uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sevent.uom)) uom, sevent.item_cost, sevent.cost_currency_code, sevent.secondary_qty, DECODE (sevent.secondary_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sevent.secondary_uom)) secondary_uom, sevent.receipt_amount, sevent.receipt_currency_code, sevent.process_batch_id, DECODE(sevent.event_type,'TRANSFER_TO_OWNED', sevent.transaction_org_code) consumption_org, DECODE(sevent.document_type,'IOT', sevent.source_document_id) interorg_transaction, sedi.doc_info_log_id, sedi.source_document_id, sedi.document_type, sedi.document_number, sedi.document_date, sedi.document_line_number, sedi.document_line_detail_number, sedi.ship_from_organization_code, sedi.ship_to_organization_code, sedi.consigned_flag, sedi.dropship_flag, DECODE (sedi.inventory_item_id, NULL, sedi.item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = sedi.inventory_item_id AND ROWNUM = 1)) item_number, sedi.item_description, sedi.supplier_number, sedi.supplier_site_code, sedi.purchasing_category, sedi.destination_type, DECODE(sedi.dropship_flag,'Y',sedi.ordered_quantity,(DECODE (sedi.document_type,'OO',DECODE (sevent.ordered_uom_quantity,NULL,sedi.ordered_quantity,sevent.ordered_uom_quantity),sedi.ordered_quantity))) as ordered_quantity, DECODE (sedi.ordered_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = sedi.ordered_uom)) ordered_uom, DECODE(sedi.dropship_flag,'Y', sedi.unit_price, NVL (sevent.ordered_unit_price, sedi.unit_price)) as unit_price, sedi.price_currency_code, sedi.ordered_amount, sedi.ordered_currency_code, DECODE (sedi.base_inventory_item_id, NULL, sedi.base_item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = sedi.base_inventory_item_id AND ROWNUM = 1)) base_item_number, sedi.auto_created_config_flag, sedi.transfer_order_flag, sedi.ship_to_party_number, sedi.sold_to_party_number, sedi.ship_to_party_site_number, sedi.link_to_document_type, sedi.link_to_document_system_code, sedi.link_to_document_id, sedi.ref_sales_order_number, sedi.ref_sales_order_line_number, sedi.ref_sales_order_system_code, sedi.referenced_rma_flag, null agreement_ptr_id, null ptr_number, DECODE(sedi.transfer_order_flag,'Y', sedi.document_number) transfer_order_no, (SELECT party_name FROM hz_parties WHERE party_number = sedi.ship_to_party_number) AS ship_to_party_name, null FROM_BU_ID, null FROM_LE_ID, null TO_BU_ID, null TO_LE_ID, null from_bu_name, null to_bu_name, null from_legal_entity, null TO_LEGAL_ENTITY, null document_flow_assignment_id, sevent.message_type, sevent.processed_flag, null pricing_uom_type, null pricing_uom_value, null pricing_uom_type_meaning, null pricing_unit_of_measure , DECODE (sevent.transaction_type_id, 6, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'N')) DSO_Flag, sevent.transaction_type_id, sevent.country_of_origin_code, DECODE (sevent.country_of_origin_code, NULL, NULL, (SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = sevent.country_of_origin_code)) country_of_origin, sevent.pjc_project_id, (SELECT SEGMENT1 FROM PJF_PROJECTS_ALL_VL where project_id =sevent.pjc_project_id) project_number FROM FOS_SOURCE_TXN_EVENTS sevent, FOS_SOURCE_EVENT_DOC_INFO sedi WHERE sevent.event_type NOT IN ('RETURN_TO_SUPPLIER','RMA_DELIVERY') AND sevent.source_document_id = sedi.link_to_document_id AND sevent.document_source_system_code = sedi.link_to_document_system_code AND sevent.document_type = sedi.link_to_document_type AND NOT EXISTS (SELECT 1 FROM fos_event_doc_info a, fos_txn_events b WHERE a.source_document_id = b.source_document_id AND a.document_type=b.document_type AND a.document_source_system_id = b.document_source_system_id AND b.event_data_id = sevent.event_data_id AND (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = b.event_definition_id)= sevent.event_type AND b.system_id = -1 AND a.source_document_id = sevent.source_document_id AND a.document_type = sevent.document_type AND a.document_source_system_id = -1) UNION ALL /* 5th Segment (For processing OO row in DS case and IOT row in Consigned case after agreement identification) */ SELECT agr.AGREEMENT_NUMBER, agr.AGREEMENT_TYPE, (select last_update_date from fos_source_txn_events where transaction_log_id = source_txn_event_id) as last_update_date, tevent.object_version_number, (SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = ptr.from_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date) AS from_primary_business_unit, (SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = ptr.to_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date) AS to_primary_business_unit, tevent.source_txn_event_id transaction_log_id, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.event_definition_id) event_type, tevent.event_definition_id, tevent.event_data_id, tevent.event_date, tevent.event_number, tevent.transaction_org_code, (select status from fos_source_txn_events where transaction_log_id = source_txn_event_id) as source_event_status, tevent.event_line_number, 'NATIVE' system_code, tevent.system_id, DECODE(REPLACE(tevent.dropship_flag || tevent.consigned_flag, 'N', ''), NULL, DECODE(tevent.status, 'COMPLETED', 'SUCCESS', fos_utilities_pkg.get_orchestration_txn_status(tevent.source_txn_event_id,docassign.document_flow_assignment_id,tevent.event_data_id,tevent.event_definition_id,tevent.system_id)), fos_utilities_pkg.get_orchestration_txn_status(tevent.source_txn_event_id,docassign.document_flow_assignment_id,tevent.event_data_id,tevent.event_definition_id,tevent.system_id)) as orchestration_status, 'NATIVE' document_source_system_code, tevent.correction_flag, DECODE(parent_event_system_id,-1,'NATIVE') parent_event_system_code, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.parent_event_definition_id) parent_event_type, tevent.parent_event_definition_id, tevent.parent_event_data_id, tevent.reference_event_data_id, (SELECT event_type FROM fos_event_definitions_b WHERE event_definition_id = tevent.reference_event_definition_id) reference_event_type, tevent.reference_event_definition_id, DECODE(reference_event_system_id,-1,'NATIVE') reference_event_system_code, tevent.unreferenced_event_flag, tevent.quantity, tevent.uom as uom_code, DECODE (tevent.uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = tevent.uom)) uom, tevent.item_cost, tevent.cost_currency_code, tevent.secondary_qty, DECODE (tevent.secondary_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = tevent.secondary_uom)) secondary_uom, tevent.receipt_amount, tevent.receipt_currency_code, null process_batch_id, DECODE(tevent.event_definition_id,400, tevent.transaction_org_code) consumption_org, DECODE(tevent.document_type,'IOT', tevent.source_document_id) interorg_transaction, null doc_info_log_id, edi.source_document_id, edi.document_type, edi.document_number, edi.document_date, edi.document_line_number, edi.document_line_detail_number, edi.ship_from_organization_code, edi.ship_to_organization_code, edi.consigned_flag, edi.dropship_flag, DECODE (edi.inventory_item_id, NULL, edi.item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = edi.inventory_item_id AND ROWNUM = 1)) item_number, edi.item_description, edi.supplier_number, edi.supplier_site_code, edi.purchasing_category, edi.destination_type, DECODE(edi.dropship_flag,'Y',edi.ordered_quantity,(DECODE (edi.document_type,'OO',DECODE (tevent.ordered_uom_quantity,NULL,edi.ordered_quantity,tevent.ordered_uom_quantity),edi.ordered_quantity))) as ordered_quantity, DECODE (edi.ordered_uom, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = edi.ordered_uom)) ordered_uom, DECODE(edi.dropship_flag,'Y', edi.unit_price, NVL (tevent.ordered_unit_price, edi.unit_price)) as unit_price, edi.price_currency_code, edi.ordered_amount, edi.ordered_currency_code, DECODE (edi.base_inventory_item_id, NULL, edi.base_item_number, (SELECT egp.item_number FROM egp_system_items_vl egp WHERE egp.inventory_item_id = edi.base_inventory_item_id AND ROWNUM = 1)) base_item_number, edi.auto_created_config_flag, edi.transfer_order_flag, edi.ship_to_party_number, edi.sold_to_party_number, edi.ship_to_party_site_number, edi.link_to_document_type, DECODE(edi.link_to_document_system_id,-1,'NATIVE') link_to_document_system_code, edi.link_to_document_id, edi.ref_sales_order_number, edi.ref_sales_order_line_number, edi.ref_sales_order_system_code, edi.referenced_rma_flag, edi.agreement_ptr_id, ptr.ptr_number, DECODE(edi.transfer_order_flag,'Y', edi.document_number) transfer_order_no, (SELECT party_name FROM hz_parties WHERE party_number = edi.ship_to_party_number) AS ship_to_party_name, edi.FROM_BU_ID, edi.FROM_LE_ID, edi.TO_BU_ID, edi.to_le_id, DECODE(edi.from_bu_id, null, null,(SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = edi.from_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date)) from_bu_name, DECODE(edi.to_bu_id, null, null,(SELECT name FROM hr_all_organization_units_f_vl WHERE organization_id = edi.to_bu_id AND nvl(docassign.effective_document_date,edi.document_date) BETWEEN effective_start_date and effective_end_date)) to_bu_name, DECODE(edi.from_le_id, null, null,(SELECT name FROM xle_entity_profiles WHERE legal_entity_id = edi.from_le_id)) AS from_legal_entity, DECODE(edi.TO_LE_ID, null, null,(SELECT NAME FROM XLE_ENTITY_PROFILES WHERE legal_entity_id = edi.to_le_id)) AS to_legal_entity, docassign.document_flow_assignment_id, tevent.message_type, null processed_flag, edi.pricing_uom_type, edi.pricing_uom_value, DECODE (edi.pricing_uom_type, NULL, NULL, (SELECT meaning FROM fnd_lookup_values_vl WHERE lookup_code = edi.pricing_uom_type AND lookup_type = 'EGP_ITEM_PRICING')) pricing_uom_type_meaning, DECODE (edi.pricing_uom_value, NULL, NULL, (SELECT unit_of_measure FROM inv_units_of_measure_vl WHERE uom_code = edi.pricing_uom_value)) pricing_unit_of_measure, (SELECT DECODE (ste.transaction_type_id, 6, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'Y'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' AND lookup_code = 'N')) FROM fos_source_txn_events ste WHERE ste.transaction_log_id = tevent.source_txn_event_id) DSO_Flag, (select transaction_type_id from fos_source_txn_events where transaction_log_id = tevent.source_txn_event_id) as transaction_type_id, tevent.country_of_origin_code, DECODE (tevent.country_of_origin_code, NULL, NULL, (SELECT territory_short_name FROM fnd_territories_vl WHERE territory_code = tevent.country_of_origin_code)) country_of_origin, tevent.pjc_project_id, (SELECT SEGMENT1 FROM PJF_PROJECTS_ALL_VL where project_id =tevent.pjc_project_id) project_number FROM FOS_TXN_EVENTS tevent, FOS_EVENT_DOC_INFO edi, FOS_DOC_FLOW_ASSIGNMENT docassign, FOS_AGREEMENT_PTR_F ptr, FOS_AGREEMENT_DEFINITION_B_F agr WHERE tevent.event_definition_id NOT IN (700,2300) AND tevent.source_document_id = edi.link_to_document_id AND tevent.document_type = edi.link_to_document_type AND docassign.SOURCE_DOCUMENT_ID (+)= edi.SOURCE_DOCUMENT_ID AND docassign.DOCUMENT_TYPE (+)= edi.DOCUMENT_TYPE AND docassign.SYSTEM_ID(+) = edi.DOCUMENT_SOURCE_SYSTEM_ID AND docassign.EVENT_DATA_ID(+) = tevent.EVENT_DATA_ID AND docassign.EVENT_DEFINITION_ID(+) = tevent.EVENT_DEFINITION_ID AND edi.AGREEMENT_PTR_ID = ptr.AGREEMENT_PTR_ID AND nvl(docassign.EFFECTIVE_DOCUMENT_DATE,edi.document_date) BETWEEN ptr.EFFECTIVE_START_DATE AND ptr.EFFECTIVE_END_DATE AND ptr.AGREEMENT_ID = agr.AGREEMENT_ID AND nvl(docassign.EFFECTIVE_DOCUMENT_DATE,edi.document_date) BETWEEN agr.EFFECTIVE_START_DATE AND agr.EFFECTIVE_END_DATE