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

您的记录到报告：日记账分录数据模板

Oracle Fusion Financials

← 返回改进指南

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

您的记录到报告：日记账分录数据模板

此数据模板全面介绍如何提取和整理Oracle Fusion Financials中的日记账分录数据，列出需要收集的关键属性和跟踪的主要活动，帮助您获取流程分析所需的信息。模板还提供实用指导，说明如何直接从系统提取这些数据。

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

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

下载模板

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

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

5 必填 6 建议填写 10 选填

	名称	说明
	开始时间 EventTime	表示特定活动或事件发生时间的时间戳。
说明此时间戳标记活动执行的确切日期和时间。在流程挖掘中，它是确定事件顺序和计算事件间时长的主要时间依据。事件时间的准确性对所有基于时间的分析都至关重要，包括计算周期时间、识别瓶颈，以及监控服务级别协议的达成情况。它提供了重建实际流程所需的时间顺序。重要性此时间戳对于排列事件顺序、计算所有流程时长以及开展任何基于时间的分析都至关重要。获取途径此信息通常存储在审计跟踪表中，或作为特定事件在GL_JE_HEADERS和GL_JE_LINES等交易表中的“最后更新日期”或“创建日期”记录。示例 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:00Z
	日记账分录ID JournalEntryId	用于唯一标识单笔日记账分录，并关联所有相关财务交易活动。
说明日记账分录ID是主要案例标识符，可唯一关联特定财务交易的所有相关活动。借助此ID，您可以跟踪单笔日记账分录从启动到最终过账的完整生命周期，确保所有借方和贷方均已入账。在流程挖掘中，此ID对于重建每笔日记账分录的端到端流程至关重要。它将“日记账分录已创建”“日记账已提交审批”和“日记账分录已过账”等不同事件串联成连贯的流程，使您能够分析周期时间、瓶颈和流程差异。重要性这是从头到尾跟踪日记账分录的基础标识，可用于分析每个唯一案例的完整流程。获取途径这是GL_JE_HEADERS和GL_JE_LINES等总账核心表中的主键。示例 JE100523JE202311001882019
	活动 ActivityName	日记账分录流程中某个时间点发生的具体业务事件或任务名称。
说明活动名称描述日记账分录生命周期中的单个步骤，例如“日记账分录已创建”或“日记账分录已批准”。此数据对于构建流程图、了解事件顺序至关重要。分析活动有助于识别流程路径，包括标准路径、偏差和返工循环。跟踪不同活动可以衡量各阶段（如审批）所花费的时间，并找出最耗时或最容易出错的步骤。重要性它定义流程步骤，构成流程图的基础，并支持对流程走向、瓶颈和差异进行分析。获取途径此属性通常根据总账模块中与日记账分录对象关联的状态变化、事件日志或审计跟踪表得出。示例日记账分录已创建日记账已提交审批日记账分录已批准日记账分录已过账
	数据最近更新时间 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系统中的来源。分录可以在总账中手动创建，也可以由应付账款、应收账款或固定资产等子分类账自动生成。日记账来源是自动化分析的重要属性。它有助于区分手动创建和系统生成的分录，支持计算“日记账分录自动化率”KPI。不同来源的分录在流程复杂度和效率方面往往存在显著差异。重要性区分手动和自动生成的分录，这对于衡量自动化率和分析流程差异至关重要。获取途径此信息位于GL_JE_HEADERS表中，字段名称通常为JE_SOURCE。示例手动应付账款资产应收账款
	日记账类别 JournalCategory	日记账分录的类别，例如“应计”“调整”或“重新分类”。
说明日记账类别根据业务用途对分录进行分类。不同类别可能采用不同的流程路径、审批规则或周期时间，因此此分类可支持更细致的流程分析。例如，月末调整日记账的流程可能比常规应计项目更复杂、时效要求更高。按类别分析流程有助于发现这些差异，并针对不同类型的日记账分录制定改进措施。重要性按日记账的业务用途细分流程，揭示不同类型分录的行为和绩效差异。获取途径此信息位于GL_JE_HEADERS表中，字段名称通常为JE_CATEGORY。示例应计手动调整重估
	日记账金额 JournalAmount	日记账分录的货币总额，通常为借方金额之和。
说明此属性表示日记账分录涉及的财务总额。金额可能会显著影响流程。例如，大额分录可能需要额外审批或更严格的审核。分析日记账金额可以按财务影响对案例进行细分，帮助回答“高金额分录是否需要更长时间才能获批？”或“超过某个金额阈值的分录是否更容易被拒绝？”等问题，为流程分析提供重要的业务背景。重要性提供关键业务背景，支持按财务影响开展分析，并帮助识别大额分录是否采用不同的流程。获取途径此值通常根据GL_JE_LINES表中某笔日记账分录的借方或贷方金额合计计算得出。示例 5000.00125000.75750.50
	用户 UserName	执行活动的用户，例如创建、批准或过账日记账分录的用户。
