How to submit RTF report using PL/SQL in Oracle Apps (Error: No completion options were requested)

Normally we use FND_REQUEST.SUBMIT_REQUEST to submit a concurrent program using PL/SQL.

If we use FND_REQUEST.SUBMIT_REQUEST to submit a RTF report related concurrent program that time we get the below error:

Error: No completion options were requested.

PL/SQL script completed normally but output not created in RTF format. 

**ADD_LAYOUT procedure should be called only when there is a layout associated to a concurrent program

We can attach a layout to the concurrent request by using another procedure ADD_LAYOUT which belongs to the same package FND_REQUEST.

For Example: This example shows – how to use ADD_LAYOUT in PL/SQL:

Step 1) Add  FND_REQUEST.ADD_LAYOUT

lb_layout  BOOLEAN; 

apps.fnd_global.apps_initialize(ln_user_id, ln_resp_id, ln_application_id);

**Use Apps Initialize

lb_layout :=

             apps.fnd_request.add_layout

                       (template_appl_name   => 'INV'     --> Data Template application short name

                        ,template_code               => 'INVMO'   --> Data Template short name (code)

                       ,template_language       => 'en'                   --> Data Template language

                       ,template_territory        => 'US'                   --> Data Template territory

                       ,output_format               => 'RTF'                --> Data Template output format.

                        ); 

Step 2) Add FND_REQUEST.SUBMIT_REQUEST

ln_request_id :=

               apps.fnd_request.submit_request

                                                (application     => 'INV'            --> Application Short Name

                                                 ,program        => 'INVMO'      --> Program Short Name

                                                 ,description    => NULL            --> Program Description

                                                 ,start_time     => NULL        

                                                 ,sub_request  => FALSE

                                                 ,argument1    => ln_organization_id        --> 1st parameter value

                                                 ,argument2    => lc_move_order_num    --> 2nd parameter value

                                                 ,argument3    => lc_move_order_num    --> 3rd parameter value

                                                 );

 

Step 3) Create concurrent program for above PL/SQL script and execute the concurrent program.

The stage registered as the first stage of this set is invalid.

  

  I have created request set using two concurrent programs.

  I ran the request set through SRS Window and I got below error.

  Error:  The stage registered as the first stage of this set is invalid.

  Action:

  In Request set Verify that the "Start Stage" is correct as follows:

   i)  Login to Oracle application and go to "System Administrator" responsibility

   ii)  Navigate to Request > Set

   iii) Query your request set

   iv) Click on the button "Link Stages"

   v)  Check the field "Start Stage" if empty then fill with the valid concurrent program name.

Then run your request set. 

RA Transaction error: The entered amount does not have the correct currency precision

At the creation of RA Transaction standard program went in error with below Error messages:

Check error in Error table: RA_Interface_Errors_All

Error# 1) The supplied amount must match unit selling price times the quantity when you do not use an AutoInvoice Clearing account.

Error# 2) The entered amount does not have the correct currency precision

Failed Reason: If you set the currency precision to 2 digits, you cannot use values with 3 digits for your transactions.

You used more than two precision in Unit Price.

Solution: Use the Manage Auto Invoice Lines spreadsheet to correct the amounts to 2-digit precision.

Use 2-digits precision in Unit Price.

SQL*Loader-128: unable to begin a session ORA-01005: null password given; logon denied

This error related to shell script file.

Error: Username:Password:

            SQL*Loader-128: unable to begin a session

            ORA-01005: null password given; logon denied

Solution: 

            Use $1 parameter  (UID_PWD=$1) in shell script (.prog) file. 

In shell script 1st five parameters are standard.

$0 - Shellscript name

$1 - Oracle username/password

$2 - User_id

$3 - Oracle Applications username

$4 - Concurrent request id

Also from $5, $6 ..... will be the shell script/user defined arguments.

FND-CP-ESP: Child: exec:: No such file or directory

This error is related to Shell Script file.
This issue happened due to below reasons:

1. No proper Soft-Link (Symbolic Link) created
2. Converted .prog file to Unix has accidentally converted in DOS format.

Check or use below steps to resolve the error:

1) Convert File format into UNIX
     dos2unix XXABC_Shell_Scrp.prog XXABC_Shell_Scrp.prog


2) Make sure that file has execution 777 permissions
    $XXABC_TOP/bin/XXABC_Shell_Scrp.prog
    =>chmod 777 XXABC_Shell_Scrp.prog


