Script to create a Debit Memo (AR)

The following script can be used to create a Basic Debit memo. The API that is used to create an AR Invoice is – “ar_invoice_api_pub.create_invoice“. The Cust_trx_type_id decides whether the invoice getting created is a Standard Invoice or a Credit Memo or a Debit memo. Based on this value, the API also checks the sign of the amount on the Line level that is being passed.

--Script to create Debit memo.
 
DECLARE
    l_index                  NUMBER := 0;
    l_line_index             NUMBER := 0;
    l_dist_index             NUMBER := 0;
    l_cm_customer_trx_id     NUMBER := 0;
    --
    l_batch_source_rec       ar_invoice_api_pub.batch_source_rec_type;
    l_trx_header_tbl         ar_invoice_api_pub.trx_header_tbl_type;
    l_trx_lines_tbl          ar_invoice_api_pub.trx_line_tbl_type;
    l_trx_line_id            NUMBER;
    l_return_status          VARCHAR2 (1);
    l_trx_dist_tbl           ar_invoice_api_pub.trx_dist_tbl_type;
    l_trx_salescredits_tbl   ar_invoice_api_pub.trx_salescredits_tbl_type;
    l_msg_data               VARCHAR2 (2000);
    l_resp_id                NUMBER;
    l_appl_id                NUMBER;
    l_user_name              VARCHAR2 (200);
    g_user_id                fnd_user.user_id%TYPE;
    l_line_number            NUMBER := 0;
    l_msg_count              NUMBER;
    l_ou_id                  hr_operating_units.organization_id%TYPE;
    l_batch_source_id        ra_batch_sources_all.batch_source_id%TYPE;
    l_cust_trx_type_id       ra_cust_trx_types_all.cust_trx_type_id%TYPE;
    l_cust_account_id        hz_cust_accounts.cust_account_id%TYPE;
    l_code_combination_id    gl_code_combinations_kfv.code_combination_id%TYPE; 
BEGIN
    DBMS_OUTPUT.put_line ('Start processing...');
 
    l_user_name := 'Phani Adivi'; --- enter your name here
 
    -- 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 Operating Unit ID
    BEGIN
        SELECT organization_id
          INTO l_ou_id
          FROM hr_operating_units
         WHERE name = 'enter OU Name';    --- enter the OU name in which this invoice is being created in
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error during deriving Operating Unit ID: ' || SQLERRM);
    END;
 
    --Get Batch Source ID
    BEGIN
        SELECT batch_source_id
          INTO l_batch_source_id
          FROM ra_batch_sources_all
         WHERE name = 'Source Name';   -- enter the Source name
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error while deriving Batch Source ID: ' || SQLERRM);
    END;
 
    -- Get Customer Trx Type ID
    BEGIN
        SELECT cust_trx_type_id
          INTO l_cust_trx_type_id
          FROM ra_cust_trx_types_all
         WHERE name = 'Debit Memo' 
           AND org_id = l_ou_id;
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error during deriving Customer Transaction Type and error is: '
                || SQLERRM);
    END;
 
    -- Get Customer Account ID
    BEGIN
        SELECT cust_account_id
          INTO l_cust_account_id
          FROM hz_cust_accounts s
         WHERE account_name = 'Account Name';   -- enter the Account name of the Customer for which this invoice is being created for
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                   'Error during deriving Customer Account ID and error is: '
                || SQLERRM);
    END;
    
    --Get Code Combination Id
    BEGIN
        SELECT code_combination_id
          INTO l_code_combination_id
          FROM gl_code_combinations_kfv
         WHERE concatenated_segments = 'concatenated_string';     -- get the concatenated segment information - this will be shown on the distributions screen
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line (
                'Error during deriving the code combination id: ' || 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);
 
    -- Prepare Credit Memo;
    l_batch_source_rec.batch_source_id := l_batch_source_id;
    l_cm_customer_trx_id := ra_customer_trx_s.NEXTVAL;
    l_index := l_index + 1;
    --
    l_trx_header_tbl (l_index).org_id := l_ou_id;
    l_trx_header_tbl (l_index).trx_header_id := l_cm_customer_trx_id;
    --l_trx_header_tbl (l_index).trx_number := 2120191;               -- Enter the transaction number if the automatic numbering is not enabled
    l_trx_header_tbl (l_index).trx_date := SYSDATE; --TO_DATE('30-NOV-2018', 'DD-MON-RRRR');
    l_trx_header_tbl (l_index).gl_date :=  TO_DATE ('30-NOV-2018', 'DD-MON-RRRR');
    l_trx_header_tbl (l_index).trx_currency := 'USD';
    l_trx_header_tbl (l_index).cust_trx_type_id := l_cust_trx_type_id;
    l_trx_header_tbl (l_index).bill_to_customer_id := l_cust_account_id;
    l_trx_header_tbl (l_index).printing_option := 'NOT';
    l_trx_header_tbl (l_index).reference_number := 21220191;
 
    -- Prepare Credit Memo Lines
    l_trx_line_id := ra_customer_trx_lines_s.NEXTVAL;
    l_line_index := l_line_index + 1;
    l_line_number := l_line_number + 1;
    l_dist_index := l_dist_index + 1;
    --
    l_trx_lines_tbl (l_line_index).trx_header_id := l_cm_customer_trx_id;
    l_trx_lines_tbl (l_line_index).trx_line_id := l_trx_line_id;
    l_trx_lines_tbl (l_line_index).line_number := l_line_number;
    l_trx_lines_tbl (l_line_index).quantity_invoiced := 1;
    l_trx_lines_tbl (l_line_index).unit_selling_price := 12;
    l_trx_lines_tbl (l_line_index).line_type := 'LINE';
    l_trx_lines_tbl (l_line_index).taxable_flag := 'N';
 
    --
    l_trx_dist_tbl (l_dist_index).trx_dist_id   := RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL;
    l_trx_dist_tbl (l_dist_index).trx_line_id   := l_trx_line_id;
    l_trx_dist_tbl (l_dist_index).ACCOUNT_CLASS := 'REV';
    l_trx_dist_tbl (l_dist_index).percent := 100;
    l_trx_dist_tbl (l_dist_index).code_combination_id := l_code_combination_id;
 
    l_return_status := NULL;
    
    --Standard API call
    ar_invoice_api_pub.create_invoice (
        p_api_version            => 1.0,
        p_commit                 => fnd_api.g_false,
        p_batch_source_rec       => l_batch_source_rec,
        p_trx_header_tbl         => l_trx_header_tbl,
        p_trx_lines_tbl          => l_trx_lines_tbl,
        p_trx_dist_tbl           => l_trx_dist_tbl,
        p_trx_salescredits_tbl   => l_trx_salescredits_tbl,
        x_return_status          => l_return_status,
        x_msg_count              => l_msg_count,
        x_msg_data               => l_msg_data);
 
    DBMS_OUTPUT.put_line ('l_return_status is: ' || l_return_status);
 
    FOR cr_rec
        IN (SELECT trx_header_id,
                   trx_line_id,
                   error_message,
                   invalid_value
              FROM ar_trx_errors_gt)
    LOOP
        l_msg_data := NULL;
        l_msg_data :=
               'API Create CM/DM:'
            || cr_rec.trx_header_id
            || '-'
            || cr_rec.trx_line_id
            || '-'
            || cr_rec.error_message;
        DBMS_OUTPUT.put_line ('Message is: ' || l_msg_data);
    END LOOP;
 
    --
    COMMIT;
END;

Leave a Reply

  • (will not be published)