Templat data Order to Cash - Penagihan dan Faktur

WITH BaseInvoices AS (
    SELECT
        rct.trx_number,
        rct.customer_trx_id,
        hp.party_name AS customer_name,
        rct.trx_date,
        rct.creation_date,
        rct.last_update_date,
        rct.complete_flag,
        aps.status AS payment_schedule_status,
        aps.amount_due_original,
        aps.due_date,
        aps.dispute_flag,
        aps.gl_date_closed,
        rct.last_printed_date,
        rct.status_trx,
        bu.bu_name AS business_unit
    FROM
        ra_customer_trx_all rct
    JOIN
        hz_cust_accounts hca ON rct.bill_to_customer_id = hca.cust_account_id
    JOIN
        hz_parties hp ON hca.party_id = hp.party_id
    JOIN
        ar_payment_schedules_all aps ON rct.customer_trx_id = aps.customer_trx_id
    JOIN
        fun_all_business_units_v bu ON rct.org_id = bu.bu_id
    WHERE
        bu.bu_name = :P_BUSINESS_UNIT
        AND rct.creation_date BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD') + 1
        AND aps.class = 'INV'
)
-- 1. Invoice Created
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Created' AS "ActivityName",
    bi.creation_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi

UNION ALL

-- 2. Invoice Completed
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Completed' AS "ActivityName",
    bi.last_update_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.complete_flag = 'Y'

UNION ALL

-- 3. Invoice Submitted for Approval
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Submitted for Approval' AS "ActivityName",
    bi.last_update_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.status_trx = '[Your Pending Approval Status]' -- Placeholder: Configure status based on your system

UNION ALL

-- 4. Invoice Approved
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Approved' AS "ActivityName",
    bi.last_update_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.status_trx = '[Your Approved Status]' -- Placeholder: Configure status based on your system

UNION ALL

-- 5. Invoice Rejected
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Rejected' AS "ActivityName",
    bi.last_update_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.status_trx = '[Your Rejected Status]' -- Placeholder: Configure status based on your system

UNION ALL

-- 6. Invoice Sent to Customer
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Sent to Customer' AS "ActivityName",
    bi.last_printed_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.last_printed_date IS NOT NULL

UNION ALL

-- 7. Payment Due Date Reached
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Payment Due Date Reached' AS "ActivityName",
    bi.due_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.due_date <= CURRENT_TIMESTAMP

UNION ALL

-- 8. Payment Reminder Issued
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Payment Reminder Issued' AS "ActivityName",
    dlt.trx_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
JOIN
    ar_dunning_letters_trx dlt ON bi.customer_trx_id = dlt.customer_trx_id

UNION ALL

-- 9. Dispute Initiated
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Dispute Initiated' AS "ActivityName",
    bi.last_update_date AS "EventTimestamp", -- Uses last update as a proxy for dispute date
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    'Disputed' AS "InvoiceStatus", -- Overrides status
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.dispute_flag = 'Y'

UNION ALL

-- 10. Customer Payment Received
SELECT DISTINCT
    bi.trx_number AS "InvoiceNumber",
    'Customer Payment Received' AS "ActivityName",
    acr.receipt_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
JOIN
    ar_receivable_applications_all ara ON bi.customer_trx_id = ara.applied_customer_trx_id
JOIN
    ar_cash_receipts_all acr ON ara.cash_receipt_id = acr.cash_receipt_id
WHERE
    ara.status = 'APP'

UNION ALL

-- 11. Payment Applied to Invoice
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Payment Applied to Invoice' AS "ActivityName",
    ara.apply_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
JOIN
    ar_receivable_applications_all ara ON bi.customer_trx_id = ara.applied_customer_trx_id
WHERE
    ara.status = 'APP'

UNION ALL

-- 12. Invoice Adjusted
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Adjusted' AS "ActivityName",
    adj.creation_date AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    bi.status_trx AS "InvoiceStatus",
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
JOIN
    ar_adjustments_all adj ON bi.customer_trx_id = adj.customer_trx_id

UNION ALL

-- 13. Invoice Closed
SELECT
    bi.trx_number AS "InvoiceNumber",
    'Invoice Closed' AS "ActivityName",
    bi.gl_date_closed AS "EventTimestamp",
    bi.amount_due_original AS "InvoiceAmount",
    bi.customer_name AS "CustomerName",
    bi.due_date AS "DueDate",
    'Closed' AS "InvoiceStatus", -- Overrides status
    bi.business_unit AS "BusinessUnit"
FROM
    BaseInvoices bi
WHERE
    bi.payment_schedule_status = 'CL' AND bi.gl_date_closed IS NOT NULL

