与信管理・債権回収データテンプレート

WITH invoices_base AS (
    SELECT 
        trx.customer_trx_id,
        trx.trx_number AS InvoiceNumber,
        hca.account_number AS CustomerNumber,
        hcp.class_category || ':' || hcp.class_code AS CustomerSegment, -- Example of segment, may need adjustment
        ps.amount_due_original AS InvoiceAmount,
        coll.name AS Collector,
        ps.due_date AS DueDate,
        trx.creation_date AS InvoiceCreationDate,
        trx.created_by AS InvoiceCreatedBy,
        ps.payment_schedule_id
    FROM 
        ra_customer_trx_all trx
        JOIN ar_payment_schedules_all ps ON trx.customer_trx_id = ps.customer_trx_id
        JOIN hz_cust_accounts hca ON trx.bill_to_customer_id = hca.cust_account_id
        JOIN hz_customer_profiles hcp ON hca.cust_account_id = hcp.cust_account_id AND hcp.site_use_id IS NULL
        LEFT JOIN iex_delinquencies_all del ON ps.payment_schedule_id = del.payment_schedule_id
        LEFT JOIN JTF_RS_RESOURCE_EXTNS_VL coll ON del.collector_id = coll.resource_id
    WHERE 
        trx.creation_date BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')
        AND trx.complete_flag = 'Y'
        AND ps.class = 'INV'
)
-- 1. Credit Review Completed
SELECT
    ib.InvoiceNumber AS "InvoiceNumber",
    'Credit Review Completed' AS "ActivityName",
    cr.review_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber AS "CustomerNumber",
    ib.CustomerSegment AS "CustomerSegment",
    ib.InvoiceAmount AS "InvoiceAmount",
    ib.Collector AS "Collector",
    NULL AS "DunningLevel",
    ib.DueDate AS "DueDate",
    cr.created_by AS "User"
FROM
    invoices_base ib
JOIN
    hz_credit_reviews cr ON ib.CustomerNumber = (SELECT hca.account_number FROM hz_cust_accounts hca WHERE hca.cust_account_id = cr.cust_account_id)
WHERE cr.review_date = (SELECT MAX(cr_inner.review_date) FROM hz_credit_reviews cr_inner WHERE cr_inner.cust_account_id = cr.cust_account_id AND cr_inner.review_date < ib.InvoiceCreationDate)

UNION ALL

-- 2. Invoice Generated
SELECT
    ib.InvoiceNumber,
    'Invoice Generated' AS "ActivityName",
    trx.creation_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    trx.created_by AS "User"
FROM
    invoices_base ib
JOIN ra_customer_trx_all trx ON ib.customer_trx_id = trx.customer_trx_id

UNION ALL

-- 3. Invoice Sent To Customer
SELECT
    ib.InvoiceNumber,
    'Invoice Sent To Customer' AS "ActivityName",
    trx.last_printed_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    trx.last_updated_by AS "User"
FROM
    invoices_base ib
JOIN ra_customer_trx_all trx ON ib.customer_trx_id = trx.customer_trx_id
WHERE trx.last_printed_date IS NOT NULL

UNION ALL

-- 4. Payment Due Date Passed
SELECT
    ib.InvoiceNumber,
    'Payment Due Date Passed' AS "ActivityName",
    ps.due_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    'SYSTEM' AS "User"
FROM
    invoices_base ib
JOIN ar_payment_schedules_all ps ON ib.payment_schedule_id = ps.payment_schedule_id
WHERE ps.due_date < SYSDATE AND ps.status = 'OP'

UNION ALL

-- 5. Dunning Procedure Initiated
SELECT 
    ib.InvoiceNumber,
    'Dunning Procedure Initiated' AS "ActivityName",
    dunn.dunning_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    TO_CHAR(dunn.dunning_level) AS "DunningLevel",
    ib.DueDate,
    dunn.created_by AS "User"
FROM 
    invoices_base ib
JOIN iex_dunning_transactions dunt ON ib.customer_trx_id = dunt.transaction_id
JOIN iex_dunnings dunn ON dunt.dunning_id = dunn.dunning_id

UNION ALL

-- 6. Collection Strategy Assigned
SELECT
    ib.InvoiceNumber,
    'Collection Strategy Assigned' AS "ActivityName",
    strat.creation_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    strat.created_by AS "User"
