Web ADI integrator "ORA-06508: PL/SQL: could not find program unit being called"

In custom Web ADI integrator, when code changes are made to the API, then loading of rows fail with the following error: ORA-06508: PL/SQL: could not find program unit being called

Solution:

To implement the solution, please execute the following steps:

1. Run the following sql to clear the shared pool:

SQL>alter system flush shared_pool;

2. Bounce the Apache server:

cd $ADMIN_SCRIPTS_HOME 

1. adapcctl.sh stop
2. adapcctl.sh start

3. adoafmctl.sh stop

4. adoafmctl.sh start

5. adoacorectl.sh stop

           6. adoacorectl.sh start 

3. Retest the issue.

"Cross currency rate is null." while calling AR_RECEIPT_API_PUB.CREATE AND APPLY API

Solution:

This error occurs when Receipt currency is different from Invoice Currency. You have to specify p_trans_to_receipt_rate parameter while calling  AR_RECEIPT_API_PUB.CREATE AND APPLY.

sample:

DECLARE

    --local Variables
    g_loc                       NUMBER :=0;
    g_msg                       VARCHAR2(500);
    l_error_description         VARCHAR2 (2000) := NULL;
    g_sysdate                   DATE  := SYSDATE;
    l_ar_receipt_succ_count     NUMBER:= 0;
    l_ar_receipt_err_count      NUMBER:= 0;
    l_ar_receipt_tot_count      NUMBER:= 0;
    l_msg_index_num             NUMBER:= 1;
    l_msg_count                 NUMBER;
    l_data_txt                  VARCHAR2(1000);
    l_msg_data                  VARCHAR2(1000);
    l_apl_return_status         VARCHAR2 (1);
    l_apl_msg_count             NUMBER;
    l_apl_msg_data              VARCHAR2 (240);
    l_org_id                    NUMBER := FND_PROFILE.VALUE('ORG_ID') ;
    l_gl_date_count             NUMBER;
    l_functional_currency fnd_currencies.currency_code%TYPE;
    l_conv_type gl_daily_conversion_types.conversion_type%TYPE;
    l_conv_rate gl_daily_rates.conversion_rate%TYPE;
    l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
    l_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
    l_return_status             VARCHAR2(10);
    l_cust_bank_acct_id         NUMBER;

   
BEGIN
 

    l_msg_count           := 0;
    l_data_txt            := NULL;
    l_msg_index_num       := NULL;
    l_gl_date_count       := 0;
    l_functional_currency := NULL;
    l_conv_type           := NULL;
    l_conv_rate           := NULL;
    l_receipt_id          := NULL;
    l_return_status       := NULL;
    l_msg_data            := NULL;
 

   
   FND_GLOBAL.APPS_INITIALIZE(0, 50259, 222);
   MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
   
    dbms_output.put_line('before calling api ');

    -- Call Api to create receipt
 
         AR_RECEIPT_API_PUB.create_and_apply
                                   ( 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,
                                   p_currency_code     => 'USD',
                                   x_return_status => l_return_status,
                                   x_msg_count => l_msg_count,
                                   x_msg_data => l_msg_data,
                                   p_amount => 100,
                                   p_receipt_number => '123767589',
                                   p_receipt_date =>  sysdate-2,
                                   p_gl_date => sysdate-2,
                                   p_customer_number => 9399,
                                   p_receipt_method_id => 21011,
                                   p_trx_number => 'P18_00004',
                                   p_cr_id => l_receipt_id ,
                                   p_org_id => 93,
                                   p_location          => 'RUSK8' ,
                                   p_show_closed_invoices => 'Y' ,
                                   p_exchange_rate_type => 1000,
                                   p_exchange_rate     => null,
                                   p_exchange_rate_date => to_date('01/04/2016','dd/mm/yyyy'),
                                   p_amount_applied => 100,
                                  p_trans_to_receipt_rate=>0.5
);
                                   
                                   
   
    dbms_output.put_line('called api ');
    IF (l_return_status = 'S') THEN
        dbms_output.put_line('Calling Api AR_RECEIPT_API_PUB.CREATE_CASH success -- >' ||l_return_status ||' Receipt Id > '||l_receipt_id);
 

    ELSE
        dbms_output.put_line('Error in Calling Receipt API:');
       
        FOR i IN 1 .. l_msg_count
        LOOP
            FND_MSG_PUB.GET(p_msg_index => i, p_encoded => 'F', p_data => l_data_txt, p_msg_index_out => l_msg_index_num );        
            l_error_description := SUBSTR(l_error_description||l_data_txt,1,400);
            DBMS_OUTPUT.put_line( l_error_description);
        END LOOP;
       
       
    END IF;

COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in procedure '||SQLERRM);
END ;
/

How to change column/attribute type on WEBADI

It is not possible to change column/attribute type from application. All you need to do update below tables from backend.

In bne_interface_cols_b update data_type column. 1-number, 2-varchar2 and  3-date

In bne_attributes update attribute2  column to NUMBER, VARCHAR2 or DATE

Error: ORA-1403: No data found in Cash Management

                       

You can encounter this type of error while trying to reconcile receipt or payment to bank statement under Cash Management responsibility.

When you click on ‘Details’, you can see the Error name:

When you encounter this error, close this window> go back to bank statement lines and Save your parameters.

Now try to do reconciliation again. See the reconciled bank statement line with Receipt.

ERROR: 'No Organizations are currently defined'

When the organization is not assigned to responsibility then the following error appears:

Organization needed as per your business requirements.

Go to Inventory> Setup> Organizations>Organization Access> Add new records in this list for> Save:

Runtime Error 91 When trying to create WebADI document

A Runtime error 91 – (Object variable or with block variable not set ) is thrown when trying to create WebADI document.

Solution 1

In Excel 2010, under Developer, go to Code tab and click on Macro Security icon. Check Trust access to VBA project object model under Developer Macro Settings.

Steps

1.    Click the Office Button (top-left button in the window) 

2.    Click the Microsoft Excel Options button. 

3.    Click Trust Center in the menu on the left. 

4.    Click Trust Center Settings in the window on the right. 

5.    In the new window that opens click Macros in the menu on the left. 

6.    Under 'Macro Settings' check 'Disable all macros with notification'. 

7.    Under 'Developer Macro Settings' check 'Trust Access to the VBA project object model'. 

8.    Click 'OK' 

9.    Click 'OK'

Solution 2

To turn off Protected View on the desktop and to prevent this message appearing uncheck the following

Excel option:

Options-> Trust Center -> Trust Center Settings -> Protected View -> Enable Protected View for files

located in potentially unsafe locations

Payables transfer to GL

                                         

After invoice and payment has been created and validated successfully it has to be transferred to General Ledger.

First of all, the invoice and payment has to be accounted which is done by running Create Accounting. Create Accounting can be run in 3 different modes:

• Draft
• Final
• Final Mode

Draft - The accounting is only created in Subledger tables and is not transferred to the General Ledger. Draft accounting can be recreated or altered.

Final - The accounting is created in Subledger tables and is not transferred to the General Ledger. Accounting created in final mode cannot be recreated or altered. Note that, the accounting entries in ‘Final” mode does not mean that it has finally been transferred to GL. In order to transfer all the entries from subledger table to GL ‘Transfer Journal Entries to GL program’ must be run.

Final Post - The accounting is created in Subledger tables and is also transferred and posted to the General Ledger tables. When you run create accounting in Final Mode but have not transferred them over to GL, and you run the Create Accounting program with Transfer to GL option, then the application does not pick up events that are already created in the final mode.

After you run create accounting for the particular Invoice, you will see the ‘Accounted’ box as YES:

In order to transfer the accounting entries to General Ledger, you need to run ‘Transfer Journal Entries to GL’ request and set the following parameters. The Transfer Journal Entries to GL program needs to be ran separately to transfer any accounting created online or created by a previous Create Accounting process that did not transfer the entries. (Final and Draft mode)

Note*: If accounting is created in error by any mode, the accounting is treated like draft accounting. It can be viewed, but it cannot be transferred. Any changes will need to be made to the transaction or journal line definition/rules to correct the accounting. Once the changes are made you can recreate the accounting in draft again, or final if the accounting is correct.

Based on the above statement, the following are possible causes of journal entries not transferred in R12:

• The accounting is created in Draft mode and cannot be transferred.
• The accounting is created in error and cannot be transferred.
• The accounting was created by Online Accounting with Final Mode.
• The accounting was created by the Create Accounting process with Transfer to General Ledger = No
• The Journal Import failed/had errors.  If the Journal Import spawned by the Transfer Journal Entries to GL program fails/has errors, ALL data included in the transfer and import is rolled back/not transferred.

Submit the Transfer Journal Entries to GL process to transfer any accounting that is created in Final mode, but not transferred.

Navigate:  Payables Responsibility > Other > Requests > Run > Select and Submit the Transfer Journal Entries to GL program with parameters to include the journal entries to be transferred.

Now search for your invoice> Go to Reports> View Accounting to see the accounting transactions:

Press to ‘View Journal Entry’ to see the accounting entries of the Invoice we created:

See the category, Journal Entry type, description of the Journal entry type. If you want to have more information, click on ‘Show additional Information’:

See the status of transfer to GL: YES

Now see the Journal created in General Ledger:

You can check the invoice number to see whether it has been transfered to GL or not:

select Transaction_number invoice_number, AH.GL_TRANSFER_STATUS_CODE, ah.*

from xla_transaction_entities te

,xla_ae_headers ah

where te.Transaction_number = :Transaction_number --Invoice_number or Check_number

and te.APPLICATION_ID IN (200,222)

AND ah.ENTITY_ID = te.ENTITY_ID

and AH.GL_TRANSFER_STATUS_CODE <> 'Y'

Invoice cancellation

   If the invoice has been paid already, they can not be deleted directly from the Invoice Workbench. The paid Invoice which is validated and matched to PO already can be cancelled through Action button which is done inside of Invoice. But before Invoice cancellation the following steps should be followed, as there is related payment and bank account reconciliation to this payment.

Steps for Invoice cancellation:

• Find Invoice
• Review Invoice
• Check the period
• Check if prepayment exists
• Check if the invoice is paid
• Unreconcile the payment if you are using CM
• Void the payment
• Unapply the prepayment
• Cancel the Invoice

In order to start cancellation process, ‘Allow Adjustments to Paid Invoices’ should be enabled from Payables Options:

Step 1:  Find Invoice using Payables Manager or AP Accountant responsibility:

Search the Invoice you want to cancel by clicking search button from the main Menu. Type the Invoice number:

Step 2 and 3: See the paid amount of the Invoice from ‘Amount Paid’ box. It is necessary to check the GL date for the Invoice, as it should be open period to be cancelled, otherwise the system will not allow you to process.

Step 4: It is necessary to check if Prepayment exists for this Invoice and if it has been applied. Copy the prepayment number from ‘View Prepayment Applications’ tab box.

Search for Prepayment by typing the Prepayment number to Invoice search box.

See the Prepayment status which is ‘Fully applied’.

Note*: When the prepayment is applied to an Invoice, Payables automatically creates payment for the Invoice and prepayment and changes the status of Invoice to Paid which reflects the invoice as being paid by the prepayment.

Step 5: Check the payment number from ‘View Payments’ tab box.

Note: This payment number is for the Invoice as well, as when the prepayment is fully applied the invoice is being considered to be paid automatically.

You have access to see payment order related to the Invoice and Prepayment from inside the Invoice (or Prepayment) by clicking on ‘Payment Overview’ box or you can copy the payment number and search it from the Payments itself.

Note* : When you click on ‘Payment Overview’ box, you can directly see all details of payment as well as the status of Payment order which is ‘Cleared’ in our case.

Find the Payment which you need to void:

To void the payment you need to click on Actions button and void it. But when the status of Payment Order is in ‘Cleared’ status which means first it has to be unreconciled from CM module for ‘Void’ button to be active.

Step 6: In order to unreconcile the payment order, you need to switch off to CM Accountant responsibility and find bank statement where this payment was reconciled.

Note*: The bank name which this payment has been paid to is reflected in payment order. You need to check the bank name where it has been paid and search according to this bank.

Search according to bank account number or GL date:

When you find the payment that you want to unreconcile, it is necessary to click on’ Reconciled’ button and finish the unreconciliation process.

Tick the box on the left and choose the Payment which needs to be unreconciled:

Step 7: The unreconcilation process has been finished. Now switch off to AP Accountant responsibility and find the payment from Payments window to void it.

Click on Actions button and void the payment.