说明此属性用于标识负责执行活动的具体员工或系统用户，对于了解工作量分配、绩效和个人瓶颈至关重要。在分析中，用户名可用于按用户筛选流程、比较用户效率，以及调查延迟或错误的原因。它直接支持“各团队用户工作量与效率”等仪表板，以及“用户日记账瓶颈指数”等KPI。重要性标明流程步骤的责任人，支持分析用户工作量、个人绩效和培训需求。获取途径可在GL_JE_HEADERS等交易表（例如CREATED_BY、LAST_UPDATED_BY字段）及相关审计跟踪表中找到。示例 john.doesusan.smithautoprocess_user
	用户部门 UserDepartment	执行活动的用户所属部门或团队。
说明此属性通过将活动关联到特定部门（如“财务”“会计运营”或“内部审计”），提供组织背景信息。通常可从用户主数据中获取。按用户部门分析有助于识别跨部门瓶颈、比较团队绩效，并了解组织内不同部门的流程执行差异。此属性对于资源分配和有针对性的流程改进举措很有价值。重要性提供组织背景信息，支持按团队或部门分析绩效，并发现跨职能效率低下的问题。获取途径此信息通常不在交易表中。必须将用户名与用户主数据表或HR系统数据联接后获取。示例总账会计财务报告应付账款
	会计期间 AccountingPeriod	日记账分录过账所属的会计期间，例如“2024年1月”。
说明会计期间用于指定交易确认所属的财务期间，是所有财务报告和分析的基础。在流程挖掘中，此属性对于趋势分析至关重要。它支持比较不同月份或季度的流程绩效，例如周期时间或拒绝率。这有助于识别季节性影响，例如月末结账期间工作量增加，并衡量流程改进随时间产生的效果。重要性支持随时间分析KPI趋势，帮助衡量流程改进效果，并识别月末工作压力等季节性规律。获取途径此信息位于GL_JE_HEADERS表中，字段名称通常为PERIOD_NAME。示例 2024年1月2024年2月2024年3月
	冲销标记 ReversalIndicator	用于标识日记账分录是否为另一笔分录冲销分录的标记。
说明此布尔属性用于识别为冲销先前已过账分录而创建的日记账分录。冲销分录属于特定类型，通常采用独特的流程，有时也会出现问题。分析此标记是支持“日记账冲销流程绩效”仪表板的关键。它可以单独筛选冲销流程，衡量其频率、原因和周期时间，帮助发现原始分录中导致冲销的潜在问题。重要性有助于单独分析冲销流程，而冲销往往表明原始交易存在错误或问题。获取途径此信息可能来自GL_JE_HEADERS表中的特定标记，例如ACCRUAL_REV_FLAG，也可通过关联至被冲销的原始日记账来识别。示例 truefalse
	分类账名称 LedgerName	记录日记账分录的总账名称。
说明分类账名称用于标识日记账分录所属的具体分类账。拥有多个法人实体或报告要求的组织可能设有多个分类账，例如用于企业会计的主分类账，以及用于本地法定报告的辅助分类账。此属性对于筛选和比较不同法人实体或业务单元的流程至关重要。它可确保分析基于正确的组织背景开展，对于财务合规和报告尤为重要。重要性支持按不同法人实体或会计框架筛选和比较流程分析，对于大型组织至关重要。获取途径可在GL_JE_HEADERS表中找到，通常通过LEDGER_ID关联GL_LEDGERS以获取名称。示例 US主分类账UK法定分类账全球合并分类账
	币种 CurrencyCode	日记账分录金额所用的币种代码，例如USD、EUR或GBP。
说明币种代码用于指定日记账分录中财务金额的币种。对于使用多种币种进行交易的跨国组织，此信息至关重要。此属性可确保财务金额得到正确解读，支持按币种筛选；在比较或汇总不同地区的金额时，也是必需字段。重要性为财务金额提供必要背景，确保金额得到准确解读，并支持按不同币种开展分析。获取途径此信息位于GL_JE_HEADERS表中，字段名称通常为CURRENCY_CODE。示例 USDEURGBPJPY
	是否按时过账 IsOnTimePosting	计算得出的标记；如果日记账在目标日期当天或之前过账，则值为true。
说明此布尔属性通过比较“日记账分录已过账”活动的时间戳与“目标过账日期”得出。如果过账时间不晚于目标日期，值为“true”；否则为“false”。此属性简化了“日记账按时过账率”KPI的计算。您可以轻松筛选和分析延迟分录，识别常见原因，例如特定日记账类别或审批人。重要性以明确的二元结果反映过账绩效，简化按时率分析和延迟根因分析。获取途径此属性由流程挖掘工具计算得出，需要“TargetPostingDate”属性和“日记账分录已过账”活动的时间戳。示例 truefalse
	是否返工 IsRework	计算得出的标记；如果日记账分录经历过拒绝和更正循环，则值为true。
