> 持续流程改进 > 记录到报告，日记账分录 > Oracle Fusion Financials > 数据模板

您的Record to Report－Journal Entry数据模板

Oracle Fusion Financials

← 返回改进指南

记录到报告，日记账分录属性 (21) 记录到报告，日记账分录活动 (12) 数据提取指南 (4)

您的Record to Report－Journal Entry数据模板

此数据模板为提取和整理Oracle Fusion Financials Journal Entry数据提供完整指南，说明需要收集的关键属性和需要跟踪的主要活动，帮助您获取流程分析所需的全部信息。您还将了解如何直接从系统中提取这些数据。

建议收集的属性
需要跟踪的主要活动
Oracle Fusion Financials数据提取指南

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

记录到报告，日记账分录属性

以下是建议纳入事件日志的数据字段，可用于全面分析记录到报告，日记账分录流程。

5 必需 6 建议 10 可选

	名称	说明
	开始时间 EventTime	表示特定活动或事件发生时间的时间戳。
说明此时间戳记录活动执行的确切日期和时间。在流程挖掘中，它是确定事件顺序和计算事件间时长的主要时间要素。事件时间的准确性对所有基于时间的分析都至关重要，包括计算周期时间、识别瓶颈以及监控相对于服务级别协议的绩效。它提供了按实际发生顺序重建流程所需的时间线。为什么重要此时间戳对于排列事件顺序、计算所有流程时长以及执行任何基于时间的分析都不可或缺。获取位置此信息通常存储在审计轨迹表中，或作为特定事件对应交易表（如GL_JE_HEADERS和GL_JE_LINES）中的“Last Update Date”或“Creation Date”保存。示例 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:00Z
	日记账分录ID JournalEntryId	单条日记账分录的唯一标识符，用于关联所有相关财务交易活动。
说明 Journal Entry ID是主要案例标识符，用于唯一关联特定财务交易集合中的所有相关活动。借助它，您可以跟踪单条日记账分录从启动到最终过账的完整生命周期，确保所有借方和贷方均得到记录。在流程挖掘中，该ID对于重建每条日记账分录的端到端路径至关重要。它将“日记账分录已创建”“日记账分录已提交审批”和“日记账分录已过账”等不同事件连接成连贯的流程，从而支持周期时间、瓶颈和流程变体分析。为什么重要这是从头到尾跟踪日记账分录的基础键，使您能够分析每个唯一案例的完整流程。获取位置这是核心总账表中的主键，例如GL_JE_HEADERS和GL_JE_LINES。示例 JE100523JE202311001882019
	活动 ActivityName	日记账分录流程中某个时间点发生的具体业务事件或任务名称。
说明 Activity Name描述日记账分录生命周期中的单个步骤，例如“日记账分录已创建”或“日记账分录已批准”。这些数据对于构建流程图和了解事件顺序至关重要。分析活动可以识别流程路径，包括标准路径、偏差和返工循环。通过跟踪不同活动，我们可以衡量审批等特定阶段的耗时，并识别最耗时或最容易出错的步骤。为什么重要它定义流程步骤，构成流程图的骨架，并支持对流转、瓶颈和变体进行分析。获取位置此属性通常源自总账模块中与日记账分录对象关联的状态变化、事件日志或审计轨迹表。示例日记账分录已创建日记账分录已提交审批日记账分录已批准日记账分录已过账
	最近数据更新时间 LastDataUpdate	源系统最近一次刷新或提取数据的时间戳。
说明此属性记录数据集最近一次更新的时间，为正在分析的数据新鲜度提供背景信息，这对于判断所得洞察的时效性十分重要。在任何分析中，尤其是运营仪表板中，了解最近数据更新时间对于建立用户对数据的信任并支持理性决策至关重要。它清晰说明了流程挖掘模型所包含数据的截止时间。为什么重要表示数据的新鲜度，帮助用户了解流程分析的时效性，并放心使用分析洞察。获取位置此值在数据提取和转换过程中生成并存储，通常是ETL/ELT作业完成时的时间戳。示例 2023-11-20T08:00:00Z2023-11-21T08:00:00Z2023-11-22T08:00:00Z
	源系统 SourceSystem	数据来源的系统或模块。
说明此属性用于标识流程数据的提取来源。在复杂的IT环境中，数据可能来自多个集成系统，该字段有助于区分不同来源。在流程分析中，了解源系统对于理解由不同系统行为导致的流程变体至关重要。它还可以通过追溯数据来源，帮助完成数据验证和问题排查。为什么重要标识数据来源，对于数据治理、验证以及了解不同系统间的流程变体至关重要。获取位置这通常是在数据提取过程中配置的静态值，也可能是源表中用于标识录入系统的字段。示例 Oracle Fusion Financials CloudOracle EBS R12Fusion GL
	拒绝原因 RejectionReason	用于说明日记账分录被拒绝原因的文本描述或代码。
说明日记账分录在审批过程中被拒绝时，此属性会记录拒绝原因。原因可以是预定义代码，也可以是审批人的自由文本备注。这是分析流程低效根因的关键属性之一。通过分析最常见的拒绝原因，组织可以识别改进方向，例如加强制单人员培训、明确操作指南或完善系统控制。它直接支持“日记账分录拒绝率分析”仪表板。为什么重要直接揭示返工和流程延迟的根因，支持有针对性地改进流程并降低拒绝率。获取位置此信息可能存储在与审批流程关联的工作流表或审计轨迹表中，也可能位于日记账头的备注字段中。示例科目组合错误缺少支持性文档超出预算
	日记账来源 JournalSource	生成日记账分录的子分类账或来源，例如“应付账款”“应收账款”或“手动”。
说明此属性表示ERP系统中日记账分录的来源。分录可以在总账中手动创建，也可以由应付账款、应收账款或固定资产等子分类账自动生成。 Journal Source是自动化分析的关键属性，有助于区分手动分录和系统生成的分录，并支持“自动化日记账分录率”KPI。不同来源的分录，其流程复杂度和效率通常存在显著差异。为什么重要区分手动分录和自动化分录，这对于衡量自动化率和分析流程差异至关重要。获取位置可在GL_JE_HEADERS表中找到，通常位于名为JE_SOURCE的字段中。示例手动应付款项资产应收款项
	日记账类别 JournalCategory	日记账分录的类别，例如“应计”“调整”或“重分类”。
说明 Journal Category根据业务目的对分录进行分类。借助此分类，可以更细致地分析流程，因为不同类别可能具有不同的流程路径、审批规则或周期时间。例如，月末调整日记账的流程可能比常规应计分录更复杂、更紧迫。按类别分析流程，有助于发现这些差异，并针对不同类型的日记账分录制定改进措施。为什么重要支持按日记账业务目的细分流程，揭示不同分录类型的行为和绩效差异。获取位置可在GL_JE_HEADERS表中找到，字段名称通常为JE_CATEGORY。示例应计手动调整重估
	日记账金额 JournalAmount	日记账分录的货币总值，通常为借方金额之和。
说明该属性表示日记账分录中发生交易的财务总额。金额可能是影响流程的重要因素。例如，高金额分录可能需要额外的审批步骤或更严格的审核。分析Journal Amount可以根据财务影响对案例进行分组，有助于回答“高金额分录是否需要更长时间才能获批？”或“超过特定阈值的分录是否更容易被拒绝？”等问题，从而为流程顺序提供重要的业务背景。为什么重要提供关键业务背景，使您能够基于财务影响开展分析，并识别高金额分录是否遵循不同流程。获取位置通常根据给定日记账分录在GL_JE_LINES表中的借方或贷方金额之和计算。示例 5000.00125000.75750.50
	用户 UserName	执行活动的用户，例如创建、审批或过账日记账分录的用户。