import requests
import pandas as pd
import datetime
import os

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance URL, username, and password
FUSION_URL = class="hl-string">"https://[your-pod].fa.em2.oraclecloud.com/fscmRestApi/resources/11.13.18.05"
USERNAME = class="hl-string">"YOUR_USERNAME"
PASSWORD = class="hl-string">"YOUR_PASSWORD"

class=class="hl-string">"hl-comment"># --- Filter Parameters ---
class=class="hl-string">"hl-comment"># Define the date range for the initial invoice query
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-06-30"
class=class="hl-string">"hl-comment"># Optional: Filter by a specific Business Unit
BUSINESS_UNIT_FILTER = class="hl-string">"" class=class="hl-string">"hl-comment"># e.g., class="hl-string">" and BusinessUnitName = 'US1 Business Unit'"

class=class="hl-string">"hl-comment"># API call settings
API_LIMIT = 100

def get_paginated_data(endpoint, params):
    class="hl-string">"""Helper function to handle pagination for Oracle Fusion REST API calls."""
    all_items = []
    url = fclass="hl-string">"{FUSION_URL}/{endpoint}"
    
    while url:
        try:
            response = requests.get(url, auth=(USERNAME, PASSWORD), params=params)
            response.raise_for_status()
            data = response.json()
            all_items.extend(data.get(class="hl-string">'items', []))
            
            class=class="hl-string">"hl-comment"># Get the next page link
            links = data.get(class="hl-string">'links', [])
            next_link = next((link[class="hl-string">'href'] for link in links if link[class="hl-string">'rel'] == class="hl-string">'next'), None)
            url = next_link
            class=class="hl-string">"hl-comment"># Params are only needed for the first request
            params = {}

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error fetching data from {url}: {e}")
            print(fclass="hl-string">"Response body: {response.text}")
            break
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Request Error: {e}")
            break
            
    return all_items


