Seu Template de Dados para Purchase to Pay

// This is a configuration guide for creating multiple Coupa reports. These reports must be combined externally to form the final event log.

// --- Report 1: Invoice Creation Event ---
// Primary Object: Invoice Header
// Columns:
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Created' (Static Value)
//   'EventTime' = Invoice Header > Created At
//   'User' = Invoice Header > Created By > Login
//   'VendorName' = Invoice Header > Supplier > Name
//   'InvoiceTotalAmount' = Invoice Header > Total
//   'PaymentDueDate' = Invoice Header > Payment Due Date
//   'PurchaseOrderNumber' = Invoice Header > PO Number
//   'InvoiceStatus' = Invoice Header > Status

// --- Report 2: Invoice Status Change Events ---
// Primary Object: Invoice Audit Trail
// Joined Object: Invoice Header (on Invoice ID)
// Columns:
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = CASE WHEN (Field Name = 'status' AND Old Value = 'new' AND New Value = 'pending_approval') THEN 'Invoice Submitted for Processing' WHEN (Field Name = 'po_match_status' AND New Value = 'matched') THEN 'Invoice Matched to PO' WHEN (Field Name = 'po_match_status' AND New Value = 'unmatched') THEN 'Matching Discrepancy Identified' WHEN (Field Name = 'status' AND New Value = 'on_hold') THEN 'Invoice Hold Placed' WHEN (Field Name = 'status' AND Old Value = 'on_hold') THEN 'Invoice Hold Released' WHEN (Field Name = 'exported' AND New Value = 't') THEN 'Invoice Posted for Payment' WHEN (Field Name = 'status' AND New Value = 'voided') THEN 'Invoice Voided' END
//   'EventTime' = Invoice Audit Trail > Created At
//   'User' = Invoice Audit Trail > Updated By > Login
//   'VendorName', 'InvoiceTotalAmount', etc. from joined Invoice Header.
// Filters:
//   Field Name IN ('status', 'po_match_status', 'exported')

// --- Report 3: Invoice Approval Events ---
// Primary Object: Approval
// Joined Object: Invoice Header (on Invoice ID)
// This report will create multiple rows from one source object. This logic needs to be applied in the external transformation script.
// Logic Block 1: Invoice Sent for Approval (First Approver Record for the Invoice)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Sent for Approval'
//   'EventTime' = MIN(Approval > Created At) GROUP BY InvoiceNumber
//   'User' = Approver associated with the MIN(Created At) timestamp.
// Logic Block 2: Invoice Rejected
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Rejected'
//   'EventTime' = Approval > Created At
//   'User' = Approval > Approver > Login
//   Filter: Approval > Status = 'rejected'
// Logic Block 3: Invoice Approved (Final Approver Record)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Approved'
//   'EventTime' = MAX(Approval > Created At) where Status is 'approved'
//   'User' = Approver associated with the MAX(Created At) timestamp.

// --- Report 4: Discrepancy Resolution Event ---
// Primary Object: Invoice Audit Trail
// Joined Object: Invoice Header (on Invoice ID)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Discrepancy Resolution Started'
//   'EventTime' = Invoice Audit Trail > Created At
//   'User' = Invoice Audit Trail > Updated By > Login
// Filter:
//   This event is difficult. A common proxy is the first user comment or field change after the PO match status changes to 'unmatched'.
//   Logic: Find the MIN(Created At) from the audit trail where the timestamp is greater than the timestamp of the 'Matching Discrepancy Identified' event.

// --- Report 5: Payment Events ---
// Primary Object: Payment Information
// Joined Object: Invoice Header (on Invoice ID)
// Logic Block 1: Payment Scheduled
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Payment Scheduled'
//   'EventTime' = Payment Information > Payment Date
//   'User' = 'System'
// Logic Block 2: Payment Executed
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Payment Executed'
//   'EventTime' = Payment Information > Paid At
//   'User' = 'System'
// Filter:
//   Payment Information > Paid At IS NOT NULL

