Converting .pld file into .pll File

In 11i COnverting .pld file in .PLL File

f60gen Module=XXSYK_XDO.pld Userid=apps/P3pt1d3s@EUSD1 parse=yes Module_Type=LIBRARY

In R12 COnverting .pld file in .PLL File

frmcmp_batch Module=XXSYK_XDO.pld Userid=apps/P3pt1d3s@EUSD1 parse=yes Module_Type=LIBRARY

Oracle Forms : Data Block Based on multiple Table

Oracle Forms : Data Block Based on multiple Table

As usual, data block in oracle forms is based on single table.
Today I will present a case to create single data block based on multiple tables.

I will implement solution on SCOTT schema.
I will display one block based on two tables(EMP and DEPT)

1- Create single data block using data block wizard based on EMP table
2- Create Layout wizard of EMP block and create it as tabular form like below image

3- For every database item in EMP block add prefix "EMP." to Column Name property 
for example EMP.EMPNO, EMP.ENAME, EMP.JOB ,............ ie.

4- I will change properties of block to get data from SCOTT.EMP, SCOTT.DEPT and add Join condition between two tables

Change the below Properties of EMP block
    Query Data Source Name :  SCOTT.EMP,SCOTT.DEPT
    DML Data Target Name   :  SCOTT.EMP,SCOTT.DEPT
    Locking Mode                    :  Delayed
    Key Mode                          :  Updatable
    Where Clause                     :  EMP.DEPTNO = DEPT.DEPTNO

5- I will add some items in EMP block based on DEPT table
Add the below two Items  
      1- Name : DNAME
          Data Type :  Char
          Data Length Semantics:  Byte
          Maximum Length :  15
          Column Name :  DEPT.DNAME
          Prompt Name :  Department Name
          Prompt Attachment Edge :  Top
          Prompt Alignment :  Center

      2- Name : LOC
          Data Type :  Char
          Data Length Semantics :  Byte
          Maximum Length :  14
          Column Name :  DEPT.LOC
          Prompt Name :  Location
          Prompt Attachment Edge :  Top
          Prompt Alignment :  Center



The layout should be like below image

6- Now I finished designing the form. Let's run it and see the data at run time.

Conclusion

You can create Data Block based on more than two tables and you can join them in Where Clause property of the block.
Display the data at block based on join is faster than  single block and get joined data in POST-QUERY trigger.

Print No Data Found in Oracle Report/ XML Report

I Have one Requirement to print No Data Found in Oracle Reports or XML Report Template,if my SQL Statement doesn't Return any Row.


We can do it in few ways.

A)-In rdf, we can define a summary column (say CF_COUNT) which counts the number of rows of a group, and in the rtf layout create a text form field and put the helptext as 

<?if:CF_COUNT=0?>No Data Found<?end if?>


B)-Other way to text in RTF Temaplate, 

In the RTF layout
create a text form field and put the help text as 


<?if: count(./LIST_G_SO_NUMBER/G_SO_NUMBER/LIST_G_SO_NUMBER1/G_SO_NUMBER1)=0?>No Data Found<?end if?>
where
./LIST_G_SO_NUMBER/G_SO_NUMBER/LIST_G_SO_NUMBER1/G_SO_NUMBER1
is the expected path to the group, from the root of the xml, that we define. This expression will count the number of occurances of the group "G_SO_NUMBER1" 



C)- Using Choose and When Option

Assume we have the following XML

<G_CUSTOMER>
 <CUSTOMER_NAME>A. C. Networks</CUSTOMER_NAME>
 <ZIP>84606</ZIP>
 <STATE>UT</STATE>
 <ADDRESS_LINE2/>
 <ADDRESS_LINE1>3405 East Bay Blvd.</ADDRESS_LINE1>
 <COUNTRY>US</COUNTRY>
 <CITY>Provo</CITY>
 <CUSTOMER_NUMBER>1143</CUSTOMER_NUMBER>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 <G_INVOICES>
  ...
 </G_INVOICES>
 </G_CUSTOMER>
<G_CUSTOMER>
 <CUSTOMER_NAME>Networks Inc</CUSTOMER_NAME>
 <ZIP>93934</ZIP>
 <STATE>CO</STATE>
 <ADDRESS_LINE2/>
 <ADDRESS_LINE1>8762 Rawlins Road</ADDRESS_LINE1>
 <COUNTRY>US</COUNTRY>
 <CITY>Castle Rock</CITY>
 <CUSTOMER_NUMBER>1143</CUSTOMER_NUMBER>
 <G_INVOICES/>
