Query variable is adding extra space in Unix(ksh)

> Set linesize variable to avoid unwanted spaces

Ex:
--
lc_rti_rec=`sqlplus -s $orauser_pwd <<+ENDOFSQL+
set feedback off
set echo off
set pages 0
set heading off
set verify off
set termout off
set feed off
set trimspool on
set trim on
set linesize 100
select fcr.outfile_name
       ||'#'||
       (select instance_name FROM v\\$instance)
  from fnd_concurrent_requests fcr
 where fcr.priority_request_id     = $ln_request_id;
exit
+ENDOFSQL+`

lc_out_file=`echo $lc_rti_rec | cut -f1 -d'#'`
echo "Output Directory>"
echo $lc_out_file
echo " "
lc_instance=`echo $lc_rti_rec | cut -f2 -d'#'`
echo "Instance>"
echo $lc_instance
echo " "
--



Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues

wf_notification/wf_notification_out/wf_deferred Link

--
SELECT n.begin_date,
       n.status,
       n.mail_status,
       n.recipient_role,
       de.def_enq_time,
       de.def_deq_time,
       de.def_state,
       ou.out_enq_time,
       ou.out_deq_time,
       ou.out_state
  FROM applsys.wf_notifications n,
       (SELECT d.enq_time def_enq_time,
               d.deq_time def_deq_time,
               TO_NUMBER((SELECT VALUE
                           FROM TABLE(d.user_data.parameter_list)
                          WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
               msg_state def_state
          FROM applsys.aq$wf_deferred d
         WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
       (SELECT o.deq_time out_deq_time,
               o.enq_time out_enq_time,
               TO_NUMBER((SELECT str_value
                           FROM TABLE(o.user_data.header.properties)
                          WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
               msg_state out_state
          FROM applsys.aq$wf_notification_out o) ou
 WHERE n.notification_id = &NOTIFICATION_ID
   AND n.notification_id = de.d_notification_id(+)
   AND n.notification_id = ou.o_notification_id(+)
--
 Ex:
SELECT A.*
   FROM APPLSYS.AQ$WF_NOTIFICATION_OUT A
WHERE A.user_data.get_string_property('NOTIFICATION_ID') = 22048249;

--
wf_notification  package is also very handy

Ex: to get notification body/subject etc.

SELECT wf_notification.getbody(<Notification Id>)
  FROM dual;
--
SELECT wf_notification.getsubject(<Notification Id>)
  FROM dual;

--
Ref: https://me-dba.com/2009/09/10/notification-mailer-troubleshooting-part-ii/

Grade Rate API(s)

Use hr_grade_rate_value_api API to create/update/delete grade rate values

Ex: Update grade rate
--
DECLARE
   CURSOR get_details
   IS
      select pgr.effective_start_date
            ,pgr.effective_end_date
            ,pgr.object_version_number
            ,pgr.value
            ,pgr.maximum
            ,pgr.mid_value
            ,pgr.minimum
            ,pgr.sequence
            ,pgr.grade_rule_id
            ,pgr.currency_code
        from per_grades pg,
             pay_grade_rules_f pgr
       where pg.name = 'AK General|12|Standard XX'
         and pgr.grade_or_spinal_point_id = pg.grade_id  
         and trunc(sysdate) between pgr.effective_start_date  and pgr.effective_end_date
         ;

   l_effective_start_date    DATE   := NULL;
   l_effective_end_date      DATE   := NULL;
   l_grade_rule_id           NUMBER := NULL;
   l_object_version_number   NUMBER := NULL;
   l_err_msg                 VARCHAR2 (500) := NULL;
   l_value                   NUMBER;
   l_mid_value               NUMBER;
   l_max_vlaue               NUMBER;
   l_mim_value               NUMBER;
BEGIN

      FOR i IN get_details    LOOP
         --
         l_object_version_number := i.object_version_number;        
         l_max_vlaue             := 40000;
         l_mim_value             := 30000;
         l_mid_value              := 35000;
         l_value                     := 35000;
         --
         BEGIN
            hr_grade_rate_value_api.update_grade_rate_value (
               p_validate                    => FALSE,
               p_grade_rule_id           => i.grade_rule_id,
               p_effective_date          => TO_DATE ('10-MAY-2014', 'DD-MON-YYYY'),
               p_datetrack_update_mode   => 'UPDATE',--'CORRECTION',
               p_currency_code           => i.currency_code,
               p_maximum                  => l_max_vlaue,
               p_mid_value                => l_mid_value,
               p_minimum                 => l_mim_value,
               p_value                        => l_value,
               p_sequence                  => i.sequence,
               p_object_version_number   => l_object_version_number,
               p_effective_start_date    => l_effective_start_date,
               p_effective_end_date      => l_effective_end_date
            );
            COMMIT;
            DBMS_OUTPUT.put_line ('Grate Rate has been Updated: ' || i.grade_rule_id  );
         EXCEPTION
            WHEN OTHERS THEN
               l_err_msg := SQLERRM;
               DBMS_OUTPUT.put_line ('Inner Exception: ' || l_err_msg);
         END;
      END LOOP;
--
EXCEPTION
   WHEN OTHERS THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
--


Ref: My Experience/Oracle Metalink/User Guides/Different other blogs available/Colleagues