Posts By: Phani Adivi


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Modify default browser to Chrome from Internet Explorer

Upon clicking the Launch Button for Jupyter Notebook on the Anaconda Navigator, the default browser in the system opens up. Usually, if the default browser is not Chrome/Mozilla, most of the corporate computers have Internet Explorer as the default browser.

Clicking jupyter Notebook Launch Button on Anaconda Navigator

In order to change the default browser from IE to Chrome, especially when you don’t have Sysadmin rights on the computer, go through the follow process.

  • Search for Anaconda Command on your computer.
  • In the command prompt, enter the following command: jupyter notebook –generate-config
  • Upon hitting enter, it gives the directory of the jupyter_notebook_config.py file.
  • Navigate to that location and open the file in notepad or notepad++
  • Search for c.NotebookApp.browser = ''
  • Since the command is commented, the application picks the default Internet explorer.
  • Change it so that it points to Chrome exe file. c.NotebookApp.browser = 'C:/Program Files (x86)/Google/Chrome/Application/chrome.exe %s'
  • Save and close the file.

Click the Launch button on the Anaconda Navigator, and it should open in Chrome now.


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

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;
/


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

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.


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Receipt Write-Off (AR) – Error Messages

There are a couple of steps to follow before doing any kind of Adjustment on AR Transactions.

  • In order to make any adjustments like a Receipt Write-Off – Approval Limits have to be defined for the user who is doing the Adjustment. If the Approval Limit is not set, then the Receivables window throws an error to set up the Approval Limit with the error Message: “APP-AR-96983: User Write-off limit does not exist.

In order to correct the above message, perform the following actions: Navigate to the following – Receivables Super User -> Setup -> Transactions -> Approval Limits. In the Approval Limits window – create a record. There exists one record with the combination of a User, Adjustment Type, Currency in the Approval Limits window. Enter a record for the user as below.

  • And there is a system profile option that needs to be setup before any adjustment is done on a receipt. Without setting that option first, the user may face the following error message – “APP-AR-96981: Please set the receipt write-off limits range system option.

In order to correct the above messages, perform the following actions: Navigate to Receivables Super User -> Setup -> System -> System Options -> Miscellaneous Tab. For the Write-off Limits Per Receipt – enter the values as required in the company. After entering the values – the screen looks like below.

These above two steps needs to be completed before creating an adjustment on an AR Receipt like a Receipt Write-Off.


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

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;


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Script to Create a Credit Memo (AR)

The following script can be used to create a Basic Credit 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 Credit 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 = 'Credit 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;


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Query to derive the 4 C’s

The following query can be used to derive the details of the 4 C’s – that is: that have been setup for a particular Ledger.

  • Chart of Accounts
  • Currency
  • Calendar
  • Accounting Convention

SELECT gll.name                       "Legal Entity Name",
       gll.short_name                 "Short Code",
       gll.ledger_category_code       "Primary/Secondary Ledger",
       gll.chart_of_accounts_id,
       -- 4 C's details below
       fifs.id_flex_structure_name    "Chart of Accounts Name",
       gll.period_set_name            "Calendar",
       gll.currency_code              "Currency",
       gll.sla_accounting_method_code "Subledger Accounting"
  FROM gl_ledgers gll, fnd_id_flex_structures_tl fifs
 WHERE     gll.chart_of_accounts_id = fifs.id_flex_num
       AND fifs.id_flex_code = 'GL#'
       AND fifs.language = 'US'
       AND gll.name = p_legal_entity_name; -- Enter Legal Entity name here


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Oracle Payables Query

Query to identify the various components that a Payables Invoice goes through a P2P process. Below query provides the details like Invoice, Supplier, Supplier Sites, Payment Method codes, terms, Distribution details, Payment schedules, Check details, Internal Bank Payment and Internal Bank details.

