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

您的记账到报表 - 日记账分录数据模板

Oracle Fusion Financials

记账到报表 - 日记账分录属性 (22) 记账到报表 - 日记账分录活动 (12) 提取指南 (4)

您的记账到报表 - 日记账分录数据模板

此数据模板为提取和构建 Oracle Fusion Financials 日记账分录数据提供了全面指导。它概述了需要收集的核心属性和需要追踪的关键活动，确保您捕获到进行稳健流程分析所需的所有信息。您还将在其中找到如何直接从系统中提取此数据的实操建议。

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

不熟悉事件日志？了解如何创建流程挖掘事件日志.

下载模板

记账到报表 - 日记账分录属性

为了全面分析您的“记账到报表 - 日记账分录”流程，建议在 event log 中包含以下数据字段。

5 必填 6 推荐 11 可选

	名称	描述
	开始时间 EventTime	指示特定活动或事件发生时间的时间戳。
描述此 timestamp 记录了活动执行的准确日期和时间。它是流程挖掘中用于确定事件顺序并计算事件之间时长的主要时间元素。 “事件时间”的准确性对于所有基于时间的分析（包括计算周期时间、识别瓶颈以及对照服务水平协议监测绩效）都至关重要。它提供了按发生顺序重构流程流向所需的年代顺序。为何重要此 timestamp 对于事件排序、计算所有流程时长以及执行任何基于时间的分析都至关重要。获取方式此信息通常存储在审计轨迹表中，或者作为特定事件在 GL_JE_HEADERS 和 GL_JE_LINES 等交易表上的“最后更新日期”或“创建日期”存储。示例 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:00Z
	日记账分录 ID JournalEntryId	单笔日记账分录的唯一标识符，关联了所有相关的财务交易活动。
描述日记账分录 ID 是主要的 case 标识符，它将与特定一组财务交易相关的所有活动唯一地关联起来。这实现了对单笔日记账分录从发起到最终过账的完整生命周期的追踪，确保所有借贷方都已入账。在 Process Mining 中，此 ID 对于重构每笔日记账分录的端到端路径至关重要。它将“已创建日记账分录”、“提交审批”和“日记账分录已过账”等零散的 event 连接成一个连贯的流程流，从而实现对周期时间、瓶颈和流程变体的分析。为何重要这是从头到尾追踪日记账分录的基础主键，使得分析每个唯一 case 的整个流程流向成为可能。获取方式这是在 GL_JE_HEADERS 和 GL_JE_LINES 等核心总账表中找到的主键。示例 JE100523JE202311001882019
	活动 ActivityName	在日记账分录流程中，特定时间点发生的业务事件或任务的名称。
描述活动名称描述了日记账分录生命周期中的单个步骤，例如“已创建日记账分录”或“已审批日记账分录”。此数据对于构建流程图和理解事件顺序至关重要。通过分析活动，可以识别流程流向，包括标准路径、偏差和返工循环。通过追踪不同的活动，我们可以衡量审批等特定阶段花费的时间，并识别哪些步骤最耗时或最容易出错。为何重要它定义了流程的步骤，构成了流程图的骨架，并支持对流程、瓶颈和变体的分析。获取方式此属性通常源自总账模块中与日记账分录对象相关的状态变更、event log 或审计轨迹表。示例已创建日记账分录日记账已提交审批日记账分录已批准日记账分录已过账
	最后数据更新 LastDataUpdate	此属性指示data最后一次从Salesforce Financial Services Cloud提取的日期和时间，为分析数据“新鲜度”提供背景。这对于dashboard用户理解分析时效性至关重要，有助管理数据及时性预期，并验证data pipelines是否按计划运行。
描述此属性记录了数据集最后一次更新的时间。它提供了关于被分析数据新鲜度的背景，这对于理解所获见解的相关性非常重要。在任何分析中，特别是在运营控制台中，了解“最后一次数据更新”时间对于用户信任数据并做出明智决策至关重要。它清晰地传达了流程挖掘模型中所含数据的截止时间点。为何重要指示数据的实时性，确保用户了解流程分析的时效性并能信任这些洞察。获取方式此值在数据提取和转换过程中生成并存储。通常是 ETL/ELT 任务完成时的 timestamp。示例 2023-11-20T08:00:00Z2023-11-21T08:00:00Z2023-11-22T08:00:00Z
	源系统 SourceSystem	数据来源的系统或模块。
