Excel Output Without XML publisher

Requirement– There are limited formats available to view Concurrent Program output. XML is one format which is not available. 

                    

Available Options – XML publisher is one frequently used solution, which has capability to display output in PDF, EXCEL and RTF formats. EXCEL file generated using xml processing is having more size compare to actuals.

                                    

Solution – This post is to discuss another way to generate output file in EXCEL format without using XML publisher. A small setup change is required.

1. Create a concurrent program having PL/SQL as executable
2. Select the output type as any available option (ex - PCI)                              
3. Print the output using FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Output Message’);
4. Change the Mime type(view option) as EXCEL for PCL file format
5. Run the concurrent program
6. Select Tool>>Copy option to see the output

Script to Update the Mime Type

Limitation – To view multi column format, use a separator during output print. Once output is opened in excel use excel options to display multi column view by using used separator.  

Update MIME type

There are few file formats available in oracle EBS to support concurrent program output.

Each file format is having standard Mime Type associated and being stored in FND_MIME_TYPES_TL Table, which can be updated to any other mime type using below script –

UPDATE fnd_mime_types_tl

SET    mime_type = 'application/vnd.ms-excel'

,      description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,      last_updated_by = 0

,      last_update_date = sysdate

WHERE  file_format_code = 'PCL'

AND   mime_type = 'application/vnd.hp-PCL';

COMMIT;

Emailing Concurrent Program Output

Requirement –  It is very common that business request to share concurrent program output file on every run.

 

 

Resolution – There are multiple options available to notify the output via email. Some of those are as below -

• - PL/SQL program unit to send the output file
• - Unix commands to send the output file
• - Email bursting concepts, if it is XML publisher report

 

Each program requires, additional effort/custom programs to achieve the emailing feature. In this post we shall learn the options already available in system. Which need small configuration change and CP will send the o/p as email.

 

 

 

Steps :-

1)      Configuration Change – 2 profile needs to be set.

·        FND: SMTP Host   >> This to store the smtp server host details

·        FND: SMTP Host  >> This to store the smtp server port details

 

 

2)     While submitting the CP, click the Delivery Opts (B)

 

 

3)     Go to Email tab

 

 

4)     Provide the To/From email address and Subject line, then press OK (B)

5)     Submit/Schedule the program

Email bursting should be better approach, if we are working with XML publisher report.

Program will send the output file to specified email address. Program log file can be referenced in case any issue occurred.

Profile to Enable Examine Option

Requirement -  Examine option in oracle core forms is not opening

On Oracle forms getting below error, while trying to explore examine options

Resolution -  Set the profile “Utilities:Diagnostics” to YES

This profile will enable examine option J

Check the examine option again

UNIX commands.....

1) zip and unzip : zip <file_name>.zip <file_required_to_zip>
                         $_Test>zip test.zip ls *.*   It will zip all files available at location and create test.zip file

2) Softt link Creation :  ln -s $FND_TOP/bin/fndcsper <filename>

