, , , , , ,

FND Messages In Oracle HRMS

FND MESSAGES:
Here i am Explaining  how to create Fnd Messages  via  E-Business suite  and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message

To create a message in the E-Business suite message library you will need the “Application Developer” responsibility.
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_CUSTOMER_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my first message
Click the save icon.

Retrieving a message using PL/SQL:

In order to retrieve the message from the database we need to use a standard API’s in the FND_MESSAGE package.
An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Optional)

Below is the PL/SQL Block to retrive the message

DECLARE
 my_message VARCHAR2(100);

 BEGIN

  –Initialize Apps Session
  fnd_global.apps_initialize( user_id      => 1234
                             ,resp_id      => 1235
                             ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility‘;
  */
 
  –Clear the existing session
  FND_MESSAGE.CLEAR;

  –Tell e business suite which message you want (custom application short name/message name) 
  FND_MESSAGE.SET_NAME(‘XXERP’,’XX_CUSTOMER_MSG’);

  –Retrieve the message
  my_message := FND_MESSAGE.GET;  

  –Output the message
  DBMS_OUTPUT.PUT_LINE(my_message);
 END;

Output for the Above Block Is : This is my first message

Using Tokens in the message:

The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time.
Open the E-Business Suite message create a New Message
Navigate to Application Developer > Application > Messages. This will launch a form

Enter a unique name for your message
Eg: XX_UNAME_TOKEN_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my second message and the Token User name is &USERNAME
Click the save icon.
Note: In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. &USERNAME
Retrieving message With Token Substitution
Here USERNAME is called as TOKEN, we will Add the value dynamically

Example Block  is below:

DECLARE
 my_message VARCHAR2(100);
BEGIN
 –Initialize Apps Session
 fnd_global.apps_initialize( user_id      => 1234
                            ,resp_id      => 1235
                            ,resp_appl_id => 1236
                           );
                         
  /*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
    select fnd.user_id ,
         fresp.responsibility_id,
         fresp.application_id
  from   fnd_user fnd,
         fnd_responsibility_tl fresp
  where  fnd.user_name = ‘OEAG’
  and    fresp.responsibility_name = ‘Custom HRMS Responsibility’;
  */
 
 –Clear the existing session
 FND_MESSAGE.CLEAR;

 –Tell e business suite which message you want (Application short name/message name)
 FND_MESSAGE.SET_NAME(‘XXERP‘,’XX_UNAME_TOKEN_MSG’);

  –Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN(‘USERNAME’,FND_GLOBAL.USER_NAME);

 –Retrieve the message
 my_message := FND_MESSAGE.GET;

 –Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;

Out put for above block is : 

This is my second message and the Token User name is IAMKRISHNA

Downloading and Uploading Messages using the Generic Loader

To download our example message we would use the following command at the Unix prompt on the mid-tier:
 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’PER’ MESSAGE_NAME=”XX_UNAME_TOKEN_MSG”

To Upload our example message we would use the following command at the Unix prompt on the mid-tier:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt

/by
, , , , , ,

Complete GL Interface – PL/SQL Procedure

Pre-Requisites:
    ————–
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined

    Interface Tables:
    —————-
      GL_INTERFACE
 
    Base Tables:
    ———–
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES

 Standard Program:
 Go to General Ledger Vision Operations(USA)
   Run =>Import
 Here Give the Source name and Save.

While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..

   Validation Columns:
   ——————
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id

    Source = ‘NEW’
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.

Control file for GL_Interface:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
 SET_OF_BOOKS_ID,
 ACCOUNTING_DATE,
 CURRENCY_CODE,
 DATE_CREATED,
 CREATED_BY,
 ACTUAL_FLAG,
 USER_JE_CATEGORY_NAME,
 USER_JE_SOURCE_NAME,
 SEGMENT1,
 SEGMENT2,
 SEGMENT3,
 SEGMENT4,
 SEGMENT5,
 ENTERED_DR,
 ENTERED_CR,
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 GROUP_ID)

BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11

Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’    log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’

exit 0

GL Interface Package:

CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;