说明此属性用于标识负责执行活动的具体员工或系统用户，对于了解工作负载分配、绩效和识别个人瓶颈至关重要。在分析中，UserName支持按用户筛选流程、比较用户效率以及调查延误或错误原因。它直接支持“跨团队用户工作负载与效率”等仪表板，以及“用户日记账瓶颈指数”等KPI。为什么重要标识流程步骤的责任归属，支持分析用户工作负载、个人绩效和培训需求。获取位置可在GL_JE_HEADERS等交易表（例如CREATED_BY、LAST_UPDATED_BY）及相关审计轨迹表中找到。示例 john.doesusan.smithautoprocess_user
	用户部门 UserDepartment	执行活动的用户所属部门或团队。
说明此属性通过将活动关联到特定部门提供组织背景，例如“财务”“会计运营”或“内部审计”。它通常源自用户主数据。按用户部门分析，有助于识别跨部门瓶颈、比较团队绩效，并了解流程执行在组织不同部门间的差异。这对于资源分配和有针对性的流程改进计划很有价值。为什么重要提供组织背景，支持按团队或部门分析绩效，并突出跨职能低效问题。获取位置交易表中通常不包含此信息。必须将UserName与用户主数据表或HR系统数据连接后获取。示例总账会计财务报告应付账款
	会计期间 AccountingPeriod	日记账分录过账所属的财务期间，例如“Jan-24”。
说明 Accounting Period用于指定交易确认所属的财务期间，是所有财务报告和分析的基础。在流程挖掘中，此属性对于趋势分析至关重要。它支持跨不同月份或季度比较流程表现，例如周期时间或拒绝率，有助于识别季节性影响，例如月末结账期间工作量增加，并衡量流程改进随时间产生的效果。为什么重要支持对KPI进行时间趋势分析，帮助衡量流程改进效果，并识别月末压力等季节性模式。获取位置可在GL_JE_HEADERS表中找到，通常位于名为PERIOD_NAME的字段中。示例 2024年1月2024年2月2024年3月
	冲销标识 ReversalIndicator	用于标识该日记账分录是否为另一笔分录的冲销分录。
说明此布尔属性用于识别为冲销此前已过账分录而创建的日记账分录。冲销分录属于特殊类型，通常遵循独立且有时存在问题的流程。分析此标识对于支持“日记账冲销流程绩效”仪表板至关重要。它支持单独分析冲销流程，衡量其频率、原因和周期时间，并识别原始分录中导致冲销的潜在问题。为什么重要有助于单独分析冲销流程，而冲销通常意味着原始交易存在错误或问题。获取位置可以通过GL_JE_HEADERS表中的特定标识字段（例如ACCRUAL_REV_FLAG）获取，也可以通过指向被冲销原始日记账的关联关系识别。示例 truefalse
	币种 CurrencyCode	日记账分录金额的币种代码，例如USD、EUR或GBP。
说明 Currency Code用于指定日记账分录中财务金额的币种。对于使用多种币种进行交易的跨国组织，这一属性必不可少。此属性可确保正确解读财务金额，支持按币种筛选，也是跨不同地区比较或汇总货币金额时所必需的字段。为什么重要为所有财务金额提供必要背景，确保准确解读，并支持针对不同币种开展分析。获取位置可在GL_JE_HEADERS表中找到，通常位于名为CURRENCY_CODE的字段中。示例 USDEURGBPJPY
	是否按时过账 IsOnTimePosting	如果日记账在目标日期当天或之前完成过账，则该计算标识为true。
说明此布尔属性通过比较“日记账分录已过账”活动的时间戳与“目标过账日期”得出。如果过账时间不晚于目标日期，则值为true，否则为false。此属性直接支持“按时过账日记账分录率”KPI，简化计算过程。它还支持轻松筛选和分析延迟分录，以识别常见延迟原因，例如特定日记账类别或审批人。为什么重要为过账绩效提供清晰的二元结果，简化按时率及延迟根因分析。获取位置此属性由流程挖掘工具计算，需要“TargetPostingDate”属性以及“日记账分录已过账”活动的时间戳。示例 truefalse
	是否返工 IsRework	如果日记账分录经历过拒绝和更正循环，则该计算标识为true。
说明该布尔属性用于识别经历过返工的案例。通常，只要某项活动发生在“日记账分录已拒绝”事件之后，就会将其标记为“true”，例如“日记账分录已更正并重新提交”。 Is Rework是一个重要的分析属性，可用于量化返工循环的数量和影响。它直接支持“平均日记账返工率”KPI，并有助于比较返工案例与首次即正确处理案例的流程顺序和持续时间。为什么重要直接标记经历过低效返工循环的案例，便于量化流程失败的频率及影响。获取位置源系统中不提供此属性，而是由流程挖掘工具分析每个案例的活动序列后计算得出。示例 truefalse
	目标过账日期 TargetPostingDate	预计完成日记账分录过账的预定日期。
说明 Target Posting Date表示日记账分录应过账至总账的截止日期，通常由服务级别协议（SLA）或月末结账计划确定，可作为衡量绩效的基准。此属性对于计算“按时过账日记账分录率”KPI至关重要。通过将实际过账日期与目标日期比较，系统可以自动标记分录是否按时完成，从而清晰衡量流程对计划的遵循情况。为什么重要定义过账的服务级别协议或截止日期，支持计算按时完成绩效KPI。获取位置这可能不是标准字段，也可能根据创建日期、会计期间或日记账类别等业务规则推导得出。示例 2023-10-312023-11-302024-01-05
	端到端周期时间 EndToEndCycleTime	从创建日记账分录到最终完成对账的总计算时长。
说明此指标表示日记账分录在整个流程中耗费的总时间，从最初的“日记账分录已创建”事件开始，到最终的“日记账分录已完成对账”事件结束，全面反映流程绩效。这是衡量整体流程效率的主要KPI，用于趋势仪表板，跟踪改进措施随时间产生的影响，并为衡量所有流程变更提供基准。为什么重要从整体层面衡量流程健康度和效率，是管理层报告的重要指标。获取位置此指标由流程挖掘平台根据每个案例首个事件和最后一个事件的时间戳差值计算。示例 P10DT5HP4DT12HP22D
	结束时间 EndTime	表示特定活动完成时间的时间戳。
说明结束时间标志着活动完成。开始时间记录活动起点，结束时间则提供终点，从而可以精确计算该步骤的处理时间。这对于计算活动处理时间至关重要，也是识别瓶颈和衡量资源效率的核心指标。例如，“日记账分录已审核”活动的结束时间与开始时间之差，就是审核人员实际处理该任务的时间。为什么重要它支持计算单个活动的实际持续时间或处理时间，这是识别效率问题的关键。获取位置与开始时间类似，此信息通常位于审计轨迹表中，也可以根据流程中后续活动的开始时间推断。示例 2023-10-26T10:15:00Z2023-11-15T14:55:10Z2024-01-05T09:22:00Z
	账簿名称 LedgerName	记录日记账分录的总账名称。
说明 Ledger Name用于标识日记账分录所属的具体账簿。对于拥有多个法人实体或多种报告要求的组织，可能存在多个账簿，例如用于企业会计的主账簿，以及用于本地法定报告的辅助账簿。此属性对于跨不同法人实体或业务单元筛选和比较流程至关重要。它可确保分析在正确的组织背景下进行，对于财务合规和报告尤其重要。为什么重要支持跨不同法人实体或会计框架筛选和比较流程分析，这对大型组织至关重要。获取位置可在GL_JE_HEADERS表中找到，通常通过LEDGER_ID关联，并可与GL_LEDGERS连接以获取名称。示例美国主账簿英国法定账簿全球合并账簿
	过账状态 PostingStatus	日记账分录当前的过账状态，例如“未过账”或“已过账”。