SELECT aia.invoice_num                 "Invoice Number",
       aps.vendor_name                 "Vendor Name" -- get vendor name from ap_suppliers table
                                                    ,
       assa.vendor_site_code           "Vendor Site Code",
       aia.invoice_id                  "Invoice ID",
       aia.invoice_currency_code       "Currency Code",
       aia.invoice_amount              "Invoice Amount",
       aia.amount_paid                 "Amount Paid",
       aia.invoice_type_lookup_code    "Invoice Type", -- values are derived from ap_lookup_codes and lookup_type = 'INVOICE TYPE'.
                                                       --STANDARD, CREDIT, DEBIT, EXPENSE REPORT, PREPAYMENT, MIXED, RETAINAGE RELEASE are other Invoice Types
       aia.description,
       aia.payment_method_lookup_code, -- values are derived from ap_lookup_codes table and lookup_type = 'PAYMENT METHOD'
                                       -- Check, Clearing, Electronic, Wire
       aia.terms_id                    "Terms ID", -- get terms name from ap_terms table
       aia.pay_group_lookup_code,                  -- values are derived from the fnd_lookup_values_vl and lookup_type = 'PAY GROUP'
       aia.org_id                      "Operating Unit ID", -- values are derived from hr_operating_units table - organization_id column
       aia.gl_date                     "GL Date",
       aia.wfapproval_status,
       ail.line_number                 "Line Number",
       ail.line_type_lookup_code       "Line Type", -- values are derived from ap_lookup_codes and lookup_type = 'INVOICE LINE TYPE'
                                                    -- Item, Freigh, Miscellaneous, Tax
       ail.amount                      "Line Amount",
       aid.dist_code_combination_id    "Distribution Code Comb ID", -- segment information can be derived from gl_code_combinations_kfv
       aid.accounting_event_id         "Invoice Accounting Event ID", -- will be used to link to SLA tables
       apsa.amount_remaining           "Remaining Invoice Amount",
       apsa.due_date                   "Due Date",
       aipa.accounting_event_id        "Payment Accounting Event ID",
       aca.amount                      "Check Amount",
       aca.check_number                "Check Number",
       aca.checkrun_name               "Payment Process Request",
       idpa.document_amount            "Payment Amount",
       idpa.pay_proc_trxn_type_code    "Payment Processing Document",
       idpa.calling_app_doc_ref_number "Invoice Number",
       ipa.paper_document_number       "Payment Number",
       ipa.payee_name                  "Paid to Name",
       ipa.payee_address1              "Paid to Address",
       ipa.payee_city                  "Paid to City",
       ipa.payee_postal_code           "Payee Postal Code",
       ipa.payee_state                 "Payee State",
       ipa.payee_country               "Payee Country",
       ipa.payment_profile_acct_name   "Payment Process Profile",
       ipa.int_bank_name               "Payee Bank Name",
       ipa.int_bank_number             "Payee Bank Number",
       ipa.int_bank_account_name       "Payee Bank Account Name",
       ipa.int_bank_account_number     "Payee Bank Account Number"
  FROM ap_invoices_all               aia,
       ap_invoice_lines_all          ail,
       ap_invoice_distributions_all  aid,
       ap_suppliers                  aps,
       ap_supplier_sites_all         assa,
       ap_payment_schedules_all      apsa,
       ap_invoice_payments_all       aipa,
       ap_checks_all                 aca,
       iby_docs_payable_all          idpa,
       iby_payments_all              ipa
 WHERE     1 = 1
       AND aia.invoice_id = ail.invoice_id
       AND aia.invoice_id = aid.invoice_id
       AND aia.vendor_id = aps.vendor_id
       AND aps.vendor_id = assa.vendor_id
       AND aia.invoice_id = apsa.invoice_id
       AND aia.invoice_id = aipa.invoice_id
       AND aipa.check_id = aca.check_id
       AND aia.invoice_id = idpa.calling_app_doc_unique_ref2
       AND idpa.calling_app_id = 200
       AND aps.party_id = idpa.payee_party_id
       AND ipa.payment_id = idpa.payment_id
       AND aps.segment1 = ipa.payee_supplier_number
       -- and assa.vendor_site_id = ipa.supplier_site_id
       AND assa.org_id = aia.org_id
       AND aca.vendor_site_id = assa.vendor_site_id
       AND invoice_num = p_invoice_num; -- Enter Invoice number here. 


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Banks in Oracle R12