描述此属性标识了提取流程数据的源系统。在复杂的 IT 环境中，数据可能来自多个集成的系统，此字段有助于区分它们。在流程分析中，了解源系统对于理解可能由不同系统行为驱动的流程变体至关重要。通过将数据溯源至其起点，它还有助于数据验证和故障排除。为何重要识别数据来源，这对于数据治理、验证以及理解不同系统间的流程变体至关重要。获取方式这通常是数据提取期间配置的静态值，也可以是源表中标识录入系统的字段。示例 Oracle Fusion Financials CloudOracle EBS R12Fusion GL
	拒绝原因 RejectionReason	解释日记账分录为何被拒绝的文字说明或代码。
描述当日记账分录在审批过程中被退回时，此属性捕获退回原因。这可以是一个预定义的代码，也可以是审批人的自由文本评论。这是流程低效根本原因分析中最重要的属性之一。通过分析最常见的退回原因，组织可以识别待改进领域，例如加强对填报人的培训、制定更清晰的指南或增强系统控制。它直接支持“日记账分录退回率分析”仪表板。为何重要直观揭示返工和流程延迟的根本原因，从而实现有针对性的改进，降低退回率。获取方式此信息可能存储在与审批流程相关的 workflow 表或审计轨迹表中，或者存储在日记账抬头的备注字段中。示例账户组合错误支持文档不足超出预算
	日记账来源 JournalSource	生成日记账分录的子账或来源，例如“应付”、“应收”或“手工”。
描述此属性指示了 ERP 系统内日记账分录的来源。分录可以手动在总账中创建，也可以从应付账款、应收账款或固定资产等子账自动生成。日记账来源是自动化分析的关键属性。它有助于区分手动分录和系统生成分录，支持“日记账分录自动化率”KPI。来自不同来源的分录流程在复杂性和效率方面通常存在显著差异。为何重要区分手动分录和自动分录，这对于衡量自动化率和分析流程差异至关重要。获取方式可在 GL_JE_HEADERS 表中找到，通常在 JE_SOURCE 字段。示例手动应付账款资产应收账款
	日记账类别 JournalCategory	日记账分录的类别，例如“应计”、“调整”或“重分类”。
描述日记账类别根据业务目的对分录进行分类。这种分类可以实现更细致的流程分析，因为不同的类别可能有不同的流程路径、审批规则或周期时间。例如，月末调整日记账的流程可能比常规预提流程更复杂且紧急。按类别分析流程有助于发现这些差异，并针对特定类型的日记账分录量身定制改进方案。为何重要允许按日记账的业务目的对流程进行细分，揭示不同分录类型的不同行为和表现。获取方式可在 GL_JE_HEADERS 表中找到，通常在 JE_CATEGORY 字段。示例预提手动调整重估
	日记账金额 JournalAmount	日记账分录的总货币价值，通常是借方总额。
描述此属性代表日记账分录中交易的总财务金额。金额是影响流程的一个重要因素。例如，高金额的分录可能需要额外的审批步骤或更详尽的复核。分析“日记账金额”可以按财务影响对 case 进行细分。它有助于回答诸如“高金额分录的审批时间是否更长？”或“超过一定阈值的分录是否更容易被退回？”等问题。这为流程流向提供了关键的业务背景。为何重要提供关键的业务背景，支持基于财务影响的分析，并帮助识别高价值分录是否遵循了不同的流程。获取方式此值通常计算为给定日记账分录在 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	日记账分录过账所在的会计期间，例如“2024-01”。
描述会计期间指定了交易被确认的财务期间。这是所有财务报告和分析的基础。在 Process Mining 中，此属性对于趋势分析至关重要。它支持对比不同月份或季度的流程绩效（如周期时间或退回率）。这有助于识别季节性影响（例如月末结账期间工作量增加），并衡量流程改进随时间推移产生的效果。为何重要支持 KPI 随时间变化的趋势分析，有助于衡量流程改进效果并识别季节性模式（如月末压力）。获取方式可在 GL_JE_HEADERS 表中找到，通常在 PERIOD_NAME 字段。示例 Jan-24Feb-24Mar-24
	冲销标志 ReversalIndicator	一个指示该日记账分录是否为另一分录冲销的标记。