3) Check symbolic link 
     ln -s $FND_TOP/bin/fndcpesr/ $<PROD_TOP>/bin/<host file name without
extension>.

=>ln -s $FND_TOP/bin/fndcpesr XXABC_Shell_Scrp

Note: Use shell script file without Extension in Symbolic link creation.

PL/SQL Scripts to Register Concurrent Program, Executable, Parameters using API

Using API (FND_PROGAM)  you will create a concurrent program, executable, and add parameters to existing program.

Also delete a concurrent program and executable using API

Below are sample PLSQL scripts. 

--1) Register an Executable

DECLARE

    lc_executable_name      VARCHAR2(240)  := 'Daily Claim Report';

    lc_short_name                VARCHAR2(50)    := 'XX007CLAIM';

    lc_application_name     VARCHAR2(240)   := 'XXPO';  -- Short name

    lc_description                 VARCHAR2(240)   := 'Claim Report';

    lc_executable_method   VARCHAR2(240)  := 'PL/SQL Stored Procedure';

    lc_execution_file_name VARCHAR2(45)    := 'XX007_Claim.main';

    lc_language_code           VARCHAR2(5)       := 'US';   -- Default Value is 'US'  
    ln_executable_id              NUMBER            := 0;

BEGIN

       FND_PROGRAM.EXECUTABLE

                          (executable                     => lc_executable_name,

                           application                     => lc_application_name,

                           short_name                   => lc_short_name,

                           description                     => lc_description,

                           execution_method       => lc_executable_method,

                           execution_file_name    => lc_execution_file_name,

                           subroutine_name         => NULL,

                           icon_name                     => NULL,

                           language_code              => lc_language_code

                           );

        COMMIT;
        --
        -- To check whether Concurrent Executable is registered or not
        --
        BEGIN
    SELECT executable_id INTO ln_executable_id
            FROM fnd_executables
   WHERE executable_name = 'XX007CLAIM'
        AND application_id = (SELECT application_id FROM fnd_application
                                      WHERE application_short_name = 'XXPO');
        EXCEPTION
        WHEN others THEN
dbms_output.put_line('Error in Executable: '||SQLERRM);
END;
        --
--

EXCEPTION

        WHEN others THEN

        dbms_output.put_line('Error: '||SQLERRM);

END;

/

--2) Register a Concurrent Program

DECLARE 

  --

  lc_program                            VARCHAR2(240);

  lc_application                        VARCHAR2(10);

  lc_enabled                             VARCHAR2(5);

  lc_short_name                       VARCHAR2(30);

  lc_description                        VARCHAR2(240);

  lc_executable_short_name    VARCHAR2(30);

  lc_executable_application     VARCHAR2(10);

  lc_save_output                       VARCHAR2(5);

  lc_print                                  VARCHAR2(5);

  lc_style_required                   VARCHAR2(5);

  lc_use_in_srs                         VARCHAR2(5);

  lc_allow_disabled_values     VARCHAR2(5);

  lc_run_alone                          VARCHAR2(5);

  lc_output_type                       VARCHAR2(30);

  lc_enable_trace                      VARCHAR2(5);

  lc_restart                                VARCHAR2(5);

  lc_nls_compliant                    VARCHAR2(5);

  lc_language_code                  VARCHAR2(20);

  ln_executable_id                    NUMBER;

  lc_check                                 VARCHAR2(5) := NULL;

  --

