Topic: Oracle Products

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

Posted by Phani Adivi on February 5, 2019 in Oracle Home, Receivables

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

Script to create a Receipt Write-off (AR) 0

Posted by Phani Adivi on February 2, 2019 in Oracle Home, Receivables

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.

Receipt Write-Off (AR) – Error Messages 0

Posted by Phani Adivi on February 2, 2019 in Oracle Home, Receivables

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.

Script to create a Debit Memo (AR) 0

Posted by Phani Adivi on February 1, 2019 in Oracle Home, Receivables

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;

Script to Create a Credit Memo (AR) 0

Posted by Phani Adivi on February 1, 2019 in Oracle Home, Receivables

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;

Query to derive the 4 C’s 0

Posted by Phani Adivi on January 31, 2019 in General Ledger, Oracle Home

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

Oracle Payables Query 0

Posted by Phani Adivi on January 31, 2019 in Oracle Home, Payables

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.

Script to Reverse an AR Receipt 0

Posted by Phani Adivi on November 9, 2018 in Receivables

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;

TCA Architecture 1

Posted by Phani Adivi on October 8, 2018 in Oracle Products

Understanding TCA Architecture in Oracle Applications:

Oracle Trading Community Architecture (TCA) is a data model that allows us to understand and manage the complex interlinkages between the parties, customers, sites, their uses and relationships. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information.

What is the key difference between a Party and an Account?

As explained well here in this link, the concept of a Party came into existence when Oracle bought Siebel. The main difference between a Party and a Customer is that a Party can be an entity that may/may not have bought an item/service from the company. But an account is an entity that has made a purchase in some form from the company already.

Consider a scenario where you walk into a TV store to look at the new TV models and their price. There is a fair chance that you may/may not buy a TV that fits your taste and budget and leave. In the scenario where you did not buy, but the store manager takes some basic information from you like your name, address, email id, telephone number and other details like budget and tv model. The manager then enters this information into a database where you will become a Party. This information is used by the company to send communication and marketing emails about new models and prices that may entice a Party to become a Customer. All this activity is maintained in CRM Module where the target is to make the Party a Customer and maintain relationship with the same. (In all the above scenario, you are still a Party).

Now, that you got all those emails and messages, and you got satisfied and you bought a TV from that store. Now, you became a Customer. All the transaction details related to this deal are stored using the account number. Thus, in Oracle, all the details in Accounts Receivables use the Customer account number for report generation purposes.

Can a Party exist without being a Customer?

Yes. A person may just receive information from the company but did not buy any item yet, in this scenario that person/entity is still a Party and not a Customer.

What is the use of a Party without being a Customer?

The CRM Module main motto is marketing and it is the process of identifying, creating and retaining a Customer. The CRM looks at a Party (that is not a customer yet) as a Potential Customer and tries to reach him/her/entity with marketing emails and information. A Party becomes a Customer and more Customers bring in revenue.

Where is a Party Used?

When a Party is created, it creates a record in HZ_PARTIES table. This is primarily used by the CRM Module.

How To: Create a Sales Order 0

Posted by Phani Adivi on September 24, 2018 in Order Management

In the Order to Cash (OTC) process in Oracle Applications, one of the first steps is to Create a Sales Order in Order Management Module.

The usual process of creating a Sales Order involves inserting data into an inbound staging table and then calling a Custom concurrent program. The detailed steps are as below:

Insert data into staging tables.
Validate data in the staging tables.
Insert the validated data into Oracle Order Management Interface Tables.
Run the Seeded Oracle “Order Import” Concurrent program to Create Sales Order.

These steps are explained as below:

Insert data into staging tables: Basing on the source system, data is inserted into Custom staging tables via a middleware application like Webmethods or SOA.

Validating Data in the staging tables: Some of the major validations that need to be done before inserting data into Interface tables are:

Item Validation: The item that is being used on the Sales order need to be validated. The item has to exist in the Item Master as well should be assigned to the inventory org from where the Item is being shipped from.
```
SELECT *
  FROM mtl_system_items_b
 WHERE segment1 = 'TEST_ITEM_1234';
```
Customer Validation: The Customer for which the order is being created for, should exist in the system already.
```
SELECT *
  FROM hz_cust_accounts
 WHERE account_number = '12345678'; -- p_customer_number
```

Get BILL_TO Validation: For the Customer that exists, check to see if a valid BILL_TO Site exists.

  SELECT hcsua.org_id, hcsua.site_use_id, hps.party_site_id
            FROM hz_cust_accounts        hca
                ,hz_cust_acct_sites_all  hcasa
                ,hz_cust_site_uses_all   hcsua
                ,hz_party_sites          hps
            WHERE     hca.cust_account_id = hcasa.cust_account_id
                  AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
                  AND hcasa.status = 'A'
                  AND hcsua.status = 'A'
                  AND hcasa.org_id = hcsua.org_id
                  AND hcsua.site_use_code = 'BILL_TO'
                  AND hps.identifying_address_flag = 'Y'
                  AND hps.party_site_id = hcasa.party_site_id
                  AND hca.cust_account_id = '&Cust_account_id'
                  AND hcsua.org_id = '&Operating_unit_id'
                  AND ROWNUM < 2;

GL_Period Validation: Validate whether the Order data has Open GL period or not.

SELECT gps.period_name,
       gps.set_of_books_id,
       gps.end_date,
       gps.closing_status
  FROM gl_periods          gp,
       gl_period_statuses  gps,
       gl_sets_of_books    gsob,
       hr_operating_units  hou
 WHERE     gp.period_name = gps.period_name
       AND gp.period_set_name = gsob.period_set_name
       AND gsob.set_of_books_id = hou.set_of_books_id
       AND gps.application_id = 222
       AND gps.set_of_books_id = gsob.set_of_books_id
       AND '&Order Date' BETWEEN gp.start_date AND gp.end_date
       AND hou.organization_id = '&Organization_id';

UOM Code Validation: Validate Unit of Measure validation.
```
SELECT uom_code FROM mtl_units_of_measure_vl;
```
Operating Unit Validation: Validate the Operating Unit for which the order is being created in.
```
SELECT organization_id
  FROM hr_operating_units
 WHERE name = ''
```

Get Price List: Derive the Price List.

SELECT list_header_id, name
  FROM qp_list_headers qlh
 WHERE     qlh.orig_org_id = '&Organization_id'
       AND currency_code = '&Currency_code'
       AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (qlh.start_date_active),
                                        TRUNC (SYSDATE) - 1)
                               AND NVL (TRUNC (qlh.end_date_active),
                                        TRUNC (SYSDATE) + 1)
       AND qlh.active_flag = 'Y';

Derive Tax Rate Code: Derive the tax rate code based on the tax code passed by the source system.

SELECT zrab.tax_regime_code,
       zrgb.tax_regime_id,
       zrab.tax,
       zrab.tax_status_code,
       zrab.tax_rate_code,
       zrab.tax_rate_id
  FROM zx_rates_b zrab, zx_regimes_b zrgb
 WHERE     zrab.tax_regime_code = zrgb.tax_regime_code
       AND TRUNC (SYSDATE) BETWEEN NVL (zrab.effective_from,
                                        TRUNC (SYSDATE) - 1)
                               AND NVL (zrab.effective_to,
                                        TRUNC (SYSDATE) + 1)
       AND zrab.active_flag = 'Y'                     -- Added for Defect#7614
       AND zrab.tax_rate_code = '&tax_rate_code';