描述此布尔属性识别为冲销先前已过账分录而创建的日记账分录。冲销是一种特殊类型的日记账分录，通常遵循一个独特且有时会出现问题的流程。分析此标志是支持“日记账冲销流程绩效”控制台的关键。它支持隔离冲销流程以衡量其频率、原因和周期时间，从而帮助识别导致必须冲销的原始分录中的潜在问题。为何重要有助于隔离并分析冲销流程，冲销通常是原始交易中存在错误或问题的指标。获取方式这可以是 GL_JE_HEADERS 表上的特定标志（如 ACCRUAL_REV_FLAG），或者通过链接到被冲销的原始日记账来识别。示例 truefalse
	分类账名称 LedgerName	记录日记账分录的总账名称。
描述账簿名称识别了日记账分录所属的特定账簿。在拥有多个法人实体或报告要求的组织中，可能存在多个账簿，例如用于集团核算的主账簿和用于当地法定报告的次级账簿。此属性对于过滤和对比不同法人实体或业务单元的流程至关重要。它确保分析是在正确的组织背景下进行的，这对于财务合规和报告尤为重要。为何重要支持在不同的法律实体或会计框架下对流程分析进行筛选和比较，这对于大型组织至关重要。获取方式可在 GL_JE_HEADERS 表中找到，通常通过 LEDGER_ID 关联，可与 GL_LEDGERS 表连接以获取名称。示例美国主账簿英国法定账簿全球合并分类账
	日记账审批时间 JournalApprovalTime	日记账提交与最终审批或退回决定之间的计算时长。
描述此指标衡量从日记账提交审批到获准或被退回所经历的时间。它是审批 workflow 效率的关键衡量标准。 “日记账审批时间”是“平均日记账审批时间”KPI 和“日记账分录审批瓶颈”仪表板的直接输入数据。分析这一时长有助于精准发现审批链中的延迟，无论这些延迟是由特定审批人、部门还是日记账类型引起的。为何重要直接衡量审批工作流的效率，这通常是流程延迟的主要来源。获取方式这是流程挖掘工具中的一个计算指标。它是“日记账已提交审批”与“日记账分录已审批”或“日记账分录已退回”事件之间的时间差。示例 P2DT4H30MPT8HP5D
	是否按时过账 IsOnTimePosting	一个计算出的标记，如果日记账在目标日期或之前过账，则为 true。
描述此布尔属性是通过对比“日记账分录已过账”活动的 timestamp 与“目标过账日期”得出的。如果过账发生在目标日期当天或之前，则值为“true”；否则为“false”。此属性通过简化计算，直接支持“日记账准时过账率”KPI。它允许轻松过滤和分析逾期分录，以识别延迟的常见原因，例如特定的日记账类别或审批人。为何重要为过账绩效提供清晰的正误结果，简化准时率分析并挖掘延迟的根本原因。获取方式此属性由流程挖掘工具计算。它需要“TargetPostingDate”属性以及“日记账分录已过账”活动的 timestamp。示例 truefalse
	是否返工 IsRework	一个计算出的标记，如果日记账分录经历了拒绝和更正循环，则为 true。
描述此布尔属性用于识别经历了返工的 case。通常，对于“日记账分录已退回”事件之后的任何活动（如“日记账已修正并重新提交”），该属性会被标记为“true”。 “是否返工”是一个强大的分析属性，它支持轻松量化返工循环的数量和影响。它直接支持“平均日记账返工率”KPI，并有助于对比返工 case 与首次即正确处理的 case 的流程流向和时长。为何重要直接标记经历过低效返工循环的案例，从而轻松量化流程失败的频率和影响。获取方式源系统中不直接提供此项内容。它是流程挖掘工具通过分析每个 case 的活动顺序计算得出的。示例 truefalse
	目标过账日期 TargetPostingDate	预定的日记账分录预期过账日期。