FROM
    invoices_base ib
JOIN iex_strategy_work_items swi ON ib.payment_schedule_id = swi.payment_schedule_id
JOIN iex_strategies_vl strat ON swi.strategy_id = strat.strategy_id

UNION ALL

-- 7. Collector Action Completed
SELECT
    ib.InvoiceNumber,
    task_type.name AS "ActivityName",
    task.actual_end_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    res.source_name AS "User"
FROM 
    invoices_base ib
JOIN jtf_task_references_b ref ON ib.customer_trx_id = ref.object_id AND ref.object_type_code = 'OKC_K_HEADER'
JOIN jtf_tasks_b task ON ref.task_id = task.task_id
JOIN jtf_task_types_vl task_type ON task.task_type_id = task_type.task_type_id
JOIN jtf_rs_resource_extns_vl res ON task.owner_id = res.resource_id
WHERE task.actual_end_date IS NOT NULL

UNION ALL

-- 8. Promise To Pay Created
SELECT
    ib.InvoiceNumber,
    'Promise To Pay Created' AS "ActivityName",
    prom.creation_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    prom.created_by AS "User"
FROM 
    invoices_base ib
JOIN iex_promise_details prom ON ib.payment_schedule_id = prom.payment_schedule_id

UNION ALL

-- 9. Dispute Registered
SELECT
    ib.InvoiceNumber,
    'Dispute Registered' AS "ActivityName",
    ps.dispute_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    ps.last_updated_by AS "User"
FROM 
    invoices_base ib
JOIN ar_payment_schedules_all ps ON ib.payment_schedule_id = ps.payment_schedule_id
WHERE ps.amount_in_dispute IS NOT NULL AND ps.dispute_date IS NOT NULL

UNION ALL

-- 10. Dispute Resolved
SELECT
    ib.InvoiceNumber,
    'Dispute Resolved' AS "ActivityName",
    disp.resolution_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    disp.last_updated_by AS "User"
FROM 
    invoices_base ib
JOIN ar_disputes_all disp ON ib.payment_schedule_id = disp.payment_schedule_id
WHERE disp.status = 'CLOSED' AND disp.resolution_date IS NOT NULL

UNION ALL

-- 11. Payment Received
SELECT
    ib.InvoiceNumber,
    'Payment Received' AS "ActivityName",
    cr.receipt_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    cr.created_by AS "User"
FROM 
    invoices_base ib
JOIN ar_receivable_applications_all app ON ib.payment_schedule_id = app.applied_payment_schedule_id
JOIN ar_cash_receipts_all cr ON app.cash_receipt_id = cr.cash_receipt_id
WHERE app.status = 'APP'

UNION ALL

-- 12. Payment Applied
SELECT
    ib.InvoiceNumber,
    'Payment Applied' AS "ActivityName",
    app.apply_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    app.created_by AS "User"
FROM 
    invoices_base ib
JOIN ar_receivable_applications_all app ON ib.payment_schedule_id = app.applied_payment_schedule_id
WHERE app.status = 'APP'

UNION ALL

-- 13. Invoice Closed
SELECT
    ib.InvoiceNumber,
    'Invoice Closed' AS "ActivityName",
    ps.gl_date_closed AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    ps.last_updated_by AS "User"
FROM 
    invoices_base ib
JOIN ar_payment_schedules_all ps ON ib.payment_schedule_id = ps.payment_schedule_id
WHERE ps.status = 'CL' AND ps.gl_date_closed IS NOT NULL

UNION ALL

-- 14. Invoice Written Off
SELECT
    ib.InvoiceNumber,
    'Invoice Written Off' AS "ActivityName",
    adj.apply_date AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    ib.CustomerNumber,
    ib.CustomerSegment,
    ib.InvoiceAmount,
    ib.Collector,
    NULL AS "DunningLevel",
    ib.DueDate,
    adj.created_by AS "User"
FROM
    invoices_base ib
JOIN ar_adjustments_all adj ON ib.customer_trx_id = adj.customer_trx_id
JOIN ar_receivables_trx_all rt ON adj.receivables_trx_id = rt.receivables_trx_id
WHERE rt.name = '[Your Write-Off Activity Name]' -- Example: 'Bad Debt Write-off'