Script to create a Cash Receipt using Oracle API (AR)

The following post can be used to create a Cash receipt in Oracle Receivables. The “ar_receipt_api_pub.create_cash” SQL API is used to create cash receipts for the payment received in the form of a check or cash. Cash receipts can be created as identified (with a customer) or as unidentified (without a customer).

DECLARE
    l_return_status       VARCHAR2 (1);
    l_msg_count           NUMBER;
    l_msg_data            VARCHAR2 (240);
    p_count               NUMBER := 0;
    l_cash_receipt_id     ar_cash_receipts_all.cash_receipt_id%TYPE;
    l_user_name           fnd_user.user_name%TYPE := 'PADIVI';
    g_user_id             fnd_user.user_name%TYPE;
    l_resp_id             fnd_responsibility.responsibility_id%TYPE;
    l_appl_id             fnd_application.application_id%TYPE;
    l_ou_id               hr_operating_units.organization_id%TYPE;
    l_receipt_method_id   ar_receipt_methods.receipt_method_id%TYPE;
BEGIN
    -- Get User ID
    BEGIN
        SELECT user_id
          INTO g_user_id
          FROM fnd_user
         WHERE user_name = l_user_name;
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error during deriving User ID and error is: ' || SQLERRM);
    END;

    --Get Responsibility ID
    BEGIN
        SELECT RESPONSIBILITY_ID
          INTO l_resp_id
          FROM fnd_responsibility
         WHERE responsibility_key = 'RECEIVABLES_MANAGER'; --- enter the responsibility name that has access to create Invoices
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error during deriving Responsibility ID and error is: '
                || SQLERRM);
    END;

    --Get Application ID
    BEGIN
        SELECT application_id
          INTO l_appl_id
          FROM fnd_application
         WHERE application_short_name = 'AR';
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error during deriving application ID and error is: '
                || SQLERRM);
    END;

    -- Get Organization ID for that Operating Unit
    BEGIN
        SELECT organization_id
          INTO l_ou_id
          FROM hr_operating_units
         WHERE name = 'p_ou_name';
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error while deriving Operating Unit ID and error is: '
                || SQLERRM);
    END;

    -- Get Receipt Method Id
    BEGIN
        SELECT RECEIPT_METHOD_ID
          INTO l_receipt_method_id
          FROM AR_RECEIPT_METHODS
         WHERE NAME = 'p_receipt_method'; -- Enter receipt method here.    
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error while deriving Operating Unit ID and error is: '
                || SQLERRM);
    END;

    mo_global.init ('AR');
    mo_global.set_policy_context ('S', l_ou_id);
    fnd_global.apps_initialize (user_id        => g_user_id,
                                resp_id        => l_resp_id,
                                resp_appl_id   => l_appl_id);

    ar_receipt_api_pub.create_cash (
        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       => l_return_status,
        x_msg_count           => l_msg_count,
        x_msg_data            => l_msg_data,
        p_currency_code       => 'USD',
        p_amount              => 105,              -- Pass the Receipt Amount
        p_receipt_number      => 'Test Rcpt 101',  -- Pass the Receipt Number
        p_receipt_date        => SYSDATE,
        p_gl_date             => '30-NOV-2018',  
        p_customer_number     => 'p_customer_number',    -- Account number of the Customer against whom the Receipt is being created for. Hz_cust_accounts.account_number
        p_receipt_method_id   => l_receipt_method_id,
        p_cr_id               => l_cash_receipt_id);

    DBMS_OUTPUT.put_line (
           'API Return Status is - '
        || l_return_status
        || ' and l_msg_count is - '
        || l_msg_count);
    DBMS_OUTPUT.put_line ('Cash Receipt id is - ' || l_cash_receipt_id);


    IF l_msg_count = 1
    THEN
        DBMS_OUTPUT.put_line ('Error Message is - ' || l_msg_data);
    ELSIF l_msg_count > 1
    THEN
        LOOP
            p_count := p_count + 1;
            l_msg_data :=
                fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

            IF l_msg_data IS NULL
            THEN
                EXIT;
            END IF;

            DBMS_OUTPUT.put_line (
                'Message ' || p_count || '. ' || l_msg_data);
        END LOOP;
    END IF;
END;
/

Leave a Reply

  • (will not be published)