SQL & PL/SQL

Anonymous Block:

DECLARE
    l_return_status              VARCHAR2 (1);
    l_msg_count                  NUMBER;
    l_msg_data                   VARCHAR2 (240);
    l_ErrMsg                     VARCHAR2 (2400);
    lv_sqlerrm                   VARCHAR2 (2400);
    W_Message                    VARCHAR2 (2400);
    W_status                     VARCHAR2 (2400);
    l_new_trx_number             ra_customer_trx.trx_number%TYPE;
    l_new_customer_trx_id        ra_customer_trx.customer_trx_id%TYPE;
    l_new_customer_trx_line_id   ra_customer_trx_lines.customer_trx_line_id%TYPE;
    l_new_rowid                  VARCHAR2 (240);
    l_new_status                 VARCHAR2 (240);
    W_TRX_Number                 VARCHAR2 (240);
    LN_MSG_INDEX_OUT             VARCHAR2 (240);
BEGIN
    /*------------------------------+
    | Setting global initialization |
    +-------------------------------*/
    BEGIN
            FND_GLOBAL.apps_initialize (2605, 52695, 222);
--            MO_GLOBAL.init('S');
            mo_global.set_policy_context ('S', 2157);
    END;

    ar_deposit_api_pub.CREATE_DEPOSIT (
        p_api_version                => 1.0,
        p_init_msg_list              => FND_API.G_TRUE,
        p_deposit_date               => TRUNC (SYSDATE),
        p_currency_code              => 'AED',
        p_amount                     => 120,
        p_term_id                    => 5,
        p_cust_trx_type_id           => 9507,
--        p_org_id => 2157,
        p_bill_to_customer_id        => 9745106,
        p_description                => 'Generic Commitment',
        p_batch_source_id            => -1,
        p_class                      => 'DEP',
        p_start_date_commitment      => SYSDATE,
        p_commit                     => FND_API.G_TRUE,
        X_new_trx_number             => l_new_trx_number,
        X_new_customer_trx_id        => l_new_customer_trx_id,
        X_new_customer_trx_line_id   => l_new_customer_trx_line_id,
        X_new_rowid                  => l_new_rowid,
        x_return_status              => l_return_status,
        x_msg_count                  => l_msg_count,
        x_msg_data                   => l_msg_data);

    IF l_new_customer_trx_id > 0
    THEN
        BEGIN
            SELECT TRX_NUMBER
              INTO W_TRX_Number
              FROM ra_customer_trx
             WHERE customer_trx_id = l_new_customer_trx_id;
        END;

        DBMS_OUTPUT.put_line (
            'Generated Oracle Txn Number-->' || W_TRX_Number);
        W_Status := 'S';
        W_Message := 'Invoice is Created';
        DBMS_OUTPUT.put_line (W_Status);
    END IF;

    IF    l_return_status = fnd_api.g_ret_sts_error
       OR l_return_status = fnd_api.g_ret_sts_unexp_error
    THEN
        DBMS_OUTPUT.put_line ('FND_MSG_PUB.Count_Msg ');
        DBMS_OUTPUT.put_line (l_return_status || ':' || SQLERRM);

        FOR l_msg_count IN 1 .. FND_MSG_PUB.Count_Msg
        LOOP
            FND_MSG_PUB.Get (p_msg_index       => l_msg_count,
                             p_encoded         => 'F',
                             p_data            => l_msg_data,
                             p_msg_index_OUT   => ln_msg_index_out);
            lv_sqlerrm :=
                SUBSTR (lv_sqlerrm || REPLACE (l_msg_data, CHR (10), ''),
                        1,
                        3998);
            W_Status := 'E';
            W_Message := lv_sqlerrm;
            DBMS_OUTPUT.put_line (l_return_status || ':' || lv_sqlerrm);
        END LOOP;
    --    FND_MSG_PUB.Delete_Msg;
    END IF;

    DBMS_OUTPUT.put_line (
           'W_Status-'
        || W_Status
        || '-W_Message-'
        || W_Message
        || '-W_TRX_Number-'
        || W_TRX_Number);
EXCEPTION
    WHEN OTHERS
    THEN
        W_Status := 'E';
        W_Message := lv_sqlerrm;
END;

--COMMIT;

Output:

Generated Oracle Txn Number-->11086
S
W_Status-S-W_Message-Invoice is Created-W_TRX_Number-11086

The same block created as procedure

Create procedure p_test()
as
begin
--Anonymous Block code
end;

Executing:

begin
p_test();
end;

Output:

W_Status-E The header was not successfully inserted for the deposit ORA-29471: DBMS_SQL access denied-W_TRX_Number-

Please suggest.

Regards,

Afzal.