Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

From Garith Roberts post

If you are working with Oracle Applications, here's how you can initialize your session in whatever tool you are using to mimic the login process and pick up profile option values. The key profile option here is usually org_id so you can select from organization aware views, but it applied equally to other profile options, i.e. you can then use

fnd_profile.value('PROFILE_OPTION_NAME');

to get values from profile options. You need to be logged into the database as the APPS user. The examples set up the session for SYSADMIN user, System Administrator responsibility. e.g.

SQL*Plus

exec fnd_global.apps_initialize(0,20420,1);

e.g. for PL/SQL, TOAD, SQLDeveloper, SQL Navigator etc.:

begin fnd_global.apps_initialize(0,20420,1); end;

The parameters used here are:

1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID

To get these you have a couple of choices a) SQL - Replace SYSADMIN and System Administrator with your user and responsibility:

select 'begin fnd_global.apps_initialize(' ||
       fu.user_id || ',' ||
       fr.responsibility_id || ',' ||
       fr.application_id || '); end;' || chr(10) || '/'
from   fnd_user fu
,      fnd_responsibility_tl fr
where  fu.user_name = 'SYSADMIN'
and    fr.responsibility_name = 'System Administrator';

b) In your Oracle Applications forms session. Login as your user and navigate to the required responsibility. Open a function that uses Oracle forms Go to Help > Diagnostics > Examine In the Block enter $PROFILES$ In the field enter the appropriate field name for the parameter:

1. User_ID = USER_ID
2. Responsibility_ID = RESP_ID
3. Responsibility_Application_ID = RESP_APPL_ID

R12 Considerations
With the introduction of R12 Oracle introduced the concept of “Multi Organization Access Control” which allowed a user to operate across different organizations using a single responsibility. The introduction of this feature meant that the fnd_global.apps_initialize function was no longer sufficient to establish the context session and an additional API was introduced called MO_GLOBAL.
Within R12 after calling the apps_intialize API you must then call MO_GLOBAL.INIT(‘<PRODUCT_SHORT_NAME>’) where PRODUCT_SHORT_NAME is the product you are coding for i.e. ONT, PER, AMS etc…

From Gareth Roberts post. 
Every once in a while I've had the need to validate code combinations, and dynamically create them on the fly. With a properly structured Account Generator workflow, you can just throw in the "Validate Code Combination" function from Standard Flexfield Workflow. But what about a nice neat, keep it all in PL/SQL scenario? Well, it ain't too hard, here's some code for the General Ledger Accounting Flexfield, shouldn't be too hard to adapt for other key flexfields. Remove the dbms_output calls and adjust as you need:

create or replace function create_ccid ( p_concat_segs in varchar2 ) return varchar2 is -- pragma autonomous_transaction; -- if you need autonomy! l_keyval_status BOOLEAN; l_coa_id NUMBER; begin begin select chart_of_accounts_id into l_coa_id from gl_sets_of_books where set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID'); exception when no_data_found then dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID'); dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize'); raise; end; -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION -- create will only work if dynamic inserts on and cross validation rules not broken l_keyval_status := fnd_flex_keyval.validate_segs( 'CREATE_COMBINATION', 'SQLGL', 'GL#', l_coa_id, p_concat_segs, 'V', sysdate, 'ALL', NULL, NULL, NULL, NULL, FALSE,FALSE, NULL, NULL, NULL); if l_keyval_status then return 'S'; else return 'F'; end if; end create_ccid;

Gotta have an example of its use, even easier: set serverout on size 1000000 declare l_status varchar2(1); begin l_status := create_ccid('1-9999-9999-9999-9999'); dbms_output.put_line('Returned: ' || l_status); commit; end;

And of course remember to COMMIT; If you need to query back what you just created, how about: select gcc.code_combination_id , gcc.concatenated_segments , gcc.enabled_flag , gcc.start_date_active , gcc.end_date_active , gcc.detail_posting_allowed , gcc.detail_budgeting_allowed from gl_code_combinations_kfv gcc where concatenated_segments = '1-9999-9999-9999-9999';

PS. Check out posts on the GL Account Structure and fnd_global.apps_initialize if necessary.

Disabling Accounts using Concurrent programs

There are two useful concurrent programs you can leverage in GL to disable accounts en-mass.

 Program - Inherit Segment Value Attributes

 When this program is run it will automatically propagate the attributes of a segment value to all account code combinations which contain that segment value.This feature eases chart-of-accounts maintenance by automatically replicating changes in segment value attributes to the accounts containing each segment value. For example, if you disable a particular cost centre segment value in the Segment Values window, you can optionally require that all accounts which contain that cost centre are also automatically disabled.
You need to run this program (from the Submit Request window), there are no parameters to define. The program automatically updates the accounting flexfield structure assigned to the set of books for your responsibility.
Use the Segment Value Inheritance Execution Report to view the account code combinations which have been changed. This report is automatically generated on completion of the Segment Value Inheritance Program.
 ____________________________________________________________
Prevent Action
Individual segment value attributes override account code combination attributes.You can prevent selected accounts from being affected by segment value attribute changes by selecting the 'Preserved' check box in the GL Accounts window.
>>Setup>>Financials>>Accounts>>Combinations 

 Cross Validation Violation Report

Accounts that violate CVR can be automatically disabled too
The standard GL report "Cross Validation Violation Report" has an option to disable all combinations violating the rules.  

The following have to be added to the GL Super User (and any other Resp that plan to use them)

