Upload-File-to-Application-Server-Oracle-OAF

This library is the step of our journey to build java based utilities, Use this Utilities and report if found any Bug.

Source is available here 

GitHub Source

Setup for Installation

Install OAF and configure its envirnoment as per your Oracle E-Buisness Suite.

Read Documentations: Documents.

Open This project in Oracle JDeveloper

OAF Current Row Values

Add Current Row Values in Event Parameters.

Parameters: Open dialog and add parameters by choosing your own Parameter name and its value from current row.

${oa.FileSetupVO1.SrlFileSetupId}

FileSetupVO1 is my view name

SrlFileSetupId is my field from view

HOW TO DELETE A DFF CONTEXT

Sometime I’m quite annoyed by the typo mistake when creating a DFF context. The DFF segment screen doesn’t allow deletion of context. Fortunately, Oracle has internal API to do such thing. Following is a sample.
–*******************************************
–* Delete a descriptive flexfield
–*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_application_id                NUMBER := 0;
l_descriptive_flexfield_name    VARCHAR2(100) :=  ‘FND_COMMON_LOOKUPS’ ;
l_descriptive_flex_context_cod  VARCHAR2(100) :=  ‘XFND_CLWW_PURGE_FOLDER’;
BEGIN
–FND_DESCRIPTIVE_FLEXS_PKG –this package is for DFF
–FND_DESCR_FLEX_CONTEXTS_PKG –this package is for DFF Context
–FND_DESCR_FLEX_COL_USAGE_PKG –this package is for DFF Column useage
–When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
–so when deleting a DFF Context, both the context and column usage should be deleted.
FOR c IN (SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE application_id = l_application_id
AND descriptive_flexfield_name = l_descriptive_flexfield_name
AND descriptive_flex_context_code = l_descriptive_flex_context_cod)
LOOP

fnd_descr_flex_col_usage_pkg.delete_row(
x_application_id                => l_application_id
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
,x_application_column_name       => c.application_column_name
);
END LOOP;
fnd_descr_flex_contexts_pkg.delete_row(
x_application_id                => l_application_id,
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
);

–commit;
end;

Compile Oracle form scripts

For single form

frmcmp_batch userid=apps/apps module=/opt/oracle/fs1/EBSapps/appl/au/12.0.0/forms/US/Form.fmb output_file=/opt/oracle/fs1/EBSapps/appl/fnd/12.0.0/forms/US/Form.fmx module_type=form batch=no compile_all=yes



For all forms/Lib

Copy following code and paste in notepad and save as Compile.cmd

@ECHO OFF 
cls 
Echo compiling libraries 10g .... 
for %%f IN (*.pll) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=library compile_all=yes window_state=minimize 
ECHO libraries compilation complete
ECHO ----------------------------------
Echo compiling menus 10g .... 
for %%f IN (*.mmb) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=menu compile_all=yes window_state=minimize 
ECHO menus compilation complete
ECHO ----------------------------------
Echo compiling forms 10g .... 
for %%f IN (*.fmb) do frmcmp userid=User_name/pass@orcl module=%%f batch=yes module_type=form compile_all=yes window_state=minimize 
ECHO form compilation complete
ECHO ----------------------------------
ECHO Form Compilation complete

Enable / Disable Field in Oracle Forms

Case Study :

There are multiple columns in Oracle tabular form and they are depend on each other as Numeric sequence. User enter cannot enter value in columns if parent  of current item is null or child of current item is not null.

Add procedure in form and enter your your fields name in array as sequence you want .
after this call this code

begin 
when_New_item_instance;
end;

in When-new-item-instance trigger.


PROCEDURE when_New_item_instance IS

  type Items_List_t is varray(3) of Varchar2(200);

  l_Items_List Items_List_t := Items_List_t('BlockName.Item1',
                                            'BlockName.Item2',
                                            'BlockName.Item3');

  l_triggered_Item varchar2(200) default :system.trigger_item;

  l_item_enabled boolean default true;

  l_triggered_item_ind number;

BEGIN

  for ldx in 1 .. l_Items_List.count loop
 
    if l_triggered_Item = l_Items_List(ldx)
   
     then
   
      l_triggered_item_ind := ldx;
   
    end if;
 
  end loop;


if l_triggered_item_ind  is null then
null;
end;
  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind - 1)) is null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind + 1)) is not null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  if l_item_enabled then
 
    app_item_property.set_property(l_triggered_Item, enabled, property_on);
 
  else
 
    app_item_property.set_property(l_triggered_Item, enabled, property_off);
 
  end if;