Step 8: Now go inside the Invoice and unapply the Prepayment by clicking on Actions button.

Step 9: Now you are ready to cancel the Invoice after you have unapplied prepayment, unreconciled and voided the payment related to this Invoice.

Note*: When Invoice is cancelled, the amount of Invoice is set to zero. It means the Invoice will be there, but it will not be reflected in any accounting analysis as the account will be zero.

The Invoice will exist but the status will show as ‘Cancelled’.

PR CREATION

   Oracle Purchasing enables you to create online Purchase Requisitions for goods and services of the company and gives an opportunity for control of the created Purchase Requisitions by approvers by which approvers can approve/reject, provide a note or do any other modifications if necessary from any computer device. Online Purchasing Requisitions (PRs) makes possible of preventing some misleading information that occurs during paper work, saves time and money and increases efficiency.

Here, I will show you how to create Purchase Requisition type of Requisition:

Navigate to >Purchasing Super User responsibility> Requisitions>Requisitions

Purchase Requisition header

Enter the Operating Unit you create PR for:

Choose the correct type and add Description for creation of this Purchase Requisition:

Enter the Department name, Cost Center number that this purchase requisition number is created for.

Purchase Requisition Lines

Choose the correct line type>Goods Direct Delivery

Note: Line types are distinguished between amount and quantity based lines. When you enter quantity based line which can be Goods Direct Delivery or Goods to Warehouse then you enter quantity and price for the item., i.e. >>buy 10 leaflets for promotion

>>If you buy service which are an amount based then the quantity is entered as 1 and price of service as it is., i.e. > buy translation services for 500 USD.

Enter the item code that you want to purchase:

Note: All the items are assigned to Item Category Sets. Definition of category sets enables to categorize the same type of items under the one category set. When you choose the Item code-Item Category Set, Units of Measure (UOM), Description and Price is displayed automatically. This set up to the Item is done through Inventory Accountant responsibility>Master Items.

*You should not do any modifications in Item Category Set once you have created the PR and PO related to this.

* The Item number cannot be modified after you have saved your work. In order to change it, clear the line and try to recreate it from beginning. User is allowed to change the Type, Description, UOM, Quantity, Price, Need by date in after saving records in Requisition form.

Enter the quantity you wish to purchase and the date that you need the item:

You can leave a note to the buyer and also select the name of the buyer from the Buyer box window:

You can also identify justification for creation of this PR and also can leave a note to receiver. But note that, these fields are not mandatory and you can leave them blank if you desire.

Choose the currency you want to purchase the item for:

Enter the expense account and Project number if needed:

Choose the Destination Type:

• Inventory- Goods are received into inventory upon delivery
• Expense- Goods are expensed from the system upon delivery

Select the correct Organization type and Location:

After you fill all mandatory fields, you can save your work in which the system will generate unique PR number and PR status will stay as . You can come back and continue with your PR later on.

Search your PR from Requisition Summary:

See the PR you have created and click open to continue:

Click on Distributions button to check the charge account:

Click to Charge Account box and it will appear automatically:

Save you work and go back to PR.

PR is ready to be sent to approval:

Tick the button and choose the correct hierarchy which is set up for your department. Choose the final approver from box:

Click OK. The approvers will receive notification to their email about PR pending approval.

You can check the approval path of the PR from Requisition Summary window.

Enter the PR number and click on Find:

The status of PR will be In Process until it is approved.

Go to Tools> View Action History and track your PR until it is finally approved.

Requisition can have the following different authorization statuses:

Approved, Cancelled, In Process, Incomplete, Pre-Approved, Rejected, or Returned.

• Requisition can be in Pre-Approved status when Approver forwards the document to another approver by mistake or some other business needs. Once the Requisition is in Pre-Approved status, you cannot finally close Requisition. Also, it is not possible to create a receipt against the Requisition document with Pre-Approved status.

Note: The requisition cannot be viewed once it is approved (in Approved status). It can only be viewed when it is in Incomplete, Returned, and Rejected statuses.

Control Document Actions

The control actions which can be performed through Requisition Document is Cancel And Finally Close .These actions can be performed only if the Requisition is approved.  You cannot cancel Requisition which is in Incomplete/In process statuses.

Note*: The Requisition line and header cannot be cancelled or finally closed after Purchase Order has been created against the Requisition document.