描述目标过账日期代表了日记账分录过账到总账的截止日期，通常由服务水平协议 (SLA) 或月末结账计划决定。它作为衡量绩效的基准。此属性对于计算“日记账准时过账率”KPI 至关重要。通过将实际过账日期与此目标进行对比，系统可以自动将分录标记为准时或逾期，从而清晰地衡量流程对计划的执行情况。为何重要定义过账的服务水平协议或截止日期，从而能够计算按时完成情况的 KPI。获取方式这可能不是标准字段。它可能是根据基于创建日期、会计期间或日记账类别的业务规则推导出来的。示例 2023-10-312023-11-302024-01-05
	端到端周期时间 EndToEndCycleTime	从日记账分录创建到最终对账的总计算时长。
描述此指标代表日记账分录在整个流程中花费的总时间，从最开始的“已创建日记账分录”事件到最后的“日记账已对账”事件。它提供了流程绩效的全景视图。这是衡量流程整体效率的核心 KPI。它用于趋势控制台中，以追踪改进方案随时间推移产生的影响，并为衡量所有流程变更提供基准。为何重要提供对整个流程健康状况和效率的高维度衡量，是管理层报告的关键指标。获取方式此指标由流程挖掘平台计算，作为每个 case 第一个和最后一个事件 timestamp 之间的时间差。示例 P10DT5HP4DT12HP22D
	结束时间 EndTime	指示特定活动完成的时间戳。
描述结束时间标志着活动的完成。开始时间记录起点，结束时间则提供终点，从而能够精确计算该特定步骤的处理时间。这对于计算活动处理时间至关重要，而活动处理时间是识别瓶颈和衡量资源效率的核心指标。例如，“日记账分录已复核”活动的结束时间与开始时间之差，即为复核人员在该任务上花费的实际时间。为何重要它可以计算单个活动的实际持续时间或处理时间，这是识别效率问题的关键。获取方式与开始时间类似，这通常可以在审计轨迹表中找到，或者根据流程中后续活动的开始时间推导出来。示例 2023-10-26T10:15:00Z2023-11-15T14:55:10Z2024-01-05T09:22:00Z
	货币 CurrencyCode	日记账分录金额的币种代码，例如 CNY、USD 或 EUR。
描述币种代码指定了日记账分录中财务金额的货币。这对于开展多币种业务跨国组织至关重要。此属性可确保财务价值得到正确解读。它支持按币种过滤，并且是在执行涉及不同地区货币金额对比或汇总的分析时的必填字段。为何重要为财务金额提供必要的背景，确保数据解读准确，并支持针对不同币种的专项分析。获取方式可在 GL_JE_HEADERS 表中找到，通常在 CURRENCY_CODE 字段。示例美元EURGBPJPY
	过账状态 PostingStatus	日记账分录的当前过账状态，例如“未过账”或“已过账”。
描述此属性反映了日记账分录在总账中过账的当前状态。它是衡量日记账分录处于其生命周期哪个阶段的关键指标，特别是对于进行中的 case。 “过账状态”对于“日记账分录状态实时追踪”控制台至关重要，它能提供所有活跃分录的快照。它有助于监控未过账日记账的积压情况，并识别流程最后阶段的延迟。为何重要指示日记账的当前状态，这对于监控积压工作和跟踪进行中分录的状态至关重要。获取方式可在 GL_JE_HEADERS 表中找到，通常在 STATUS 或 POSTING_STATUS 列。示例已过账未过账错误

必填推荐可选

记账到报表 - 日记账分录活动

为了在日记账分录中实现准确的流程发现，需要在 event log 中捕获以下关键流程步骤和里程碑。

5 推荐 7 可选

	活动	描述
	已创建日记账分录	此活动标志着日记账分录流程的开始。它代表用户创建新日记账抬头并开始录入数据，但尚未提交任何复核或审批。