Understanding Banks in Oracle R12

Bank Details such as Bank Account Name, Bank Account Number etc. are stored in multiple tables in Oracle Apps R12 environment. It may be confusing to see that sometimes the same information such as bank account name is stored in different tables. Here, we shall learn the differences between those tables and their uses.

There are two types of Banks and related bank accounts in R12.

  1. Internal
  2. External

The basic difference between an Internal bank/bank account and an External bank/bank account is that an internal bank account is an account that is created from the Cash Management module and the External Bank/bank accounts are the ones that are created from the sub-ledger modules such as the Payables or Receivables. Bank Accounts related to Suppliers or Customers are the externals bank accounts.

The Internal bank accounts and its details are stored in the CE tables like “CE_BANK_ACCOUNTS” and the external bank accounts and its details are stored in IBY tables like “IBY_EXT_BANK_ACCOUNTS”.

Internal banks and Bank Accounts – Technical details:

Steps to create a Bank, Bank Branch and a Bank Account

Navigate to Cash Management Super User -> Setup -> Banks -> Banks. Click Create Button to create a new bank.

Create Bank:

Create Bank Address: In the Step 2 – Click on Create button to create Bank Address and click Apply.

Create Contact: On step 3 – click on Create button to create a contact for the bank.

 That’s it – A new Bank with the name Test Bank 4 is created.

Creating a Bank Branch

Navigate to Cash Management Super User -> Setup -> Banks -> Banks – Click on Bank Branches and click Create Button.

First the following oracle page asks to enter the bank name for which the branch is being created for. And Click continue. We get two options here – either to “Create a new Branch” or to “Select an Existing party”. Let’s create a new branch.

Create Branch Address

Create a Contact for the Bank Branch

A bank branch is also created.

Now that a Bank, its Bank Branch, Branch Address and a Contact point is created, lets create a Bank Account and Assign it to the Bank that was created earlier.

Go to the navigation – Cash Management Super User -> Setup -> Banks -> Bank Accounts -> Click Create. The following oracle page asks for the Country, Bank name and Branch name for which the account is being created for. Select the Bank and Branch Name that we created before.

Identify the Bank Account Owner – the corresponding Legal Entity identifier and the Uses of the Bank Account. For which the Bank account will be used for:

Create Bank Account information like Bank Account Name, Account Number and Currency code.

Create the Bank Account Controls that has General control information like Cash Account, Bank Charges, Realized gain, Foreign Exchange charges, Cash Clearing account, Bank Errors, Realized Loss account etc. Click Save and Next and Click Finish.

Add Organization Access – in order to Assign a bank account to an operating unit.

Technical information about Banks and Bank Branches for CE (Cash Management)

SELECT *
  FROM CEFV_BANK_BRANCHES
 WHERE bank_name = 'Test Bank 4';
SELECT *
  FROM ce_bank_accounts
 WHERE bank_account_name = 'Test Bank Account 4';
SELECT org_id              "Operating Unit ID",
       ap_use_enable_flag  "Bank Acc used for Payables",
       ar_use_enable_flag  "Bank Acc used for Receivables",
       xtr_use_enable_flag "Bank Acc used for Treasury",
       PAY_USE_ENABLE_FLAG "Bank Acc used for Payroll",
       bank_account_id,
       bank_acct_use_id,
       c.*
  FROM ce_bank_acct_uses_all c
 WHERE bank_account_id = (SELECT bank_account_id
                            FROM ce_bank_accounts
                           WHERE bank_account_name = 'Test Bank Account 4');
