Script to create a Receipt Write-off (AR)

The following post will detail the steps that are done to create a Receipt Write-Off on an AR Receipt. The PL/SQL API Ar_receipt_api_pub.activity_application is used to apply an adjustment on a Receipt – in this case – a Write-Off on a Receipt.

In this post – before doing a Write-Off on a Receipt – let us create a sample Receipt first. In order to create a Receipt – Navigate to the following: Receivables Superuser -> Receipts -> Receipts. Enter the following mandatory fields like – Receipt Method, Receipt Number, Receipt Amount, Customer details and click Save. Upon creating a Receipt – it shows up as below:

-- Script to create a Receipt Write-off
DECLARE
    CURSOR c_receipt_data
    IS
        SELECT cash_receipt_id, receipt_number, amount, org_id
          FROM ar_Cash_receipts_all
         WHERE receipt_number = '2220192'   -- Enter your receipt number here
           AND status = 'UNAPP';

    l_applied_payment_schedule_id    NUMBER;
    l_receivables_trx_id             NUMBER;
    
    x_return_status                  VARCHAR2 (200);
    x_msg_count                      NUMBER;
    x_msg_data                       VARCHAR2 (200);
    p_link_to_customer_trx_id        ra_customer_trx.customer_trx_id%TYPE;
    p_apply_date                     ar_receivable_applications.apply_date%TYPE;
    p_apply_gl_date                  ar_receivable_applications.gl_date%TYPE;
    p_ussgl_transaction_code         ar_receivable_applications.ussgl_transaction_code%TYPE;
    p_attribute_rec                  AR_RECEIPT_API_PUB.attribute_rec_type;
    p_global_attribute_rec           AR_RECEIPT_API_PUB.global_attribute_rec_type;
    p_comments                       ar_receivable_applications.comments%TYPE;
    p_application_ref_type           ar_receivable_applications.application_ref_type%TYPE;
    p_application_ref_id             ar_receivable_applications.application_ref_id%TYPE;
    p_application_ref_num            ar_receivable_applications.application_ref_num%TYPE;
    p_secondary_application_ref_id   ar_receivable_applications.secondary_application_ref_id%TYPE;
    p_payment_set_id                 ar_receivable_applications.payment_set_id%TYPE;
    p_receivable_application_id      ar_receivable_applications.receivable_application_id%TYPE;
    p_customer_reference             ar_receivable_applications.customer_reference%TYPE;
    p_val_writeoff_limits_flag       VARCHAR2 (200);
    p_called_from                    VARCHAR2 (200);
    p_netted_receipt_flag            VARCHAR2 (200);
    p_netted_cash_receipt_id         ar_cash_receipts.cash_receipt_id%TYPE;
    p_secondary_app_ref_type         ar_receivable_applications.secondary_application_ref_type%TYPE;
    p_secondary_app_ref_num          ar_receivable_applications.secondary_application_ref_num%TYPE;

BEGIN

    l_applied_payment_schedule_id := -3;

    SELECT RECEIVABLES_TRX_ID 
      INTO l_receivables_trx_id
      FROM ar_receivables_trx_all
     WHERE name = 'Receipt Write Off';

    FOR rec_recpt_data IN c_receipt_data
    LOOP 
    
    mo_global.init ('AR');
    mo_global.set_policy_context ('S', rec_recpt_data.org_id);
    
         AR_RECEIPT_API_PUB.Activity_application (
                                        p_api_version                    => 1.0,
                                        p_init_msg_list                  => FND_API.G_TRUE,
                                        p_commit                         => FND_API.G_TRUE,
                                        p_validation_level               => FND_API.G_VALID_LEVEL_FULL,
                                        x_return_status                  => x_return_status,
                                        x_msg_count                      => x_msg_count,
                                        x_msg_data                       => x_msg_data,
                                        p_cash_receipt_id                => rec_recpt_data.cash_receipt_id,
                                        p_receipt_number                 => rec_recpt_data.receipt_number,
                                        p_amount_applied                 => rec_recpt_data.amount,
                                        p_applied_payment_schedule_id    => l_applied_payment_schedule_id,
                                        p_link_to_customer_trx_id        => p_link_to_customer_trx_id,
                                        p_receivables_trx_id             => l_receivables_trx_id,
                                        p_apply_date                     => p_apply_date,
                                        p_apply_gl_date                  => p_apply_gl_date,
                                        p_ussgl_transaction_code         => p_ussgl_transaction_code,
                                        p_attribute_rec                  => p_attribute_rec,
                                        p_global_attribute_rec           => p_global_attribute_rec,
                                        p_comments                       => p_comments,
                                        p_application_ref_type           => p_application_ref_type,
                                        p_application_ref_id             => p_application_ref_id,
                                        p_application_ref_num            => p_application_ref_num,
                                        p_secondary_application_ref_id   => p_secondary_application_ref_id,
                                        p_payment_set_id                 => p_payment_set_id,
                                        p_receivable_application_id      => p_receivable_application_id,
                                        p_customer_reference             => p_customer_reference,
                                        p_val_writeoff_limits_flag       => p_val_writeoff_limits_flag,
                                        p_called_from                    => p_called_from,
                                        p_netted_receipt_flag            => p_netted_receipt_flag,
                                        p_netted_cash_receipt_id         => p_netted_cash_receipt_id,
                                        p_secondary_app_ref_type         => p_secondary_app_ref_type,
                                        p_secondary_app_ref_num          => p_secondary_app_ref_num);
        
        dbms_output.put_line ('Return status from API is: '||x_return_status);
        dbms_output.put_line ('Return= '||SUBSTR(x_msg_data,500));
        dbms_output.put_line ('Receivable Application ID is: '||p_receivable_application_id);    
    END LOOP; 

COMMIT; 

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;

And thus, happy Adjusting Write-offs.

Leave a Reply

  • (will not be published)