说明此布尔属性通过计算识别经历过返工的案例。通常，凡是发生在“日记账分录已拒绝”事件之后的活动（例如“日记账已更正并重新提交”），都会标记为“true”。是否返工是一个重要的分析属性，可用于轻松量化返工循环的数量和影响。它直接支持“日记账平均返工率”KPI，并帮助比较返工案例与首次处理正确的案例在流程路径和时长上的差异。重要性直接标记经历过低效返工循环的案例，便于量化流程失误的频率和影响。获取途径源系统中不提供此属性。流程挖掘工具会分析每个案例的活动顺序并计算得出。示例 truefalse
	目标过账日期 TargetPostingDate	预先设定的日记账分录预期过账日期。
说明目标过账日期表示日记账分录应过账至总账的截止日期，通常由服务级别协议（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
	过账状态 PostingStatus	日记账分录当前的过账状态，例如“未过账”或“已过账”。
说明此属性反映日记账分录在总账中的当前过账状态，是判断日记账分录生命周期进度的重要指标，尤其适用于仍在处理中的案例。过账状态是“日记账分录实时状态跟踪”仪表板的重要数据，可展示所有活跃分录的当前情况。它有助于监控尚未过账日记账的积压，并识别流程最后阶段的延迟。重要性显示日记账当前状态，对于监控积压和跟踪处理中分录的进度至关重要。获取途径此信息位于GL_JE_HEADERS表中，列名通常为STATUS或POSTING_STATUS。示例已过账未过账错误

必填建议填写选填

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

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

5 建议填写 7 选填

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

建议填写选填

数据提取指南

如何从Oracle Fusion Financials获取数据

步骤

进入“Reports and Analytics”工作区：登录Oracle Fusion Financials。通过Navigator菜单依次进入Tools和Reports and Analytics。这里是创建和管理所有分析与报表的中心。
创建BI Publisher数据模型：对于此类事件日志提取，直接创建BI Publisher数据模型比从OTBI分析开始更合适。这样可以使用包含UNION ALL语句的自定义SQL查询。点击“Browse Catalog”，进入合适的文件夹，然后选择Create > Data Model。
定义SQL查询数据集：在数据模型编辑器中，点击“+”图标添加数据集，并选择“SQL Query”。为数据集设置易于识别的名称，例如“JournalEventLog”。
输入SQL查询：选择“Oracle BI EE”作为数据源。将下方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”。此外，用户的数据安全配置文件必须允许访问所查询的特定分类账和业务单元。
计划任务：为获得最佳性能并避免影响系统用户，建议在非高峰时段（例如夜间）运行提取任务。

查询示例 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。选择合适的数据源，通常为FUSIONDB。将提供的完整SQL查询粘贴到SQL Query文本框。
定义参数：查询使用日期范围参数（p_start_date和p_end_date）。Oracle BI Publisher会自动识别这些参数。请将其配置为Text类型，并设置易于理解的标签，例如“开始日期（YYYY-MM-DD）”和“结束日期（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文件，以便以清晰、结构化的格式导入流程挖掘工具。

查询示例 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进行分析。

配置

凭据：基本身份验证需要有效的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角色，或具有同等权限的自定义角色。

查询示例 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总账主题区域或Public View Objects，以及必要的源系统授权。请与Oracle管理员确认已配置的BICC交付目标及其访问凭据。
在BI Cloud Connector控制台中，确定包含日记账抬头、日记账行、审批历史、附件、过账状态、对账状态和冲销信息的Public View Objects。记录环境中配置的准确对象名称和属性名称。执行提取脚本前，请将占位符替换为这些名称。
为选定的总账Public View Objects配置BICC任务。如果存在可靠的最后更新时间属性，请使用增量提取。首次加载时，选择三至六个月的受控时间范围，或其他已批准的会计期间范围。
配置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设为事件时间戳。

配置

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

查询示例 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日记账分录数据转化为可执行的洞察，加快记录到报告周期。立即开始优化财务运营。

返回改进指南

下载模板

立即加快记录到报告：日记账分录流程

将日记账分录周期时间缩短30%，简化财务运营。

开始免费试用

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

您的记录到报告：日记账分录数据模板

您的记录到报告：日记账分录数据模板

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

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

数据提取指南

Oracle OTBI和BI Publisher报表导出

步骤

配置

查询示例 sql

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

步骤

配置

查询示例 sql

使用Oracle Fusion Financials REST API的Python脚本

步骤

配置

查询示例 python

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

步骤

配置

查询示例 sql

准备好开始了吗？

立即加快记录到报告：日记账分录流程