3) Execute the enviornment script : . ./*.env

Set Enviornment Variable in Unix Server

Purpose - To set enviornment variable on unix server (for example Product_TOP and custom_TOP ).

Enviornment Variable - is the shell  script needs to run and assign the corrosponding path to variables.

How to Set -
 >> Login to unix server
 >>  locate  *.env
 >>  execute the *.env script

Default Location : /oracle/ebs/apps/apps_st/appl

File Name Format : $DBserviceName_$InstanceName.env ( * File name can be anything)
Command -
To set the enviornment variable = . ./XXXXX.env 
To validate the enviornment variable = echo $XXX_TOP

Desktop Integrator - A webADI Introduction

Introduction :

For single records transaction Oracle suggest to use the application and
provides open interface and public APIs for bulk transaction.

webADI is another strong feature provided by Oracle for bulk operations,
which allows user to provide input in MS application (Word or Excel..etc).

Why WebADI ? :

It requires technical expertise for both interface and API usages.

For interface user should know to insert all transactional data into interface table and API needs transactional data to be imported in any portable format to server.

WebADI does not have such technical dependency as it is available to user as function and Launch desktop integrator (Excel) to feed transactional data.

This provides reporting option also.

How It works :

        For this one interface package required to do  initial validations.

Post it we can use any of options -   

1) Either insert all data into Interface Tables and run the Import
    Program Manually

2) Call API inside the interface program and do transactions directly  

Components  :  

1) Integrator  - Basic definition and includes user name, internal
                       name and application name

2) Interface   -  Most Important section , which directs data to flow (can
                       be either of Table or Procedure/Function type)

3) Content  -    This is required if webADI is being used for reporting
                       purpose also

4)  Upload    -   Controls the upload options

5)  Importer –  Definition of import options

6)  Layout  -     Attributes arrangement on excel

    

Responsibility - Desktop Integration Manager

Enable/Disable Concurrent Program Parameters

Requirement - Business need is to have multiple parameters but end user will be allowed to use one at a time. selection of any parameter, should disable others.  

Solution - Below is one possible option which provides the same behavior in other way.

This requires one additional parameter as Selection/Choice which will decide the enabled parameter.

There will be as many dummy (non displayed) parameters as required parameters in program. actual parameters will be dependent on respective dummy parameters.

Dummy parameters will be dependent on selection parameters and hold values based on selection.

Example - User requires 2 parameters supplier number and name to be available on one report and only one should be selected at time. So the requirement was to make other disable. dependent parameters has limitations and it cannot flow bottom to top. we can achieve this by introducing one selection parameter, which can hold 2 values -

1)      Name – Supplier Name

2)      Number – Supplier Number

Initially both Supplier Number and Name range parameter will be disabled.

 

Selection = Name , will enable supplier Name parameter range.

Selection = Number , will enable supplier Number parameter range.

Implementation – Create 3 dummy parameter (1 displayable and 2 non displayable).

           

Parameter	Value Set	Enable	Display
Test1	VS0	Y	Y
Test2	VS1	Y	N
Test3	VS2	Y	N

Value Set - VS0, will hold below values

Parameter Test2 and Test3 :

 

Default Value  select decode(:$FLEX$.VS0,'NUMBER','Y',NULL) from dual

Test3 will have similar definition.

Value Set =  VS2

Default Value  select decode(:$FLEX$.VS0,'NAME','Y',NULL) from dual

Now include the below condition for Number and Supplier Value set definition –

Supplier Number Value set  -   :$FLEX$.VS1='Y'

Supplier Name Value set     -   :$FLEX$.VS2='Y'

          

Assign Responsibility using database

Requirement – To assign any responsibility to a fnd user using database. This is useful if we does not have sysadmin responsibility.

Solution  - Following API can be used to assign any responsibility to a user

  fnd_user_resp_groups_api.Insert_Assignment

                                    ( user_id                                   =>ln_user_id,

                                    responsibility_id                      => c1.responsibility_id,

                                    responsibility_application_id =>c1.responsibility_application_id,

                                    start_date                               =>SYSDATE,

                                    end_date                                 =>SYSDATE+1,

                                    description                              =>'null');

Example – Assign system administrator responsibility to user sandeep

DECLARE
  ln_user_id      NUMBER;

  CURSOR curr_add_resp
  is
    SELECT distinct responsibility_id,
      responsibility_application_id,
      security_group_id
    from fnd_user_resp_groups
    WHERE responsibility_id IN
      (SELECT responsibility_id
      from fnd_responsibility_vl
      WHERE responsibility_name IN ('System Administrator','Functional Administrator')
      )  
  ;
BEGIN  
              /* Fetch user id for fnd user */
              SELECT user_id
              INTO ln_user_id
              FROM fnd_user
              WHERE user_name='SKP';
             
              /*Responsibility Assignments */
              FOR c1 IN  curr_add_resp LOOP
                        BEGIN
                          fnd_user_resp_groups_api.Insert_Assignment
                                                            ( user_id =>ln_user_id,
                                                            responsibility_id => c1.responsibility_id,
                                                            responsibility_application_id =>c1.responsibility_application_id,
                                                            start_date =>sysdate,
                                                            end_date =>SYSDATE+100,
                                                            description =>'null');
                        EXCEPTION
                        WHEN OTHERS THEN
                          dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
                        END;
               END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
END;   

Create FNDUSER From database

Requirement – To create FNDUSER without having access to application.

Solution  - Following API can be used to create a FND USER.

fnd_user_pkg.createuser

                                    ( x_user_name =>lv_user_name,

                                      x_owner        =>lv_owner,

                                      x_unencrypted_password     =>lv_password );

Example – create FNDUSER “Sandeep” using database.

DECLARE

  lv_user_name    VARCHAR2(100):= 'SANDEEP';

  lv_owner        VARCHAR2(10) := 'CUST';

  lv_password     VARCHAR2(20) := 'sandeep'; 
 ln_user_id         NUMBER;

BEGIN

  /*Create FND User*/ 

    fnd_user_pkg.createuser

                                    ( x_user_name                                                =>lv_user_name,

                                      x_owner                                                        =>lv_owner,

                                      x_unencrypted_password                             =>lv_password

                        );

    BEGIN

                          SELECT user_id

                          INTO ln_user_id

                          FROM fnd_user

                          WHERE user_name=lv_user_name;  

    EXCEPTION

                        WHEN no_data_found THEN

                          dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);

    END;

    IF ln_user_id IS NOT NULL THEN

      dbms_output.put_line('Successfully created user : '||lv_user_name);

      dbms_output.put_line('user id = '||ln_user_id);

    END IF;

EXCEPTION

    WHEN OTHERS THEN

    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);

END;