BEGIN

  --

  lc_program                            := 'Daily Claim Report XX007';

  lc_application                        := 'XXPO';

  lc_enabled                             := 'Y';

  lc_short_name                       := 'XX007CLAIM';

  lc_description                        := 'OracleAppsDNA Test Program';

  lc_executable_short_name    := 'XX007CLAIM';

  lc_executable_application     := 'XXPO';

  lc_save_output                      := 'Y';

  lc_print                                  := 'Y';

  lc_style_required                   := 'N';

  lc_use_in_srs                         := 'Y';

  lc_allow_disabled_values     := 'N';

  lc_run_alone                          := 'N';

  lc_output_type                      := 'TEXT';

  lc_enable_trace                     := 'Y';

  lc_restart                               := 'Y';

  lc_nls_compliant                   := 'Y';

  lc_language_code                 := 'US';

  ln_executable_id                  := 0;

 --

 -- Executable is mandatory to create  Concurrent Program:

    BEGIN

                SELECT executable_id INTO ln_executable_id

                FROM fnd_executables

                WHERE executable_name = 'XX007CLAIM'

                  AND application_id = (SELECT application_id FROM fnd_application

                                                        WHERE application_short_name = 'XXPO');

    EXCEPTION

                  WHEN others THEN

                  dbms_output.put_line('Error in Executable: '||SQLERRM);

    END;

 --

 --Calling API to create concurrent program definition

 --

 apps.fnd_program.register

        (program                         => lc_program,

         application                     => lc_application,

         enabled                          => lc_enabled,

         short_name                    => lc_short_name,

         description                     => lc_description,

         executable_short_name => lc_executable_short_name,

         executable_application  => lc_executable_application,

         execution_options          => NULL,

         priority                           => NULL,

         save_output                   => lc_save_output,

         print                              => lc_print,

         cols                               => NULL,

         ROWS                          => NULL,

         STYLE                         => NULL,

         style_required               => lc_style_required,

         printer                           => NULL,

         request_type                  => NULL,

         request_type_application  => NULL,

         use_in_srs                      => lc_use_in_srs,

         allow_disabled_values   => lc_allow_disabled_values,

         run_alone                      => lc_run_alone,

         output_type                   => lc_output_type,

         enable_trace                  => lc_enable_trace,

         restart                            => lc_restart,

         nls_compliant                 => lc_nls_compliant,

         icon_name                     => NULL,--

         language_code                 => lc_language_code,

         mls_function_short_name   => NULL,

         mls_function_application   => NULL,

         incrementor                   => NULL,

         refresh_portlet               => NULL

         ); 

  --

  COMMIT;

  --To check whether Concurrent Program is registered or not

  --

         BEGIN

             SELECT 'Y'

             INTO lc_check

             FROM fnd_concurrent_programs

             WHERE concurrent_program_name = 'XX007CLAIM'

                  AND application_id = (SELECT application_id FROM fnd_application

                                                   WHERE application_short_name = 'XXPO');

             --

             DBMS_OUTPUT.put_line ('Concurrent Program Registered Successfully');

             --

        EXCEPTION

             WHEN NO_DATA_FOUND THEN

             dbms_output.put_line ('Concurrent Program Registration Failed');

        END;

END;

/

--3) Add parameters to created Concurrent Program.

       BEGIN
             FND_PROGRAM.PARAMETER

                    (program_short_name     => 'XX007CLAIM',
                     application               => 'XXPO',
                     sequence                  => 10,
                     parameter                 => 'Claim_Num',
                     description               => 'Claim Number',   --DEFAULT NULL,
                     enabled                     => 'Y',                       --DEFAULT 'Y',
                     value_set                  => '15 Characters',    -- this is existing value set name
                      default_type             => NULL,             -- DEFAULT NULL,
                     default_value           => NULL,             -- DEFAULT NULL,
                     required                    => 'N',                   -- DEFAULT 'N',
                     enable_security        => 'N',                   -- DEFAULT 'N',
                     range                        => NULL,             --DEFAULT NULL,
                     display                     => 'Y',                    --DEFAULT 'Y',
                     display_size             => 15,
                     description_size       => 50,
                     concatenated_description_size => 25,
                     prompt                   => 'Claim_Num',      --DEFAULT NULL,
                     token                      => NULL                  --DEFAULT NULL
                  );
              COMMIT;

       EXCEPTION
              WHEN others THEN
              dbms_output.put_line('Parameter not added.');

       END;
       /

--4) Delete a concurrent Program and Executable using API:

DECLARE
   v_short_name   VARCHAR2 (50) := 'XX007CLAIM';
   v_app_full_name VARCHAR2(80) := 'Custom PO';
BEGIN
   --
   -- API call to delete Concurrent Program:
   --
     fnd_program.delete_program ( v_short_name,v_app_full_name);
     dbms_output.put_line ('Concurrent Program successfully Deleted: ' || v_short_name);
   --
   --API call to delete Executable
   --
     fnd_program.delete_executable (v_short_name, v_app_full_name);
     dbms_output.put_line ('Executable successfully Deleted: ' || v_short_name);
   
END;
/

Using Trigger restrict to drop table

Using BEFORE DROP Trigger you will restrict to drop database objects.