</G_CUSTOMER>

Notice the second CUSTOMER has a no INVOICES present. We can test for that and replace an invoice section with a 'No Data Found' string.

The 'if' statement in XSL does not have a 'then else' format i.e. all we have is 'if expr end if'. There is an alternative, the 'choose' statement, its a little verbose but we can use it.

<?choose:?>
 <?when:count(TRX_NUMBER) > 0?>
     Invoice Table
 <?end when?>
 <?otherwise:?>
     No Data Found
 <?end otherwise?>
<?end choose?>

Notice all we are doing is counting how many instances of TRX_NUMBER are present. If there are one or more present then the Invoice Table is rendered otherwise we get the 'No Data Found' string.

System Variables In Oracle Forms (D2K Forms)

System Variables

A system variable is an Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.

Oracle Forms maintains the values of system variables on a per form basis. That is, the values of all system variables correspond only to the current form.

List of system variables

SYSTEM.BLOCK_STATUS : Represents the status of the block where the cursor is located, or the current block during trigger processing. The value can be one of three character strings:

• CHANGED -Indicates that the block contains at least one Changed record.

• NEW - Indicates that the block contains only New records.

• QUERY - Indicates that the block contains only Valid records that have been retrieved from the database.

SYSTEM.COORDINATION_OPERATION : This system variable works with its companion SYSTEM.MASTER_BLOCK to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation.

SYSTEM.CURRENT_BLOCK : The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on the current navigation unit:

• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name of the block that Oracle Forms is processing or that the cursor is in.

• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value ofSYSTEM.CURRENT_BLOCK is NULL.

The value is always a character string.

Note: SYSTEM.CURRENT_BLOCK is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_BLOCK and SYSTEM.TRIGGER_BLOCKinstead.

SYSTEM.CURRENT_DATETIME : Is a variable representing the operating system date. The value is a CHAR string in the following format:

DD-MON-YYYY HH24:MI:SS

SYSTEM.CURRENT_FORM : Represents the name of the form that Oracle Forms is executing. The value is always a character string.

SYSTEM.CURRENT_ITEM : The value that the SYSTEM.CURRENT_ITEM system variable represents depends on the current navigation unit:

• If the current navigation unit is the item (as in the Pre- and Post-Item triggers), the value ofSYSTEM.CURRENT_ITEM is the name of the item that Oracle Forms is processing or that the cursor is in. The returned item name does not include a block name prefix.

• If the current navigation unit is the record, block, or form (as in the Pre- and Post- Record, Block, and Form triggers), the value of SYSTEM.CURRENT_ITEM is NULL.

The value is always a character string.

Note: SYSTEM.CURRENT_ITEM is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM or SYSTEM.TRIGGER_ITEMinstead.

SYSTEM.CURRENT_VALUE : Represents the value of the item that is registered in SYSTEM.CURRENT_ITEM.

The value is always a character string.

Note: SYSTEM.CURRENT_VALUE is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM and SYSTEM.CURSOR_VALUEinstead.

SYSTEM.CURSOR_BLOCK : The value that the SYSTEM.CURSOR_BLOCK system variable represents depends on the current navigation unit:

• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURSOR_BLOCK is the name of the block where the cursor is located. The value is always a character string.

• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value ofSYSTEM.CURSOR_BLOCK is NULL.

SYSTEM.CURSOR_ITEM : Represents the name of the block and item, block.item, where the input focus (cursor) is located.

The value is always a character string.

SYSTEM.CURSOR_RECORD : Represents the number of the record where the cursor is located. This number represents the record's current physical order in the block's list of records. The value is always a character string.

SYSTEM.CURSOR_VALUE : Represents the value of the item where the cursor is located. The value is always a character string.

SYSTEM.CUSTOM_ITEM_EVENT : Stores the name of the event fired by a VBX control.

SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS : Stores the supplementary arguments for an event fired by a VBX control.

SYSTEM.DATE_THRESHOLD* : Represents the database date requery threshold. This variable works in conjunction with the three system variables $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$, and controls how often Oracle Forms synchronizes the database date with the RDBMS. The value of this variable must be specified in the following format: MI:SS .

Because frequent RDBMS queries can degrade performance, it is best to keep this value reasonably high. However, keep in mind that if the value is not synchronized often enough, some time discrepancy can occur. In addition, if you are building a client-server application, the performance implications ofSYSTEM.DATE_THRESHOLD could vary depending on the complexity of your network configuration.