import requests
import pandas as pd
import time
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Coupa instance URL (e.g., class="hl-string">'https://mycompany.coupahost.com')
COUPE_INSTANCE_URL = class="hl-string">'YOUR_COUPE_INSTANCE_URL' 
class=class="hl-string">"hl-comment"># Replace with your Coupa API Key
API_KEY = class="hl-string">'YOUR_API_KEY'
class=class="hl-string">"hl-comment"># Set the start date for the extraction (YYYY-MM-DD format)
START_DATE = class="hl-string">'2023-01-01' 

class=class="hl-string">"hl-comment"># --- API Setup ---
API_BASE_URL = fclass="hl-string">"{COUPE_INSTANCE_URL}/api"
HEADERS = {
    class="hl-string">'Accept': class="hl-string">'application/json',
    class="hl-string">'X-COUPA-API-KEY': API_KEY
}

class=class="hl-string">"hl-comment"># --- Event Log Storage ---
event_log_records = []

def get_paginated_data(endpoint_url, params=None):
    class="hl-string">"""Fetches all pages of data from a Coupa API endpoint."""
    all_data = []
    url = fclass="hl-string">"{API_BASE_URL}/{endpoint_url}"
    
    class=class="hl-string">"hl-comment"># Coupa uses offset-based pagination, 50 is a common limit
    offset = 0
    while True:
        paginated_params = params.copy() if params else {}
        paginated_params[class="hl-string">'offset'] = offset
        paginated_params[class="hl-string">'limit'] = 50
        
        try:
            response = requests.get(url, headers=HEADERS, params=paginated_params)
            response.raise_for_status()
            data = response.json()
            if not data: class=class="hl-string">"hl-comment"># Break if the page is empty
                break
            all_data.extend(data)
            offset += len(data)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {url}: {e}")
            break
    return all_data

def add_event(invoice, activity_name, event_time, user_info=None):
    class="hl-string">"""Helper function to create and add an event record."""
    if not event_time:
        return class=class="hl-string">"hl-comment"># Do not add events without a timestamp

    record = {
        class="hl-string">'InvoiceNumber': invoice.get(class="hl-string">'invoice-number'),
        class="hl-string">'ActivityName': activity_name,
        class="hl-string">'EventTime': event_time,
        class="hl-string">'User': user_info.get(class="hl-string">'login') if user_info else None,
        class="hl-string">'VendorName': invoice.get(class="hl-string">'supplier', {}).get(class="hl-string">'name'),
        class="hl-string">'InvoiceTotalAmount': invoice.get(class="hl-string">'total-with-taxes'),
        class="hl-string">'PaymentDueDate': invoice.get(class="hl-string">'payment-date'),
        class="hl-string">'PurchaseOrderNumber': invoice.get(class="hl-string">'purchase-order', {}).get(class="hl-string">'number') if invoice.get(class="hl-string">'purchase-order') else None,
        class="hl-string">'InvoiceStatus': invoice.get(class="hl-string">'status')
    }
    event_log_records.append(record)

