Jouw datatemplate voor debiteurenbeheer

/* 
  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

/* 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