SYSTEM.EFFECTIVE_DATE* : Represents the effective database date. The variable value must always be in the following format:

DD-MON-YYYY HH24:MI:SS .

SYSTEM.EVENT_WINDOW : The SYSTEM.EVENT_WINDOW system variable represents the name of the last window that was affected by an action that caused one of the window event triggers to fire. The following triggers cause this variable to be updated:

• WHEN-WINDOW-ACTIVATED

• WHEN-WINDOW-CLOSED

• WHEN-WINDOW-DEACTIVATED

• WHEN-WINDOW-RESIZED

From within these triggers, you can assign the value of the variable to any of the following:

• global variable

• parameter

• variable

• item, including a null canvas item

SYSTEM.FORM_STATUS : Represents the status of the current form. The value can be one of three character strings:

• CHANGED - Indicates that the form contains at least one block with a Changed record. The value ofSYSTEM.FORM_STATUS becomes CHANGED only after at least one record in the form has been changed and the associated navigation unit has also changed.

• NEW - Indicates that the form contains only New records.

• QUERY - Indicates that a query is open. The form contains at least one block with QUERY records and no blocks with CHANGED records.

SYSTEM.LAST_FORM : Represents the form module ID of the previous form in a multi-form application, where multiple forms have been invoked using OPEN_FORM. The value can be one of two character strings: either the form module ID or NULL.

SYSTEM.LAST_QUERY : Represents the query SELECT statement that Oracle Forms most recently used to populate a block during the current Run form session. The value is always a character string.

SYSTEM.LAST_RECORD : Indicates whether the current record is the last record in a block's list of records. The value is one of the following two CHAR values:

• TRUE - Indicates that the current record is the last record in the current block's list of records.

• FALSE - Indicates that the current record is not the last record in the current block's list of records.

SYSTEM.MASTER_BLOCK : This system variable works with its companion SYSTEM.COORDINATION_OPERATION to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation. The values of the two system variables remain constant throughout the clearing phase of any block synchronization. SYSTEM.MASTER_BLOCK represents the name of the driving master block, and SYSTEM.COORDINATION_OPERATION represents the coordination-causing event that occurred on the master block.

More details are in the description for SYSTEM.COORDINATION_OPERATION.

SYSTEM.MESSAGE_LEVEL* : Represents one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The value is always a character string.

During a Runform session, Oracle Forms suppresses all messages with a severity level that is the same or lower (less severe) than the indicated severity level.

Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:

:System.Message_Level := value;

The legal values for SYSTEM.MESSAGE_LEVEL are 0, 5, 10, 15, 20, and 25. Oracle Forms does not suppress prompts or vital error messages, no matter what severity level you select.

SYSTEM.MODE : SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string.

• NORMAL - Indicates that the form is currently in normal processing mode.

• ENTER-QUERY - Indicates that the form is currently in Enter Query mode.

• QUERY - Indicates that the form is currently in fetch processing mode, meaning that a query is currently being processed.

SYSTEM.MOUSE_BUTTON_PRESSED : Indicates the number of the button that was clicked. Mouse button support is limited to buttons 1 and 2 (left or middle) on a three button mouse. The value is always a character string.

SYSTEM.MOUSE_BUTTON_SHIFT_STATE : Indicates the key that was pressed during the click, such as SHIFT, ALT, or CONTROL. The value is always a character string.

SYSTEM.MOUSE_CANVAS : If the mouse is in a canvas, SYSTEM.MOUSE_CANVAS represents the name of that canvas as a CHARvalue. If the mouse is in an item, this variable represents the name of the canvas containing the item.

SYSTEM.MOUSE_CANVAS is NULL if:

• the mouse is not in a canvas

• the platform is non-GUI

SYSTEM.MOUSE_FORM : If the mouse is in a form module, SYSTEM.MOUSE_FORM represents the name of that form module as a CHAR value. For example, if the mouse is in Form_Module1, the value for SYSTEM.MOUSE_ITEM isFORM_MODULE1.

Note: SYSTEM.MOUSE_FORM is NULL if the platform is not a GUI platform.

SYSTEM.MOUSE_ITEM : If the mouse is in an item, SYSTEM.MOUSE_ITEM represents the name of that item as a CHAR value. For example, if the mouse is in Item1 in Block2, the value for SYSTEM.MOUSE_ITEM is :BLOCK2.ITEM1.