def main():
    class="hl-string">"""Main extraction function."""
    print(class="hl-string">"Starting Coupa invoice data extraction...")
    
    class=class="hl-string">"hl-comment"># Fetch all invoices updated since the start date
    invoice_params = {class="hl-string">'updated-at[gt]': START_DATE}
    invoices = get_paginated_data(class="hl-string">'invoices', invoice_params)
    print(fclass="hl-string">"Found {len(invoices)} invoices to process.")

    for i, invoice in enumerate(invoices):
        print(fclass="hl-string">"Processing invoice {i+1}/{len(invoices)}: {invoice.get('invoice-number')}")
        invoice_id = invoice.get(class="hl-string">'id')

        class=class="hl-string">"hl-comment"># Activity: Invoice Created
        add_event(invoice, class="hl-string">'Invoice Created', invoice.get(class="hl-string">'created-at'), invoice.get(class="hl-string">'created-by'))

        class=class="hl-string">"hl-comment"># Activity: Invoice Submitted for Processing
        add_event(invoice, class="hl-string">'Invoice Submitted for Processing', invoice.get(class="hl-string">'submitted-at'), invoice.get(class="hl-string">'submitted-by'))

        class=class="hl-string">"hl-comment"># Activities related to matching status
        if invoice.get(class="hl-string">'po-match-status') == class="hl-string">'matched':
            add_event(invoice, class="hl-string">'Invoice Matched to PO', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))
        elif invoice.get(class="hl-string">'po-match-status') in [class="hl-string">'unmatched', class="hl-string">'mismatched']:
            add_event(invoice, class="hl-string">'Matching Discrepancy Identified', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))
            class=class="hl-string">"hl-comment"># Inference for Discrepancy Resolution Started is complex. We approximate it as the next update.
            class=class="hl-string">"hl-comment"># A more robust solution would track status changes over time.
            add_event(invoice, class="hl-string">'Discrepancy Resolution Started', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))

        class=class="hl-string">"hl-comment"># Activities from Approvals endpoint
        approvals = get_paginated_data(fclass="hl-string">'invoices/{invoice_id}/approvals')
        if approvals:
            class=class="hl-string">"hl-comment"># Sent for Approval is often the creation of the first approval object
            first_approval = approvals[0]
            add_event(invoice, class="hl-string">'Invoice Sent for Approval', first_approval.get(class="hl-string">'created-at'), first_approval.get(class="hl-string">'created-by'))
            
            for approval in approvals:
                approver = approval.get(class="hl-string">'approver')
                if approval.get(class="hl-string">'status') == class="hl-string">'approved':
                    add_event(invoice, class="hl-string">'Invoice Approved', approval.get(class="hl-string">'updated-at'), approver)
                elif approval.get(class="hl-string">'status') == class="hl-string">'rejected':
                    add_event(invoice, class="hl-string">'Invoice Rejected', approval.get(class="hl-string">'updated-at'), approver)

        class=class="hl-string">"hl-comment"># Activities based on status and flags
        if invoice.get(class="hl-string">'on-hold'):
            class=class="hl-string">"hl-comment"># This timestamp is an approximation, representing the last update time when the hold was active.
            add_event(invoice, class="hl-string">'Invoice Hold Placed', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))
        class=class="hl-string">"hl-comment"># We canclass="hl-string">'t reliably detect 'Hold Released' without historical data, so we omit it for accuracy.

        if invoice.get(class="hl-string">'exported') == True:
            add_event(invoice, class="hl-string">'Invoice Posted for Payment', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))

        if invoice.get(class="hl-string">'status') == class="hl-string">'void':
            add_event(invoice, class="hl-string">'Invoice Voided', invoice.get(class="hl-string">'updated-at'), invoice.get(class="hl-string">'updated-by'))
        
        class=class="hl-string">"hl-comment"># Activities from Payments endpoint
        payments = get_paginated_data(class="hl-string">'payments', {class="hl-string">'invoice-id': invoice_id})
        for payment in payments:
            add_event(invoice, class="hl-string">'Payment Scheduled', payment.get(class="hl-string">'created-at'), payment.get(class="hl-string">'created-by'))
            if payment.get(class="hl-string">'payment-date'):
                add_event(invoice, class="hl-string">'Payment Executed', payment.get(class="hl-string">'payment-date'), payment.get(class="hl-string">'updated-by'))

        class=class="hl-string">"hl-comment"># A short delay to respect API rate limits
        time.sleep(0.5)

    if not event_log_records:
        print(class="hl-string">"No events were generated. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(event_log_records)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m-%d %H:%M:%S')
    df = df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'])
    
    output_filename = class="hl-string">'coupa_invoice_event_log.csv'
    df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')
    print(fclass="hl-string">"Extraction complete. Event log saved to {os.path.abspath(output_filename)}")

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

// This is a configuration guide for creating multiple Coupa reports. These reports must be combined externally to form the final event log.

// --- Report 1: Invoice Creation Event ---
// Primary Object: Invoice Header
// Columns:
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Created' (Static Value)
//   'EventTime' = Invoice Header > Created At
//   'User' = Invoice Header > Created By > Login
//   'VendorName' = Invoice Header > Supplier > Name
//   'InvoiceTotalAmount' = Invoice Header > Total
//   'PaymentDueDate' = Invoice Header > Payment Due Date
//   'PurchaseOrderNumber' = Invoice Header > PO Number
//   'InvoiceStatus' = Invoice Header > Status

// --- Report 2: Invoice Status Change Events ---
// Primary Object: Invoice Audit Trail
// Joined Object: Invoice Header (on Invoice ID)
// Columns:
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = CASE WHEN (Field Name = 'status' AND Old Value = 'new' AND New Value = 'pending_approval') THEN 'Invoice Submitted for Processing' WHEN (Field Name = 'po_match_status' AND New Value = 'matched') THEN 'Invoice Matched to PO' WHEN (Field Name = 'po_match_status' AND New Value = 'unmatched') THEN 'Matching Discrepancy Identified' WHEN (Field Name = 'status' AND New Value = 'on_hold') THEN 'Invoice Hold Placed' WHEN (Field Name = 'status' AND Old Value = 'on_hold') THEN 'Invoice Hold Released' WHEN (Field Name = 'exported' AND New Value = 't') THEN 'Invoice Posted for Payment' WHEN (Field Name = 'status' AND New Value = 'voided') THEN 'Invoice Voided' END
//   'EventTime' = Invoice Audit Trail > Created At
//   'User' = Invoice Audit Trail > Updated By > Login
//   'VendorName', 'InvoiceTotalAmount', etc. from joined Invoice Header.
// Filters:
//   Field Name IN ('status', 'po_match_status', 'exported')

// --- Report 3: Invoice Approval Events ---
// Primary Object: Approval
// Joined Object: Invoice Header (on Invoice ID)
// This report will create multiple rows from one source object. This logic needs to be applied in the external transformation script.
// Logic Block 1: Invoice Sent for Approval (First Approver Record for the Invoice)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Sent for Approval'
//   'EventTime' = MIN(Approval > Created At) GROUP BY InvoiceNumber
//   'User' = Approver associated with the MIN(Created At) timestamp.
// Logic Block 2: Invoice Rejected
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Rejected'
//   'EventTime' = Approval > Created At
//   'User' = Approval > Approver > Login
//   Filter: Approval > Status = 'rejected'
// Logic Block 3: Invoice Approved (Final Approver Record)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Invoice Approved'
//   'EventTime' = MAX(Approval > Created At) where Status is 'approved'
//   'User' = Approver associated with the MAX(Created At) timestamp.

// --- Report 4: Discrepancy Resolution Event ---
// Primary Object: Invoice Audit Trail
// Joined Object: Invoice Header (on Invoice ID)
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Discrepancy Resolution Started'
//   'EventTime' = Invoice Audit Trail > Created At
//   'User' = Invoice Audit Trail > Updated By > Login
// Filter:
//   This event is difficult. A common proxy is the first user comment or field change after the PO match status changes to 'unmatched'.
//   Logic: Find the MIN(Created At) from the audit trail where the timestamp is greater than the timestamp of the 'Matching Discrepancy Identified' event.

// --- Report 5: Payment Events ---
// Primary Object: Payment Information
// Joined Object: Invoice Header (on Invoice ID)
// Logic Block 1: Payment Scheduled
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Payment Scheduled'
//   'EventTime' = Payment Information > Payment Date
//   'User' = 'System'
// Logic Block 2: Payment Executed
//   'InvoiceNumber' = Invoice Header > Invoice Number
//   'ActivityName' = 'Payment Executed'
//   'EventTime' = Payment Information > Paid At
//   'User' = 'System'
// Filter:
//   Payment Information > Paid At IS NOT NULL