URL to Open Invoice in Oracle Core Applciations

Posted by moizjuzar

Requirement: Provide a URL from an Invoice Report that will open the invoice in Oracle Core Applications

Pre-Requisites: Using a view based reporting solution, the idea is that when an Accounts Payables runs a report, is able to launch a link that opens the invoice in the Core Application.

Oracle APIs Used:

FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL – This allows the generation of the URL based on a FUNCTION_ID, APPLICATION_ID, RESPONSIBILITY_ID, SECURITY_GROUP_ID. This function has other parameters that can be used as required.
To gather the required parameters:
1. FUNCTION_ID; the FND_FUNCTION.GET_FUNCTION_ID – This API gets the function_id for ‘AP_APXINWKB_SUMMARY_VIEW’ function.
2. APPLICATION_ID, RESPONSIBILITY_ID, SECURITY_GROUP_ID – Please refer to the security initialization blog post to see how to gather these details.
To view a list of all the available functions:

SELECT function_id,
function_name,
application_id,
parameters
from fnd_form_functions;

Final Solution – created a basic invoices query:

SELECT invoice_num from ap_invoices_all

Now adding the API to generate the URL:

SELECT invoice_num, '&lt;a href=&quot;'
|| apps.FND_RUN_FUNCTION.
get_run_function_url (
apps.fnd_function.
get_function_id ('AP_APXINWKB_SUMMARY_VIEW'),
200,
50554,
0,
'INVOICE_ID=' || api.invoice_id)
|| '&quot;&gt; view_inv&lt;/a&gt;'
view_inv
FROM ap_invoices_All aia;

Make sure you pass the correct parameters into each API to view the result. Click on the URL and it will launch the respective invoice in Oracle Core Applications. You can now reference this API call in any query that has invoice_id available to be passed.

AP: Trial Balance Report does not return any data – EBS R12.1.3

Posted by moizjuzar

Issue: The Accounts Payables Trial Balance Report does not return back any data. However invoices have been entered and are accounted to the General Ledger. The report completes, however no data is returned.

Solution:

Oracle Metalink: NOTE.553484.1

Navigate to the Payables Responsibility –> Setup –> Accounting Setups –> Subledger Accounting Setups –> Open Account Balances Listing Definitions
Query your Report Definition. Also select how it is defined by (either by Accoutning Flexfield or Segment)
Click on the Update icon[pencil] in the Actions column
Click on Apply (Nothing needs to be changed on this screen, just click Apply)

Here is the query to verify that the Trial Balance was rebuilt:

  SELECT xtb.definition_code,
         xah.ledger_id,
         DECODE (xah.upg_batch_id, NULL, 'No', 'Yes') "11i_upgrade",
         MIN (xtb.creation_date) min_creation_date,
         COUNT (*) events_count
    FROM xla_trial_balances xtb, xla_ae_headers xah
   WHERE xtb.ae_header_id = xah.ae_header_id(+)
GROUP BY xtb.definition_code,
         xah.ledger_id,
         DECODE (xah.upg_batch_id, NULL, 'No', 'Yes')
ORDER BY xtb.definition_code, xah.ledger_id;

What is FNDLOAD and how is it used in EBS r12?

Posted by moizjuzar

FNDLOAD is a concurrent program that allows the moving of metadata between Oracle Applications between database and text file. It allows downloading of meta data directly via a UNIX command and then can be moved and uploaded to another instance. The FNDLOAD conversion of meta data into Oracle is specified by a configuration file in the .LCT (Loader configuration file). The actual metadata file that FNDLOAD provides is in .LDT (Loader data file) format. Oracle provides .lct files for the following and are located in this directory:

$FND_TOP/patch/115/import

1. FND Messages – afmdmsg.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afmdmsg.lct
  your_filename.ldt
  FND_NEW_MESSAGES
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"
  MESSAGE_NAME=message_name

**If you would like to download all messages please omit the line called MESSAGE_NAME, hence it will download all messages for a specific Application.

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afmdmsg.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

**Using UPLOAD_MODE=REPLACE and CUSTOM_MODE=FORCE is usually used when REPLACING rather than CREATING. It can be omitted if it is the first time that the objects are being loaded. There onwards, to replace the objects you would have to use UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Lookups – aflvmlu.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/aflvmlu.lct
  your_filename.ldt
  FND_LOOKUP_TYPE
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"
  LOOKUP_TYPE="lookup_type"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/aflvmlu.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Concurrent Programs – afcpprog.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afcpprog.lct
  your_filename.ldt
  PROGRAM
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"
  CONCURRENT_PROGRAM_NAME="Program_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afcpprog.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Profile Options – afscprof.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afscprof.lct
  your_filename.ldt
  PROFILE 
  PROFILE_NAME="Profile_Name"
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afscprof.lct
  your_filename.ldt - WARNING=YES 
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. Request Groups – afcpreqg.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afcpreqg.lct
  your_filename.ldt
  REQUEST_GROUP
  REQUEST_GROUP_NAME="Request_Group_Name"
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afcpreqg.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. Menus – afsload.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afsload.lct
  your_filename.ldt
  MENU
  MENU_NAME="Menu_Name"
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afsload.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. EBS User – afscursp.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afscursp.lct
  your_filename.ldt
  FND_USER 
  USER_NAME="User_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afscursp.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