为何重要这是该流程的主要开始 event。分析从该活动到后续步骤的时间有助于衡量初始数据录入效率和整体流程周期时间。获取方式此事件通常根据 GL_JE_HEADERS 表中特定“日记账分录 ID”的创建日期 timestamp 推导得出。创建记录的用户也可以在该表中找到。捕获使用 GL_JE_HEADERS 表中的 CREATION_DATE。事件类型 inferred
	日记账分录已对账	该日记账分录已在期末对账流程中完成匹配和清理。这确认了该交易与其他财务数据（如银行对账单）一致。
为何重要此活动是日记账生命周期的真实终点。衡量从过账到对账的时间是评估财务结账流程效率的关键 KPI。获取方式此事件通常在 Oracle 财务合并与关账云服务 (FCCS) 或账目核对云服务 (ARCS) 中捕获，而不是在总账模块本身。它是根据与日记账链接的对账记录上的状态变更推导出来的。捕获将日记账数据与 ARCS 或 FCCS 表中的对账状态更新进行关联。事件类型 inferred
	日记账分录已批准	指定的审批人已正式审批该日记账分录，确认其准确性和有效性。这是审批 workflow 的最后一步，为过账扫清了障碍。
为何重要该里程碑标志着审批流程的结束。从提交到审批的时间是衡量 workflow 效率和识别审批瓶颈的关键 KPI。获取方式此事件推导自 GL_JE_HEADERS 表中的状态变更，其中 APPROVAL_STATUS_CODE 被更新为“APPROVED”。工作流表中包含审批人的身份和 timestamp。捕获追踪 GL_JE_HEADERS 中 APPROVAL_STATUS_CODE 更改为“APPROVED”时的 timestamp。事件类型 inferred
	日记账分录已过账	该日记账分录的财务数据已成功记录在总账中。借贷项现已反映在账户余额中。
为何重要这是一个关键的里程碑，代表日记账已计入正式财务记录。它对于衡量准时过账率以及从创建到过账的总时间至关重要。获取方式此事件推导自 GL_JE_HEADERS 表中的状态变更，其中 STATUS 字段更改为“P”（已过账）。GL_JE_BATCHES 表也有过账状态。捕获追踪 GL_JE_HEADERS 中 STATUS 更改为“P”时的 timestamp。事件类型 inferred
	日记账已提交审批	当用户正式将填写完成的日记账分录提交到审批 workflow 时，会触发此活动。它将日记账从草稿或未完成状态转变为待审批状态。
为何重要这是一个关键的里程碑，它标志着审批周期时间衡量和瓶颈识别的开始。它将数据录入阶段与复核审批阶段区分开来。获取方式此事件推导自 GL_JE_HEADERS 表中的状态变更，特别是当 APPROVAL_STATUS_CODE 更改为“REQUIRED”或“INITIATED”等值时。提交日期的 timestamp 也可能被记录。捕获追踪 GL_JE_HEADERS 中 APPROVAL_STATUS_CODE 更改为指示提交状态时的 timestamp。事件类型 inferred
	已附原始凭证	代表将发票或电子表格等原始凭证上传到日记账分录的操作。这通常是为了给审计人员和审批人提供背景信息和证据。
为何重要追踪此活动有助于了解延迟是否是由缺失原始凭证引起的。它还提供了关于合规性以及日记账分录在进入审批 workflow 之前完整性的见解。获取方式这可能难以作为独立事件进行追踪。它可以从链接到 GL_JE_HEADERS 中日记账分录记录的附件表（如 FND_ATTACHED_DOCUMENTS）中的 timestamp 中推导出来。捕获从与该日记账关联的 FND_ATTACHED_DOCUMENTS 表中记录的创建日期推断。事件类型 inferred
	日记账冲销已处理	已创建并过账冲销日记账分录，以抵消原始日记账在后续期间的财务影响。这是处理预提业务的常见操作。
为何重要追踪冲销有助于识别频繁被冲销的分录类型，并分析冲销流程本身的效率。这可以揭示应计项目管理中存在的问题。获取方式这是通过识别一个明确链接到原始分录作为冲销分录的新日记账分录来推断的。GL_JE_HEADERS 表具有 REVERSAL_PERIOD 和 REVERSAL_FLAG 等字段来识别并链接这些分录。捕获识别新日记账的创建和过账日期，该日记账的标题引用了被冲销的原始日记账。事件类型 inferred
	日记账分录已审核	可能在正式审批流程之前或作为流程一部分发生的审核步骤。这代表由同事或经理进行的检查，以在提交给最终审批人之前确保准确性和合规性。
