您的应收账款数据模板
您的应收账款数据模板
- 应收账款分析推荐属性全集
- 需要监控的核心流程活动和里程碑
- Oracle Fusion Financials专用数据提取指南
应收账款属性
| 名称 | 说明 | ||
|---|---|---|---|
| 事件时间戳 EventStartDateTime | 活动发生的具体日期和时间。 | ||
| 说明 该属性记录活动在系统中发生的准确时刻，用于按时间顺序排列事件，也是流程挖掘中所有基于时间的计算依据。 通过分析时间戳，企业可以计算活动之间的周期时间，例如发票创建到发送之间的时长。它对于衡量应收账款周转天数等KPI，以及识别付款行为中的时间模式至关重要。 为什么重要 用于计算持续时间、交付周期和周期时间。 获取位置 Oracle Fusion Financials：各类交易表中的CREATION_DATE或LAST_UPDATE_DATE列。 示例 2023-10-15T08:30:00Z2023-10-16T14:45:12Z2023-11-01T09:00:00Z | |||
| 发票编号 InvoiceNumber | Oracle Fusion为发票交易分配的唯一标识符。 | ||
| 说明 该属性是应收账款模块中识别财务义务的唯一键，将调整、争议和付款等后续活动与原始销售交易关联起来。 在流程挖掘分析中，该属性充当Case ID。分析人员可以追踪应收账款从创建到完全结清或核销的端到端生命周期，从而计算周期时间并识别流程变体。 为什么重要 它是跟踪从授信到收款生命周期的基本分析单位。 获取位置 Oracle Fusion Financials：RA_CUSTOMER_TRX_ALL.TRX_NUMBER 示例 INV-2023-00110056789AR-99887755002211 | |||
| 活动名称 ActivityName | 应收账款流程中执行的具体事件或操作。 | ||
| 说明 该属性描述流程中采取的步骤，例如创建发票、登记付款或发起争议。它定义流程图的流向，并支持可视化事件顺序。 分析人员利用此字段识别流程变体、循环和瓶颈。该字段对于判断是否遵循标准操作流程，以及计算返工或手动干预等特定事件的发生频率至关重要。 为什么重要 用于定义流程顺序并展示事件序列。 获取位置 来源于交易历史表，例如AR_PAYMENT_SCHEDULES_ALL、RA_CUST_TRX_LINE_GL_DIST_ALL。 示例 发票已创建付款提醒已发送已登记部分付款争议案件已创建 | |||
| 最后数据更新时间 LastDataUpdate | 数据在挖掘工具中最后一次刷新的时间戳。 | ||
| 说明 该属性表示数据集最后一次与源Oracle系统同步的时间，帮助用户了解分析数据的新鲜度，以及洞察是否反映运营当前状态。 监控此字段有助于确保仪表板展示最新信息，尤其适用于监控未结争议或未应用现金等运营情况。 为什么重要 提供数据新鲜度和可靠性的背景信息。 获取位置 提取时的系统时间。 示例 2023-11-15T23:59:59Z2023-11-16T00:00:00Z | |||
| 源系统 SourceSystem | 数据产生并留存的记录系统。 | ||
| 说明 该属性标识流程数据提取自哪个软件环境。在此场景中，它确认数据来自Oracle Fusion Financials环境。 对于单一系统提取，它通常是静态值；但在合并多个ERP实例或集成第三方催收工具时，该属性至关重要，可确保多系统流程环境中的数据血缘和可追溯性。 为什么重要 确保数据血缘，并区分不同的ERP实例。 获取位置 在提取过程中硬编码，或在数据管道中配置。 示例 Oracle Fusion FinancialsOracle Cloud ERP-美国Oracle Cloud ERP-EMEA | |||
| 业务单元 BusinessUnit | 组织内负责该发票的运营实体。 | ||
| 说明 该属性映射至Oracle Fusion中的组织ID，代表拥有应收账款的具体业务单元或部门，可用于比较企业不同部分的流程绩效。 比较不同业务单元的争议解决时间或DSO等KPI，有助于管理层识别高绩效团队并推广最佳实践，也能发现可能需要增加资源或重新设计流程的单元。 为什么重要 用于组织基准比较和绩效对比的关键维度。 获取位置 Oracle Fusion Financials：通过ORG_ID关联HR_ORGANIZATION_UNITS.NAME。 示例 美国东部销售EMEA服务APAC制造 | |||
| 交易类型 TransactionType | 应收账款单据的分类，包括发票、贷项通知单和借项通知单。 | ||
| 说明 该属性区分不同类型的财务单据，常见值包括Invoice、Credit Memo和Debit Memo。这一区分对“贷项通知单数量与返工”仪表板至关重要。 通过筛选此属性，分析人员可以单独识别由贷项通知单引起的返工循环，或专门分析主要开票流程，从而了解应收账款工作量的构成。 为什么重要 区分标准发票、调整和更正。 获取位置 Oracle Fusion Financials：RA_CUST_TRX_TYPES_ALL.NAME 示例 发票贷项通知单借项通知单拒付 | |||
| 催收人员姓名 CollectorName | 分配给该发票的催收专员或资源名称。 | ||
| 说明 该属性标识负责催收发票款项的具体员工或团队成员，是“催收人员处理量”仪表板的关键维度。 利用此字段的数据，组织可以衡量每位催收人员的生产力、识别培训需求并平衡工作量，同时强化责任落实，推动财务团队统一催收方式。 为什么重要 用于资源绩效分析和工作量平衡的关键字段。 获取位置 Oracle Fusion Financials：与客户档案关联的AR_COLLECTORS.NAME。 示例 John Smith催收团队AJane Doe | |||
| 到期日 DueDate | 预计收到付款的日期。 | ||
| 说明 该属性是根据发票日期和付款条款计算出的付款截止日期，用于判断付款是否逾期。 它用于“催收提醒时间偏差”KPI，以衡量团队相对于截止日期采取行动的主动程度，也是账龄报告中区分未到期和逾期应收账款的阈值。 为什么重要 判断逾期情况和按时付款绩效的主要基准。 获取位置 Oracle Fusion Financials：AR_PAYMENT_SCHEDULES_ALL.DUE_DATE 示例 2023-11-302023-12-152024-01-01 | |||
| 发票金额 InvoiceAmount | 发票的货币总金额。 | ||
| 说明 该属性表示发票原始应付金额，是许多分析的主要加权因素，使企业能够优先处理高价值交易，而非仅关注交易量。 在“未应用贷项与收入流失视图”中，该字段有助于量化未解决项目的财务影响，也用于计算加权平均应收账款周转天数，从更贴近财务价值的角度评估流程效率。 为什么重要 为分析提供财务权重，并支持基于价值的优先级排序。 获取位置 Oracle Fusion Financials：RA_CUSTOMER_TRX_ALL.AMOUNT_DUE_ORIGINAL 示例 1500.00250.5010000.00 | |||
| 客户名称 CustomerName | 交易中被开具账单的实体名称。 | ||
| 说明 该属性标识与发票关联的客户，是在客户层面分析付款行为、争议频率和催收成效的基础。 分析人员利用此字段找出经常延迟付款或发起争议的客户。相关洞察支持“客户付款行为分析”仪表板，并帮助针对不同客户制定信用条款和催收策略。 为什么重要 客户分析和风险画像不可或缺的字段。 获取位置 Oracle Fusion Financials：通过BILL_TO_CUSTOMER_ID关联HZ_PARTIES.PARTY_NAME。 示例 Acme CorpGlobex CorporationSoylent Corp | |||
| 客户细分 CustomerSegment | 根据客户规模、行业或风险进行的客户分类。 | ||
| 说明 该属性将客户划分为战略客户、企业客户、中小企业客户或高风险客户等群组，通常来源于Oracle Fusion中的客户类别或档案类别。 利用此属性可以分析不同市场细分中的流程变体。例如，验证“战略客户”是否获得预期的高标准服务，或确认“高风险客户”是否受到密切监控并遵守付款要求。 为什么重要 支持按细分分析催收策略和风险。 获取位置 Oracle Fusion Financials：HZ_CUSTOMER_PROFILES.PROFILE_CLASS_ID。 示例 大型企业小型企业政府高风险 | |||
| 是否自动执行 IsAutomated | 用于标识活动是否在无人干预的情况下执行。 | ||
| 说明 该布尔属性判断活动由系统流程（例如AutoInvoice、AutoLockbox）还是人工用户执行，是“现金应用自动化率”KPI的主要依据。 通过持续跟踪自动活动与手动活动的比例，组织可以验证数字化转型举措的成效，并识别仍然依赖手动操作的具体流程步骤。 为什么重要 衡量数字化转型和效率的主要指标。 获取位置 根据UserName计算，例如User == 'BATCH_USER'时为true。 示例 truefalse | |||
| 用户名 UserName | 执行该活动的系统用户。 | ||
| 说明 该属性记录执行具体活动的人员登录ID或姓名，例如登记发票或匹配银行对账单的人员，对应通用“用户”字段。 这些数据对合规审计和“催收人员处理量”仪表板至关重要，可区分机器驱动的操作（通常由“System”用户执行）与人工操作，支持自动化分析。 为什么重要 支持用户级绩效跟踪和职责分离分析。 获取位置 Oracle Fusion Financials：与用户表关联的CREATED_BY或LAST_UPDATED_BY列。 示例 sysadminjsmithfinance_batch_job | |||
| 争议原因 DisputeReason | 发起争议时分配的类别或原因代码。 | ||
| 说明 该属性记录“争议案件已开启”事件发生时提供的理由，常见值包括“定价错误”“数量不符”或“货物损坏”。 在“争议生命周期与瓶颈”仪表板中分析此属性，有助于识别付款延迟的根本原因。如果“定价错误”频繁出现，企业应调查上游销售报价流程，而不只是催收流程。 为什么重要 对延迟付款和返工进行根因分析的关键字段。 获取位置 Oracle Fusion Financials：RA_CM_REQUESTS.REASON_CODE或AR_DISPUTE_HISTORY。 示例 价格争议税务错误未收到货物重复开票 | |||
| 付款条款 PaymentTerms | 双方约定的付款时间条件，例如Net 30。 | ||
| 说明 该属性定义合同约定的付款期限，用于计算到期日，也是“催收策略有效性”仪表板的基础。 不同客户的付款条款差异可以解释DSO差异。该属性支持分析人员对绩效数据进行标准化，避免将Net 60客户与Net 30客户相比时，误判前者为“付款缓慢”。 为什么重要 结合合同约定评估付款速度。 获取位置 Oracle Fusion Financials：RA_TERMS.NAME 示例 账期30天立即付款2/10，账期30天账期60天 | |||
| 创建来源 CreationSource | 发票的来源，用于表明发票是手动创建还是导入。 | ||
| 说明 该属性揭示发票如何进入Oracle系统，例如“手动录入”“AutoInvoice”或特定外部数据源。它是“渠道”通用映射的替代指标。 这对“现金应用自动化监控”至关重要，可帮助区分完全数字化的流程与需要手动设置的流程。“手动录入”数量较高，可能表明上游集成不足或系统存在缺陷。 为什么重要 识别上游自动化程度和数据来源。 获取位置 Oracle Fusion Financials：RA_BATCH_SOURCES_ALL.NAME 示例 AutoInvoice手动项目实施订单管理 | |||
| 区域 Region | 与业务单元或客户关联的地理区域。 | ||
| 说明 该属性将交易映射至更大的地理区域，例如北美、EMEA或APAC，适用于高层管理报告和“DSO与现金周期趋势”仪表板。 区域分析有助于考虑付款行为中的文化差异，例如南欧相比美国通常采用更长的付款期限，并确保全球KPI结合正确的本地背景进行解读。 为什么重要 为全球报告提供高层级地理细分。 获取位置 Oracle Fusion Financials：根据业务单元或客户地址得出。 示例 北美EMEAAPACLATAM | |||
| 折扣资格日期 DiscountEligibilityDate | 客户可享受提前付款折扣的最后日期。 | ||
| 说明 该属性标记客户享受“2/10 Net 30”等条款的截止日期，即10天内付款可享受2%折扣。它是“提前付款折扣分析”仪表板所必需的字段。 将付款情况与该日期进行比较，可以得出“提前付款折扣获取率”，帮助企业了解折扣策略是否有效加快现金流，或是否被客户忽略。 为什么重要 支持分析激励措施的有效性和现金流加速效果。 获取位置 Oracle Fusion Financials：AR_PAYMENT_SCHEDULES_ALL.DISCOUNT_DATE 示例 2023-11-102023-12-05 | |||
| 是否返工 IsRework | 用于标识发票是否经历更正或争议循环。 | ||
| 说明 该布尔属性识别发票是否经历与错误更正相关的活动，例如“已开具贷项通知单”或“发票已调整”。它支持“贷项通知单数量与返工”仪表板。 识别返工案例有助于将“正常路径”流程与问题流程区分开来。高返工率通常预示主数据或销售订单录入流程存在上游数据质量问题。 为什么重要 用于识别流程顺序中的浪费和低效。 获取位置 计算逻辑：当案例包含“已开具贷项通知单”或“争议案件已开启”时为True。 示例 truefalse | |||
| 货币代码 CurrencyCode | 发票金额所使用的货币。 | ||
| 说明 该属性指定财务金额所使用的货币，例如USD、EUR。正确解读发票金额，以及在需要统一全球报告货币时执行货币转换，都离不开此属性。 对于全球化组织，该属性有助于比较不同经济区域的催收绩效，并帮助财务团队将汇率影响与运营流程绩效区分开来。 为什么重要 为多币种环境中的财务数值提供背景信息。 获取位置 Oracle Fusion Financials：RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE 示例 USDEURGBPJPY | |||
应收账款活动
| 活动 | 说明 | ||
|---|---|---|---|
| 发票已创建 | 该活动表示系统中发票记录的初始创建，记录交易表头首次保存到Oracle应收账款表中的时间戳。 | ||
| 为什么重要 标志流程生命周期的开始，并为账龄计算提供基准。对于计算总周期时间和发送前置时间至关重要。 获取位置 来源于RA_CUSTOMER_TRX_ALL表的CREATION_DATE或TRX_DATE列。 采集 插入交易行时记录 事件类型 explicit | |||
| 发票已发送 | 表示通过打印、电子邮件或XML向客户发送发票，标志着发票从企业移交给客户。 | ||
| 为什么重要 对于衡量Billing Dispatch Performance至关重要。创建与发送之间的间隔会直接延迟现金催收周期。 获取位置 来源于RA_CUSTOMER_TRX_ALL中的PRINTING_ORIGINAL_DATE；如果使用XML，则来源于Oracle Collaboration Messaging Framework中的特定日志。 采集 比较状态字段变更前后的值 事件类型 inferred | |||
| 发票已完成 | 表示发票创建流程已完成，发票可以继续处理、打印和过账。当交易状态从未完成变为完成时，该事件随之发生。 | ||
| 为什么重要 区分起草时间与处理时间。此处的延迟表明内部开票生成流程存在瓶颈。 获取位置 当RA_CUSTOMER_TRX_ALL中的COMPLETE_FLAG变为“Y”时识别。 采集 比较状态字段变更前后的值 事件类型 inferred | |||
| 发票已结清 | 发票在系统中关闭的最终状态，通常是因为付款、贷项通知单或调整使余额归零。 | ||
| 为什么重要 该事件的时间戳用于计算应收账款周转天数（DSO），代表流程实例的结束。 获取位置 当AR_PAYMENT_SCHEDULES_ALL中的STATUS变更为“CL”（Closed）时识别。 采集 比较状态字段变更前后的值 事件类型 inferred | |||
| 已收到全额付款 | 当收款应用使发票余额降为零时发生。这是收款流程的主要成功事件。 | ||
| 为什么重要 对提前付款折扣分析至关重要。该事件的时间决定现金是否在折扣期限内收回。 获取位置 来源于AR_RECEIVABLE_APPLICATIONS_ALL，其中STATUS = 'APP'，且结果AMOUNT_DUE_REMAINING为0。 采集 通过比较字段X和Y得出 事件类型 calculated | |||
| 已登记部分付款 | 当收款应用于发票但金额低于未结余额总额时发生。发票仍保持未结状态，但余额有所减少。 | ||
| 为什么重要 频繁发生表明付款行为较为分散（部分付款频率KPI），会增加对账工作量。 获取位置 来源于AR_RECEIVABLE_APPLICATIONS_ALL，其中STATUS = 'APP'且AMOUNT_APPLIED < AMOUNT_DUE_REMAINING。 采集 执行交易X时记录 事件类型 explicit | |||
| 争议已解决 | 表示争议调查结束。结果可能是批准贷项通知单（争议有效）或拒绝（争议无效）。 | ||
| 为什么重要 用于计算争议平均解决时间。解决时间过长会降低客户满意度并推高DSO。 获取位置 根据RA_CM_REQUESTS_ALL中状态变更为“APPROVED”或“REJECTED”得出。 采集 比较状态字段变更前后的值 事件类型 inferred | |||
| 争议案件已创建 | 标志针对发票正式争议的启动。在调查问题期间，标准催收活动会暂停。 | ||
| 为什么重要 重要的瓶颈指标。高争议率通常表明履约或计费准确性存在上游质量问题。 获取位置 通过RA_CM_REQUESTS_ALL中的记录，或与发票关联的特定Credit Memo Request工作流识别。 采集 执行交易X时记录 事件类型 explicit | |||
| 付款提醒已发送 | 记录向客户发出催缴函或催收提醒的事件。该事件由Advanced Collections模块生成。 | ||
| 为什么重要 对于分析Collection Strategy Effectiveness至关重要。将其与付款数据关联，有助于确定哪种提醒策略能够最快回收现金。 获取位置 位于IEX_DUNNING或IEX_STRATEGY_WORK_ITEMS表中，并与客户账户关联。 采集 执行交易X时记录 事件类型 explicit | |||
| 发票已核销 | 一种特定的调整类型：剩余余额被认定为无法收回，并作为坏账核销。这是一个负向终止状态。 | ||
| 为什么重要 对监控财务健康状况至关重要，可将运营效率（付款速度）与信用质量问题区分开来。 获取位置 来源于AR_ADJUSTMENTS_ALL，其中调整类型归类为“Write-off”，或关联至坏账账户。 采集 执行交易X时记录 事件类型 explicit | |||
| 发票已调整 | 记录对发票余额进行的手动调整，例如小额核销或汇率调整，与贷项通知单相区分。 | ||
| 为什么重要 帮助识别收入流失，以及无需付款即可清除余额的非标准流程路径。 获取位置 来源于与发票关联的AR_ADJUSTMENTS_ALL表。 采集 执行交易X时记录 事件类型 explicit | |||
| 发票已过账至总账 | 记录发票会计分录完成并转入General Ledger的事件，确保财务合规并为期间结账做好准备。 | ||
| 为什么重要 虽然不会影响客户视图，但此处的延迟会影响财务结账周期和报告及时性。 获取位置 来源于RA_CUST_TRX_LINE_GL_DIST_ALL表中的GL_DATE。 采集 执行交易X时记录 事件类型 explicit | |||
| 已开具贷项通知单 | 记录应用于发票的贷项通知单交易创建。该交易会减少应付余额，通常用于处理争议或退货。 | ||
| 为什么重要 用于跟踪贷项通知单返工率和收入流失。贷项通知单频繁出现，通常表明计费存在系统性错误。 获取位置 来源于RA_CUSTOMER_TRX_ALL，其中TRX_TYPE为Credit Memo，且RELATED_CUSTOMER_TRX_ID与发票匹配。 采集 执行交易X时记录 事件类型 explicit | |||
| 已收到付款承诺 | 记录客户承诺在指定日期前支付指定金额。催收人员通常会在与客户沟通时手动录入该信息。 | ||
| 为什么重要 对于Customer Payment Behavior Analysis至关重要。承诺未兑现表明信用风险较高，未来可能形成坏账。 获取位置 来源于Collections模块中的IEX_PROMISE_DETAILS表。 采集 执行交易X时记录 事件类型 explicit | |||
| 银行对账单已匹配 | 表示应用于发票的收款已与银行对账单中的一行完成对账，确认现金已实际到账。 | ||
| 为什么重要 用于衡量现金应用自动化程度。付款登记与银行匹配之间的时间差代表尚未确认的现金。 获取位置 通过对账参考信息，将AR_CASH_RECEIPTS_ALL与CE_STATEMENT_LINES（现金管理）关联。 采集 比较状态字段变更前后的值 事件类型 inferred | |||
提取指南
步骤
登录Oracle BI Cloud Connector（BICC）控制台。进入Manage Offerings and Data Stores部分。
配置存储连接。确保已连接Oracle Universal Content Management（UCM）或外部对象存储（例如OCI Object Storage），用于存放提取的CSV/Parquet文件。
选择Financials Offering。找到Financials Offering，以访问应收账款View Objects。
选择并配置View Objects（VO）。您必须选择构建事件日志所需的特定Public View Objects（PVO）。关键PVO包括：
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.TransactionHeaderExtractPVO（发票抬头）
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.TransactionLineExtractPVO（发票行）
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceiptApplicationExtractPVO（付款和贷项通知单应用）
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.AdjustmentExtractPVO（调整和核销）
- FscmTopModelAM.FinExtractAM.IexBiccExtractAM.PromiseDetailExtractPVO（付款承诺）
- FscmTopModelAM.FinExtractAM.IexBiccExtractAM.StrategyWorkItemExtractPVO（催款/提醒）
定义筛选条件（裁剪数据）。在Manage Extract Schedules中，或在PVO配置内，根据CreationDate或LastUpdateDate设置筛选条件，提取与分析期间相关的数据（例如最近12个月）。
计划提取任务。创建每日运行这些提取任务的作业计划。初始Full Load后选择Incremental Load，仅获取发生变化的数据。
下载并摄取数据。使用自动化脚本或集成工具从UCM/Object Storage获取文件，并将其加载到数据仓库的暂存表中（例如STG_AR_TRX_HEADER、STG_AR_APPLICATIONS）。
应用转换逻辑。针对暂存表运行Query部分提供的SQL脚本，将关系数据展平为ProcessMind事件日志格式。
验证数据类型。确保日期字段转换为datetime对象，并在转换过程中正确处理数值金额的小数位。
导出为CSV/Parquet。从数据仓库将最终结果集导出为单个文件。
上传到ProcessMind。导入文件，并将InvoiceNumber映射为Case ID、ActivityName映射为Activity、EventStartDateTime映射为Timestamp。
配置
- 提取频率：建议每日执行Incremental，以捕获最新状态变化。
- 初始加载：首次运行选择“Full Extract”，之后根据Last Update Date切换为“Incremental”。
- 关键PVO：TransactionHeaderExtractPVO、ReceiptApplicationExtractPVO、AdjustmentExtractPVO、StrategyWorkItemExtractPVO。
- 日期筛选：应用CreationDate >= '202X-01-01'筛选条件，以限制数据量。
- 获取数量：默认通常为50000行；如果使用UCM下载，请根据网络带宽调整。
- 主键：确保下游数据仓库使用PVO主键（通常为CustomerTrxId、ReceivableApplicationId等）处理upsert，以避免重复行。
- 审计历史：标准BICC PVO捕获当前状态。若要准确记录状态变化的历史时间戳（例如Dispute Opened），而事务表未保留历史记录，可能需要在Fusion中启用审计策略并提取Audit View Objects。
a 示例查询 sql
/*
Transformation Script for Oracle BICC Data
Assumes raw BICC PVO CSVs are loaded into a SQL Staging Area with tables named:
- STG_AR_TRX_HEADER (TransactionHeaderExtractPVO)
- STG_AR_APPLICATIONS (ReceiptApplicationExtractPVO)
- STG_AR_ADJUSTMENTS (AdjustmentExtractPVO)
- STG_IEX_PROMISES (PromiseDetailExtractPVO)
- STG_IEX_STRATEGY (StrategyWorkItemExtractPVO)
- STG_CE_STMTS (BankStatementLineExtractPVO - Optional/Advanced)
*/
WITH Base_Log AS (
/* 1. Invoice Created */
SELECT
TrxNumber AS InvoiceNumber,
'Invoice Created' AS ActivityName,
CreationDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
(Quantity * UnitSellingPrice) AS InvoiceAmount,
TrxClass AS TransactionType,
CreatedBy AS UserName,
'Yes' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE TrxClass IN ('INV', 'DM')
UNION ALL
/* 2. Invoice Completed */
SELECT
TrxNumber AS InvoiceNumber,
'Invoice Completed' AS ActivityName,
TrxDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
LastUpdatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE CompleteFlag = 'Y'
AND TrxClass IN ('INV', 'DM')
UNION ALL
/* 3. Invoice Dispatched */
/* Using PrintingOriginalDate as proxy for dispatch */
SELECT
TrxNumber AS InvoiceNumber,
'Invoice Dispatched' AS ActivityName,
PrintingOriginalDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
LastUpdatedBy AS UserName,
'Yes' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE PrintingOriginalDate IS NOT NULL
AND TrxClass IN ('INV', 'DM')
UNION ALL
/* 4. Invoice Posted to GL */
SELECT
TrxNumber AS InvoiceNumber,
'Invoice Posted to GL' AS ActivityName,
GlDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
'System' AS UserName,
'Yes' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE GlDate IS NOT NULL
AND TrxClass IN ('INV', 'DM')
UNION ALL
/* 5. Payment Reminder Sent */
/* Links via Customer or Account, mapped back to Trx via Collections Strategy logic */
/* Simplified join assumption based on Trx Id availability in Work Item */
SELECT
H.TrxNumber AS InvoiceNumber,
'Payment Reminder Sent' AS ActivityName,
W.CreationDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
H.TrxClass AS TransactionType,
W.CreatedBy AS UserName,
'Yes' AS IsAutomated
FROM STG_IEX_STRATEGY W
JOIN STG_AR_TRX_HEADER H ON W.ObjectPk1 = H.CustomerTrxId
WHERE W.WorkItemTemplateName LIKE '%Reminder%'
UNION ALL
/* 6. Promise to Pay Received */
SELECT
H.TrxNumber AS InvoiceNumber,
'Promise to Pay Received' AS ActivityName,
P.CreationDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
P.PromiseAmount AS InvoiceAmount,
H.TrxClass AS TransactionType,
P.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_IEX_PROMISES P
JOIN STG_AR_TRX_HEADER H ON P.CustTrxId = H.CustomerTrxId
UNION ALL
/* 7. Dispute Case Opened */
/* Triggered when dispute amount is updated/created */
SELECT
TrxNumber AS InvoiceNumber,
'Dispute Case Opened' AS ActivityName,
DisputeDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
LastUpdatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE DisputeDate IS NOT NULL
UNION ALL
/* 8. Dispute Resolved */
/* Approximated by update date when dispute amount returns to 0 after being positive */
/* Note: Accurate dispute history requires Audit Trail extraction. This is a best-effort proxy based on header state. */
SELECT
TrxNumber AS InvoiceNumber,
'Dispute Resolved' AS ActivityName,
LastUpdateDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
LastUpdatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE DisputeDate IS NOT NULL AND DisputeAmount = 0
UNION ALL
/* 9. Credit Memo Issued (Applied) */
SELECT
H.TrxNumber AS InvoiceNumber,
'Credit Memo Issued' AS ActivityName,
APP.ApplyDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
APP.AmountApplied AS InvoiceAmount,
H.TrxClass AS TransactionType,
APP.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_APPLICATIONS APP
JOIN STG_AR_TRX_HEADER H ON APP.AppliedCustomerTrxId = H.CustomerTrxId
WHERE APP.ApplicationType = 'CM' -- Credit Memo application
UNION ALL
/* 10. Partial Payment Posted */
SELECT
H.TrxNumber AS InvoiceNumber,
'Partial Payment Posted' AS ActivityName,
APP.ApplyDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
APP.AmountApplied AS InvoiceAmount,
H.TrxClass AS TransactionType,
APP.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_APPLICATIONS APP
JOIN STG_AR_TRX_HEADER H ON APP.AppliedCustomerTrxId = H.CustomerTrxId
WHERE APP.ApplicationType = 'CASH'
AND APP.Status = 'APP'
AND (H.AmountDueRemaining > 0) -- Invoice still has balance
UNION ALL
/* 11. Full Payment Received */
SELECT
H.TrxNumber AS InvoiceNumber,
'Full Payment Received' AS ActivityName,
APP.ApplyDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
APP.AmountApplied AS InvoiceAmount,
H.TrxClass AS TransactionType,
APP.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_APPLICATIONS APP
JOIN STG_AR_TRX_HEADER H ON APP.AppliedCustomerTrxId = H.CustomerTrxId
WHERE APP.ApplicationType = 'CASH'
AND APP.Status = 'APP'
AND H.AmountDueRemaining = 0 -- Invoice fully paid
UNION ALL
/* 12. Bank Statement Matched */
/* Requires joining Receipt Application -> Cash Receipt -> Bank Statement Line */
/* Placeholder logic assuming availability of Bank Statement PVO data */
SELECT
H.TrxNumber AS InvoiceNumber,
'Bank Statement Matched' AS ActivityName,
BSL.StatementDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
BSL.Amount AS InvoiceAmount,
H.TrxClass AS TransactionType,
BSL.CreatedBy AS UserName,
'Yes' AS IsAutomated
FROM STG_AR_APPLICATIONS APP
JOIN STG_AR_TRX_HEADER H ON APP.AppliedCustomerTrxId = H.CustomerTrxId
-- Join to Receipt then to Bank Stmt would happen here
JOIN STG_CE_STMTS BSL ON APP.CashReceiptId = BSL.ReferenceId -- Simplified Join
WHERE APP.ApplicationType = 'CASH'
UNION ALL
/* 13. Invoice Adjusted */
SELECT
H.TrxNumber AS InvoiceNumber,
'Invoice Adjusted' AS ActivityName,
ADJ.ApplyDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
ADJ.Amount AS InvoiceAmount,
H.TrxClass AS TransactionType,
ADJ.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_ADJUSTMENTS ADJ
JOIN STG_AR_TRX_HEADER H ON ADJ.CustomerTrxId = H.CustomerTrxId
WHERE ADJ.AdjustmentType != 'WRITE_OFF'
UNION ALL
/* 14. Invoice Written Off */
SELECT
H.TrxNumber AS InvoiceNumber,
'Invoice Written Off' AS ActivityName,
ADJ.ApplyDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
H.BusinessUnitName AS BusinessUnit,
H.BillToCustomerName AS CustomerName,
H.InvoiceCurrencyCode AS Currency,
ADJ.Amount AS InvoiceAmount,
H.TrxClass AS TransactionType,
ADJ.CreatedBy AS UserName,
'No' AS IsAutomated
FROM STG_AR_ADJUSTMENTS ADJ
JOIN STG_AR_TRX_HEADER H ON ADJ.CustomerTrxId = H.CustomerTrxId
WHERE ADJ.AdjustmentType = 'WRITE_OFF'
UNION ALL
/* 15. Invoice Cleared */
/* The moment the invoice balance hits 0 */
SELECT
TrxNumber AS InvoiceNumber,
'Invoice Cleared' AS ActivityName,
LastUpdateDate AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
BusinessUnitName AS BusinessUnit,
BillToCustomerName AS CustomerName,
InvoiceCurrencyCode AS Currency,
NULL AS InvoiceAmount,
TrxClass AS TransactionType,
LastUpdatedBy AS UserName,
'Yes' AS IsAutomated
FROM STG_AR_TRX_HEADER
WHERE AmountDueRemaining = 0
)
SELECT
InvoiceNumber,
ActivityName,
EventStartDateTime,
SourceSystem,
GETDATE() AS LastDataUpdate,
BusinessUnit,
CustomerName,
Currency,
InvoiceAmount,
TransactionType,
UserName,
IsAutomated
FROM Base_Log
WHERE EventStartDateTime IS NOT NULL
ORDER BY InvoiceNumber, EventStartDateTime步骤
登录Oracle Fusion Applications：进入Tools > Reports and Analytics。点击Browse Catalog打开Oracle BI Publisher界面。
创建数据模型：点击左上角的New，选择Data Model。这是存放SQL提取逻辑的容器。
定义SQL数据集：在左侧的Data Model树中点击Data Sets，然后选择New Data Set > SQL Query。
配置数据源：为数据集命名（例如
ProcessMining_AR）。选择ApplicationDB_FSCM（Financials Supply Chain Management）作为数据源，以确保能够访问所需的AR和RA表。粘贴查询：复制下方Query部分提供的完整SQL脚本，并粘贴到SQL Query文本框中。除非需要重命名特定Flexfields（DFF），否则不要修改核心逻辑。
设置参数：查询包含占位符
:p_start_date，用于按交易创建日期筛选。在数据模型的Parameters选项卡中创建名为p_start_date的新参数，数据类型设为Date，并设置默认值（例如01-01-2023）。查看数据：点击Data选项卡，输入有效日期参数，然后点击View。确保输出包含
InvoiceNumber、ActivityName和EventStartDateTime等列。保存数据模型：将对象保存到Shared Folders > Custom目录（例如
/Shared Folders/Custom/ProcessMining/AR_Extract_DM）。计划/导出：要提取大量数据，请使用此数据模型点击Create Report。在报表编辑器中确认布局为简单表格，然后保存报表。接着使用Scheduler运行报表，并将数据输出为CSV或XML。
最终格式处理：下载输出文件。如果是CSV，请确保日期格式一致，建议使用ISO 8601格式。将文件上传到ProcessMind，并将
InvoiceNumber映射为Case ID、ActivityName映射为Activity、EventStartDateTime映射为Timestamp。
配置
- 数据源：使用
ApplicationDB_FSCM访问Financials表。 - 日期筛选：查询使用
ra_customer_trx_all.creation_date >= :p_start_date。请将其配置为滚动时间窗口加载数据（例如最近12个月）。 - 性能：对于超过100,000张发票的数据集，可在测试期间添加
ROWNUM限制，或按月分批提取。 - 业务单元筛选：如果您的组织包含多个业务单元且只需分析其中一个，请在Where子句中取消注释
AND trx.org_id = ...行。 - 用户名：查询通过
FND_USER将CREATED_BY用户ID解析为用户名。请确保提取用户拥有读取FND_USER的权限。 - 高级催收：Payment Reminder Sent和Promise to Pay Received活动依赖IEX（Advanced Collections）模块表。如果您未使用该模块，这些部分将返回零行。
a 示例查询 sql
/* 1. Invoice Created */
SELECT
trx.trx_number AS InvoiceNumber,
'Invoice Created' AS ActivityName,
trx.creation_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ra_customer_trx_all trx
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON trx.created_by = u.user_id
WHERE
trx.creation_date >= :p_start_date
UNION ALL
/* 2. Invoice Completed */
SELECT
trx.trx_number AS InvoiceNumber,
'Invoice Completed' AS ActivityName,
trx.trx_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'Y' AS IsAutomated
FROM
ra_customer_trx_all trx
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON trx.last_updated_by = u.user_id
WHERE
trx.complete_flag = 'Y'
AND trx.creation_date >= :p_start_date
UNION ALL
/* 3. Invoice Dispatched */
SELECT
trx.trx_number AS InvoiceNumber,
'Invoice Dispatched' AS ActivityName,
COALESCE(trx.printing_original_date, trx.printing_last_printed, trx.last_update_date) AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'Y' AS IsAutomated
FROM
ra_customer_trx_all trx
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON trx.last_updated_by = u.user_id
WHERE
(trx.printing_original_date IS NOT NULL OR trx.printing_count > 0)
AND trx.creation_date >= :p_start_date
UNION ALL
/* 4. Invoice Posted to GL */
SELECT
trx.trx_number AS InvoiceNumber,
'Invoice Posted to GL' AS ActivityName,
MAX(dist.gl_date) AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
'System' AS UserName,
'Y' AS IsAutomated
FROM
ra_customer_trx_all trx
JOIN ra_cust_trx_line_gl_dist_all dist ON trx.customer_trx_id = dist.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
WHERE
dist.account_class = 'REC'
AND dist.posting_control_id != -3
AND trx.creation_date >= :p_start_date
GROUP BY
trx.trx_number,
hou.name,
party.party_name,
trx.invoice_currency_code,
ps.amount_due_original,
type.name
UNION ALL
/* 5. Payment Reminder Sent (Advanced Collections) */
SELECT
trx.trx_number AS InvoiceNumber,
'Payment Reminder Sent' AS ActivityName,
dun.creation_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'Y' AS IsAutomated
FROM
iex_dunning_transactions dun
JOIN ar_payment_schedules_all ps ON dun.payment_schedule_id = ps.payment_schedule_id
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
LEFT JOIN fnd_user u ON dun.created_by = u.user_id
WHERE
trx.creation_date >= :p_start_date
UNION ALL
/* 6. Promise to Pay Received */
SELECT
trx.trx_number AS InvoiceNumber,
'Promise to Pay Received' AS ActivityName,
pp.creation_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
iex_promise_details pp
JOIN ar_payment_schedules_all ps ON pp.payment_schedule_id = ps.payment_schedule_id
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
LEFT JOIN fnd_user u ON pp.created_by = u.user_id
WHERE
trx.creation_date >= :p_start_date
UNION ALL
/* 7. Dispute Case Opened */
SELECT
trx.trx_number AS InvoiceNumber,
'Dispute Case Opened' AS ActivityName,
req.creation_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ra_cm_requests req
JOIN ra_customer_trx_all trx ON req.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON req.created_by = u.user_id
WHERE
trx.creation_date >= :p_start_date
UNION ALL
/* 8. Dispute Resolved */
SELECT
trx.trx_number AS InvoiceNumber,
'Dispute Resolved' AS ActivityName,
req.last_update_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ra_cm_requests req
JOIN ra_customer_trx_all trx ON req.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON req.last_updated_by = u.user_id
WHERE
req.status_code IN ('APPROVED', 'REJECTED')
AND trx.creation_date >= :p_start_date
UNION ALL
/* 9. Credit Memo Issued */
SELECT
trx.trx_number AS InvoiceNumber,
'Credit Memo Issued' AS ActivityName,
cm.trx_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ra_customer_trx_all cm
JOIN ra_customer_trx_all trx ON cm.previous_customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
LEFT JOIN fnd_user u ON cm.created_by = u.user_id
WHERE
trx.creation_date >= :p_start_date
UNION ALL
/* 10 & 11. Partial and Full Payment */
SELECT
trx.trx_number AS InvoiceNumber,
CASE
WHEN ps.status = 'CL' AND app.amount_applied = app.amount_applied_from THEN 'Full Payment Received'
WHEN ps.status = 'CL' AND ps.amount_due_remaining = 0 AND app.application_rule = '60' THEN 'Full Payment Received'
ELSE 'Partial Payment Posted'
END AS ActivityName,
app.apply_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ar_receivable_applications_all app
JOIN ar_payment_schedules_all ps ON app.applied_payment_schedule_id = ps.payment_schedule_id
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
LEFT JOIN fnd_user u ON app.created_by = u.user_id
WHERE
app.status = 'APP'
AND app.application_type = 'CASH'
AND trx.creation_date >= :p_start_date
UNION ALL
/* 12. Bank Statement Matched */
SELECT
trx.trx_number AS InvoiceNumber,
'Bank Statement Matched' AS ActivityName,
recon.creation_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'Y' AS IsAutomated
FROM
ce_statement_reconcils_all recon
JOIN ar_cash_receipt_history_all crh ON recon.reference_id = crh.cash_receipt_history_id
JOIN ar_cash_receipts_all cr ON crh.cash_receipt_id = cr.cash_receipt_id
JOIN ar_receivable_applications_all app ON cr.cash_receipt_id = app.cash_receipt_id
JOIN ar_payment_schedules_all ps ON app.applied_payment_schedule_id = ps.payment_schedule_id
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
LEFT JOIN fnd_user u ON recon.created_by = u.user_id
WHERE
recon.status_flag = 'M'
AND trx.creation_date >= :p_start_date
UNION ALL
/* 13 & 14. Invoice Adjusted and Written Off */
SELECT
trx.trx_number AS InvoiceNumber,
CASE
WHEN adj.adjustment_type = 'W' THEN 'Invoice Written Off'
ELSE 'Invoice Adjusted'
END AS ActivityName,
adj.apply_date AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
u.user_name AS UserName,
'N' AS IsAutomated
FROM
ar_adjustments_all adj
JOIN ar_payment_schedules_all ps ON adj.payment_schedule_id = ps.payment_schedule_id
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
LEFT JOIN fnd_user u ON adj.created_by = u.user_id
WHERE
adj.status = 'A'
AND trx.creation_date >= :p_start_date
UNION ALL
/* 15. Invoice Cleared (Final Close) */
SELECT
trx.trx_number AS InvoiceNumber,
'Invoice Cleared' AS ActivityName,
ps.gl_date_closed AS EventStartDateTime,
'Oracle Fusion' AS SourceSystem,
SYSDATE AS LastDataUpdate,
hou.name AS BusinessUnit,
party.party_name AS CustomerName,
trx.invoice_currency_code AS Currency,
ps.amount_due_original AS InvoiceAmount,
type.name AS TransactionType,
'System' AS UserName,
'Y' AS IsAutomated
FROM
ar_payment_schedules_all ps
JOIN ra_customer_trx_all trx ON ps.customer_trx_id = trx.customer_trx_id
JOIN ra_cust_trx_types_all type ON trx.cust_trx_type_id = type.cust_trx_type_id
JOIN hr_operating_units hou ON trx.org_id = hou.organization_id
JOIN hz_cust_accounts cust ON trx.bill_to_customer_id = cust.cust_account_id
JOIN hz_parties party ON cust.party_id = party.party_id
WHERE
ps.status = 'CL'
AND ps.gl_date_closed IS NOT NULL
AND trx.creation_date >= :p_start_date立即加快应收账款催收
将DSO缩短15至20天，立即解决现金流缺口。
无需信用卡，5分钟完成设置。