Tuesday, July 28, 2015

SQL to convert GMT to EST with Daylight Savings

SELECT to_date(
           to_char(
             to_timestamp_tz(
               to_char(to_timestamp('2015-11-01 06:00:00','yyyy-mm-dd hh24:mi:ss'),'YYYYMMDDHH24:MI:SS')||' '||'GMT'
                            ,'YYYYMMDDHH24:MI:SS TZR') at time zone 'America/New_York'
                   ,'YYYYMMDDHH24:MI:SS')
                 ,'YYYYMMDDHH24:MI:SS') datelight_GMT_EST
  FROM DUAL

Wednesday, April 29, 2015

Supplier Remittance Advice

For remittance advice Oracle fetches the email address from 'iby_external_payees_all' table.

When testing any changes to the remittance advice make sure to update the email before running the concurrent program.

SELECT   aps.segment1 "Vendor Number"
        ,aps.vendor_name "Vendor Name"
        ,iepa.remit_advice_email "Remittance Advice Email"
  FROM  ap_suppliers aps
       ,iby_external_payees_all iepa
 WHERE  iepa.payee_party_id = aps.party_id
   AND  aps.vendor_name= '<>';


You can also see the same at Payment Details -> Supplier / Supplier Site Level. Navigate to “Separate Remittance Advice Delivery” Tab.

Wednesday, February 25, 2015

Update Workflow Administrator Role in Oracle Applications 11i/R12 from backend

For looking at workflow details that are owned by other users or status diagram in Oracle Applications use the following update statement to update.

By default (in 11i & R12) this role is set to user sysadmin (In old versions 11.5.8 or prior, it used to set to *)

UPDATE wf_resources
   SET text = ’ * ’
 WHERE NAME = ’wf_admin_role’;

Thursday, July 31, 2014

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.