Your Accounts Receivable Data Template
Your Accounts Receivable Data Template
- Comprehensive set of recommended attributes for AR analysis
- Core process activities and milestones to monitor
- System specific extraction guidance for Oracle Fusion Financials
Accounts Receivable Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The specific event or action performed in the accounts receivable process. | ||
| Description This attribute describes the step taken in the process, such as creating an invoice, posting a payment, or opening a dispute. It defines the flow of the process map and allows for the visualization of the sequence of events. Analysts use this field to identify process variants, loops, and bottlenecks. It is essential for determining adherence to standard operating procedures and for calculating the frequency of specific events like rework or manual interventions. Why it matters Required to define the process flow and visualize the sequence of events. Where to get Derived from transaction history tables (e.g., AR_PAYMENT_SCHEDULES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL). Examples Invoice CreatedPayment Reminder SentPartial Payment PostedDispute Case Opened | |||
| Event Timestamp EventStartDateTime | The specific date and time when an activity occurred. | ||
| Description This attribute records the exact moment an activity took place within the system. It is used to order events chronologically and is the basis for all time-based calculations in process mining. By analyzing timestamps, the business can calculate cycle times between activities, such as the duration between invoice creation and dispatch. It is critical for measuring KPIs like Days Sales Outstanding and identifying temporal patterns in payment behavior. Why it matters Foundational for calculating duration, lead times, and cycle times. Where to get Oracle Fusion Financials: CREATION_DATE or LAST_UPDATE_DATE columns across various transaction tables. Examples 2023-10-15T08:30:00Z2023-10-16T14:45:12Z2023-11-01T09:00:00Z | |||
| Invoice Number InvoiceNumber | The unique identifier assigned to the invoice transaction in Oracle Fusion. | ||
| Description This attribute serves as the unique key for identifying financial obligations within the Accounts Receivable module. It links all subsequent activities, such as adjustments, disputes, and payments, to the original sales transaction. In process mining analysis, this attribute functions as the Case ID. It allows analysts to trace the end-to-end lifecycle of a receivable from the moment it is created until it is fully cleared or written off, facilitating the calculation of cycle times and process variants. Why it matters It is the fundamental unit of analysis for tracking the credit-to-cash lifecycle. Where to get Oracle Fusion Financials: RA_CUSTOMER_TRX_ALL.TRX_NUMBER Examples INV-2023-00110056789AR-99887755002211 | |||
| Last Data Update LastDataUpdate | The timestamp of when the data was last refreshed in the mining tool. | ||
| Description This attribute indicates when the data set was last synchronized with the source Oracle system. It helps users understand the recency of the analysis and whether the insights reflect the current state of operations. Monitoring this field is important for ensuring that dashboards are displaying up-to-date information, especially for operational monitoring of open disputes or unapplied cash. Why it matters Provides context on data freshness and reliability. Where to get System time at moment of extraction. Examples 2023-11-15T23:59:59Z2023-11-16T00:00:00Z | |||
| Source System SourceSystem | The system of record where the data originated. | ||
| Description This attribute identifies the software environment from which the process data was extracted. In this context, it confirms that the data comes from the Oracle Fusion Financials environment. While often a static value for a single-system extraction, it becomes crucial when merging data from multiple ERP instances or when integrating third-party collection tools. It ensures data lineage and traceability in multi-system process landscapes. Why it matters Ensures data lineage and distinguishes between different ERP instances. Where to get Hardcoded during extraction or configured in the data pipeline. Examples Oracle Fusion FinancialsOracle Cloud ERP - USOracle Cloud ERP - EMEA | |||
| Business Unit BusinessUnit | The operational entity within the organization responsible for the invoice. | ||
| Description This attribute maps to the Organization ID in Oracle Fusion, representing the specific business unit or division that owns the receivable. It enables the segmentation of process performance across different parts of the enterprise. Comparing KPIs such as Dispute Resolution Time or DSO across different business units helps leadership identify high-performing teams and standardize best practices. It also highlights units that may require additional resources or process re-engineering. Why it matters Key dimension for organizational benchmarking and performance comparison. Where to get Oracle Fusion Financials: HR_ORGANIZATION_UNITS.NAME linked via ORG_ID. Examples US East SalesEMEA ServicesAPAC Manufacturing | |||
| Collector Name CollectorName | The name of the collection agent or resource assigned to the invoice. | ||
| Description This attribute identifies the specific employee or team member responsible for collecting payment on the invoice. It is the key dimension for the 'Collection Agent Throughput' dashboard. Data from this field enables the organization to measure productivity per agent, identify training needs, and balance workloads. It fosters accountability and helps in standardizing collection efforts across the finance team. Why it matters Key for resource performance analysis and workload balancing. Where to get Oracle Fusion Financials: AR_COLLECTORS.NAME associated with the Customer Profile. Examples John SmithCollections Team AJane Doe | |||
| Customer Name CustomerName | The name of the entity billed in the transaction. | ||
| Description This attribute identifies the customer associated with the invoice. It is fundamental for analyzing payment behaviors, dispute frequencies, and collection effectiveness at the client level. Analysts use this field to pinpoint specific customers who frequently pay late or raise disputes. This insight supports the 'Customer Payment Behavior Analysis' dashboard and helps in tailoring credit terms and collection strategies to individual client profiles. Why it matters Essential for customer-centric analysis and risk profiling. Where to get Oracle Fusion Financials: HZ_PARTIES.PARTY_NAME linked via BILL_TO_CUSTOMER_ID. Examples Acme CorpGlobex CorporationSoylent Corp | |||
| Customer Segment CustomerSegment | The classification of the customer based on size, industry, or risk. | ||
| Description This attribute categorizes customers into groups such as Strategic, Enterprise, SME, or High Risk. It is often derived from the customer class or profile class in Oracle Fusion. Using this attribute allows for the analysis of process variants across different market segments. For instance, it helps verify if 'Strategic' customers are receiving the intended white-glove service or if 'High Risk' customers are being monitored closely for payment compliance. Why it matters Allows for segmented analysis of collection strategies and risk. Where to get Oracle Fusion Financials: HZ_CUSTOMER_PROFILES.PROFILE_CLASS_ID. Examples EnterpriseSmall BusinessGovernmentHigh Risk | |||
| Due Date DueDate | The date by which the payment is expected to be received. | ||
| Description This attribute is the deadline for payment calculated based on the Invoice Date and Payment Terms. It serves as the reference point for determining if a payment is late. It is used in the 'Collection Reminder Timing Variance' KPI to measure how proactively the team acts relative to the deadline. It is also the threshold for classifying receivables as current or overdue in aging reports. Why it matters The primary baseline for determining delinquency and on-time performance. Where to get Oracle Fusion Financials: AR_PAYMENT_SCHEDULES_ALL.DUE_DATE Examples 2023-11-302023-12-152024-01-01 | |||
| Invoice Amount InvoiceAmount | The total monetary value of the invoice. | ||
| Description This attribute represents the original amount due on the invoice. It serves as the primary weighting factor for many analyses, allowing the business to prioritize high-value transactions over low-value volume. In the context of the 'Unapplied Credits and Leakage View', this field helps quantify the financial impact of unresolved items. It is also used to calculate the weighted average Days Sales Outstanding, providing a more financial-centric view of process efficiency. Why it matters Provides financial weight to the analysis and supports value-based prioritization. Where to get Oracle Fusion Financials: RA_CUSTOMER_TRX_ALL.AMOUNT_DUE_ORIGINAL Examples 1500.00250.5010000.00 | |||
| Is Automated IsAutomated | Flag indicating if the activity was performed without human intervention. | ||
| Description This boolean attribute determines if an activity was executed by a system process (e.g., AutoInvoice, AutoLockbox) or a human user. It is the primary driver for the 'Cash Application Automation Rate' KPI. By tracking the ratio of automated to manual activities over time, the organization can validate the success of digital transformation initiatives and identify specific process steps that remain stubbornly manual. Why it matters Primary metric for digital transformation and efficiency measurement. Where to get Calculated logic based on UserName (e.g., if User == 'BATCH_USER' then true). Examples truefalse | |||
| Transaction Type TransactionType | The classification of the receivable document (Invoice, Credit Memo, Debit Memo). | ||
| Description This attribute distinguishes between different types of financial documents. Common values include Invoice, Credit Memo, and Debit Memo. This distinction is vital for the 'Credit Memo Volume and Rework' dashboard. By filtering on this attribute, analysts can isolate rework loops caused by credit memos or focus specifically on the main invoicing flow. It helps in understanding the composition of the receivables workload. Why it matters Distinguishes standard invoices from adjustments and corrections. Where to get Oracle Fusion Financials: RA_CUST_TRX_TYPES_ALL.NAME Examples InvoiceCredit MemoDebit MemoChargeback | |||
| User Name UserName | The system user who performed the activity. | ||
| Description This attribute records the login ID or name of the person who executed the specific activity (e.g., posted the invoice, matched the bank statement). It maps to the generic 'User' field. This data is vital for compliance auditing and for the 'Collection Agent Throughput' dashboard. It allows for the separation of machine-driven actions (often done by a 'System' user) from human actions, supporting automation analysis. Why it matters Enables user-level performance tracking and segregation of duties analysis. Where to get Oracle Fusion Financials: CREATED_BY or LAST_UPDATED_BY columns joined to user tables. Examples sysadminjsmithfinance_batch_job | |||
| Creation Source CreationSource | The origin of the invoice, indicating if it was manual or imported. | ||
| Description This attribute reveals how the invoice entered the Oracle system, such as 'Manual Entry', 'AutoInvoice', or via specific external feeds. It is a proxy for the 'Channel' generic mapping. This is crucial for the 'Cash Application Automation Monitor'. It helps distinguish between processes that are fully digital and those requiring manual setup. High volumes of 'Manual Entry' may indicate a lack of upstream integration or system deficiencies. Why it matters Identifies the level of upstream automation and data origin. Where to get Oracle Fusion Financials: RA_BATCH_SOURCES_ALL.NAME Examples AutoInvoiceManualProjects ImplementationOrder Management | |||
| Currency Code CurrencyCode | The currency in which the invoice amount is denominated. | ||
| Description This attribute specifies the currency (e.g., USD, EUR) for the financial amounts. It is necessary for correctly interpreting the Invoice Amount and for performing currency conversions if a global reporting currency is required. For global organizations, this attribute helps analyzing collection performance across different economic regions and allows finance teams to separate forex impacts from operational process performance. Why it matters Contextualizes financial values in multi-currency environments. Where to get Oracle Fusion Financials: RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE Examples USDEURGBPJPY | |||
| Days Sales Outstanding DaysSalesOutstanding | The number of days between invoice creation and clearance. | ||
| Description This calculated attribute measures the duration from 'Invoice Created' to 'Invoice Cleared'. It is the direct calculation for the 'Average Days Sales Outstanding' KPI. While this can be calculated dynamically in dashboards, having it as a pre-calculated attribute on the case level allows for easier filtering and segmentation (e.g., showing all cases where DSO > 60 days). Why it matters The definitive efficiency metric for Accounts Receivable. Where to get Calculated: Date(Invoice Cleared) - Date(Invoice Created). Examples 45 days12 days60 days | |||
| Discount Eligibility Date DiscountEligibilityDate | The final date a customer can pay to receive an early payment discount. | ||
| Description This attribute marks the deadline for the customer to take advantage of terms like '2/10 Net 30' (2% discount if paid within 10 days). It is required for the 'Early Payment Discount Analytics' dashboard. Analyzing payments against this date reveals the 'Early Payment Discount Capture Rate'. It helps the business understand if their discount strategies are effectively accelerating cash flow or if they are being ignored by customers. Why it matters Supports analysis of incentive effectiveness and cash flow acceleration. Where to get Oracle Fusion Financials: AR_PAYMENT_SCHEDULES_ALL.DISCOUNT_DATE Examples 2023-11-102023-12-05 | |||
| Dispute Reason DisputeReason | The category or reason code assigned when a dispute is opened. | ||
| Description This attribute captures the justification provided when a 'Dispute Case Opened' activity occurs. Common values might include 'Pricing Error', 'Quantity Mismatch', or 'Damaged Goods'. Analyzing this attribute in the 'Dispute Lifecycle and Bottlenecks' dashboard helps identify root causes of payment delays. If 'Pricing Error' is frequent, the business knows to investigate the upstream sales quoting process rather than just the collections process. Why it matters Critical for root cause analysis of delayed payments and rework. Where to get Oracle Fusion Financials: RA_CM_REQUESTS.REASON_CODE or AR_DISPUTE_HISTORY. Examples Price DisputeTax ErrorGoods Not ReceivedDuplicate Billing | |||
| Is Rework IsRework | Flag indicating if the invoice underwent correction or dispute loops. | ||
| Description This boolean attribute identifies if an invoice has experienced activities associated with error correction, such as 'Credit Memo Issued' or 'Invoice Adjusted'. It supports the 'Credit Memo Volume and Rework' dashboard. Identifying rework cases helps isolate 'happy path' processes from problematic ones. High rework rates are a leading indicator of upstream data quality issues in the master data or sales order entry processes. Why it matters Identifies waste and inefficiency in the process flow. Where to get Calculated: True if case contains 'Credit Memo Issued' or 'Dispute Case Opened'. Examples truefalse | |||
| Payment Terms PaymentTerms | The agreed-upon conditions for payment timing (e.g., Net 30). | ||
| Description This attribute defines the contractually agreed time frame for payment. It is used to calculate the Due Date and is essential for the 'Collection Strategy Effectiveness' dashboard. Variations in payment terms across customers can explain differences in DSO. This attribute allows analysts to normalize performance data, ensuring that a customer with Net 60 terms isn't unfairly flagged as a 'slow payer' compared to one with Net 30 terms. Why it matters Contextualizes payment speed against contractual agreements. Where to get Oracle Fusion Financials: RA_TERMS.NAME Examples Net 30Immediate2/10 Net 30Net 60 | |||
| Region Region | Geographic region associated with the business unit or customer. | ||
| Description This attribute maps the transaction to a broader geographic area, such as North America, EMEA, or APAC. It is useful for high-level executive reporting and for the 'DSO and Cash Cycle Trends' dashboard. Regional analysis helps account for cultural differences in payment behavior (e.g., longer standard payment terms in Southern Europe vs. the US) and ensures that global KPIs are interpreted with the correct local context. Why it matters Provides high-level geographic segmentation for global reporting. Where to get Oracle Fusion Financials: Derived from Business Unit or Customer Address. Examples North AmericaEMEAAPACLATAM | |||
Accounts Receivable Activities
| Activity | Description | ||
|---|---|---|---|
| Full Payment Received | Occurs when a receipt application reduces the invoice balance to zero. This is the primary success event for the collection process. | ||
| Why it matters Critical for Early Payment Discount Analytics. Timing of this event determines if cash was collected within the discount window. Where to get Sourced from AR_RECEIVABLE_APPLICATIONS_ALL where STATUS = 'APP' and the resulting AMOUNT_DUE_REMAINING is 0. Capture Derive from comparing field X to Y Event type calculated | |||
| Invoice Cleared | The final state where the invoice is closed in the system, usually because the balance is zero due to payment, credit memo, or adjustment. | ||
| Why it matters The timestamp for this event is used to calculate the Days Sales Outstanding (DSO). It represents the end of the process instance. Where to get Identified when the STATUS in AR_PAYMENT_SCHEDULES_ALL changes to 'CL' (Closed). Capture Compare status field before/after Event type inferred | |||
| Invoice Completed | Indicates that the invoice creation process is finished and the invoice is ready to be processed, printed, and posted. This occurs when the transaction status changes from incomplete to complete. | ||
| Why it matters Differentiates between drafting time and processing time. Delays here indicate bottlenecks in the internal billing generation process. Where to get Identified when the COMPLETE_FLAG in RA_CUSTOMER_TRX_ALL transitions to 'Y'. Capture Compare status field before/after Event type inferred | |||
| Invoice Created | This activity marks the initial creation of the invoice record in the system. It captures the timestamp when the transaction header is first saved in the Oracle Receivables tables. | ||
| Why it matters Establishes the start of the process lifecycle and the baseline for aging calculations. Essential for calculating total cycle time and lead time to dispatch. Where to get Derived from RA_CUSTOMER_TRX_ALL table using the CREATION_DATE or TRX_DATE column. Capture Logged when transaction row is inserted Event type explicit | |||
| Invoice Dispatched | Represents the transmission of the invoice to the customer via print, email, or XML. This marks the handoff from the organization to the customer. | ||
| Why it matters Critical for measuring Billing Dispatch Performance. The gap between creation and dispatch directly delays the cash collection cycle. Where to get Inferred from PRINTING_ORIGINAL_DATE in RA_CUSTOMER_TRX_ALL or specific logs in the Oracle Collaboration Messaging Framework if using XML. Capture Compare status field before/after Event type inferred | |||
| Partial Payment Posted | Occurs when a receipt is applied to the invoice, but the amount is less than the total outstanding balance. This leaves the invoice open with a reduced balance. | ||
| Why it matters High frequency indicates fragmented payment behavior (Partial Payment Frequency KPI) which increases reconciliation effort. Where to get Sourced from AR_RECEIVABLE_APPLICATIONS_ALL where STATUS = 'APP' and AMOUNT_APPLIED < AMOUNT_DUE_REMAINING. Capture Logged when transaction X executed Event type explicit | |||
| Bank Statement Matched | Indicates that the receipt applied to the invoice has been reconciled with a line on the bank statement. This confirms the cash has actually hit the bank account. | ||
| Why it matters Measures Cash Application Automation. The gap between payment posting and bank matching represents unconfirmed cash. Where to get Joined from AR_CASH_RECEIPTS_ALL to CE_STATEMENT_LINES (Cash Management) via the reconciliation reference. Capture Compare status field before/after Event type inferred | |||
| Credit Memo Issued | Captures the creation of a credit memo transaction applied against the invoice. This reduces the balance due, often in response to a dispute or return. | ||
| Why it matters Tracks Credit Memo Rework Rate and revenue leakage. Frequent credit memos indicate systemic billing errors. Where to get Sourced from RA_CUSTOMER_TRX_ALL where TRX_TYPE is Credit Memo and RELATED_CUSTOMER_TRX_ID matches the invoice. Capture Logged when transaction X executed Event type explicit | |||
| Dispute Case Opened | Marks the initiation of a formal dispute regarding the invoice. This halts standard collection activities while the issue is investigated. | ||
| Why it matters Major bottleneck indicator. High dispute rates suggest upstream quality issues in fulfillment or billing accuracy. Where to get Identified by records in RA_CM_REQUESTS_ALL or specific Credit Memo Request workflows linked to the invoice. Capture Logged when transaction X executed Event type explicit | |||
| Dispute Resolved | Indicates the conclusion of the dispute investigation. The outcome could be a credit memo approval (valid dispute) or rejection (invalid dispute). | ||
| Why it matters Necessary for calculating Average Dispute Resolution Time. Long resolution times negatively impact customer satisfaction and DSO. Where to get Derived from the status change to 'APPROVED' or 'REJECTED' in RA_CM_REQUESTS_ALL. Capture Compare status field before/after Event type inferred | |||
| Invoice Adjusted | Captures manual adjustments to the invoice balance, such as small write-offs or currency adjustments, distinct from credit memos. | ||
| Why it matters Helps identify revenue leakage and non-standard process paths where balances are cleared without payment. Where to get Sourced from AR_ADJUSTMENTS_ALL table linked to the invoice. Capture Logged when transaction X executed Event type explicit | |||
| Invoice Posted to GL | Records the event where the invoice accounting entries are finalized and transferred to the General Ledger. This ensures financial compliance and period close readiness. | ||
| Why it matters While not affecting the customer view, delays here impact the financial close cycle and reporting timeliness. Where to get Derived from the GL_DATE in the RA_CUST_TRX_LINE_GL_DIST_ALL table. Capture Logged when transaction X executed Event type explicit | |||
| Invoice Written Off | A specific type of adjustment where the remaining balance is deemed uncollectible and written off as bad debt. This is a negative terminal state. | ||
| Why it matters Critical for financial health monitoring. Separates operational efficiency (speed of payment) from credit quality issues. Where to get Sourced from AR_ADJUSTMENTS_ALL where the adjustment type is classified as 'Write-off' or linked to a Bad Debt account. Capture Logged when transaction X executed Event type explicit | |||
| Payment Reminder Sent | Captures the issuance of a dunning letter or collection reminder to the customer. This event is generated by the Advanced Collections module. | ||
| Why it matters Essential for analyzing Collection Strategy Effectiveness. Correlating this with payments helps determine which reminder strategies yield the fastest cash recovery. Where to get Located in the IEX_DUNNING or IEX_STRATEGY_WORK_ITEMS tables linked to the customer account. Capture Logged when transaction X executed Event type explicit | |||
| Promise to Pay Received | Records a commitment from the customer to pay a specific amount by a specific date. This is typically entered manually by a collection agent during customer interaction. | ||
| Why it matters Key for Customer Payment Behavior Analysis. Broken promises indicate high credit risk and potential future bad debt. Where to get Sourced from the IEX_PROMISE_DETAILS table in the Collections module. Capture Logged when transaction X executed Event type explicit | |||
Extraction Guides
Steps
Access the Oracle BI Cloud Connector (BICC) Console. Navigate to the Manage Offerings and Data Stores section.
Configure the Storage Connection. Ensure you have a valid connection to Oracle Universal Content Management (UCM) or an external Object Storage (like OCI Object Storage) where the extracted CSV/Parquet files will be deposited.
Select the Financials Offering. Locate the Financials offering to access Accounts Receivable View Objects.
Select and Configure View Objects (VOs). You must select the specific Public View Objects (PVOs) required to construct the event log. Essential PVOs include:
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.TransactionHeaderExtractPVO (Invoice Headers)
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.TransactionLineExtractPVO (Invoice Lines)
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.ReceiptApplicationExtractPVO (Payments and CM Applications)
- FscmTopModelAM.FinExtractAM.ArBiccExtractAM.AdjustmentExtractPVO (Adjustments and Write-offs)
- FscmTopModelAM.FinExtractAM.IexBiccExtractAM.PromiseDetailExtractPVO (Promises to Pay)
- FscmTopModelAM.FinExtractAM.IexBiccExtractAM.StrategyWorkItemExtractPVO (Dunning/Reminders)
Define Filter Criteria (Pruning). In the Manage Extract Schedules or within the PVO configuration, set a filter on CreationDate or LastUpdateDate to extract data relevant to your analysis period (e.g., last 12 months).
Schedule the Extract. Create a job schedule to run these extracts daily. Choose Incremental Load to only fetch changed data after the initial Full Load.
Download and Ingest. Use an automated script or integration tool to pick up the files from UCM/Object Storage and load them into your data warehouse staging tables (e.g., STG_AR_TRX_HEADER, STG_AR_APPLICATIONS).
Apply Transformation Logic. Run the SQL script provided in the Query section against your staging tables to flatten the relational data into the ProcessMind event log format.
Validate Data Types. Ensure date fields are cast to datetime objects and numeric amounts handle decimals correctly during the transformation.
Export to CSV/Parquet. Export the final result set from your data warehouse as a single file.
Upload to ProcessMind. Import the file, mapping InvoiceNumber to Case ID, ActivityName to Activity, and EventStartDateTime to Timestamp.
Configuration
- Extraction Frequency: Daily (Incremental) recommended to capture latest status changes.
- Initial Load: Select 'Full Extract' for the first run, then switch to 'Incremental' based on Last Update Date.
- Key PVOs: TransactionHeaderExtractPVO, ReceiptApplicationExtractPVO, AdjustmentExtractPVO, StrategyWorkItemExtractPVO.
- Date Filtering: Apply filters on CreationDate >= '202X-01-01' to limit volume.
- Fetch Size: Default is usually 50000 rows; adjust based on network bandwidth if using UCM download.
- Primary Keys: Ensure your downstream data warehouse handles upserts using the PVO Primary Keys (usually CustomerTrxId, ReceivableApplicationId, etc.) to prevent duplicate rows.
- Audit History: Standard BICC PVOs capture current state. For exact historical timestamping of status changes (like Dispute Opened), enabling Audit Policies in Fusion and extracting Audit View Objects may be required if the transactional tables do not persist the history.
a Sample Query config
/*
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, EventStartDateTimeSteps
Log in to Oracle Fusion Applications: Navigate to Tools > Reports and Analytics. Click on Browse Catalog to open the Oracle BI Publisher interface.
Create Data Model: Click New (top left) and select Data Model. This is the container for your SQL extraction logic.
Define SQL Data Set: Under the Data Model tree on the left, click Data Sets, then select New Data Set > SQL Query.
Configure Data Source: Name the data set (e.g.,
ProcessMining_AR). SelectApplicationDB_FSCM(Financials Supply Chain Management) as the Data Source. This ensures access to the required AR and RA tables.Paste Query: Copy the complete SQL script provided in the Query section below and paste it into the SQL Query text box. Do not modify the core logic unless you need to rename specific Flexfields (DFFs).
Set Parameters: The query includes a placeholder
:p_start_datefor filtering by transaction creation date. In the Parameters tab of the Data Model, create a new parameter namedp_start_date, Data Type: Date, and set a default value (e.g.,01-01-2023).View Data: Click the Data tab, enter a valid date for the parameter, and click View. Ensure the output contains rows with columns like
InvoiceNumber,ActivityName, andEventStartDateTime.Save Data Model: Save the object in your Shared Folders > Custom directory (e.g.,
/Shared Folders/Custom/ProcessMining/AR_Extract_DM).Schedule/Export: To extract large volumes, click Create Report using this Data Model. In the report editor, verify the layout is a simple table. Save the report. Then, use the Scheduler to run the report and output the data as CSV or XML.
Final Formatting: Download the output file. If CSV, ensure the date format is consistent (ISO 8601 preferred). Upload this file to ProcessMind mapping
InvoiceNumberas Case ID,ActivityNameas Activity, andEventStartDateTimeas Timestamp.
Configuration
- Data Source: Use
ApplicationDB_FSCMto access Financials tables. - Date Filter: The query uses
ra_customer_trx_all.creation_date >= :p_start_date. Configure this to load data on a rolling window (e.g., last 12 months). - Performance: For datasets exceeding 100,000 invoices, consider adding a
ROWNUMlimit during testing or chunking the extraction by month. - Business Unit Filtering: If your organization has multiple Business Units and you only need one, uncomment the
AND trx.org_id = ...line in theWhereclauses. - User Names: The query resolves
CREATED_BYuser IDs to User Names viaFND_USER. Ensure the extraction user has permission to readFND_USER. - Advanced Collections: The activities 'Payment Reminder Sent' and 'Promise to Pay Received' rely on the IEX (Advanced Collections) module tables. If you do not use this module, these sections will simply return zero rows.
a Sample Query 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