SELECT hou.name                  "Operating Unit Name",
       xep.name                  "Legal Entity Name",
       cebb.bank_name,
       cebb.bank_number,
       cebb.bank_branch_name,
       cebb.bank_branch_number,
       cebb.bank_branch_type, -- Branch type indicates which list the bank routing number is on. Valid types are ABA, CHIPS, SWIFT, OTHER.
       cebb.address_line1,    -- Branch details - Branch Address details below
       cebb.address_line2,
       cebb.address_line3,
       cebb.address_line4,
       cebb.city,                                               -- Branch City
       cebb.state,                                             -- Branch State
       cebb.zip_code,
       cebb.country,
       --ceba.bank_account_number,
       ceba.asset_code_combination_id,
       glcc.concatenated_segments,
       cebau.ap_use_enable_flag  "Bank Acc used for Payables",
       cebau.ar_use_enable_flag  "Bank Acc used for Receivables",
       cebau.xtr_use_enable_flag "Bank Acc used for Treasury",
       cebau.PAY_USE_ENABLE_FLAG "Bank Acc used for Payroll"
  FROM cefv_bank_branches        cebb,
       ce_bank_accounts          ceba,
       ce_bank_acct_uses_all     cebau,
       gl_code_combinations_kfv  glcc,
       hr_operating_units        hou,
       xle_entity_profiles       xep
 WHERE     1 = 1
       AND cebb.bank_branch_id = ceba.bank_branch_id
       AND ceba.asset_code_combination_id = glcc.code_combination_id
       AND ceba.bank_account_id = cebau.bank_account_id
       AND cebau.org_id = hou.organization_id
       AND ceba.account_owner_org_id = xep.legal_entity_id
       AND bank_name = 'Test Bank 4';


Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/wp-content/themes/WpPremium/author.php on line 78

Procure to Pay (P2P)

In this article, we shall try to understand the end to end process involved in the P2P process from a functional as well a technical point of view. We shall look at the navigation required during each step and the tables that get affected during that process.

High level Scenario: Consider a car company that buys tires from a third party supplier. And when the Supplier sends in those tires, the company fixes the tires to the cars in their assembly lines and continue their manufacturing process of building the cars. In this scenario, let us suppose the number of tires that this car company has, has fallen below a count of 100. That’s when the company’s procurement team requests the third party seller to replenish the tires inventory by sending out a new batch of tires.

In the above mentioned scenario, from an ERP perspective, the tires count is maintained in Inventory module. Suppliers are maintained in the Accounts Payables (AP) module. Procurement for new tires is made and requested from the Procurement module. And when the Invoice is paid – the Payment accounting is created as journals and printed as reports in the General Ledger module.

Thus, a P2P process flows through the following steps:

  • Create / Raise requirement for the item.
  • Create Requisition
  • Request for Quotation
  • Create Quotation
  • Quotation Analysis
  • Create Purchase Order
  • Create Invoice
  • Create Accounting (for Invoice)
    • Interface to GL
    • Journal Import and Create Journals
    • Post the Journals
  • Create Payment
  • Create Accounting (for Payment)
    • Interface to GL
    • Journal Import and Create Journals
    • Post the Journals

Step 1: Requesting for Item

Create an item – VI50000.

In the above image, we can see that for this item – in the General Planning tab –

  • Inventory Planning Method is defined as Min-Max.
  • Min-Max Quantity section the Minimum quantity is 100 and Maximum quantity is 1000.
  • Make or Buy is – Buy
  • And the Source Type is – Supplier.

That means, whenever the on-hand quantity of that item falls below a minimum of 100, then the Min-Max Planning report automatically replenishes the quantity of the item upto 1000. The tables affected during this step are:

Query to item details from the Item Master table:
SELECT *
   FROM mtl_system_items_b
  WHERE segment1 = 'VI50000';
Query to find out the onhand quantity. 
SELECT *
  FROM mtl_onhand_quantities_detail
 WHERE inventory_item_id IN (SELECT inventory_item_id
                               FROM mtl_system_items_b
                              WHERE segment1 = 'VI50000');
SELECT *
  FROM mtl_onhand_quantities
 WHERE inventory_item_id IN (SELECT inventory_item_id
                               FROM mtl_system_items_b
                              WHERE segment1 = 'VI50000');

Step 2: Now, run the Min-Max Planning Report.

Inventory Super User -> Planning -> Min-Max Planning Report

Min-Max Planning Report Navigation.

Step 3: Upon Running this report – the process of creating a requisition starts. Once this Min-Max Planning report is completed, a record is inserted into the Po Requisitions Interface table. A record is inserted into the PO_REQUISITIONS_INTERFACE_ALL table.

--The record in this table can be identified using the item_id of the item that we are procuring. 

SELECT *
  FROM PO_REQUISITIONS_INTERFACE_ALL
 WHERE ITEM_ID IN (SELECT inventory_item_id
                               FROM mtl_system_items_b
                              WHERE segment1 = 'VI50000');

The PO_REQUISITIONS_INTERFACE_ALL table contains requisition information from other applications. Each row includes all the information necessary to create approved or unapproved requisitions in Oracle Purchasing. The Oracle Purchasing Requisition Import feature uses this information to create new requisition headers, lines and distributions.

Step 4 –  Create Requisition: Run the “REQUISITION IMPORT” concurrent program to import the requisition from the interface table to the base tables, thereby creating the Requisition in Oracle.

Requisition Import Concurrent Program

The Requisition Import Concurrent Program validates the data in the PO_REQUISITIONS_INTERFACE_ALL table and derives additional information as required. Upon completion, data is loaded into the PO Requisition Base Tables. The base tables are:

  • PO_REQUISITION_HEADERS_ALL
  • PO_REQUISITION_LINES_ALL
  • PO_REQ_DISTRIBUTIONS_ALL

In order to identify the record in these base table – get the inventory_item_id of the above item

SELECT requisition_header_id
  FROM PO_REQUISITION_LINES_ALL
 WHERE item_id IN (SELECT inventory_item_id
                     FROM mtl_system_items_b
                    WHERE segment1 = 'VI50000')
and org_id = p_org_id;                      -- get the requisition_header_id and requisition_line_id

SELECT segment1  "REQUISITION NUMBER"
  FROM PO_REQUISITION_HEADERS_ALL
 WHERE requisition_header_id = p_req_header_id 
   AND org_id = p_org_id;                                 -- use the requisition_header_id here

SELECT code_combination_id
  FROM PO_REQ_DISTRIBUTIONS_ALL
 WHERE REQUISITION_LINE_ID = p_req_line_id;               -- use the requisition_line_id here

PO_REQ_DISTRIBUTIONS_ALL table contains the information about the accounting distributions associated with each requisition line. Each
requisition line must have at least one accounting distribution.

From the above query – get the code_combination_id value – that can used to get the accounting string.

SELECT *
  FROM GL_CODE_COMBINATIONS_KFV
 WHERE code_combination_id = p_code_combination_id       -- get the accounting string. 

SELECT *
  FROM GL_CODE_COMBINATIONS
 WHERE code_combination_id = p_code_combination_id

Approved Requisitions can be accessed from – PO Super User -> Requisitions -> Requisition Summary.

Requisition Headers
Requisition Lines
Req Distribution – Charge Account (code combination)

If there are any errors then, then error records are written into PO_INTERFACE_ERRORS table.

The join is – PO_REQUISITIONS_INTERFACE_ALL.transaction_id = PO_INTERFACE_ERRORS.interface_transaction_id

STEP5 – REQUEST FOR QUOTATION – RFQ

