Submit GL Journal Import From PLSQL

GL Journal Import is used to import journals from sub ledgers and external systems into Oracle GL. We can do it manually or programatically. This post describes how to call the GL Journal Import from plsql procedure. We will be looking at importing AR (Receivable) Journal Entries.


Pre Requisite step is to insert the journal records to be imported into GL_INTERFACE Table. The status of the records in the GL_INTERFACE table for new records should be “NEW”.

DECLARE      l_conc_id          NUMBER;    l_int_run_id       NUMBER;    l_access_set_id    NUMBER;    l_org_id           NUMBER := 81;    l_sob_id           NUMBER := 101;    l_user_id          NUMBER := FND_GLOBAL.USER_ID;    l_resp_id          NUMBER := FND_GLOBAL.RESP_ID;    l_resp_app_id      NUMBER := FND_GLOBAL.RESP_APPL_ID;   BEGIN      fnd_global.apps_initialize    (       user_id       => l_user_id       --User Id       ,resp_id      => l_resp_id       --Responsibility Id       ,resp_appl_id => l_resp_app_id   --Responsibility Application Id    );      mo_global.set_policy_context('S',l_org_id);      SELECT   gl_journal_import_s.NEXTVAL      INTO   l_int_run_id      FROM   dual;      SELECT   access_set_id      INTO   l_access_set_id      FROM   gl_access_sets     WHERE   name = 'VISION OPERATIONS SET' ;      INSERT INTO gl_interface_control    (       je_source_name       ,interface_run_id       ,status       ,set_of_books_id    )    VALUES    (       'Receivables'       ,l_int_run_id       ,'S'       ,l_sob_id    );      l_conc_id := fnd_request.submit_request                    ( application   => 'SQLGL'                     ,program       => 'GLLEZL'                     ,description   => NULL                     ,start_time    => SYSDATE                     ,sub_request   => FALSE                     ,argument1     => l_int_run_id    --interface run id                     ,argument2     => l_access_set_id --data access set_id                     ,argument3     => 'N'             --post to suspense                     ,argument4     => NULL            --from date                     ,argument5     => NULL            --to date                     ,argument6     => 'N'             --summary mode                     ,argument7     => 'N'             --import DFF                     ,argument8     => 'Y'             --backward mode                    );      COMMIT;      DBMS_OUTPUT.PUT_LINE('GL Import Submitted. Request Id : '||l_conc_id);   EXCEPTION    WHEN OTHERS THEN         DBMS_OUTPUT.PUT_LINE('Error while submitting the GL Import Program.');       DBMS_OUTPUT.PUT_LINE('Error : '||SQLCODE||'-'||SUBSTR(SQLERRM,1,200)); END;

Enable OAF Pages for Personalization

Enable OAF Pages for Personalization

Profile Name	Profile Value
FND: Diagnostics	Yes
Personalize Self-Service Defn	Yes
FND: Personalization Region Link Enabled	Yes / Minimal
Disable Self-Service Personal	No

Meanings:

1. FND: Diagnostics

Setting the FND: Diagnostics (FND_DIAGNOSTICS) profile option to “Yes” will enable the diagnostics global button to be rendered on the screen.

2. Personalize Self-Service Defn

Set the value to Yes to allow ‘Personalize’ link to appear at the right top of the OAF page.

3. FND: Personalization Region Link Enabled

It renders the “Personalize  Region” links above each  region in a page. Each link  takes you first to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which  you selected the “Personalize  Region” link.

Two Options

Set the value to Yes to display all the personalization links above each OAF page region.

Set the value to Minimal to display key regional links.

4. Disable Self-Service Personal

Set the value to No will enable all OAF personalizations on all pages at all levels (Function, Site, Responsibility, User)

Validation on Concurrent Program Parameter

Recently While working on one of the Finance Report , i got one requirement to Validate Concurrent Program Parameters.

Requirement was : Date Range should not allow Future date to be picked.

So to fulfill this requirement , i had to create one Value set with Type as Special.

Go to Edit Information

Select Event as Validate

Write the custom code to validate your requirements.

Add this value Set to your Program and Verify the results.

ORA-01861: literal does not match format string

Cause:  FDPSTP failed due to ORA-01861: literal does not match format string
ORA-06512: at line 1

Starting with Oracle Application Version 11.5.10.2, Oracle does not allow you to use FND_DATE4 parameters and instead recommends FND_STANDARD_DATE data type. Thus, concurrent program using date parameters would be assigned value set FND_STANDARD_DATE. 

The point to be noted here is, format mask for the FND_STANDARD_DATE data type is "YYYY/MM/DD HH24:MI:SS". However, in most of the date format in Oracle Database will have format mask "DD-MON-YYYY HH24:MI:SS". Because of difference in above two format masks, you get error "ORA-01861: literal does not match format string".

Following is the workaround to avoid such errors: 
1. Define the date parameter as VARCHAR2 in Oracle Procedure. 
2. Register Date Parameter in Concurrent Program with Value Set as "FND_STANDARD_DATE".
3. Use FND_DATE.CANONICAL_TO_DATE function to convert varchar2 value to oracle date format & then use this value in your oracle procedure. 

Example: 


CREATE PROCEDURE XX_TEMP (errbuf      OUT  VARCHAR2
                         ,retcode     OUT  VARCHAR2
                         ,i_from_date  IN  VARCHAR2
                         ,i_to_date    IN  VARCHAR2 
                         )