说明此属性反映日记账分录在总账中的过账状态，是判断分录所处生命周期阶段的重要指标，尤其适用于正在处理的案例。 Posting Status是“实时日记账分录状态跟踪”仪表板的关键属性，可展示所有活动分录的当前状态。它有助于监控未过账日记账积压，并识别流程最后阶段的延迟。为什么重要表示日记账当前所处状态，对于监控积压和跟踪进行中分录的状态至关重要。获取位置可在GL_JE_HEADERS表中找到，通常位于STATUS或POSTING_STATUS等列中。示例已过账未过账错误

必需建议可选

记录到报告，日记账分录活动

以下是建议在事件日志中记录的关键流程步骤和里程碑，可用于准确发现日记账分录流程。

5 建议 7 可选

	活动	说明
	日记账分录已创建	此活动标志着日记账分录流程的启动。它表示用户创建新的日记账头并开始录入数据，但尚未提交审核或审批。
为什么重要这是流程的主要开始事件。分析从此活动到后续步骤所需的时间，有助于衡量初始数据录入效率和整体流程周期时间。获取位置此事件通常根据GL_JE_HEADERS表中特定Journal Entry ID对应的创建日期时间戳推断得出。创建记录的用户也可在该表中获取。采集使用GL_JE_HEADERS表中的CREATION_DATE。事件类型 inferred
	日记账分录已对账	日记账分录已在期末账户对账流程中完成匹配和核销。这表明该交易与银行对账单等其他财务数据一致。
为什么重要此活动是日记账生命周期的真正终点。衡量从过账到对账的时间，是评估财务结账流程效率的关键KPI。获取位置此事件通常记录在Oracle Financial Consolidation and Close Cloud Service（FCCS）或Account Reconciliation Cloud Service（ARCS）中，而不是总账本身。您可以根据与日记账关联的对账记录状态变化进行推断。采集将日记账数据与ARCS或FCCS表中的对账状态更新进行关联。事件类型 inferred
	日记账分录已批准	指定审批人正式批准日记账分录，确认其准确性和有效性。这是审批工作流的最后一步，之后即可进行过账。
为什么重要此里程碑标志着审批流程结束。提交到批准之间的时间是衡量工作流效率和识别审批瓶颈的关键KPI。获取位置当GL_JE_HEADERS表中的APPROVAL_STATUS_CODE更新为“APPROVED”时，可根据状态变化推断此事件。工作流表中包含审批人的身份和时间戳。采集跟踪GL_JE_HEADERS中APPROVAL_STATUS_CODE变更为“APPROVED”时的时间戳。事件类型 inferred
	日记账分录已提交审批	用户正式将已完成的日记账分录提交至审批工作流时，会发生此活动。日记账由草稿或未完成状态转为待审批状态。
为什么重要这是一个关键里程碑，标志着审批周期计时开始，可用于识别瓶颈。它将数据录入阶段与审核审批阶段区分开来。获取位置此事件可根据GL_JE_HEADERS表中的状态变化推断，具体表现为APPROVAL_STATUS_CODE变更为“REQUIRED”或“INITIATED”等值。系统也可能记录提交日期时间戳。采集跟踪GL_JE_HEADERS中APPROVAL_STATUS_CODE变更为提交状态时的时间戳。事件类型 inferred
	日记账分录已过账	日记账分录的财务数据已成功记录到总账中。借方和贷方金额现已反映在账户余额中。
为什么重要这是一个关键里程碑，表示日记账已进入正式财务记录。它对于衡量按时过账率以及从创建到过账的总耗时至关重要。获取位置当GL_JE_HEADERS表中的STATUS字段变更为“P”（Posted）时，可根据状态变化推断此事件。GL_JE_BATCHES表中也包含过账状态。采集跟踪GL_JE_HEADERS中STATUS变更为“P”时的时间戳。事件类型 inferred
	已附加支持性文档	表示将发票、电子表格等支持性文档附加到日记账分录的操作。这样通常是为了向审计人员和审批人提供背景信息及证据。
为什么重要跟踪此活动有助于判断延误是否由文档缺失造成，也能了解日记账分录进入审批工作流前的合规性和完整性。获取位置由于它通常不是独立记录的事件，因此较难跟踪。您可以根据FND_ATTACHED_DOCUMENTS等附件表中的时间戳进行推断，并将其关联到GL_JE_HEADERS中的日记账记录。采集根据与日记账关联的FND_ATTACHED_DOCUMENTS记录创建日期进行推断。事件类型 inferred
	日记账冲销已处理	系统已创建并过账冲销日记账分录，在后续期间抵销原日记账的财务影响。这是应计项目中的常见操作。
为什么重要跟踪冲销有助于识别经常被冲销的分录类型，并分析冲销流程本身的效率，也可以揭示应计项目管理中的问题。获取位置通过识别与原日记账明确关联、用于冲销原日记账的新日记账分录，可以推断此事件。GL_JE_HEADERS表中的REVERSAL_PERIOD和REVERSAL_FLAG等字段可用于识别和关联这些分录。采集识别新日记账的创建日期和过账日期，其头信息应引用原日记账已被冲销。事件类型 inferred
	日记账分录已审核	此审核步骤可能发生在正式审批之前，也可能属于正式审批的一部分。它表示同事或经理在分录进入最终审批人环节前，对其准确性和合规性进行检查。
为什么重要单独分析此活动，有助于区分初步审核时间和最终审批时间。如果审核阶段较为非正式但耗时较长，也可以发现隐藏瓶颈。获取位置在多阶段审批工作流中，这可能是一个明确步骤，并记录在工作流历史表中。如果审核属于非正式操作，则不会被记录。若最终审批决定前记录了特定用户操作，也可以据此推断。采集分析工作流历史表，查找最终“Approved”状态之前的中间审批或审核步骤。事件类型 inferred
	日记账分录已拒绝	审批人审核日记账分录后，因错误、缺少文档或违反政策而拒绝该分录。此操作会将日记账退回创建人进行更正。
为什么重要此活动对于分析返工循环、拒绝率和一次通过率至关重要。频繁被拒通常表明数据质量或培训存在问题。获取位置当GL_JE_HEADERS表中的APPROVAL_STATUS_CODE更新为“REJECTED”时，可根据状态变化推断此事件。工作流历史会记录执行该操作的用户和时间戳。采集跟踪GL_JE_HEADERS中APPROVAL_STATUS_CODE设置为“REJECTED”时的时间戳。事件类型 inferred
	日记账已更正并重新提交	日记账分录被拒后，创建人进行必要更正并重新提交审批。此活动表示同一日记账开始新的审批周期。
为什么重要跟踪返工对于了解流程低效至关重要。结合“日记账分录已拒绝”这一活动，可以衡量返工时间和频率。获取位置这是日记账处于“REJECTED”状态后再次发生的“日记账分录已提交审批”事件。通过分析单个Journal Entry ID的状态变化序列即可识别。采集识别同一案例ID中发生在拒绝事件之后的提交事件时间戳。事件类型 inferred
	日记账过账已启动	已开始将获批日记账过账至总账。此步骤可以自动执行，也可以手动执行，将日记账加入队列，等待过账程序处理。
为什么重要此活动将审批与技术过账流程区分开来。审批完成到启动过账之间的延误，可能表明过账引擎存在调度问题或资源限制。获取位置可以根据GL_JE_BATCHES表中的状态变化推断，也可以使用与日记账批次关联的过账并发请求提交时间。采集确定特定日记账批次对应的General Ledger Posting程序请求提交时间。事件类型 inferred
	过账已验证	这是过账后的验证步骤，由用户或系统确认日记账已正确过账且余额符合预期。此步骤通常属于手动控制环节。