Once the Requisition is created, now we have to Create RFQ – that is Request for Quotation – asking the Suppliers to provide their Quotation. Once we receive their quotations, we can decide which quote from the Suppliers is best for our requirement.

Navigation – Purchasing Super User -> RFQ’s and Quotations -> RFQ’s

Create an RFQ by selecting the following fields:

  • Type – Standard RFQ (the other two types are Bid RFQ, Catalog RFQ).
  • Status – Active
  • Close date – provide a date until when this RFQ is valid
  • Item – Item name
  • Target Price – provide a target price
  • Currency – Provide the currency details.
  • Terms – Payment Terms, Freight Terms, FOB.
  • Price Break – In the Price Break – you can provide the quantity and a discount.

And click on Save. Tables that get affected when Request for Quotation is created are:

SELECT *
  FROM po_headers_all
 WHERE segment1 = '1011'
   AND org_id = p_org_id 
   AND type_lookup_code = 'RFQ';

SELECT *
  FROM PO_LINES_ALL
 WHERE po_header_id = p_header_id;           -- use po_header_id from above

Click on Suppliers tab on the RFQ window to assign the RFQ to that particular Supplier. Here you can see that this RFQ has been assigned to a couple of Suppliers.

This information is stored in PO_RFQ_VENDORS table. This table stores the information about the set of suppliers assigned to an RFQ. Each row in this table identifies the Supplier who received this RFQ. In the above example, we would get 2 records.

SELECT *
  FROM po_rfq_vendors
 WHERE po_header_id = p_header_id;    - get the po_header_id from above query 

Once the RFQ is completed – run the PRINTED RFQ Report concurrent program that will send the RFQ details to the supplier via email / fax or other methods that have been entered. Once the below program completes, the status on the RFQ changes from ACTIVE to PRINTED.

Step 6 – Quotations

Now that an RFQ is created and sent out to Suppliers, the Suppliers send us their respective Quotes back. We select the required quote and create it in Oracle for record purpose. A Quote can be created from the RFQ screen itself. Open the RFQ -> Tools – Copy Document. Give the following values:

  • Action – Entire RFQ
  • Type – Standard Quotation
  • Supplier – Select one of the two suppliers that we entered above.
  • Site – Auto populates.

Once OK is pressed, a note appears that “A new Quotation has been copied from this RFQ. The Quotation document number is – ..

As can be seen above – a Quotation is created for the corresponding RFQ. And the tables affected are:

SELECT *
  FROM PO_HEADERS_ALL
 WHERE segment1 = '1013'      --- quotation number
   AND org_id = p_org_id 
   AND type_lookup_code = 'QUOTATION'

SELECT *
  FROM PO_LINES_ALL
 WHERE po_header_id = p_header_id;    -- po_header_id from above

SELECT *
  FROM PO_LINE_LOCATIONS_ALL
 WHERE     po_header_id = p_header_id
       AND po_line_id = p_line_id
       AND org_id = p_org_id;

Click on Approve Button.

Step 7 – Quote Analysis

In this step – the Quote that was approved earlier is Analyzed and is approved. Navigation: Purchasing Super User -> RFQ’s and Quotations -> Quote Analysis -> Enter the RFQ Number. Click on Approve Entire Quotation

Step 8 – Create Purchase Order (PO)

Now that the Quote analysis is completed – we are ready to create a Purchase Order. Navigation – Purchasing Super User -> Purchase Orders -> Purchase Orders.

Enter the Supplier Name, Supplier Site, Bill-To, Ship-To, enter the Item information. As soon as we enter the quantity, the price changes to the discounted price on the quotation.

Click on Terms – and we have information about Payment Terms (30 Net or Immediate), Freight (Due), Carrier, FOB, Supplier Note, Receiver Note etc.

Click on Shipments – and we get information about the Ship-To organization Name, UOM, Quantity, Promised Date, Need By Date, Charge Account and Amount.