IS
   ld_from_date DATE := FND_DATE.CANONICAL_TO_DATE (i_from_date);
   ld_to_date   DATE := FND_DATE.CANONICAL_TO_DATE (i_to_date);

   /* Use  ld_from_date and ld_to_date instead of i_from_date and i_to_date in procedure */
END XX_TEMP;
/

Find File Header Version in Unix

My requirement was to update File Header for one of the SR with Oracle, Generally DBA's take care of this activity , but during odd hours one can also find out the File header version with "adident" Command.

Example:

SR Asked to update the file version for the file named "appvndrb.pls" from AP_TOP

Login into Putty

Go to path where file is kept in Server

cd $AP_TOP/patch115/sql

Execute below Command

$ adident Header appvndrb.pls  (file name)

It Should give you the file header version.

Sed Command in Linux/Unix with examples

SED command in UNIX is stands for stream editor and it can perform lot’s of function on file like, searching, find and replace, insertion or deletion.
Though most common use of SED command in UNIX is for substitution or for find and replace.

1.Replacing or substituting string : Sed command is mostly used to replace the text in a file. The below simple sed command replaces the word "shobhit" with “sharma” in the file.

$sed 's/shobhit/sharma/' Demofile.txt

Here the “s” specifies the substitution operation. The “/” are delimiters.

By default, the sed command replaces the first occurrence of the pattern in each line and it won’t replace the second, third…occurrence in the line by this command.


2.Replacing the nth occurrence of a pattern in a line : Use the /1, /2 etc flags to replace the first, second occurrence of a pattern in a line. The below command replaces the second occurrence of the word "shobhit" with "sharma” in each line of the file.

$sed 's/shobhit/sharma/2' Demofile.txt

3.Replacing all the occurrence of the pattern in a line : The substitute flag /g (global replacement) specifies the sed command to replace all the occurrences of the string in the line.

$sed 's/shobhit/sharma/g' Demofile.txt


4.Replacing string on a range of lines : You can specify a range of line numbers to the sed command for replacing a string.

$sed '1,3 s/shobhit/sharma/' Demofile.txt

Here the sed command replaces the lines with range from 1 to 3. Another example is

       $sed '2,$ s/shobhit/sharma/' Demofile.txt

Here $ indicates the last line in the file. So the sed command replaces the text from second line to last line in the file.


5.Deleting lines from a particular file : SED command can also be used for deleting lines from a particular file. SED command is used for performing deletion operation without even opening the file


 Examples:

1. To Delete a particular line say n in this example

$ sed 'nd' Demofile.txt
Example:
$ sed '5d' Demofile.txt


2. To Delete a last line
$ sed '$d' Demofile.txt


3. To Delete line from range x to y

$ sed 'x,yd' Demofile.txt
Example:
$ sed '3,6d' Demofile.txt

5. To Delete from nth to last line

$ sed 'nth,$d' Demofile.txt
Example:
$ sed '12,$d' Demofile.txt


6. To Delete pattern matching line

$ sed '/pattern/d' Demofile.txt
Example:
$ sed '/abc/d' Demofile.txt

grep command in Unix

grep (globally search for regular expression and print out) command in Unix/Linux

The grep filter searches a file for a particular pattern of characters, and displays all lines that contain that pattern.

grep [options] pattern [files]

Options Description
-c : This prints only a count of the lines that match a pattern
-h : Display the matched lines, but do not display the filenames.
-i : Ignores, case for matching
-l : Displays list of a filenames only.
-n : Display the matched lines and their line numbers.
-v : This prints out all the lines that do not matches the pattern
-e exp : Specifies expression with this option. Can use multiple times.
-f file : Takes patterns from file, one per line.
-E : Treats pattern as an extended regular expression (ERE)
-w : Match whole word
-o : Print only the matched parts of a matching line,
 with each such part on a separate output line.



 1. Case insensitive search : The -i option enables to search for a string case insensitively in the give file.   It Can matches the words like “SHOBHIT”, “Shobhit”, “shobhit”, "ShoBhit"
 
$grep -i "ShoBhit" Demofile.txt

2. Displaying the count of number of matches : We can find the number of lines that matches the given string/pattern

$grep -c "Shobhit" Demofile.txt

3. Display the file names that matches the pattern : We can just display the files that contains the given string/pattern.

$grep -l "Shobhit" *

or
 
$grep -l "Shobhit" Demofile1.txt Demofile2.txt Demofile3.txt 

4. Checking for the whole words in a file : By default, grep matches the given string/pattern even if it found as a substring in a file.The -w option to grep makes it match only the whole words.

$ grep -w "Shobhit" Demofile.txt

5. Show line number while displaying the output using grep -n : To show the line number of file with the line matched.

$ grep -n "Shobhit" Demofile.txt

6. Inverting (Exlude) the pattern match : You can display the lines that are not matched with the specified search sting pattern using the -v option.

$ grep -v "Shobhit" Demofile.txt

7. Matching the lines that start with a string : The ^ regular expression pattern specifies the start of a line. This can be used in grep to match the lines which start with the given string or pattern.

$ grep "^Shobhit" Demofile.txt

8.Matching the lines that end with a string : The $ regular expression pattern specifies the end of a line.This can be used in grep to match the lines which end with the given string or pattern.
 
$ grep "hit$" Demofile.txt

9.Specifies expression with -e option. Can use multiple times :

$grep –e "Shobhit" –e "Sobhit" –e "Shobit" Demofile.txt