为什么重要分析此活动有助于了解过账后手动控制和质量保证所耗费的时间，也可以发现自动化验证流程的机会。获取位置这不太可能是Oracle Fusion中的明确事件。通常需要根据其他操作进行推断，例如用户运行特定报告，或更新自定义状态字段，但后者并非标准功能。采集需要使用自定义逻辑，例如跟踪验证报告的运行时间，或描述性弹性域的更新时间。事件类型 inferred

建议可选

数据提取指南

如何从Oracle Fusion Financials获取数据

步骤

进入Reports and Analytics工作区：登录Oracle Fusion Financials。使用Navigator菜单进入Tools，然后选择Reports and Analytics。这里是创建和管理所有分析与报表的中心。
创建BI Publisher数据模型：对于此类事件日志提取，与其从OTBI Analysis开始，不如直接创建BI Publisher Data Model。这样可以使用包含UNION ALL语句的自定义SQL查询。点击“Browse Catalog”，进入合适的文件夹，然后选择Create > Data Model。
定义SQL查询数据集：在Data Model编辑器中，点击“+”图标添加新的Data Set，然后选择“SQL Query”。为数据集设置描述性名称，例如“JournalEventLog”。
输入SQL查询：选择“Oracle BI EE”作为Data Source。复制下方Query部分的完整SQL脚本，并粘贴到SQL Query文本区域。该查询会将所需活动和属性提取到统一结构中。
定义参数：提供的查询使用:start_date、:end_date和:ledger_name等参数。数据模型界面会自动识别这些参数。您必须将其配置为参数，设置合适的数据类型（Date或Text），并在需要时设置默认值，以便灵活执行报表。
根据数据模型创建报表：保存数据模型后，返回目录。点击Create > Report。在报表创建向导中，选择“Use Existing Data Model”，然后找到刚创建的数据模型。
配置报表布局：在布局编辑器中选择“Table”布局。必须将数据模型中的所有列拖放到表格中，确保导出时包含这些列。无需复杂格式设置。
将CSV设为默认输出格式：在报表编辑器中进入“View a List”链接。在“Output Formats”部分确认已选择“CSV”。建议取消选择其他格式，以简化调度。保存报表。
调度报表以自动提取：在目录中找到已保存的报表，点击“More”，然后选择“Schedule”。创建新的调度作业，并配置运行频率，例如每天或每周。
配置导出和传送：在调度作业的“Output”选项卡中指定输出详情。选择报表并将格式设为“CSV”。在“Destination”选项卡中，您可以将文件自动传送到内容服务器位置（UCM）或外部SFTP服务器。
获取并准备数据：调度作业运行后，从配置的目标位置获取生成的CSV文件。确保列标题符合ProcessMind的数据接入要求：JournalEntryId、ActivityName、EventTime、SourceSystem、LastDataUpdate、UserName等。文件现已准备好上传。

配置

数据源：查询应针对BI Publisher中的“Oracle BI EE”数据源运行。该数据源连接到支持OTBI的底层交易数据库视图。
关键主题区域（供参考）：查询逻辑主要基于“General Ledger - Journals Real Time”主题区域中的数据。同时，查询还会逻辑关联“HCM - Workforce Management - Worker Assignment Real Time”中的用户详细信息，以及可能包含审批历史的工作流主题区域。
日期范围筛选：必须将数据限制在可管理的时间范围内。查询包含:start_date和:end_date参数。建议首次分析提取3到6个月的数据。筛选条件应用于日记账创建日期。
分类账筛选：使用:ledger_name参数，将提取范围限制为特定主分类账。一次提取所有分类账可能生成极大的文件并导致性能问题。
前提条件：创建和计划报表的用户必须具备适当的BI角色，例如“BI Administrator”或“BI Author”。此外，用户的数据安全配置文件必须允许访问所查询的特定分类账和业务单元。
计划运行：为获得最佳性能并避免影响系统用户，请将提取任务安排在非高峰时段运行，例如夜间。

a 示例查询 sql

WITH JournalHeaders AS (
    SELECT
        gjh.je_header_id,
        gjh.ledger_id,
        gl.name AS ledger_name,
        gjb.je_batch_id,
        gjh.doc_sequence_value,
        gjh.name AS journal_name,
        gjc.user_je_category_name AS journal_category,
        gjs.user_je_source_name AS journal_source,
        gjh.default_effective_date,
        gjh.creation_date,
        gjh.created_by,
        gjh.last_update_date,
        gjh.last_updated_by,
        gjh.posted_date,
        gjb.approval_status_code AS batch_approval_status,
        gjh.accrual_rev_period_name,
        gjh.accrual_rev_je_header_id,
        (SELECT SUM(accounted_dr) FROM gl_je_lines WHERE je_header_id = gjh.je_header_id) AS journal_amount
    FROM
        gl_je_headers gjh
    JOIN
        gl_je_batches gjb ON gjh.je_batch_id = gjb.je_batch_id
    JOIN
        gl_ledgers gl ON gjh.ledger_id = gl.ledger_id
    JOIN
        gl_je_categories_tl gjc ON gjh.je_category = gjc.je_category_name AND gjc.language = USERENV('LANG')
    JOIN
        gl_je_sources_tl gjs ON gjh.je_source = gjs.je_source_name AND gjs.language = USERENV('LANG')
    WHERE
        gjh.creation_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')
        AND gl.name = :ledger_name
)
-- 1. Journal Entry Created
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    jh.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment", -- Placeholder: Configure based on your department tracking
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 2. Supporting Documentation Attached
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    fad.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    fnd_attached_documents fad
JOIN
    JournalHeaders jh ON fad.pk1_value = TO_CHAR(jh.je_header_id) AND fad.entity_name = 'GL_JE_HEADERS'