Tools>>Copy doesn't work!

Ensure that the following profile options have the Desired value

1. "RRA: Enabled" (/FS_ENABLED) should be set to  "Yes"
2. "RRA: Service Prefix" (/FS_SVC_PREFIX)  should be  null-ed out , no spaces allowed

After making the changes, Log-out and Login back and retest the issue.

Backend Profile Option Update

Here's a little bit pulled down from the net on how to update profile options via TOAD or SQL developer or heck anything for that matter i.e. java etc.

I locked myself out of Oracle by choosing some debug option in MO and used this stuff to get me back in.

The table fnd_profile_options_tl, profile options names are kept.  Now find the corresponding the Profile option name for which you need to update from backend. For this example I took my favorite “ORG_ID”

SELECT profile_option_name
  FROM fnd_profile_options_tl
 WHERE user_profile_option_name LIKE 'MO%'

It returns more than one row but i can make out that "ORG_ID" is the PROFILE_OPTION_NAME for MO: Operating Unit. Now I need to know the Org_ID of the Org whose value is to be set in MO: Operating Unit. SO I use the simple select as below

SELECT organization_id, NAME
  FROM hr_all_organization_units;

From the organization name I find the one which will be the default Operating Unit, and I note the ID. In my case the ID for my default Operating Unit is 286. Now with the code below I set the profile option value using fnd_profile.save.

DECLARE
   stat   BOOLEAN;
BEGIN
   DBMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.ENABLE (100000);
   stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');
   IF stat
   THEN
      DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
   ELSE
      DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
   END IF;
   COMMIT;
END;

Socket Mode vs Servlet Mode in Oracle R12.1.3

I am not sure what it was but I was chasing my tail just trying to get the AP Invoice entry form open after moving from Oracle R12 12.1.1 to 12.1.3. So I started doubting the setups, searching metalink for some poor soul who had already been through whatever it was that was stopping my damn form from opening up after a really quick setup.  No such luck.  Found a patch but related to a package that wasn't compiling and I did not have that problem. Heck it's my party and I'll patch if I want to so I figured what the heck. 250mb worth of patch in about 25 mins and ready for the form to open.  No such luck.

Then I tried an AR quick setup and tried the Transactions form. No go?  WTF.

Grasping at this point I figured bugger, it only takes a few minutes to switch to socket mode and I figured I had nothing to lose at this point.  So low and behold flash up comes the damn forms. Both of them, AR and AP now working. Switched it back to Servlet mode for shits and giggles and forms hang once again.

So lesson learned. Instead of waiting until the last resort to switch out to socket mode do it up front.  I then read a bit about it and turns out that switching back (servlet is default in R12) has been one of the lessons learned by some that have upgraded from 11i.

Sure there is something wrong with the way my machine is configured with regards to servlet mode but then again many can surely get away for a bit longer on socket mode if they are not employing SSL and some other goodies.  Elsewhere in this blog there is a guide on how to switch.

Load a txt File as a Table in PLSQL

CREATE OR REPLACE DIRECTORY ext_tab_data AS '/mnt/bu';

CREATE TABLE countries_ext (
  country_code      VARCHAR2(5),
  country_name      VARCHAR2(50),
  country_language  VARCHAR2(50)
)

ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      country_code      CHAR(5),
      country_name      CHAR(50),
      country_language  CHAR(50)
    )
  )
  LOCATION ('Countries1.txt','Countries2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;


select * from countries_ext;

drop table countries_ext;

Countries1.txt
Countries2.txt

PLSQL Write to txt file using UTL_File.fopen

In order to write to a text file from PLSQL using UTL_FILE do the following

Find out where your utl directory has been set in the init.ora
SELECT *
  FROM V$PARAMETER
  WHERE NAME = 'utl_file_dir'


Then you can do something like the following:

declare
    f utl_file.file_type;
    s varchar2(200) := 'this is some info';
   
begin
    f := utl_file.fopen('/usr/tmp','sample2.txt','W');
    utl_file.put_line(f,s);
    utl_file.fclose(f);
end;

Get Rid of DFF in Oracle Apps Value Set entry form.

Get rid of annoying DFF in entering values in value sets.

Lookup the DFF Flexfield Segment Values using Application Developer Responsibility and do the following:

1. Un-Freeze
2. Un-Check Displayed
3. Check Synchronize with Reference Field.
4. Save
5.  Re-Freeze

Determine DFF in Forms

The first step in enabling a Descriptive Flex Field (DFF) in a seeded Form is to find out the name of the DFF. Identifying a DFF involves the following steps:

1. Navigate to the Form which contains the DFF which needs to be identified. In this example, we will consider the Transactions Form under the Receivables Manager responsibility.
2. Click on the DFF and then go to Help>Diagnostics>Examine to open the  ’Examine Field and Variable Values’ window, note down the Block and Field names.

3. In the ‘Examine Field and Variable Values’ window, select  $DESCRIPTIVE_FLEXFIELD$ as the Block and enter <BLOCK_NAME>.<FIELD_NAME> as the Field. <BLOCK_NAME> and <FIELD_NAME> are the values obtained in Step#2. Press the TAB key or click on the Value field. The name of the DFF will be displayed in the Value field along with the application under which it is registered.
4. You can now navigate to Application Developer>Flexfield>Descriptive>Register  and execute a query with the DFF name (obtained in Step#3) in the Title field to obtain the complete details of the DFF.