为何重要隔离此活动有助于区分初步审核时间和最终审批时间。如果审核阶段是非正式但耗时的，这可以揭示隐藏的瓶颈。获取方式这可能是多级审批 workflow 中的一个明确步骤，记录在工作流历史表中。如果是口头非正式的，则无法捕获。如果在最终审批决定之前记录了特定的用户操作，则可以推导出来。捕获分析工作流历史表，在最终“已批准”状态之前查找中间审批或审核步骤。事件类型 inferred
	日记账分录已拒绝	审批人审核了日记账分录，并因错误、缺乏证明文件或违反政策而予以拒绝。此操作会将日记账退回给创建者进行更正。
为何重要此活动对于分析返工循环、退回率和“一次性准确率”指标至关重要。频繁的退回通常意味着数据质量或培训存在问题。获取方式此事件推导自 GL_JE_HEADERS 表中的状态变更，即当 APPROVAL_STATUS_CODE 更新为“REJECTED”时。工作流历史记录将记录该操作的用户和 timestamp。捕获追踪 GL_JE_HEADERS 中 APPROVAL_STATUS_CODE 设置为“REJECTED”时的 timestamp。事件类型 inferred
	日记账已更正并重新提交	在日记账分录被拒绝后，创建者进行必要的更正并重新提交审批。此活动代表同一日记账新一轮审批循环的开始。
为何重要追踪返工对于理解流程低效至关重要。此活动与“日记账分录已退回”结合，可以衡量返工时间和频率。获取方式这是之前处于“已退回”状态的日记账随后再次触发的“日记账提交审批”事件。它是通过分析单个“日记账分录 ID”的状态变更顺序来识别的。捕获识别同一 Case ID 在拒绝事件之后发生的提交事件时间戳。事件类型 inferred
	日记账过账已发起	已审批日记账过账到总账的流程已经开始。这可以是一个自动或手动步骤，将日记账放入过账程序处理的队列中。
为何重要此活动将审批与技术过账流程分开。审批与开始过账之间的延迟可能预示着过账引擎存在计划安排问题或资源限制。获取方式这可以从 GL_JE_BATCHES 表中的状态变更中推导出来，或者通过与日记账批次相关的过账请求的提交时间来确定。捕获识别特定日记账批次的总账过账程序的请求提交时间。事件类型 inferred
	过账已验证	过账后的验证步骤，由用户或系统确认日记账是否已正确过账且余额符合预期。这通常是一个手动控制步骤。
为何重要分析此活动有助于了解过账后在手动控制和质量保证上花费的时间。它可以突出自动化验证流程的机会。获取方式这在 Oracle Fusion 中不太可能是一个明确的事件。它需要从其他操作中推导出来，例如用户运行特定报告或更新自定义状态字段，但这并非标准操作。捕获需要自定义逻辑，例如追踪校验报告的运行时间或描述性弹性字段的更新。事件类型 inferred

推荐可选

提取指南

如何从 Oracle Fusion Financials 获取您的数据

步骤