LEFT JOIN
    per_users u ON fad.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(fad.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 3. Journal Submitted For Approval
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Submitted For Approval' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_transactions wft ON wft.transaction_id = jh.je_batch_id AND wft.application_id = 101 AND wft.transaction_class = 'Journal Batch'
JOIN
    fun_all_wf_approvals wfa ON wft.transaction_id = wfa.transaction_id AND wfa.response = 'SUBMITTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 4. Journal Entry Reviewed & 7. Journal Entry Approved
SELECT
    jh.je_header_id AS "JournalEntryId",
    CASE
        WHEN wfa.approval_level < (SELECT MAX(approval_level) FROM fun_all_wf_approvals WHERE transaction_id = wfa.transaction_id) THEN 'Journal Entry Reviewed'
        ELSE 'Journal Entry Approved'
    END AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'APPROVED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    wfa.reason AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'REJECTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 6. Journal Corrected and Resubmitted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Corrected and Resubmitted' AS "ActivityName",
    resubmit_events.created_date AS "EventTime",
    resubmit_events.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN (
    SELECT wfa.transaction_id, wfa.created_date, wfa.updated_by
    FROM fun_all_wf_approvals wfa
    WHERE wfa.response = 'SUBMITTED'
    AND EXISTS (
        SELECT 1
        FROM fun_all_wf_approvals prev_wfa
        WHERE prev_wfa.transaction_id = wfa.transaction_id
        AND prev_wfa.response = 'REJECTED'
        AND prev_wfa.created_date < wfa.created_date
    )
) resubmit_events ON jh.je_batch_id = resubmit_events.transaction_id
LEFT JOIN
    per_users u ON resubmit_events.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(resubmit_events.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 8. Journal Posting Initiated
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Posting Initiated' AS "ActivityName",
    gjb.last_update_date AS "EventTime",
    gjb.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_batches gjb ON jh.je_batch_id = gjb.je_batch_id
WHERE
    gjb.status = 'I' -- 'I' typically means Selected for Posting
    AND jh.posted_date IS NOT NULL
LEFT JOIN
    per_users u ON gjb.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(gjb.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 9. Journal Entry Posted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    jh.posted_date AS "EventTime",
    jh.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(jh.posted_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.posted_date IS NOT NULL

UNION ALL

-- 10. Posting Verified - This is a placeholder as it is typically a manual control step not logged in a standard table.
-- This part of the query will return no rows unless you have a custom solution, such as a Descriptive Flexfield (DFF), to track this.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Posting Verified' AS "ActivityName",
    jh.last_update_date AS "EventTime", -- Placeholder timestamp
    jh.last_updated_by AS "UserName", -- Placeholder user
    NULL AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
WHERE
    1 = 2 -- This condition ensures no rows are returned. Replace with logic for your custom DFF or tracking field.

UNION ALL

-- 11. Journal Entry Reconciled - This depends on integration with Oracle Account Reconciliation (ARCS).
-- This query assumes reconciliation status is written back to GL_RECONCILIATION_REFERENCES.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Reconciled' AS "ActivityName",
    grr.last_update_date AS "EventTime",
    grr.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_lines gjl ON jh.je_header_id = gjl.je_header_id
JOIN
    gl_reconciliation_references grr ON gjl.je_header_id = grr.je_header_id AND gjl.je_line_num = grr.je_line_num
WHERE
    grr.status = 'R' -- Assuming 'R' means Reconciled. Verify based on your ARCS configuration.
LEFT JOIN
    per_users u ON grr.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(grr.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 12. Journal Reversal Processed
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Reversal Processed' AS "ActivityName",
    rev_jh.creation_date AS "EventTime",
    rev_jh.created_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_headers rev_jh ON jh.je_header_id = rev_jh.accrual_rev_je_header_id
LEFT JOIN
    per_users u ON rev_jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(rev_jh.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.accrual_rev_je_header_id IS NOT NULL

步骤

进入BI Publisher：登录Oracle Fusion Financials。使用Navigator菜单进入Tools，然后选择Reports and Analytics。这将打开Business Intelligence目录。
创建新的数据模型：在BI目录中点击Create并选择Data Model，打开数据模型编辑器。
定义SQL查询数据集：在数据模型编辑器的Diagram选项卡中点击加号图标，然后选择SQL Query。在新对话框中为数据集设置名称，例如ProcessMind_JE_EventLog。选择合适的Data Source，通常为FUSIONDB。将提供的完整SQL查询粘贴到SQL Query文本区域。
定义参数：查询使用日期范围参数（p_start_date和p_end_date）。Oracle BI Publisher会自动识别这些参数。您应将其配置为Text类型，并设置易于理解的标签，例如“Start Date (YYYY-MM-DD)”和“End Date (YYYY-MM-DD)”。
保存数据模型：粘贴查询并定义参数后，点击OK，将数据模型保存到合适的文件夹，例如Shared Folders/Custom/ProcessMining。
创建报表：点击数据模型编辑器右上角的Create Report按钮，启动报表创建向导。
配置报表布局：在向导中选择使用当前数据模型的选项。选择“Table”布局，将数据源中的所有列拖放到表格布局区域。取消选择“Show Grand Totals Row”。
保存报表：将报表保存到与数据模型相同的文件夹中。设置描述性名称，例如“Journal Entry Event Log Report”。
设置默认输出格式：编辑报表并进入“Viewing”选项卡。在“Output Formats”下确认已选择CSV，并最好将其设为默认格式，确保输出可直接使用。
运行并导出：打开报表，系统会提示您输入开始和结束日期参数。输入所需日期范围并点击Apply。报表运行后，点击Export并选择CSV格式，下载事件日志文件。
准备上传：打开下载的CSV文件。确认列标题符合预期的属性名称（JournalEntryId、ActivityName、EventTime等），并在上传到ProcessMind平台前检查数据格式是否正确。

配置

前提条件：运行报表的用户必须具备访问总账数据所需的数据安全权限，例如General Accountant角色或自定义BI角色。同时还需要拥有BI Publisher工具的访问权限。
数据源：BI Publisher数据模型应配置为使用FUSIONDB应用数据库连接。
日期范围参数：查询使用p_start_date和p_end_date参数控制数据提取时间窗口。建议先从3到6个月的数据开始，以确保性能。
报表筛选器：对于大型组织，强烈建议直接在查询的JournalHeaders公用表表达式的WHERE子句中添加筛选条件。按LEDGER_ID或LEGAL_ENTITY_ID筛选对于控制数据量至关重要。
性能注意事项：针对大型生产数据库执行此查询可能消耗大量资源。请将报表安排在非高峰时段运行，以尽量减少对系统性能的影响。
输出格式：报表必须配置为输出CSV文件，以便以清晰、结构化的格式将数据导入流程挖掘工具。

a 示例查询 sql

WITH JournalHeaders AS (
    SELECT
        gjh.JE_HEADER_ID,
        gjh.NAME AS JOURNAL_NAME,
        gjh.LEDGER_ID,
        gjh.JE_CATEGORY,
        gjh.JE_SOURCE,
        gjh.STATUS,
        gjh.APPROVAL_STATUS_CODE,
        gjh.CREATED_BY,
        gjh.CREATION_DATE,
        gjh.LAST_UPDATED_BY,
        gjh.LAST_UPDATE_DATE,
        gjh.POSTED_DATE,
        gjh.ACCRUAL_REV_JE_HEADER_ID,
        gjh.ACCRUAL_REV_PERIOD_NAME,
        gjs.USER_JE_SOURCE_NAME,
        gjc.USER_JE_CATEGORY_NAME,
        (SELECT SUM(NVL(gjl.ACCOUNTED_DR, 0)) FROM GL_JE_LINES gjl WHERE gjl.JE_HEADER_ID = gjh.JE_HEADER_ID) AS JOURNAL_AMOUNT
    FROM
        GL_JE_HEADERS gjh
    JOIN
        GL_JE_SOURCES_TL gjs ON gjh.JE_SOURCE = gjs.JE_SOURCE_NAME AND gjs.LANGUAGE = USERENV('LANG')
    JOIN
        GL_JE_CATEGORIES_TL gjc ON gjh.JE_CATEGORY = gjc.JE_CATEGORY_NAME AND gjc.LANGUAGE = USERENV('LANG')
    WHERE
        gjh.CREATION_DATE BETWEEN TO_TIMESTAMP(:p_start_date || ' 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(:p_end_date || ' 23:59:59.999', 'YYYY-MM-DD HH24:MI:SS.FF')
        AND gjh.ACTUAL_FLAG = 'A'
        -- Recommended: Add a filter for your specific ledger(s) to improve performance
        -- AND gjh.LEDGER_ID IN ([Your Ledger ID])
),
UserDepartment AS (
    -- This subquery is an example. The join logic to find a user's department may need to be adjusted
    -- based on your specific Oracle HCM configuration (e.g., using different assignment types or tables).
    SELECT 
        pu.USERNAME, 
        org.NAME as Department
    FROM PER_USERS pu
    LEFT JOIN PER_ALL_ASSIGNMENTS_F asg ON pu.PERSON_ID = asg.PERSON_ID 
        AND asg.ASSIGNMENT_TYPE IN ('E', 'C') 
        AND asg.PRIMARY_FLAG = 'Y'
        AND SYSDATE BETWEEN asg.EFFECTIVE_START_DATE AND asg.EFFECTIVE_END_DATE
    LEFT JOIN HR_ALL_ORGANIZATION_UNITS_F_VL org ON asg.ORGANIZATION_ID = org.ORGANIZATION_ID
        AND SYSDATE BETWEEN org.EFFECTIVE_START_DATE AND org.EFFECTIVE_END_DATE
    WHERE pu.USERNAME IS NOT NULL
)
-- 1. Journal Entry Created
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    jh.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.CREATED_BY = ud.USERNAME

UNION ALL

-- 2. Supporting Documentation Attached
SELECT
    TO_NUMBER(fad.PK1_VALUE) AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    fad.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    fad.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    FND_ATTACHED_DOCUMENTS fad
JOIN
    JournalHeaders jh ON fad.PK1_VALUE = TO_CHAR(jh.JE_HEADER_ID)
LEFT JOIN UserDepartment ud ON fad.CREATED_BY = ud.USERNAME
WHERE
    fad.ENTITY_NAME = 'GL_JE_HEADERS'

UNION ALL

-- 3. Journal Submitted For Approval
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Submitted For Approval' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE IN ('REQUIRED', 'INITIATED')

UNION ALL

-- 4. Journal Entry Reviewed (Placeholder)
-- This activity requires custom logic based on your approval workflow configuration.
-- The query below is a placeholder and may not return any data without customization.
-- It assumes a review happens if the approval status is updated but not yet approved or rejected.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    -- Rejection Reason is not a standard field. This might be found in workflow tables.
    '[Configure based on your system]' AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE = 'REJECTED'

UNION ALL

-- 6. Journal Corrected and Resubmitted
-- This logic assumes a resubmission is an update that occurs after a rejection.
-- A more precise method would involve analyzing workflow history, which is complex.
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Corrected and Resubmitted' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE IN ('REQUIRED', 'INITIATED')
    AND jh.JE_HEADER_ID IN (SELECT h.JE_HEADER_ID FROM GL_JE_HEADERS h WHERE h.APPROVAL_STATUS_CODE = 'REJECTED')

UNION ALL

-- 7. Journal Entry Approved
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Approved' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE = 'APPROVED'

UNION ALL

-- 8. Journal Posting Initiated
-- The exact initiation time is not stored. We infer it as 5 minutes before the final posted date.
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Posting Initiated' AS "ActivityName",
    (jh.POSTED_DATE - INTERVAL '5' MINUTE) AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    -- Posting is often a system process, so user might not be available.
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.STATUS = 'P' AND jh.POSTED_DATE IS NOT NULL

UNION ALL

-- 9. Journal Entry Posted
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    jh.POSTED_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.STATUS = 'P' AND jh.POSTED_DATE IS NOT NULL

UNION ALL

-- 10. Posting Verified (Placeholder)
-- This is a manual control activity not tracked by default. This query must be customized.
-- It assumes a descriptive flexfield named 'POSTING_VERIFIED_DATE' on GL_JE_HEADERS exists.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 11. Journal Entry Reconciled (Placeholder)
-- Reconciliation is typically done in other modules. This query must be customized.
-- It assumes a custom table or field indicates when a journal is reconciled.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 12. Journal Reversal Processed
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Reversal Processed' AS "ActivityName",
    rev_jh.POSTED_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    rev_jh.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
JOIN
    GL_JE_HEADERS rev_jh ON jh.ACCRUAL_REV_JE_HEADER_ID = rev_jh.JE_HEADER_ID
LEFT JOIN UserDepartment ud ON rev_jh.CREATED_BY = ud.USERNAME
WHERE
    jh.ACCRUAL_REV_JE_HEADER_ID IS NOT NULL
    AND rev_jh.STATUS = 'P'
ORDER BY
    "JournalEntryId", "EventTime"

步骤

前提条件：确保已安装Python 3以及requests和pandas库。您需要拥有Oracle Fusion Financials用户账户，并具备访问总账日记账REST API的必要权限。请获取Oracle Fusion实例URL、用户名和密码。
设置脚本：创建新的Python脚本文件。首先导入所需库：requests、json、pandas和datetime。
配置：在脚本中定义Oracle Fusion凭据和基础URL变量。同时设置数据提取参数，例如查询的开始日期和结束日期。
身份验证：脚本使用基本身份验证。requests库负责在请求标头中对用户名和密码进行编码。
定义API调用函数：创建用于处理API请求的函数。该函数应使用offset和limit参数管理分页，并循环发起连续API调用，直到获取指定日期范围内的全部日记账记录。
构建API查询：在API请求中使用q参数，根据LastUpdateDate筛选日记账。这样即可针对特定时间范围提取数据，例如LastUpdateDate between '2023-01-01' and '2023-03-31'。
开始获取数据：调用该函数，从.../fscmRestApi/resources/11.13.18.05/journals端点开始获取数据。函数将返回完整的日记账分录列表。
处理和转换数据：遍历API返回的每个日记账分录。针对每个日记账分录，根据不同业务活动（Journal Entry Created、Journal Entry Approved等）生成多条事件记录。使用API响应中的日期字段和状态代码（CreationDate、PostedDate、ApprovalStatus等）创建这些独立事件。
构建事件日志：生成每条事件记录时，将其作为字典存入列表。该列表将作为事件日志的原始数据。
创建DataFrame：处理完所有日记账分录后，将事件字典列表转换为Pandas DataFrame，以便进一步处理。
确定最终列：选择并重命名DataFrame列，使其符合ProcessMind所需架构：JournalEntryId、ActivityName、EventTime、UserName等。
导出为CSV：将最终DataFrame保存为CSV文件。随后即可将该文件直接上传到ProcessMind进行分析。

配置

凭据：Basic Authentication需要有效的Oracle Fusion用户名和密码。
实例URL：需要Oracle Fusion Financials实例的基础URL来构建API端点，例如https://[your-instance].fa.us2.oraclecloud.com。
API端点：脚本通过fscmRestApi/resources/11.13.18.05/journals端点获取General Ledger数据。
日期范围筛选：脚本应配置start_date和end_date，限制提取范围。首次分析建议使用3至6个月的数据；持续使用时，可配置为每天或每周增量加载。
分页限制：limit参数控制每次API调用获取的记录数。通常设置为200至500较为高效，可平衡调用次数和单次响应大小。
所需Oracle角色：用户账户必须具备通过REST API读取Journal数据所需的权限，例如General Accountant，或具有同等权限的自定义角色。

a 示例查询 python

import requests
import pandas as pd
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
FUSION_URL = class="hl-string">"https://[your-instance].fa.us2.oraclecloud.com"
USERNAME = class="hl-string">"[your-username]"
PASSWORD = class="hl-string">"[your-password]"

class=class="hl-string">"hl-comment"># --- Parameters ---
class=class="hl-string">"hl-comment"># Set the date range for the extraction (YYYY-MM-DD format)
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-06-30"
PAGE_LIMIT = 500 class=class="hl-string">"hl-comment"># Number of records per API call

def get_all_journals(base_url, user, password, start_date, end_date):
    class="hl-string">"""Fetches all journals from the Oracle Fusion REST API within a date range, handling pagination."""
    all_journals = []
    offset = 0
    has_more = True
    
    api_url = fclass="hl-string">"{base_url}/fscmRestApi/resources/11.13.18.05/journals"
    
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourcecollection+json'
    }
    
    query_filter = fclass="hl-string">"LastUpdateDate between '{start_date}' and '{end_date}'"
    
    while has_more:
        params = {
            class="hl-string">'q': query_filter,
            class="hl-string">'onlyData': class="hl-string">'true',
            class="hl-string">'limit': PAGE_LIMIT,
            class="hl-string">'offset': offset,
            class="hl-string">'fields': class="hl-string">'JeHeaderId,CreatedBy,CreationDate,LastUpdatedBy,LastUpdateDate,PostedDate,ApprovalStatus,ActualFlag,DefaultEffectiveDate,Description,JeCategory,JeSource,PeriodName,TotalDebit,ReversalDate,ReversalPeriodName,ExternalReference,attachments'
        }
        
        print(fclass="hl-string">"Fetching records from offset {offset}...")
        try:
            response = requests.get(api_url, auth=(user, password), headers=headers, params=params)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)
            data = response.json()
            
            journals = data.get(class="hl-string">'items', [])
            all_journals.extend(journals)
            
            has_more = data.get(class="hl-string">'hasMore', False)
            offset += PAGE_LIMIT

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            has_more = False

    return all_journals

def transform_to_event_log(journals):
    class="hl-string">"""Transforms the raw journal data into a structured event log."""
    event_log = []
    extraction_timestamp = datetime.now(timezone.utc).isoformat()

    for jrnl in journals:
        journal_id = jrnl.get(class="hl-string">'JeHeaderId')
        created_by = jrnl.get(class="hl-string">'CreatedBy')
        last_updated_by = jrnl.get(class="hl-string">'LastUpdatedBy')
        approval_status = jrnl.get(class="hl-string">'ApprovalStatus')
        rejection_reason_text = class="hl-string">"Rejected per workflow comments" if approval_status == class="hl-string">'REJECTED' else None

        class=class="hl-string">"hl-comment"># 1. Journal Entry Created
        if jrnl.get(class="hl-string">'CreationDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Entry Created',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'CreationDate'),
                class="hl-string">'UserName': created_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 2. Supporting Documentation Attached (Approximation)
        if jrnl.get(class="hl-string">'attachments') and len(jrnl[class="hl-string">'attachments']) > 0:
             event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Supporting Documentation Attached',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class=class="hl-string">"hl-comment"># Approximation: uses last update date
                class="hl-string">'UserName': last_updated_by, 
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 3, 4, 5, 6, 7: Approval Workflow Events (Inferred from final status)
        class=class="hl-string">"hl-comment"># Note: This block infers events. For a more detailed history, workflow-specific APIs would be needed.
        if approval_status in [class="hl-string">'IN_PROCESS', class="hl-string">'REQUIRED']:
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Submitted For Approval',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        elif approval_status == class="hl-string">'REJECTED':
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Rejected',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by, class=class="hl-string">"hl-comment"># Approver is often the last updater
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': rejection_reason_text,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            class=class="hl-string">"hl-comment"># A resubmission is assumed if a rejected journal is updated again
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Corrected and Resubmitted',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': created_by, class=class="hl-string">"hl-comment"># Typically the creator corrects it
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        elif approval_status == class="hl-string">'APPROVED':
            class=class="hl-string">"hl-comment"># class="hl-string">'Journal Entry Reviewed' is often an implicit part of approval
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Reviewed',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Approved',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 8, 9: Posting Events
        if jrnl.get(class="hl-string">'PostedDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Posting Initiated',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'PostedDate'), class=class="hl-string">"hl-comment"># Assuming initiation is same as posted time
                class="hl-string">'UserName': class="hl-string">'System', class=class="hl-string">"hl-comment"># Posting is often a system process
                class="hl-string">'UserDepartment': class="hl-string">'Finance Operations', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Posted',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'PostedDate'),
                class="hl-string">'UserName': class="hl-string">'System',
                class="hl-string">'UserDepartment': class="hl-string">'Finance Operations', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        
        class=class="hl-string">"hl-comment"># 12. Journal Reversal Processed
        if jrnl.get(class="hl-string">'ReversalDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Reversal Processed',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'ReversalDate'),
                class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># Activities not directly available from this API, added as placeholders for potential enrichment
        class=class="hl-string">"hl-comment"># Posting Verified, Journal Entry Reconciled

    return pd.DataFrame(event_log)

class=class="hl-string">"hl-comment"># --- Main Execution ---
if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting Oracle Fusion Journal Entry extraction...")
    raw_journals = get_all_journals(FUSION_URL, USERNAME, PASSWORD, START_DATE, END_DATE)
    
    if raw_journals:
        print(fclass="hl-string">"Successfully extracted {len(raw_journals)} journal records.")
        event_log_df = transform_to_event_log(raw_journals)
        
        if not event_log_df.empty:
            class=class="hl-string">"hl-comment"># Save to CSV
            output_filename = fclass="hl-string">"oracle_fusion_journal_events_{START_DATE}_to_{END_DATE}.csv"
            event_log_df.to_csv(output_filename, index=False)
            print(fclass="hl-string">"Event log saved to {output_filename}")
        else:
            print(class="hl-string">"No events were generated from the extracted data.")
    else:
        print(class="hl-string">"No data extracted. Please check configuration and API availability.")

步骤

确认您的环境中已提供BI Cloud Connector、所需的Oracle Fusion Financials General Ledger主题区域或Public View Objects，以及源系统所需授权。与Oracle管理员确认已配置的BICC交付目标及其访问凭据。
在BI Cloud Connector控制台中，确定包含日记账抬头、日记账行、审批历史、附件、过账状态、对账状态和冲销信息的Public View Objects。记录您环境中配置的准确对象名和属性名。执行前，将提取脚本中的占位符替换为这些名称。
为选定的General Ledger Public View Objects配置BICC作业。如果存在可靠的最后更新时间属性，请使用增量提取。初始加载可使用受控的3至6个月期间，或其他经批准的会计期间范围。
配置BICC，将提取文件交付到指定云存储位置。确认文件格式、分隔符、字符编码、压缩方式、分区、命名规则和交付计划。保留提取水位线或上次成功提取的时间戳。
将交付的文件提取到经批准的暂存区。将每个源文件加载到对应的暂存表或视图中。使用您环境中记录的准确源对象和属性映射。不要假设Public View Object与底层数据库表具有相同结构。
将时间戳统一为一个时区，并将源时间戳转换为一致的时间戳数据类型。将源更新时间戳保留为LastDataUpdate。将源标识和活动属性转换为ProcessMind使用的输出名称。
将每个专业占位符替换为实际暂存对象和属性名称后，执行以下查询。查询会为12项必需活动分别明确创建一行事件日志记录，不会推断源数据中不存在的活动。
验证每一行输出都包含JournalEntryId、ActivityName、EventTime、SourceSystem和LastDataUpdate。检查重复事件、时间戳顺序、被拒绝和重新提交的日记账、冲销关系以及缺少可选属性的记录。
将结果导出为分隔文件或ProcessMind支持的其他格式。必须包含准确的列名JournalEntryId、ActivityName、EventTime、SourceSystem、LastDataUpdate、UserName、UserDepartment、JournalCategory、JournalSource、JournalAmount和RejectionReason。上传文件时，将JournalEntryId配置为案例标识，ActivityName配置为活动，EventTime配置为事件时间戳。

配置

针对Journal标头、Journal行、审批或工作流历史、附件、过账、对账和冲销数据所需的General Ledger Public View Objects配置BICC。必须在您环境的BICC目录中确认准确的对象名称和属性。
首次提取使用3至6个月的时间范围。只有在验证数据量、存储容量、提取时长和下游处理时间后，才应延长范围。
应用已批准的筛选条件，包括会计日期或最后更新时间、Ledger、法人实体、业务单元、可用时的公司代码、Journal类别、Journal来源、可用时的单据类型以及Journal状态。仅使用所选Public View Objects支持的筛选条件。
使用源系统最后更新时间属性和持久化水位线配置增量提取。必要时加入较小的重叠时间窗口，以防遗漏延迟到达的更新，然后根据JournalEntryId、ActivityName、EventTime和相关源事件标识符去重。
确保所有源对象使用兼容的提取键和刷新计划。审批、附件、过账、对账和冲销数据的刷新时间可能不同。
对于大型数据集，按会计期间、Ledger、法人实体或其他受BICC支持的分区属性进行分区。避免在工作时间提取不受限制的历史数据。
在转换前保留源时间戳和源更新时间戳。记录EventTime和LastDataUpdate使用的时区。
前置条件包括访问BI Cloud Connector控制台、配置和运行所选提取任务的授权、访问已配置的云存储目标、访问暂存环境，以及查看所需General Ledger和工作流数据的授权。
确认在预期开展相关活动的情况下，所需Oracle Fusion Financials模块、工作流配置、附件存储、过账流程和对账数据源均已启用。
不要将缺少源记录视为某项活动未发生的证据。BICC输出取决于所选Public View Objects、筛选条件、刷新时间、保留策略和可用历史数据。

a 示例查询 sql

WITH
journal_created AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Created' AS ActivityName,
        CAST([Journal Created Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Created By attribute] AS VARCHAR(255)) AS UserName,
        CAST([Created By Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal header staging object]
    WHERE [Journal Created Timestamp attribute] IS NOT NULL
),
supporting_documentation_attached AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Supporting Documentation Attached' AS ActivityName,
        CAST([Attachment Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Attachment User attribute] AS VARCHAR(255)) AS UserName,
        CAST([Attachment User Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal attachment staging object]
    WHERE [Attachment Timestamp attribute] IS NOT NULL
),
journal_submitted_for_approval AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Submitted For Approval' AS ActivityName,
        CAST([Submission Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Submitter attribute] AS VARCHAR(255)) AS UserName,
        CAST([Submitter Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal approval staging object]
    WHERE [Submission Timestamp attribute] IS NOT NULL
),
journal_entry_reviewed AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Reviewed' AS ActivityName,
        CAST([Review Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Reviewer attribute] AS VARCHAR(255)) AS UserName,
        CAST([Reviewer Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal review staging object]
    WHERE [Review Timestamp attribute] IS NOT NULL
),
journal_entry_rejected AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Rejected' AS ActivityName,
        CAST([Rejection Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Rejector attribute] AS VARCHAR(255)) AS UserName,
        CAST([Rejector Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST([Rejection Reason attribute] AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal rejection staging object]
    WHERE [Rejection Timestamp attribute] IS NOT NULL
),
journal_corrected_and_resubmitted AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Corrected and Resubmitted' AS ActivityName,
        CAST([Correction Resubmission Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Resubmitter attribute] AS VARCHAR(255)) AS UserName,
        CAST([Resubmitter Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST([Rejection Reason attribute] AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal resubmission staging object]
    WHERE [Correction Resubmission Timestamp attribute] IS NOT NULL
),
journal_entry_approved AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Approved' AS ActivityName,
        CAST([Approval Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Approver attribute] AS VARCHAR(255)) AS UserName,
        CAST([Approver Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal approval staging object]
    WHERE [Approval Timestamp attribute] IS NOT NULL
),
journal_posting_initiated AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Posting Initiated' AS ActivityName,
        CAST([Posting Initiated Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Posting Initiated By attribute] AS VARCHAR(255)) AS UserName,
        CAST([Posting Initiated By Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal posting staging object]
    WHERE [Posting Initiated Timestamp attribute] IS NOT NULL
),
journal_entry_posted AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Posted' AS ActivityName,
        CAST([Posted Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Posted By attribute] AS VARCHAR(255)) AS UserName,
        CAST([Posted By Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal posting staging object]
    WHERE [Posted Timestamp attribute] IS NOT NULL
),
posting_verified AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Posting Verified' AS ActivityName,
        CAST([Posting Verification Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Verifier attribute] AS VARCHAR(255)) AS UserName,
        CAST([Verifier Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Posting verification staging object]
    WHERE [Posting Verification Timestamp attribute] IS NOT NULL
),
journal_entry_reconciled AS (
    SELECT
        CAST([Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Entry Reconciled' AS ActivityName,
        CAST([Reconciliation Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Reconciled By attribute] AS VARCHAR(255)) AS UserName,
        CAST([Reconciled By Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Journal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal reconciliation staging object]
    WHERE [Reconciliation Timestamp attribute] IS NOT NULL
),
journal_reversal_processed AS (
    SELECT
        CAST([Original Journal Entry ID attribute] AS VARCHAR(255)) AS JournalEntryId,
        'Journal Reversal Processed' AS ActivityName,
        CAST([Reversal Posted Timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Source System attribute] AS VARCHAR(255)) AS SourceSystem,
        CAST([Last Update Timestamp attribute] AS TIMESTAMP) AS LastDataUpdate,
        CAST([Reversal User attribute] AS VARCHAR(255)) AS UserName,
        CAST([Reversal User Department attribute] AS VARCHAR(255)) AS UserDepartment,
        CAST([Journal Category attribute] AS VARCHAR(255)) AS JournalCategory,
        CAST([Journal Source attribute] AS VARCHAR(255)) AS JournalSource,
        CAST([Reversal Amount attribute] AS DECIMAL(38, 10)) AS JournalAmount,
        CAST(NULL AS VARCHAR(4000)) AS RejectionReason
    FROM [Journal reversal staging object]
    WHERE [Reversal Posted Timestamp attribute] IS NOT NULL
)
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_created
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM supporting_documentation_attached
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_submitted_for_approval
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_entry_reviewed
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_entry_rejected
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_corrected_and_resubmitted
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_entry_approved
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_posting_initiated
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_entry_posted
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM posting_verified
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_entry_reconciled
UNION ALL
SELECT JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, UserDepartment, JournalCategory, JournalSource, JournalAmount, RejectionReason FROM journal_reversal_processed
ORDER BY JournalEntryId, EventTime, ActivityName;

准备好开始了吗？

利用此模板将Oracle Fusion Journal Entry数据转化为可执行洞察，加快Record to Report周期。立即开始优化财务运营。

返回改进指南下载模板

立即加快Record to Report－Journal Entry流程

将Journal Entry周期时间缩短30%，优化财务运营。

开始免费试用

无需信用卡，立即激活试用。

您的Record to Report－Journal Entry数据模板

您的Record to Report－Journal Entry数据模板

记录到报告，日记账分录属性

记录到报告，日记账分录活动

数据提取指南

Oracle OTBI和BI Publisher报表导出

步骤

配置

a 示例查询 sql

使用直接SQL数据模型查询的BI Publisher报表

步骤

配置

a 示例查询 sql

使用Oracle Fusion Financials REST API的Python脚本

步骤

配置

a 示例查询 python

使用BI Cloud Connector（BICC）批量提取数据

步骤

配置

a 示例查询 sql

准备好开始了吗？

立即加快Record to Report－Journal Entry流程

您的Record to Report－Journal Entry数据模板

您的Record to Report－Journal Entry数据模板

记录到报告，日记账分录属性

记录到报告，日记账分录活动

数据提取指南

Oracle OTBI和BI Publisher报表导出

步骤

配置

a 示例查询 sql

使用直接SQL数据模型查询的BI Publisher报表

步骤

配置

a 示例查询 sql

使用Oracle Fusion Financials REST API的Python脚本

步骤

配置

a 示例查询 python

使用BI Cloud Connector（BICC）批量提取数据

步骤

配置

a 示例查询 sql

准备好开始了吗？

立即加快Record to Report－Journal Entry流程

我们重视您的隐私