Click on Shipments – and More – we get information on how the Match Approval Level is defined. Whether it is a 2-way or 3-way or 4-way match approval. And Invoice Match Option – Receipt / PO.

Once the PO Is approved, we can check the PO details from the backend. The tables that get affected are:

SELECT *
  FROM PO_HEADERS_ALL
 WHERE segment1 = '10428'
   AND org_id = p_org_id;                -- get po_header_id

SELECT *
  FROM PO_LINES_ALL
 WHERE po_header_id = p_header_id;       -- get po_line_id

SELECT *
  FROM PO_LINE_LOCATIONS_ALL
 WHERE     po_header_id = p_header_id
       AND po_line_id = p_line_id
       AND org_id = p_org_id;             -- get line_location_id

SELECT *
  FROM PO_DISTRIBUTIONS_ALL
 WHERE     po_header_id = p_header_id
       AND po_line_id = p_line_id
       AND line_location_id = p_line_location_id
       AND org_id = p_org_id;

Step 9 – Receiving

Once the PO Is created, the Supplier sends the items to the Ship-To organization specified. And once we get the goods, we receive them in Oracle too. Navigation – Purchasing Super User -> Receiving -> Receipts. Enter the PO Number and click Find to get the receipt details.

Once it is saved, we receive it in EBS. The tables that get affected are:

SELECT *
  FROM RCV_SHIPMENT_HEADERS
 WHERE receipt_num = '1031' 
   AND ship_to_org_id = p_ship_to_org_id;

SELECT *
  FROM RCV_SHIPMENT_LINES
 WHERE shipment_header_id = p_shipment_header_id;

SELECT *
  FROM RCV_TRANSACTIONS
 WHERE shipment_header_id = p_shipment_header_id;

Step 10 – Create Invoice (Payables Invoice)

Once we received the items, we create a Payables Invoice in order to send it to the Supplier so they can pay out that amount. The Invoice is created for the amount for which the PO was created for. In the PO Number field, enter the PO number that was created earlier. And Distribution Information can be seen by clicking All Distributions.

Invoice Header and Lines

Click on Actions to Validate the Invoice.

After clicking validation – the status on the Invoice changes to “Validated”

Tables Affected after creating the invoice are:

SELECT *
  FROM AP_INVOICES_ALL
 WHERE invoice_num = 'Inv_V1';

SELECT *
  FROM ap_invoice_lines_all
 WHERE invoice_id = p_invoice_id;

SELECT *
  FROM ap_invoice_distributions_all
 WHERE invoice_id = p_invoice_id;

Step 11 – Create Accounting

After the invoice is validate – Click Actions and Click Create Accounting.

When the Create Accounting program is run, it processes the eligible accounting events to create the subledger journal entries. This program:

  1. Validates and creates the journal entries.
  2. Transfers the journal entries in the current batch run to General Ledger and starts the GL posting process.
  3. Generates the Subledger Accounting Program Report, which has the results of the Create Accounting program.

Before running the Create Accounting program, the Accounting_event_id column in the AP_INVOICE_DISTRIBUTIONS_ALL is NULL. After running the program, the field is populated with the value that can be used in the Subledger Accounting (SLA) tables. The tables affected after running the Create Accounting are as below.

SELECT accounting_event_id
  FROM AP_INVOICE_DISTRIBUTIONS_ALL
 WHERE invoice_id = p_invoice_id;          -- get the accounting_event_id

SELECT *
  FROM XLA_AE_HEADERS
 WHERE event_id = p_accounting_event_id;  -- get the ae_header_id 

SELECT *
  FROM XLA_AE_LINES
 WHERE ae_header_id = p_xla_ae_header_id;   

Step 12 – Journal Import