Inserting Data into Interface Table: Upon Validation, Data is inserted into the following Order Interface Tables:
- Headers Interface: OE_HEADERS_IFACE_ALL.
  - This is the Headers Interface table that captures all the data that goes into the Order Header data (later into OE_ORDER_HEADERS_ALL table). Some of the important columns to be populated are: Operation_code, order_number, orig_sys_document_ref, org_id, Ordered_date, order_type_id, sold_to_org_id, ship_from_org_id, invoice_to_org_id, booked_flag, change_sequence etc.
- Lines Interface: OE_LINES_IFACE_ALL
  - This is the Lines Interface table that captures all the data that goes onto the Line Level on the Sales order (later onto the OE_ORDER_LINES_ALL table). Some of the important columns are: Org_id, orig_sys_document_ref, inventory_item_id (item), ordered_quantity, order_quantity_uom, unit_list_price, unit_selling_price, fulfillment_set_id etc.
- Payments Interface: OE_PAYMENTS_IFACE_ALL
  - The Payments Interface table captures data that is shown later on the payments screen on the order. This table has the data that is later populated into OE_PAYMENTS table. Some important columns are: Payment_number, orig_sys_document_ref, orig_sys_payment_ref, payment_type_code, payment_amount, check_number, credit_card_number, credit_card_expiration_date etc.
- Actions Interface: OE_ACTIONS_IFACE_ALL
  - The Actions interface table is used to load data that will perform any Action to be taken on the order once the order is created. Some uses include Cancelling a line, Applying Hold etc.. Some important columns are: Org_id, Hold_id, Hold_type_code, Operation_Code.
- Adjustment Interface: OE_PRICE_ADJS_IFACE_ALL
  - The Adjustment interface table holds the data that later goes to the OE_Price_Adjustments table. This table is used to store price adjustments that have been applied to an order or a line.

Submit Order Import program: Once data is inserted into the above interface tables, the Seeded Oracle job – Order Import is submitted as a Concurrent program. The code to call the program is as below:

x_request_id :=
            fnd_request.submit_request (
               application   => 'ONT',
               program       => 'OEOIMP',
               description   => 'EBS Order Import',
               start_time    => SYSDATE,
               sub_request   => FALSE,
               argument1     => Operating Unit,
               argument2     => Order Source,
               argument3     => Orig Sys Doc Ref,
               argument4     => Operation Code,
               argument5     => Validate Only -- Yes or No,
               argument6     => Debug Level,   -- 1
               argument7     => Number of Instances,
               argument8     => Sold To Org ID,
               argument9     => Sold To Org,
               argument10    => Change Sequence,
               argument11    => Enable Single Line Queue for Instances,
               argument12    => Trim Trailing Blanks, --- Y
               argument13    => Process Orders With No Org Specified,
               argument14    => Default Operating Unit,
               argument15    =>  Validate DFFs ?     )

Order Interface Errors: During importing the Orders from Interface tables to the Order base tables, if an error occurs due to a setup issue or a data issue, then the corresponding error messages are stored in OE_PROCESSING_MSGS_TL table.
```
SELECT OPT.*
FROM OE_PROCESSING_MSGS OPM
       ,OE_PROCESSING_MSGS_TL OPT
WHERE OPM.TRANSACTION_ID = OPT.TRANSACTION_ID
AND ORIGINAL_SYS_DOCUMENT_REF = dof_ref; -- ORIGINAL_SYS_DOCUMENT_REF in headers interface
```
Oracle Base Tables: Upon completion of the “Order Import” seeded program, the base tables where order information is stored are:
- Header Data: OE_ORDER_HEADERS_ALL
- Lines Data: OE_ORDER_LINES_ALL
- Payments Data: OE_PAYMENTS
- Adjustments Data: OE_PRICE_ADJUSTMENTS
- Holds Data: OE_ORDER_HOLDS_ALL
- Order Sources: OE_ORDER_SOURCES
- Sets data – Arrival Set, Ship Set, Fulfillment Set: OE_SETS
- Transaction Types: OE_TRANSACTION_TYPES_TL.

Topic: Oracle Products

Recent Posts