END;





Note: 
Incase of non-EBS application 
replace 
app_item_property.set_property(l_triggered_Item, enabled, property_on);
with 
set_item_property(l_triggered_Item, enabled, property_on);

System Variables In Oracle Forms list

List Of System Varible That are use iN oracle Forms

System variable

SYSTEM.BLOCK_STATUS

SYSTEM.COORDINATION_OPERATION

SYSTEM.CURRENT_BLOCK

SYSTEM.CURRENT_DATETIME

SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_ITEM

SYSTEM.CURRENT_VALUE

SYSTEM.CURSOR_BLOCK

SYSTEM.CURSOR_ITEM

SYSTEM.CURSOR_RECORD

SYSTEM.CURSOR_VALUE

SYSTEM.DATE_THRESHOLD*

SYSTEM.EFFECTIVE_DATE*

SYSTEM.EVENT_WINDOW

SYSTEM.FORM_STATUS

SYSTEM.LAST_FORM

SYSTEM.LAST_QUERY

SYSTEM.LAST_RECORD

SYSTEM.MASTER_BLOCK

SYSTEM.MESSAGE_LEVEL*

SYSTEM.MODE

SYSTEM.MOUSE_BUTTON_PRESSED

SYSTEM.MOUSE_BUTTON_SHIFT_STATE

SYSTEM.MOUSE_ITEM

SYSTEM.MOUSE_CANVAS

SYSTEM.MOUSE_X_POS

SYSTEM.MOUSE_Y_POS

SYSTEM.MOUSE_RECORD

SYSTEM.MOUSE_RECORD_OFFSET

SYSTEM.RECORD_STATUS

SYSTEM.RIGHT_MOUSE_TRIGGER_NODE

SYSTEM.SUPPRESS_WORKING*

SYSTEM.TAB_NEW_PAGE

SYSTEM.TAB_PREVIOUS_PAGE

SYSTEM.TRIGGER_BLOCK

SYSTEM.TRIGGER_ITEM

SYSTEM.TRIGGER_MENUOPTION

SYSTEM.TRIGGER_NODE

SYSTEM.TRIGGER_NODE_SELECTED

SYSTEM.TRIGGER_RECORD

ABS function in SQL

Case Study:

Get only positive Values

Practice Table:

create table plch_employees (

   id    integer primary key

, name  varchar2(40)

, gender char(1)         

)

/

insert into plch_employees values (-100, 'Mr. John Doe','M')

/

insert into plch_employees values (-200, 'Mrs. Jane Doe','F')

/

insert into plch_employees values (300, 'Ms. Julie Doe','F')

/

insert into plch_employees values (-400, 'Mr. Jack Doe','M')

/

insert into plch_employees values (500, 'Dr. James Doe','M')

/

insert into plch_employees values (600, 'Jonathan Doe','M')

/

insert into plch_employees values (700, 'Jeff Jr. Doe','M')

/

commit

/

ABS

Syntax

Purpose

The ABS function returns the absolute value of a number, which will always be zero or a positive number.

This function takes as an argument any numeric data type or any nonnumeric data

type that can be implicitly converted to a numeric data type.

Examples

The following example returns the absolute value of -15:

SELECT ABS (-15) "Absolute"

FROM DUAL;

Absolute

----------

15

Print Report Directly oracle Forms

Oracle Forms : Print Report Directly

September 21, 2012

Usually we display Oracle Reports in Oracle Forms in PDF format and then the user print it through Adobe Reader  program or any other  PDF reader program.

But the requirement is to print Oracle Report directly without  displaying the report at screen.

To achieve this requirement in Oracle Forms 6i it is easy as you you set DESTTYPE parameter in report file to PRINTER and It will work correctly, But this feature not work with Oracle Forms 10g.

Some developers use java bean for this purpose but I will produce the below workaround to print report directly without using java bean.

1- Run Report and save it as PDF file at application server.

    I will share folder c:\temp at application server for everyone for read only and save reports PDF files in this folder. The share path of the folder is \\ApplicationServer_IP\Temp\

2- Silent Print of PDF file at user machine using PDF reader programs like Adobe Reader

3- Close PDF reader program after printing.

Some Developers may use ORARRP utility and do its required configuration at server and client machine for direct printing too.

Procedure Code

I create Procedure (Program Unit) in Oracle Forms to do the previous steps

PROCEDURE PRINT_DRIRECTLY (IN_SERVER_NAME  VARCHAR2, 

               IN_REP_NAME    VARCHAR2, 

               IN_OBJ_NAME    VARCHAR2) 