SYSTEM.MOUSE_ITEM is NULL if:

• the mouse is not in an item

• the platform is not a GUI platform

SYSTEM.MOUSE_RECORD : If the mouse is in a record, SYSTEM.MOUSE_RECORD represents that record's record number as aCHAR value.

Note: SYSTEM.MOUSE_RECORD is 0 if the mouse is not in an item (and thus, not in a record).

SYSTEM.MOUSE_RECORD_OFFSET : If the mouse is in a record, SYSTEM.MOUSE_RECORD_OFFSET represents the offset from the first visible record as a CHAR value.

For example, if the mouse is in the second of five visible records in a multi-record block,SYSTEM.MOUSE_RECORD_OFFSET is 2. (SYSTEM.MOUSE_RECORD_OFFSET uses a 1-based index).

Note: SYSTEM.MOUSE_RECORD_OFFSET is 0 if the mouse is not in an item (and thus, not in a record).

SYSTEM.MOUSE_X_POS : Represents (as a CHAR value) the x coordinate of the mouse in the units of the current form coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.

Note: SYSTEM.MOUSE_X_POS is always NULL on character mode platforms.

SYSTEM.MOUSE_Y_POS : Represents (as a CHAR value) the y coordinate of the mouse, using units of the current coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.

Note: SYSTEM.MOUSE_Y_POS is always NULL on character mode platforms.

SYSTEM.RECORD_STATUS : Represents the status of the record where the cursor is located. The value can be one of four character strings:

• CHANGED - Indicates that a queried record's validation status is Changed.

• INSERT - Indicates that the record's validation status is Changed and that the record does not exist in the database.

• NEW - Indicates that the record's validation status is New.

• QUERY - Indicates that the record's validation status is Valid and that it was retrieved from the database.

SYSTEM.SUPPRESS_WORKING* : suppresses the "Working..." message in Runform, in order to prevent the screen update usually caused by the display of the "Working..." message. The value of the variable is one of the following two CHARvalues:

• TRUE - Prevents Oracle Forms from issuing the "Working..." message.

• FALSE - Allows Oracle Forms to continue to issue the "Working..." message.

SYSTEM.TRIGGER_BLOCK : Represents the name of the block where the cursor was located when the current trigger initially fired. The value is NULL if the current trigger is a Pre- or Post-Form trigger. The value is always a character string.

SYSTEM.TRIGGER_ITEM : Represents the item (BLOCK.ITEM) in the scope for which the trigger is currently firing. When referenced in a key trigger, it represents the item where the cursor was located when the trigger began. The value is always a character string.

SYSTEM.TRIGGER_RECORD : Represents the number of the record that Oracle Forms is processing. This number represents the record's current physical order in the block's list of records. The value is always a character string.

All system variables, except the four indicated with an asterisk (*), are read-only variables. These four variables are the only system variables to which you can explicitly assign values.

Date and Time System Default Values

Oracle Forms also supplies six special default values -- $$DATE$$, $$DATETIME$$, $$TIME$$, $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ -- that supply date and time information and have special restrictions on their use:

• If you're building client/server applications, consider the performance implications of going across the network to get date and time information.

• If you're accessing a non-ORACLE datasource, avoid using $$DBDATE$$ and$$DBDATETIME$$. Instead, use a When-Create-Record trigger to select the current date in a datasource-specific manner.

• Use $$DATE$$, $$DATETIME$$, and $$TIME$$ to obtain the local system date/time; use$$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ to obtain the database date/time, which may differ from the local system date/time if, for example, you're connecting to a remote database in a different time zone.

• Use these variables only to set the value of the Default Value, Range Low Value or Range High Value property.

$$DATE$$ : Retrieves the current operating system date. You can use $$DATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, or DATETIME data type. You also can use $$DATE$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.

$$DATETIME$$ : Retrieves the current operating system date and time. You can use $$DATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type. You also can use $$DATETIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.

The difference between $$DATE$$ and $$DATETIME$$ is that the time component for $$DATE$$is always fixed to 00:00:00, compared to $$DATETIME$$, which includes a meaningful time component, such as 09:17:59.

Note: Do not use $$DATETIME$$ instead of $$DATE$$ unless you plan to specify the time component. If, for example, you use $$DATETIME$$ with the default DATE format mask of DD-MON-YY, you would be committing values to the database that the user would not see, because the format mask does not include a time component. Then, because you had committed specific time information, when you later queried on date, the values would not match and you would not return any rows.