**Extracting the data definitions for a user will also extract the responsibilities associated with the user and automatically assign them to the user (in the new instance).

8. EBS Responsibility – afscursp.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afscursp.lct
  your_filename.ldt
  FND_RESPONSIBILITY
  RESP_KEY="Responsibility"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afscursp.lct
  your_filename.ldt
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

9. Value Sets – afffload.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afffload.lct
  your_filename.ldt
  VALUE_SET
  FLEX_VALUE_SET_NAME="Value_Set_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afffload.lct
  your_filename.ldt - WARNING=YES
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Form Functions – afsload.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afsload.lct
  your_filename.ldt
  FUNCTION 
  FUNCTION_NAME="Function_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afsload.lct
  your_filename.ldt - WARNING=YES
UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

11. Alerts – alr.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/alr.lct
  your_filename.ldt
  ALR_ALERTS
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"
  ALERT_NAME="Alert_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/alr.lct
  your_filename.ldt
  CUSTOM_MODE=FORCE

12. Descriptive Flexfields (DFF) – afffload.lct

To Download:

FNDLOAD apps/$apps_password 0 Y DOWNLOAD
  $FND_TOP/patch/115/import/afffload.lct
  your_filename.ldt
  DESC_FLEX
  APPLICATION_SHORT_NAME="APPLICATION_SHORT_NAME"
  DESCRIPTIVE_FLEXFIELD_NAME="Flexfield_Name"

To Upload:

FNDLOAD apps/$apps_password 0 Y UPLOAD
  $FND_TOP/patch/115/import/afffload.lct
  your_filename.ldt
  CUSTOM_MODE=FORCE

**You can identify DFF by running the following SQL query against your EBS database:

  SELECT application_id, descriptive_flexfield_name, application_table_name
    FROM fnd_descriptive_flexs_vl
   WHERE application_table_name = '&Table_Name'
ORDER BY application_table_name;

Oracle R12 APPS Initialization

Posted by moizjuzar

Oracle has a delivered API to initialize the applications context in standalone sessions that are not initialized directly via the application (SQL Editor). Every time you need to view secure data, you have to call the oracle initialization API – fnd_global.apps_initalize to see secured data.

An example would be querying AP_INVOICES (a synonym for the table AP_INVOICES_ALL) vs. querying AP_INVOICES_ALL.


select * from apps.AP_INVOICES;

select * from apps.AP_INVOICES_ALL;

The AP_INVOICES_ALL must have fetched data, however the AP_INVOICES synonym must have NOT fetched any data. This is because AP_INVOICES is not outside Oracle VPD security (database level security). After initializing using the script below, you will then be able to query AP_INVOICES synonym and see all data based on the security rules defined for the responsibility/user.

There are 4 parameters used to do the apps initialize:

User Id, Responsibility Id, Application Id, Security Group Id

The FND_GLOBAL.APPS_INITIALIZE can be called using the 4 parameters as shown below:


exec fnd_global.apps_initialize(1013415, 50559, 222, 0);

The below script will prompt for a User Name (from FND_USER) and display the APPS.INITIALIZE script with the defined parameters(user id, responsibility id, application id, business group id) to easily copy and execute to initialize the Oracle Applications.

SELECT resp.responsibility_name,
          'exec fnd_global.apps_initialize('
       || fu.user_id
       || ', '
       || resp.responsibility_id
       || ', '
       || furg.responsibility_application_id
       || ', '
       || furg.security_group_id
       || ');'
          text_apps_initialize,
       'exec mo_global.init(' || '''' || fa.application_short_name || ''');'
          mo_global_init
  FROM apps.fnd_user_resp_groups furg,
       fnd_application fa,
       apps.fnd_user fu,
       apps.fnd_responsibility_vl resp
 WHERE     furg.user_id = fu.user_id
       AND furg.responsibility_id = resp.responsibility_id
       AND fa.application_id = furg.responsibility_application_id       
       AND fu.user_name = '&User_Name';

Tablespace Size – Free Space vs. Used Space

Posted by moizjuzar

  SELECT tbls.tablespace,
         tbls.totalspace AS "Total Space (MB)",
         ROUND ((tbls.totalspace - fsp.freespace), 2) AS "Used Space (MB)",
         fsp.freespace AS "Freespace (MB)",
         ROUND (((tbls.totalspace - fsp.freespace) / tbls.totalspace) * 100, 2)
            AS "Used %",
         ROUND ((fsp.freespace / tbls.totalspace) * 100, 2) AS "Free %"
    FROM (SELECT ROUND (SUM (d.bytes) / (1024 * 1024)) AS totalspace,
                   d.tablespace_name tablespace
              FROM dba_data_files d
          GROUP BY d.tablespace_name) tbls,
         (SELECT ROUND (SUM (f.bytes) / (1024 * 1024)) AS Freespace,
                   f.tablespace_name tablespace
              FROM dba_free_space f
          GROUP BY f.tablespace_name) fsp
   WHERE tbls.tablespace = fsp.tablespace
ORDER BY tbls.tablespace;

Oracle Notes

Database, Applications and my General Notes for Oracle

URL to Open Invoice in Oracle Core Applciations

AP: Trial Balance Report does not return any data – EBS R12.1.3

What is FNDLOAD and how is it used in EBS r12?

Oracle R12 APPS Initialization

Tablespace Size – Free Space vs. Used Space