IS 

   LC$REP            VARCHAR2 (100); 

   LC$REP_STATUS     VARCHAR2 (20); 

   LC$LN$ADOBE_PATH  VARCHAR2 (500); 

   LN$PROCESS_ID     WEBUTIL_HOST.PROCESS_ID;

   REPID             REPORT_OBJECT; 

   LC$FILE_PATH      VARCHAR2 (1024); 

   LC$TEMP_PATH CONSTANT VARCHAR2 (256) 

      := '\\ApplicationServer_IP\Temp\' ; 

BEGIN 

   LC$FILE_PATH := LC$TEMP_PATH || 'ReportName.pdf'; 

   REPID := FIND_REPORT_OBJECT (IN_OBJ_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_FILENAME, IN_REP_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_SERVER, IN_SERVER_NAME); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_EXECUTION_MODE, BATCH); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_COMM_MODE, SYNCHRONOUS); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESTYPE, FILE); 

  

   --File name must be unique otherwise it will override the old file with same name 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESNAME, LC$FILE_PATH); 

  

   SET_REPORT_OBJECT_PROPERTY (REPID, REPORT_DESFORMAT, 'pdf'); 

  

   LC$REP := RUN_REPORT_OBJECT (REPID); 

  

   LC$REP_STATUS := REPORT_OBJECT_STATUS (LC$REP); 

  

   WHILE LC$REP_STATUS IN ('RUNNING', 'OPENING_REPORT', 'ENQUEUED') 

   LOOP 

    LC$REP_STATUS := REPORT_OBJECT_STATUS (LC$REP); 

   END LOOP; 

  

   IF LC$REP_STATUS = 'FINISHED' 

   THEN 

    IF WEBUTIL_FILE_TRANSFER.IS_AS_READABLE (LC$FILE_PATH) 

    THEN 

      -- Try to get the correct instaled version of Adobe Reader program 

  

      FOR I IN REVERSE 5 .. 13 

      LOOP 

       BEGIN 

         LC$LN$ADOBE_PATH := 

          CLIENT_WIN_API_ENVIRONMENT. 

          READ_REGISTRY ( 

             'HKEY_LOCAL_MACHINE\SOFTWARE\Adobe\Acrobat Reader\' 

            || I 

            || '.0\Installer', 

            'Path', 

            TRUE); 

       EXCEPTION 

         WHEN NO_DATA_FOUND 

         THEN 

          --If path not exists in Registery, it will raise NO_DATA_FOUND EXCEPTION 

  

          NULL; 

       END; 

  

       IF LC$LN$ADOBE_PATH IS NOT NULL 

       THEN 

         EXIT; 

       END IF; 

      END LOOP; 

  

      BEGIN 

       LN$PROCESS_ID := 

         WEBUTIL_HOST. 

         NONBLOCKING ( 

            '"' 

          || LC$LN$ADOBE_PATH 

          || 'Reader\AcroRd32.exe" /H /P ' 

          || LC$FILE_PATH); 

      EXCEPTION 

       WHEN OTHERS 

       THEN 

         NULL; 

      END; 

  

      --sleep for sometime until printing is finished 

  

      DBMS_LOCK.SLEEP (3); 

  

      -- Teminate Adobe Reader Program Process 

  

      IF NOT WEBUTIL_HOST.ID_NULL (LN$PROCESS_ID) 

      THEN 

       WEBUTIL_HOST.TERMINATE_PROCESS (LN$PROCESS_ID); 

      END IF; 

    ELSE 

      MESSAGE (

            LC$FILE_PATH || ' is unreadable path at application server');

    END IF; 

   END IF; 

END PRINT_DRIRECTLY; 

Use Guidelines

1- You pass three parameters to procedure

     a- IN_SERVER_NAME  : Report service name

     b- IN_REP_NAME : physical file path of report file (rdf , rep files)

     c- IN_OBJ_NAME : Report object name in your form

2- I expect that the installed program at client machine is Adobe reader if there are another program you use, then you should modify the code in below lines

LN$PROCESS_ID := 

         WEBUTIL_HOST. 

         NONBLOCKING ( 

            '"' 

          || LC$LN$ADOBE_PATH 

          || 'Reader\AcroRd32.exe" /H /P ' 

          || LC$FILE_PATH); 

3- You should make report file name (Generated PDF file after running report) unique over the application, You should modify the below code to make it unique.

LC$FILE_PATH := LC$TEMP_PATH || 'ReportName.pdf';