When a user connected as XX tries to drop a database object, the database fires the trigger before dropping the object.

Example:

    

       1) Create table:

       

          Create Table xx.xx_test_007

               (emp_no NUMBER,

                emp_name VARCHAR2(200),

                doj DATE

               );

       Output: table XX.XX_TEST_007 created.

      2) Create Trigger   

          Schema Name:  XX

     

         CREATE OR REPLACE TRIGGER drop_trigger

         BEFORE DROP ON XX.SCHEMA

         BEGIN

            RAISE_APPLICATION_ERROR (

               num => -20000,

               msg => 'Cannot drop object');

         END;

        /

   

      Output: TRIGGER DROP_TRIGGER compiled

      3) Try to drop table PO.XX_TEST_007

   

        DROP TABLE XX.XX_TEST_007;

      Output:

               SQL Error: ORA-00604: error occurred at recursive SQL level 1

               ORA-20000: Cannot drop object

               ORA-06512: at line 2

               00604. 00000 -  "error occurred at recursive SQL level %s"

               *Cause:    An error occurred while processing a recursive SQL statement

                          (a statement applying to internal dictionary tables).

               *Action:   If the situation described in the next error on the stack

                          can be corrected, do so; otherwise contact Oracle Support.

Calling JAVA class using Oracle database

--1) JAVA Class use in PLSQL block using Function:

       Using PLSQL you can run Java stored procedures in the same way as PLSQL stored procedures.

       In oracle database, Java is usually invoked through PLSQL interface.

     

       -- 1) Create Java class

              CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Technical" AS

              public class Technical

             {

                public static String test007()

               {

                return "Oracle Technical";

               }

            };

            /

       -- 2) Create PLSQL Function to call Java class

             CREATE OR REPLACE FUNCTION Technicaltest007

             RETURN VARCHAR2 AS

                LANGUAGE JAVA NAME 'Technical.test007 () return java.lang.String';

             /

      -- 3) Write PLSQL Block to call created Function

     

             SET SERVEROUTPUT ON;

             DECLARE

                   lc_string VARCHAR2(300);

             BEGIN

                   lc_string := Technicaltest007();

                   dbms_output.put_line('The value of the string is:'||lc_string);

             END;

           /

PLSQL script to find out all Weekends of the Year

 FIND ALL SATURDAY AND SUNDAY OF YEAR

 Example:

     DECLARE
         ln_year NUMBER := &YEAR;
         ld_day  DATE;
     BEGIN
         ld_day := TO_DATE('01-JAN-'||TO_CHAR(ln_year), 'DD-MON-YYYY');
         WHILE ld_day < TO_DATE('31-DEC-'||TO_CHAR(ln_year), 'DD-MON-YYYY')
LOOP
           IF TO_CHAR(ld_day, 'D') IN (1,7) THEN
              dbms_output.put_line(TO_CHAR(ld_day));
           END IF;
           ld_day := ld_day + 1;
         END LOOP;
     END;
     /

Display row data in column using SQL query

By using Union ALL you will display row format records in column.

Below is an Example:
Follow below steps to convert row in column-

Step 1) Create Table and insert record:

         CREATE TABLE xx_emp_data ( emp_name VARCHAR2(50)
                                                              , emp_number VARCHAR2(50)
                                                              , salary NUMBER
                                                             );

          INSERT INTO xx_emp_data VALUES ('Sunil', 'EMP_007', 60000);
          COMMIT;

          SELECT * FROM xx_emp_data;

          Output:

         -------------------------------------------------------------
         EMP_NAME      EMP_NUMBER      SALARY
  -------------------------------------------------------------
          Sunil                    EMP_007                 60000
          -------------------------------------------------------------

Step 2) Using Union ALL you convert row into column

         SELECT 'Emp_Name' AS ColName,
                          Emp_name AS COLVALUE
          FROM xx_emp_data
          UNION ALL
          SELECT 'Emp_Number' AS ColName,
                          Emp_Number AS COLVALUE
          FROM xx_emp_data
          UNION ALL
          SELECT 'Salary' as ColName,
                          CAST(Salary AS VARCHAR(50)) AS COLVALUE
          FROM xx_emp_data;

        Output:

        ---------------------------------------
        COLNAME        COLVALUE
        ---------------------------------------
        Emp_Name           Sunil
        Emp_Number       EMP_007
        Salary                    60000
        ---------------------------------------