The Journal Import program transfers the data from the GL_INTERFACE table to the GL Base tables. The corresponding JE_Batch_id, JE_Header_ID, JE_LINE_NUM fields are populated. These can be used to check the data in the GL Base tables. The tables getting affected in GL are as below:

-- Get the je_batch_id, je_header_id and je_line_num values from the GL_Interface table. 

SELECT *
  FROM GL_JE_BATCHES
 WHERE je_batch_id = p_je_batch_id;           

SELECT *
  FROM GL_JE_HEADERS
 WHERE je_header_id = p_je_header_id;

SELECT *
  FROM GL_JE_LINES
 WHERE je_header_id = p_je_header_id 
   AND je_line_num = p_je_line_num;

Step 13 – Create Payment

Now that the Invoice was sent out to the Supplier and the Supplier pays the Invoice in full, then we record that payment in the EBS. The Navigation to the payment screen is AP Super User -> Payments -> Entry -> Payments. The other way is to go from the AP Invoice Screen. Go to the Invoice – Click Actions and Click – Pay in Full. The Payment screen opens up.

Invoice Payments screen

And once all the required fields are populated – click on Save. The following tables are populated with the payment data.

SELECT *
  FROM AP_INVOICE_PAYMENTS_ALL
 WHERE invoice_id IN (SELECT invoice_id
                        FROM AP_INVOICES_ALL
                       WHERE invoice_num = 'Inv_V1');

The AP_Invoice_Payment_Schedules_all table holds data about any pending amount on the invoice. It contains information about scheduled payments for an invoice. The AMOUNT_REMAINING columns holds the data for any amount that is pending on the invoice. The PAYMENT_STATUS_FLAG may be ‘Y’ for fully paid payment schedules, ‘N’ for unpaid scheduled payments, or ‘P’ for partially paid scheduled payments.

SELECT *
  FROM ap_payment_schedules_all
 WHERE invoice_id IN (SELECT invoice_id
                        FROM AP_INVOICES_ALL
                       WHERE invoice_num = 'Inv_V1');   -- get Check_id value

The AP_CHECKS_ALL table stores the information about any payments made to the Supplier or any refund provided to the Supplier. There exists a record for each activity in this table. This table stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. This table also stores Bank_Account_name, check_date, Check Number, Currency Code, Address Information etc..

SELECT *
  FROM ap_checks_all
 WHERE check_id = (SELECT check_id
                      FROM ap_payment_schedules_all
                     WHERE invoice_id = (SELECT invoice_id
                                            FROM AP_INVOICES_ALL
                                           WHERE invoice_num = 'Inv_V1'));

Step 14 – Create Accounting (for Payment)

Now that the Payment has been recorded in EBS (AP module), the information has to pass through the Subledger Accounting (SLA) and then to GL. In order for this to happen, on the Payments screen – click Action – and Click – Create Accounting.

Create Accounting screen (for Payments)

Once this activity is completed, the Create Accounting Seeded concurrent program starts and initiates the transfer of data from AP to GL via GL interface. The following tables get affected during this process.

--Get the Accounting_event_id value from the Payments table above. 
SELECT accounting_event_id
  FROM AP_INVOICE_PAYMENTS_ALL
 WHERE invoice_id IN (SELECT invoice_id
                        FROM AP_INVOICES_ALL
                       WHERE invoice_num = 'Inv_V1');

SELECT *
  FROM XLA_EVENTS
 WHERE event_id = p_accounting_event_id;

SELECT *
  FROM XLA_AE_HEADERS
 WHERE event_id = p_accounting_event_id;
 
SELECT *
  FROM XLA_AE_LINES
 WHERE ae_header_id = p_xla_ae_header_id; 

And once the Journal Import completes the journals can be seen from the GL base tables namely GL_JE_BATCHES,
GL_JE_HEADERS , GL_JE_LINES and upon posting the Journals – the GL data can be seen in the GL_Balances table.

That pretty much sums up the Procure to Pay Cycle in Oracle Apps.

Happy coding.