def main():
    class="hl-string">"""Main function to extract, transform, and save the event log."""
    event_log = []
    today = datetime.datetime.now().strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

    class=class="hl-string">"hl-comment"># 1. Fetch main invoice data
    print(class="hl-string">"Fetching invoices...")
    invoice_params = {
        class="hl-string">'q': fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'{BUSINESS_UNIT_FILTER}",
        class="hl-string">'fields': class="hl-string">'TransactionNumber,CreationDate,TransactionDate,CreatedBy,LastUpdateDate,LastUpdatedBy,Amount,AmountDueRemaining,Status,CompletedDate,CustomerName,DueDate,BusinessUnitName,ApprovalStatus,paymentSchedules,CustomerTransactionId',
        class="hl-string">'limit': API_LIMIT,
        class="hl-string">'expand': class="hl-string">'paymentSchedules'
    }
    invoices = get_paginated_data(class="hl-string">'receivablesInvoices', invoice_params)
    print(fclass="hl-string">"Found {len(invoices)} invoices.")

    class=class="hl-string">"hl-comment"># 2. Process each invoice to generate events
    for i, inv in enumerate(invoices):
        print(fclass="hl-string">"Processing invoice {i+1}/{len(invoices)}: {inv.get('TransactionNumber')}")
        
        case_id = inv.get(class="hl-string">'TransactionNumber')
        cust_trx_id = inv.get(class="hl-string">'CustomerTransactionId')
        common_attrs = {
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'InvoiceAmount': inv.get(class="hl-string">'Amount'),
            class="hl-string">'CustomerName': inv.get(class="hl-string">'CustomerName'),
            class="hl-string">'DueDate': inv.get(class="hl-string">'DueDate'),
            class="hl-string">'InvoiceStatus': inv.get(class="hl-string">'Status'),
            class="hl-string">'BusinessUnit': inv.get(class="hl-string">'BusinessUnitName')
        }

        class=class="hl-string">"hl-comment"># Event: Invoice Created
        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Created', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'CreationDate')})

        class=class="hl-string">"hl-comment"># Event: Invoice Completed
        if inv.get(class="hl-string">'CompletedDate'):
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Completed', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'CompletedDate')})
        
        class=class="hl-string">"hl-comment"># Events: Approval Cycle (based on final status)
        if inv.get(class="hl-string">'ApprovalStatus') == class="hl-string">'APPROVED':
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Approved', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'LastUpdateDate')})
        elif inv.get(class="hl-string">'ApprovalStatus') == class="hl-string">'REJECTED':
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Rejected', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'LastUpdateDate')})
        elif inv.get(class="hl-string">'ApprovalStatus') == class="hl-string">'PENDING':
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Submitted for Approval', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'LastUpdateDate')})
        
        class=class="hl-string">"hl-comment"># Placeholder for class="hl-string">'Invoice Sent to Customer' - requires specific configuration
        class=class="hl-string">"hl-comment"># This might come from BI Publisher or another module. Using LastUpdateDate if status is class="hl-string">'COMPLETE' as an example.
        if inv.get(class="hl-string">'Status') == class="hl-string">'COMPLETE': class=class="hl-string">"hl-comment"># This is a proxy event
             event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Sent to Customer', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'LastUpdateDate')})

        class=class="hl-string">"hl-comment"># Event: Payment Due Date Reached (calculated)
        if inv.get(class="hl-string">'DueDate') and inv.get(class="hl-string">'DueDate') < today and inv.get(class="hl-string">'AmountDueRemaining', 0) > 0:
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Payment Due Date Reached', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'DueDate')})

        class=class="hl-string">"hl-comment"># Event: Dispute Initiated
        for ps in inv.get(class="hl-string">'paymentSchedules', []):
            if ps.get(class="hl-string">'DisputeDate'):
                event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Dispute Initiated', class="hl-string">'EventTimestamp': ps.get(class="hl-string">'DisputeDate')})
                break class=class="hl-string">"hl-comment"># Assuming one dispute event per invoice for simplicity

        class=class="hl-string">"hl-comment"># Event: Invoice Closed
        if inv.get(class="hl-string">'AmountDueRemaining') == 0 and inv.get(class="hl-string">'Status') == class="hl-string">'CLOSED':
             event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Closed', class="hl-string">'EventTimestamp': inv.get(class="hl-string">'LastUpdateDate')})
        
        class=class="hl-string">"hl-comment"># 3. Fetch related events using other APIs
        if cust_trx_id:
            class=class="hl-string">"hl-comment"># Events: Payment Applied and Payment Received
            apps = get_paginated_data(class="hl-string">'receivablesReceipts', {class="hl-string">'q': fclass="hl-string">'AppliedCustomerTransactionId={cust_trx_id}', class="hl-string">'onlyData': class="hl-string">'true', class="hl-string">'fields': class="hl-string">'ReceiptNumber,ReceiptDate,ActivityApplication.ApplyDate,ActivityApplication.AppliedCustomerTransactionId'})
            for app in apps:
                for activity_app in app.get(class="hl-string">'ActivityApplication', []):
                    if activity_app.get(class="hl-string">'AppliedCustomerTransactionId') == cust_trx_id:
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Customer Payment Received', class="hl-string">'EventTimestamp': app.get(class="hl-string">'ReceiptDate')})
                        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Payment Applied to Invoice', class="hl-string">'EventTimestamp': activity_app.get(class="hl-string">'ApplyDate')})

            class=class="hl-string">"hl-comment"># Event: Invoice Adjusted
            adjustments = get_paginated_data(class="hl-string">'receivablesAdjustments', {class="hl-string">'q': fclass="hl-string">'CustomerTransactionId={cust_trx_id}', class="hl-string">'onlyData': class="hl-string">'true', class="hl-string">'fields': class="hl-string">'ApplyDate'})
            for adj in adjustments:
                event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Adjusted', class="hl-string">'EventTimestamp': adj.get(class="hl-string">'ApplyDate')})

            class=class="hl-string">"hl-comment"># Event: Payment Reminder Issued (from Delinquency data)
            reminders = get_paginated_data(class="hl-string">'collectionsDelinquentTransactions', {class="hl-string">'q': fclass="hl-string">'TransactionId={cust_trx_id}', class="hl-string">'onlyData': class="hl-string">'true', class="hl-string">'fields': class="hl-string">'LastDunningLetterDate'})
            for rem in reminders:
                if rem.get(class="hl-string">'LastDunningLetterDate'):
                    event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Payment Reminder Issued', class="hl-string">'EventTimestamp': rem.get(class="hl-string">'LastDunningLetterDate')})

    class=class="hl-string">"hl-comment"># 4. Create DataFrame and save to CSV
    if not event_log:
        print(class="hl-string">"No events generated. Exiting.")
        return

    df = pd.DataFrame(event_log)
    df = df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTimestamp'])
    class=class="hl-string">"hl-comment"># Ensure required columns are present
    df_final = df[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName', class="hl-string">'DueDate', class="hl-string">'InvoiceStatus', class="hl-string">'BusinessUnit']]

    output_filename = class="hl-string">'oracle_fusion_ar_event_log.csv'
    df_final.to_csv(output_filename, index=False)
    print(fclass="hl-string">"\nEvent log successfully saved to {os.path.abspath(output_filename)}")

if __name__ == class="hl-string">'__main__':
    main()