procedure main(errbuf out varchar2,
               retcode out varchar2
               )
 is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin
 
   dis_log(‘before entering the loop’);
 
 
 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:=’S’;
                v_tot_err_msg:=null;
                v_gl_int:=null;
              –currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
               
                –user_je_source_name validation
               
                begin
               
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;    
               
                –category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;  
                 
                end;
               
                 –user id validation
               
                begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                   
                end;
               
                 — set of books id validation
               
                begin
               
                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid set of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception atset of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;
             
                         v_gl_int.status                    :=i.status;
                        — v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        — v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         –v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         –v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        –v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                       
             
               
                 dis_log(‘before inserting the loop’);  
               
                   if v_process_flag = ‘S’ then    
               
                    insert into gl_interface values v_gl_int;
               
                   end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
                 
                 dis_log(‘after inserting the loop’);    
 end loop;
 exception
 when others then
 dis_log(‘exception occured at main loop’);
 end main;
 end xx_gl_int_pkg;

/by
, , , , , ,

How to find INV Onhand Quantity at given date?

–The query inputs the Item ID, organization ID and date.

  SELECT   SUM (target_qty), item_id
    FROM   (  SELECT   moqv.subinventory_code subinv,
                       moqv.inventory_item_id item_id,
                       SUM (transaction_quantity) target_qty
                FROM   mtl_onhand_qty_cost_v moqv
               WHERE   moqv.organization_id = :org_id
                       AND moqv.inventory_item_id = :item_id
            GROUP BY   moqv.subinventory_code,
                       moqv.inventory_item_id,
                       moqv.item_cost
            UNION
              SELECT   mmt.subinventory_code subinv,
                       mmt.inventory_item_id item_id,
                       -SUM (primary_quantity) target_qty
                FROM   mtl_material_transactions mmt, mtl_txn_source_types mtst
               WHERE   mmt.organization_id = :org_id
                       AND transaction_date >= TO_DATE (:hist_date) + 1
                       AND mmt.transaction_source_type_id =
                             mtst.transaction_source_type_id
                       AND mmt.inventory_item_id = :item_id
            GROUP BY   mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY   oq.item_id
/by
, , , , , , ,

Cursors In Oracle

CURSOR : A cursors is a pointer used to fetch rows from a result set 
Two types of classification s:


I.STATIC CURSOR S: 
Static : Normal cursor (implicit or explicit)

Cursor attributes  for implicit and explicit:

%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open

a. Implicit : 
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)

Example Implicit Cursor:

select * from emp

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;


b. Explicit : 2 network round trips. Store data first then retrieve data. 
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.

Syntax:
open c1; — cursor c1 is select <stmt>

fetch <>

exit when c1%NOTFOUND

Example Explicit cursor:

Without Using Loop s
Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

open cur1;

fetch cur1 into v_ename,v_empno,v_sal;

exit when cur1%notfound;

—<do processing>

close cur1;

end;

Using Loops:

Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

Using Loops with Cursor Parameters:

Declare

Cursor cur1( cp_deptNo Number) 
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo

l_deptNo Number :=50;
begin

For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

II. DYNAMIC CURSOR s : 

Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures. 

a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.

Example For the Ref Cursor :

–SPECK PACKAGE 
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
 AS
  TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

END REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:


–BODY PACKAGE 
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor: 
PROCEDURE 
WEAK_REF_CUR_PRC( p_deptno IN number,
                  p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
                  —- From 9i (p_cursor OUT SYS_REFCURSOR )—-
                 )
IS

BEGIN

  OPEN p_cursor FOR
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
end WEAK_REF_CUR_PRC;

— For Strong Ref Cursor: 
PROCEDURE 
STRONG_REF_CUR_PRC( p_deptno IN number,
                    p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
                  )
IS

BEGIN
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
  end STRONG_REF_CUR_PRC;
 END REFCURSOR_PKG;
/by
, , , ,

Bulk Collect in Oracle PL/SQL

We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.

In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.

insert into BULK_COLLECT_TEST

select * from BULK_COLLECT_TEST;

20000 rows created.

–BLOCK1:Using Loops
declare
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
 begin
      open c1;
       loop
       fetch c1 into rec1;
    exit when c1%notfound;
    null;
    end loop;
 end;

total Elapsed Time is : 45 Secs

–BLOCK2: Using Bulk Collecting
declare
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
begin
open c1;
   fetch c1 bulk collect into rec1;
end;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

–Bulk Collect Example using LIMIT :
declare
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
begin
    open c1;
    loop
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    null;
    end loop;
    exit when c1%notfound;
    end loop;
end;

/by