XLS Mime type in Oracle

Ensure that the ‘XLS’ in the output type of the concurrent program is not already been created by navigating using the following:
 

• System Administrator -> Concurrent -> Program -> Define
• Click on the drop down list of concurrent program output and ‘XLS’ format should not appear.

If entry doesn't exist then run the following insert statements:

INSERT INTO fnd_lookup_values
(lookup_type, LANGUAGE, lookup_code, meaning, description,
enabled_flag, start_date_active, end_date_active, created_by,
creation_date, last_updated_by, last_update_login,
last_update_date, source_lang, security_group_id,
view_application_id, territory_code, attribute_category,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
tag, leaf_node
)
VALUES ('CP_OUTPUT_FILE_TYPE', 'US', 'XLS', 'XLS', 'Excel Output',
'Y', NULL, NULL, 1,
TO_DATE ('31-MAY-02', 'DD-MON-RR'), 0, 0,
TO_DATE ('29-AUG-05', 'DD-MON-RR'), 'US', 0,
0, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL
);

INSERT INTO fnd_mime_types_tl
(file_format_code, mime_type, LANGUAGE, source_lang, created_by,
creation_date, last_updated_by,
last_update_date, last_update_login, description,
allow_client_encoding
)
VALUES ('XLS', 'application/vnd.ms-excel', 'US', 'US', 0,
TO_DATE ('22-DEC-13', 'DD-MON-RR'), 0,
TO_DATE ('22-DEC-13', 'DD-MON-RR'), 0, 'Microsoft Excel',
'Y'
);

COMMIT ;

Sql to convert Rows to a single column seperated with comma (,)

Following SQL shows how to convert multiple row output of a column and print all the output in a single column and values separated with a comma (,).

For real time example we took data on GL Journal Lines:

   SELECT RTRIM (XMLAGG (XMLELEMENT (e, segment1 || ',')).EXTRACT
                                                                   ('//text()'),
                       ','
                      ) segment1
           INTO l_company
           FROM (SELECT DISTINCT segment1
                            FROM gl_je_lines gjl,
                                 gl_code_combinations_kfv gcc
                           WHERE 1 = 1
                             AND gjl.code_combination_id =
                                                       gcc.code_combination_id
                             AND gjl.je_header_id = :l_je_header_id);

Word Wrap in Excel based Oracle Reports

Use the following oracle function to use the word wrap functionality in Oracle excel based reports.

 Create or Replace FUNCTION word_wrap (
      p_textin         IN   VARCHAR2 DEFAULT NULL,
      p_rmargin      IN   INTEGER DEFAULT 78,
      p_pad             IN   VARCHAR2 DEFAULT ' ',
      p_worddelim  IN   VARCHAR2 DEFAULT ' ',
      p_just             IN   VARCHAR2 DEFAULT 'LEFT'
   )
      RETURN VARCHAR2
/*=======================================================================
OBJECT NAME: Word_wrap
OBJECT TYPE: PUBLIC FUNCTION
DESCRIPTION: PROCEDURE USED TO WRAP THE STRING
PARAMETERS :
==========
NAME              TYPE     DESCRIPTION
----------------- -------- ---------------------------------------------
P_TEXTIN          IN      TEXT TO FORMAT - UP TO 32K
P_RMARGIN      IN      WRAP AT WHAT CCOL POSITION
P_PAD                IN      PAD THE TEXT AFTER WRAPPING WITH WHAT DEFAULT TO SPACE
P_WORDDELIM IN      WORDS DELIMEETED BY WHAT? DEFAULTS TO SPACE
P_JUST               IN      JUSTIFY HOW? LEFT,RIGHT,CENTER

RETURNS: L_TEXTOUT (WRAPPED TEXT)
=====================================================================*/
   IS
      l_textout      VARCHAR2 (32000);
      l_line         VARCHAR2 (32000);
      l_subline      VARCHAR2 (32000);
      l_sublinelen   INTEGER;
      l_nl           CHAR (1)         := CHR (10);
      l_sp           CHAR (1)         := CHR (32);
      l_textlen      INTEGER          := LENGTH (p_textin);
      l_just         CHAR (1)         := UPPER (SUBSTR (p_just, 1, 1));
      l_toffset      INTEGER          := 1;
      l_doffset      INTEGER          := 0;
      l_pad          VARCHAR2 (32000) := LPAD (l_sp, p_rmargin, l_sp);
   BEGIN
      LOOP
         EXIT WHEN l_toffset > l_textlen;
         l_line := SUBSTR (p_textin || l_pad, l_toffset, p_rmargin);
         l_doffset :=
            LEAST (REPLACE (TO_CHAR (INSTR (l_line, p_worddelim, -1), '00000'),
                            '00000',
                            '99999'
                           ),
                   REPLACE (TO_CHAR (INSTR (l_line, l_nl, -1), '00000'),
                            '00000',
                            '99999'
                           )
                  );
         l_subline := LTRIM (RTRIM (SUBSTR (l_line, 1, l_doffset - 1)));
         l_sublinelen := LENGTH (l_subline);
         l_toffset := l_toffset + l_doffset;

/******************************************************************************
CODE FOR JUSTIFICATION. BY DEFAULT THE JUSTIFICATION IS SET TO LEFT
*******************************************************************************/
         IF l_just = 'L'
         THEN
            l_textout :=
                  l_textout
               || l_subline
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_nl;
         ELSIF l_just = 'R'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_subline
               || l_nl;
         ELSIF l_just = 'C'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, (p_rmargin - l_sublinelen) / 2, p_pad)
               || l_subline
               || l_nl;
         END IF;
      END LOOP;

      RETURN l_textout;
   END word_wrap;

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

Example:
select word_wrap ('Vendor Name', 10) from dual

Read Only DFF Segment in Oracle Forms

• Create a new value set with the details as shown in the screenshot. 
• Make sure the 'Validation Type' is 'Special'
• Hit 'Edit Information'

 

• Make sure to enter the following:

Event: Edit
Function: FND SQL "BEGIN NULL; END;"

Event: Validate
Function: FND SQL "BEGIN NULL; END;"

• Assign the above value set to the DFF when you create the Descriptive Flexfield Segment.