Script to Reverse an AR Receipt

The following script can be used to reverse an AR Receipt.

DECLARE
    v_return_status        VARCHAR2 (1);
    p_count                NUMBER;
    v_msg_count            NUMBER;
    v_msg_data             VARCHAR2 (2000);
    v_cash_receipt_id      NUMBER DEFAULT 12345678;   -- cash receipt id from ar_Cash_Receipts_all table
    v_gl_date              DATE DEFAULT SYSDATE;
    v_reversal_date        DATE DEFAULT SYSDATE;
    v_context              VARCHAR2 (2);
    g_user_id              fnd_user.user_id%TYPE;
    l_resp_id              NUMBER;
    l_resp_name   CONSTANT VARCHAR2 (100) DEFAULT 'AR SUPERUSER';
    l_appl_name   CONSTANT VARCHAR2 (100) DEFAULT 'Receivables';
    l_appl_id              NUMBER;
    x_return_message       VARCHAR2 (4000);
    x_return_status        VARCHAR2 (2000);
    l_ou_name              hr_operating_units.name%TYPE DEFAULT 'USXXOUXX';  -- OU NAME
    l_organization_id      hr_operating_units.organization_id%TYPE;
    g_user_name            VARCHAR2 (200);
BEGIN
    g_user_name := 'XX1234'; -- USER NAME FOR WHO COLUMNS

    BEGIN
        SELECT user_id
          INTO g_user_id
          FROM fnd_user
         WHERE user_name = g_user_name;
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error during deriving user_id: ' || SQLERRM);
    END;

    l_resp_id :=
        xx_fnd_responsibility_pkg.get_responsibility_id (
            p_resp_name   => l_resp_name);

    IF l_resp_id IS NULL
    THEN
        x_return_status := 'E';
        x_return_message :=
            'Responsibility id not found for responsibility ' || l_resp_name;
    ELSE
        l_appl_id :=
            xx_fnd_application_pkg.get_application_id (
                p_appl_name   => l_appl_name);

        IF l_appl_id IS NULL
        THEN
            x_return_status := 'E';
            x_return_message :=
                   'Application id not found for application name '
                || l_appl_name;
            DBMS_OUTPUT.put_line (x_return_message);
        END IF;
    END IF;

    BEGIN
        SELECT organization_id
          INTO l_organization_id
          FROM hr_operating_units
         WHERE name = l_ou_name;
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error during deriving Organization_id: ' || SQLERRM);
    END;

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


    DBMS_OUTPUT.PUT_LINE ('Invoking Receipt reversal api');
    ar_receipt_api_pub.REVERSE (
        p_api_version              => 1.0,
        p_init_msg_list            => FND_API.G_FALSE,
        p_commit                   => FND_API.G_FALSE,
        p_validation_level         => FND_API.G_VALID_LEVEL_FULL,
        x_return_status            => v_return_status,
        x_msg_count                => v_msg_count,
        x_msg_data                 => v_msg_data,
        p_cash_receipt_id          => v_cash_receipt_id,
        p_receipt_number           => NULL,
        p_reversal_category_code   => 'REV',
        p_reversal_category_name   => NULL,
        p_reversal_gl_date         => v_gl_date,
        p_reversal_date            => v_reversal_date,
        p_reversal_reason_code     => 'INCORRECT INVOICE REASON',
        p_reversal_reason_name     => NULL,
        p_reversal_comments        => NULL,
        p_called_from              => NULL,
        p_cancel_claims_flag       => 'Y',
        p_org_id                   => l_organization_id);

    DBMS_OUTPUT.put_line ('Return Status of API is: ' || v_return_status);

    IF v_return_status = 'S'
    THEN
        DBMS_OUTPUT.put_line (
               'Receipt Reversal Successful for Cash Receipt ID: '
            || v_cash_receipt_id);
    ELSE
        DBMS_OUTPUT.put_line ('Message count ' || v_msg_count);

        IF v_msg_count = 1
        THEN
            DBMS_OUTPUT.put_line ('l_msg_data ' || v_msg_data);
        ELSIF v_msg_count > 1
        THEN
            LOOP
                p_count := p_count + 1;
                v_msg_data :=
                    FND_MSG_PUB.Get (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

                IF v_msg_data IS NULL
                THEN
                    EXIT;
                END IF;

                DBMS_OUTPUT.put_line (
                    'Message' || p_count || ' ---' || v_msg_data);
            END LOOP;
        END IF;
    END IF;
END;

 

Leave a Reply

  • (will not be published)