进入“报告和分析”工作区: 登录 Oracle Fusion Financials。通过导航菜单进入“工具”，选择“报告和分析”。这是创建和管理所有分析与报告的中心枢纽。
创建 BI Publisher 数据模型: 对于此类事件日志提取，直接创建 BI Publisher 数据模型比从 OTBI 分析开始更有效。这允许您使用带有 UNION ALL 语句的自定义 SQL 查询。点击“浏览目录”按钮，进入合适的文件夹，选择“创建” > “数据模型”。
定义 SQL 查询数据集: 在数据模型编辑器中，点击“+”图标添加新数据集，选择“SQL 查询”。为数据集起一个描述性名称，例如 “JournalEventLog”。
输入 SQL 查询: 选择 “Oracle BI EE” 作为数据源。复制下方查询部分提供的完整 SQL 脚本，并将其粘贴到 SQL 查询文本区域。该查询旨在将所有必需的活动和属性整合到单一的统一结构中。
定义参数: 提供的查询使用了 :start_date、:end_date 和 :ledger_name 等参数。数据模型界面会自动检测到这些参数。您必须将它们配置为参数，设置合适的数据类型（日期或文本），并在需要时设置默认值。这可以实现灵活的报告执行。
从数据模型创建报告: 保存数据模型后，返回目录。点击“创建” > “报告”。在报告创建向导中，选择“使用现有数据模型”并找到您刚创建的数据模型。
配置报告布局: 在布局编辑器中，选择“表格”布局。务必将数据模型中的所有列拖放到表格中，以确保它们包含在导出文件中。无需进行复杂的格式化。
设置默认输出为 CSV: 点击报告编辑器中的“查看列表”链接。在“输出格式”部分，确保选中 “CSV”。建议取消选中所有其他格式以简化调度。保存报告。
调度报告进行自动提取: 在目录中找到保存的报告，点击“更多”，然后选择“调度”。创建一个新的调度任务。配置运行频率，例如每日或每周。
配置导出和交付: 在调度任务的“输出”选项卡中，指定输出详情。选择您的报告并将格式设为 “CSV”。在“目标”选项卡中，您可以将文件交付配置到内容服务器 (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 查询示例 config

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。点击导航菜单进入“工具”，然后选择“报告和分析”。这将打开商业智能目录。
创建新数据模型: 在 BI 目录中，点击“创建”按钮并选择“数据模型”。这将打开数据模型编辑器。
定义 SQL 查询数据集: 在数据模型编辑器中，点击“图表”选项卡上的加号图标，然后选择“SQL 查询”。在弹出对话框中为数据集命名，例如 ProcessMind_JE_EventLog。选择适当的数据源（通常为 FUSIONDB）。将提供的完整 SQL 查询粘贴到 SQL 查询文本区域。
定义参数: 查询使用了日期范围参数 (p_start_date 和 p_end_date)。Oracle BI Publisher 会自动检测这些参数。您应将其配置为“文本”类型参数，并设置易于理解的标签，如“开始日期 (YYYY-MM-DD)”和“结束日期 (YYYY-MM-DD)”。
保存数据模型: 粘贴查询并定义参数后，点击“确定”并将数据模型保存到合适的文件夹中，例如“共享文件夹/自定义/ProcessMining”。
创建报告: 点击数据模型编辑器右上角的“创建报告”按钮。报告创建向导将启动。
配置报告布局: 在向导中，选择使用当前数据模型的选项。选择“表格”布局。将数据源中的所有列拖放到表格布局区域。取消勾选“显示总计行”选项。
保存报告: 将报告保存在与数据模型相同的文件夹中。给它起一个描述性的名称，如“日记账分录事件日志报告”。
设置默认输出格式: 编辑报告并进入“查看”选项卡。在“输出格式”下，确保选中 CSV，并最好将其设为默认格式。这可以确保输出文件可直接用于后续分析。
运行并导出: 打开报告，系统会提示您输入开始和结束日期参数。输入所需的日期范围并点击“应用”。报告运行完成后，点击“导出”按钮并选择 CSV 格式下载事件日志文件。
准备上传: 打开下载的 CSV 文件。在上传到 ProcessMind 平台之前，请验证列标题是否与预期的属性名称（JournalEntryId、ActivityName、EventTime 等）一致，且数据格式正确。

配置

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

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 库。您需要一个拥有访问总账日记账 REST API 权限的 Oracle Fusion Financials 用户账户。获取您的 Oracle Fusion 实例 URL、用户名和密码。
脚本设置: 创建一个新的 Python 脚本文件。首先导入必要的库：requests、json、pandas 和 datetime。
配置: 在脚本中，定义 Oracle Fusion 凭证和基础 URL 的变量。同时，设置数据提取的参数，如查询的开始和结束日期。
身份验证: 脚本使用基础身份验证 (Basic Authentication)。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 端点以获取总账数据。
日期范围筛选: 脚本应配置 start_date 和 end_date 以限制数据提取范围。对于初步分析，建议选择 3 到 6 个月的时间段。对于持续使用，请配置每日或每周的增量加载。
分页限制: limit 参数控制每次 API 调用获取的记录数。通常设置在 200 到 500 之间较为高效，可以平衡 API 调用次数与响应大小。
所需 Oracle 角色: 用户账户必须拥有通过 REST API 读取日记账数据的权限，例如 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.")

步骤

进入“报告和分析”工作区: 登录 Oracle Fusion Financials。通过导航菜单进入“工具”，选择“报告和分析”。这是创建和管理所有分析与报告的中心枢纽。
创建 BI Publisher 数据模型: 对于此类事件日志提取，直接创建 BI Publisher 数据模型比从 OTBI 分析开始更有效。这允许您使用带有 UNION ALL 语句的自定义 SQL 查询。点击“浏览目录”按钮，进入合适的文件夹，选择“创建” > “数据模型”。
定义 SQL 查询数据集: 在数据模型编辑器中，点击“+”图标添加新数据集，选择“SQL 查询”。为数据集起一个描述性名称，例如 “JournalEventLog”。
输入 SQL 查询: 选择 “Oracle BI EE” 作为数据源。复制下方查询部分提供的完整 SQL 脚本，并将其粘贴到 SQL 查询文本区域。该查询旨在将所有必需的活动和属性整合到单一的统一结构中。
定义参数: 提供的查询使用了 :start_date、:end_date 和 :ledger_name 等参数。数据模型界面会自动检测到这些参数。您必须将它们配置为参数，设置合适的数据类型（日期或文本），并在需要时设置默认值。这可以实现灵活的报告执行。
从数据模型创建报告: 保存数据模型后，返回目录。点击“创建” > “报告”。在报告创建向导中，选择“使用现有数据模型”并找到您刚创建的数据模型。
配置报告布局: 在布局编辑器中，选择“表格”布局。务必将数据模型中的所有列拖放到表格中，以确保它们包含在导出文件中。无需进行复杂的格式化。
设置默认输出为 CSV: 点击报告编辑器中的“查看列表”链接。在“输出格式”部分，确保选中 “CSV”。建议取消选中所有其他格式以简化调度。保存报告。
调度报告进行自动提取: 在目录中找到保存的报告，点击“更多”，然后选择“调度”。创建一个新的调度任务。配置运行频率，例如每日或每周。
配置导出和交付: 在调度任务的“输出”选项卡中，指定输出详情。选择您的报告并将格式设为 “CSV”。在“目标”选项卡中，您可以将文件交付配置到内容服务器 (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 查询示例 config

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

您的记账到报表 - 日记账分录数据模板

您的记账到报表 - 日记账分录数据模板

记账到报表 - 日记账分录属性

记账到报表 - 日记账分录活动

提取指南

Oracle OTBI 和 BI Publisher 报告导出

步骤

配置

a 查询示例 config

带有直接 SQL 数据模型查询的 BI Publisher 报告

步骤

配置

a 查询示例 sql

使用 Oracle Fusion Financials REST API 的 Python 脚本

步骤

配置

a 查询示例 python

使用 BI Cloud Connector (BICC) 进行批量数据提取

步骤

配置

a 查询示例 config

准备好开始了吗？

即刻加速您的记录到报告 - 日记账分录流程

您的记账到报表 - 日记账分录数据模板

您的记账到报表 - 日记账分录数据模板

记账到报表 - 日记账分录属性

记账到报表 - 日记账分录活动

提取指南

Oracle OTBI 和 BI Publisher 报告导出

步骤

配置

a 查询示例 config

带有直接 SQL 数据模型查询的 BI Publisher 报告

步骤

配置

a 查询示例 sql

使用 Oracle Fusion Financials REST API 的 Python 脚本

步骤

配置

a 查询示例 python

使用 BI Cloud Connector (BICC) 进行批量数据提取

步骤

配置

a 查询示例 config

我们重视您的隐私

准备好开始了吗？

即刻加速您的记录到报告 - 日记账分录流程