$$DBDATE$$ : Retrieves the current database date. You can use $$DBDATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, orDATETIME data type.

$$DBDATETIME$$ : Retrieves the current date and time from the local database. You can use $$DBDATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type.

$$DBTIME$$ : Retrieves the current time from the local database. You can use $$DBTIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR orTIME data type.

$$TIME$$ : Retrieves the current operating system time. You can use $$TIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or TIMEdata type.

You also can use $$TIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.

Local Variables

Because system variables are derived, if the value is not expected to change over the life of the trigger, you can save the system value in a local variable and use the local variable multiple times.

Printing multiple copies of Same report page

Printing multiple copies of Same report page

  Ex :- For an Invoice Report you want to Print 3 copies

  1. Customer Copy

  2. Finance Copy 

  3. Delivery Copy

  Easiest way to do this is by setting COPIES report parameter.

 But this fails when you run the report for Multiple Invoice at the same time as it Prints   the 1^st copy off all the invoice at once then the 2^nd and then the 3^rd Not all the 3 copies at once, also this fails if you want some different Labels on all the three copies ( Like Customer Copy,  Finance Copy…)

One of the shortest way to do this is to make a Cartesian Product as follows

               

                Let say your base query of report is

                SELECT invoice number, invoice_amount, customer_id FROM invoices ;

               

                To achieve 3 copy scenario.. revised query would be:

                SELECT copy_code, invoice_number, invoice_amount, customer_id  FROM invoices,

                (SELECT ‘Customer_Copy’  AS copy_code FROM dual

                UNION ALL

                SELECT ‘Finance_Copy’ AS copy_code FROM dual

                UNION ALL

                SELECT ’Dilivery_Copy’ AS copy_code FROM dual)

This will result all the three copies to come one after the other and also u can print the desired Label on all the copies.

European Currency Format

                If  suppose there is a Requirement  that only one RDF will be shared across the two countries (One European and other Non European) then its difficult to display the Currency in proper format for both the Countries

                Because the Normal Currency display format is Comma as Thousand separator and Dot as decimal separator

                ie like this 234,465,457,122.56

But for European Countries it’s the other way

Ie Dot for Thousands separator and Comma for Decimal separator

                ex 234.465.457.122,56

This might have been achieved easily if it was just for One territory, by doing some settings in the NLS parameters. But as the same RDF is to be shared in both the countries it seems to be difficult as the Report Builder and the Oracle Database Both does not support two type of format at the same time.

               

                To Achieve this an Alternate Solution will be to Set Number Format Mask from Format Trigger of Report Layout Field and put a condition that this will be applied only if the country is European and if the country is not European than put the general format mask in the Property Palate of the Report layout Field 

                E.G.(For SRW) SRW.SET_FORMAT_MASK(‘999”.”999”.”999”.”990”,”00’)

The only Drawback in this will be that the Number of ‘9’ you specify in above format mask can be the maximum length of the value coming from the D.B. if the length is more than the field may display as ****** in the output 

ERRBUFF & RETCODE

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called 
1.ERRBUFF 
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....




Example.

Concurrent Programs Warning-Error: ERRBUFF and RETCODE

The ERRBUFF can be returned with any message.

The RETCODE can be returned with one of three values:

   0  -- Success
   1  -- Warning
   2  -- Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message 'No Employee Found'. If there is any other error occurs, it will turn RED with SQLERRM message.

----------------------------------------------------------

-- Package Specification

----------------------------------------------------------

CREATE OR REPLACE PACKAGE apps.emp_test_pkg

IS

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2;

    

END emp_test_pkg;

/

----------------------------------------------------------

-- Package Body

----------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg

IS

 

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2

   IS

      lv_emp_name   VARCHAR2(300)  DEFAULT  NULL;

    

   BEGIN

       

      SELECT (papf.first_name || ' ' || papf.last_name)

        INTO lv_emp_name

        FROM per_all_people_f  papf

       WHERE 1=1

         AND papf.employee_number = p_emp_number;

    

      RETURN (lv_emp_name);

    

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         errbuff := 'No employee found for ' || p_emp_number;

         retcode := '1';        -- warning

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

       

      WHEN OTHERS THEN

       

         errbuff := SQLERRM;

         retcode := '2';        -- error

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

 

   END emp_name;

 

END emp_test_pkg;

/