bash: dos2unix: command not found

Either install it or use "sed" command instead as below

sed -i 's/\r$//' fileName

Configuration Values - APIs

-- Global and localized configuration settings provide the appropriate defaults for business groups
-- Business Rules are held in pqp_configuration_values table
-- HRMS > Other Definitions > Configuration Values
-- Here I am updating a configuration value
declare
--
ln_con_val_id  number; 
ln_bg          number;
ln_ovn         number;
lc_cat         varchar2(200);
lc_info        varchar2(200);
--
begin
--
   select pcv.PCV_INFORMATION_CATEGORY
         ,pcv.PCV_INFORMATION2
         ,pcv.business_group_id
         ,pcv.configuration_value_id
         ,pcv.object_version_number
     into lc_cat
         ,lc_info
         ,ln_bg
         ,ln_con_val_id
         ,ln_ovn     
     from pqp_configuration_types   pct
         ,pqp_configuration_modules pcm
         ,pqp_configuration_values  pcv
    where 1=1
      and pct.module_id = pcm.module_id
      and pcm.module_name = 'Assignment Budget Value Maintenance'
      and pct.description = 'Maintenance Configuration'
      and pcv.pcv_information_category = pct.configuration_type
      ;
   
    dbms_output.put_line('Before:   lc_cat: '||lc_cat||'  lc_info: '||lc_info||' in_ovn: '|| ln_ovn);
   --
   pqp_pcv_api.update_configuration_value(p_validate        => false
                                         ,p_effective_date                 => sysdate
                                         ,p_business_group_id              => ln_bg
                                         ,p_configuration_value_id         => ln_con_val_id
                                         ,p_pcv_information_category       => lc_cat
                                         ,p_pcv_information2               => 'Y'
                                         ,p_object_version_number          => ln_ovn);
                                        
   
    dbms_output.put_line('Configuration Value Updated');    
   
    commit;                             
--
exception
   when others then
      dbms_output.put_line('Error : '||sqlerrm);
      rollback;
end; 
--

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

Signing Limit - API

Unfortunately there is no any API available to enter signing limit.

Is There A Method To Enter Signing Limits Other Than Through The Form? (Doc ID 171837.1)
fix: This must be done through the form. There is no supported API or alternate method which can be used.

Through form >
Payable Manager > Employee > Signing Limit

Else,

Do the direct insert to the base table ?
           --
          insert into ap_web_signing_limits_all
                                        (document_type
                                        ,employee_id
                                        ,cost_center
                                        ,signing_limit
                                        ,last_update_date
                                        ,last_updated_by
                                        ,last_update_login
                                        ,creation_date
                                        ,created_by
                                        ,org_id
                                        )
                                  values('APEXP'
                                        ,ln_person_id
                                        ,lc_cost_center
                                        ,ln_signing_limit
                                        ,sysdate
                                        ,fnd_profile.value('USER_ID')
                                        ,fnd_profile.value('LOGIN_ID')
                                        ,sysdate
                                        ,fnd_profile.value('USER_ID')
                                        ,ln_org_id                                     
                                        );         

Ref: http://ebsanil.blogspot.co.uk/2012/09/oracle-employee-signingapproval-limits.html

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

Deployment - Web ADI

We will use the FNDLOAD utility to deploy the Web ADI to other instances.

> We just need to deploy the 'Integrator' because it also includes the realted content,layout and mappings and then the related Form Function (R12)

>> Integrator

select * --integrator_code, application_id,user_name
  from bne_integrators_vl vl
 where user_name like 'XXAK%%'
   and integrator_code like 'XXAK%';
 
fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bneintegrator.lct xxaktestadi_xintg.ldt bne_integrators integrator_asn="XXAKTEST_ADI_XINTG" integrator_code="XXAKTESTADI_XINTG"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bneintegrator.lct xxaktestadi_xintg.ldt

>> Form Function

select * --function_name
  from fnd_form_functions_vl
 where function_name like 'XXAK%';
 
fndload apps/<apps password> 0 y download $fnd_top/patch/115/import/afsload.lct xxaktestadi_func.ldt function function_name="XXAKTESTADI"
 
fndload apps/<apps password> 0 y upload $fnd_top/patch/115/import/afsload.lct xxaktestadi_func.ldt - warning=yes upload_mode=replace custom_mode=force

Please use below scripts if you have to deploy contents/layouts/mappings

>> Content 

select * --content_code
  from bne_content_cols_vl
 where content_code like '%XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnecont.lct xxaktestadi_cnt2.ldt bne_contents content_asn="XXAK" content_code="XXAKTESTADI_CNT2"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnecont.lct xxaktestadi_cnt2.ldt

>> Layout

select *--LAYOUT_CODE
  from bne_layouts_vl vl
 where integrator_code like 'XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnelay.lct xxaktestadi_lay.ldt bne_layouts layout_asn="XXAK" layout_code="XXAKTESTADI"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnelay.lct xxaktestadi_lay.ldt


>> Mapping

select * --mapping_code, integrator_code
  from bne_mappings_vl
 where mapping_code like 'XXAK%';

fndload apps/<apps password> 0 y download $bne_top/patch/115/import/bnemap.lct xxaktestadi_map.ldt bne_mappings mapping_asn="XXAK" mapping_code="XXAKTESTADI"

fndload apps/<apps password> 0 y upload $bne_top/patch/115/import/bnemap.lct xxaktestadi_map.ldt

Delete Custom Table Registration

--
BEGIN
/*
   AD_DD.DELETE_COLUMN('XXAK'
                                                  , 'XXAK_WEB_ADI_DOWNLAOD_TBL'
                                                 ,'ORDER_NUMBER'
                                                    );
*/
   AD_DD.DELETE_TABLE('XXAK' -- Application short Name
                                             ,'XXAK_WEB_ADI_DOWNLAOD_TBL'  -- Table Name
                                              );
 
   COMMIT;
 
   dbms_output.put_line('Deleted :');



EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line('Error :'||sqlerrm);
      ROLLBACK;
END;
--


Related Post: Register Custom Table

Register Custom Table

Navigation: Application Developer > Application > Database > Table



Registration
--
DECLARE
   vc_appl_short_name  VARCHAR2 (40) := 'XXAK';
   vc_tab_name              VARCHAR2 (32) := 'XXAK_WEB_ADI_DOWNLAOD_TBL';
   vc_tab_type               VARCHAR2 (50) := 'T';
   vn_next_extent          NUMBER        := 512; -- Default Value
   vn_pct_free               NUMBER        := 10;  -- Default Value
   vn_pct_used              NUMBER        := 70;  -- Default Value
   -- Table Details
   CURSOR cur_tab_details (c_tab_name varchar2)
   IS
    SELECT table_name
          ,next_extent
          ,pct_free
          ,pct_used                          
      FROM dba_tables
     WHERE table_name = c_tab_name;
   
   -- Column Details
   CURSOR cur_col_details (c_tab_name varchar2)
   IS
     SELECT column_name
           ,column_id
           ,data_type
           ,data_length
           ,nullable
      FROM all_tab_columns
     WHERE table_name = c_tab_name;
   
   --Primary keys details
   CURSOR cur_pri_key (c_tab_name varchar2)
   IS
    SELECT constraint_name
          ,table_name
      FROM all_constraints
     WHERE constraint_type = 'P'
       AND table_name = c_tab_name;
     
   -- primary key column details
   CURSOR cur_pri_key_details (c_tab_name varchar2, c_constraint_name varchar2)
   IS  
    SELECT column_name
          ,position
      FROM dba_cons_columns
     WHERE table_name = c_tab_name
       AND constraint_name = c_constraint_name;
 
BEGIN
   -- Register Table
   -- Get the table details
   FOR rec_tab_details IN cur_tab_details(vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name    => rec_tab_details.table_name,
                            p_tab_type      => vc_tab_type,
                            p_next_extent => NVL(rec_tab_details.next_extent, vn_next_extent),
                            p_pct_free      => NVL(rec_tab_details.pct_free, vn_pct_free),
                            p_pct_used     => NVL(rec_tab_details.pct_used, vn_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Register Column(s)
   -- Get the column details of the table
   FOR rec_col_details IN cur_col_details(vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => rec_col_details.column_name,
                             p_col_seq              => rec_col_details.column_id,
                             p_col_type             => rec_col_details.data_type,
                             p_col_width            => rec_col_details.data_length,
                             p_nullable             => rec_col_details.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns

   -- Register Primary Key
   -- Get the primary key detail of the table
   FOR rec_pri_key IN cur_pri_key(vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_appl_short_name,
                                  p_key_name             => rec_pri_key.constraint_name,
                                  p_tab_name             => rec_pri_key.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'N',
                                  p_enabled_flag         => 'Y'
                                 );
      -- Register Primary Key Columns
      -- Get the primary key column details
      FOR rec_pri_key_details IN  cur_pri_key_details (rec_pri_key.table_name,rec_pri_key.constraint_name)
      LOOP
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_appl_short_name,
                                      p_key_name             => rec_pri_key.constraint_name,
                                      p_tab_name             => rec_pri_key.table_name,
                                      p_col_name             => rec_pri_key_details.column_name,
                                      p_col_sequence         => rec_pri_key_details.position
                                     );
      END LOOP; -- End Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Table: '||vc_tab_name||' Registered');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error in Registration: '||SQLERRM);
      ROLLBACK;
END;
--


Related Post:  Delete Custom Table Registration

Delete/End Date Element Entry - HRMS APIs

--
declare
--
   ld_effective_start_date      date;
   ld_effective_end_date       date;
   lb_delete_warning             boolean;
   ln_element_entry_id          number;
   ln_object_version_number number;
   ln_assin_id                          number;
   --  
   lc_element_name       pay_element_types_f.element_name%type := 'AK Element';
   lc_emp_num               per_all_people_f.employee_number := '123425';

begin
--
     select pee.element_entry_id
           ,pee.object_version_number
           ,paf.assignment_id
       into ln_element_entry_id
           ,ln_object_version_number
           ,ln_assin_id
       from per_all_people_f        per
           ,per_all_assignments_f   paf
           ,pay_element_entries_f   pee
           ,pay_element_types_f_tl  petl
           ,pay_element_types_f     pet
      where per.employee_number = lc_emp_num
        and trunc(sysdate) between per.effective_start_date and per.effective_end_date
        and paf.person_id       = per.person_id
        and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
        and pee.assignment_id   = paf.assignment_id
        and pee.element_type_id = pet.element_type_id
        and trunc(sysdate) between pee.effective_start_date and pee.effective_end_date
        and petl.element_name   = lc_element_name
        and petl.language       = 'US'
        and pet.element_type_id = petl.element_type_id;
       
   /* Date Track modes
    DELETE             >> end date element entry
    DELETE_NEXT_CHANGE >> delete next changes
    FUTURE_CHANGE      >> delete future changes
    ZAP                >> completely remove element entry from the database
   */      

    pay_element_entry_api.delete_element_entry
      (p_validate                     => false
      ,p_datetrack_delete_mode        => 'DELETE'
      ,p_effective_date               => trunc(sysdate)
      ,p_element_entry_id             => ln_element_entry_id
      ,p_object_version_number        => ln_object_version_number
      ,p_effective_start_date         => ld_effective_start_date
      ,p_effective_end_date           => ld_effective_end_date
      ,p_delete_warning               => lb_delete_warning
      ) ;
 
--
commit;
--
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
      rollback;
end;

Update Element Entry - HRMS APIs

--
declare
--
cursor cur_input_name(c_ele_name   varchar2)
  is
    select piv.display_sequence
          ,piv.name
          ,piv.element_type_id
      from pay_element_types_f_tl petl
          ,pay_element_types_f    pet
          ,pay_input_values_f     piv
     where petl.element_name   = c_ele_name
       and petl.language       = 'US'
       and pet.element_type_id = petl.element_type_id
       and piv.element_type_id = pet.element_type_id
       order by piv.display_sequence;
--      
   ld_effective_start_date   date;
   ld_effective_end_date     date;
   ln_object_version_number  pay_element_entries_f.object_version_number %type;
   lb_update_warning         boolean;
   ln_screen_entry_value     pay_element_entry_values_f.screen_entry_value%type;
   ln_element_type_id        pay_element_types_f.element_type_id%type;
   ln_input_value_id1        pay_input_values_f.input_value_id%type;
   ln_input_value_id2        pay_input_values_f.input_value_id%type;
   ln_input_value_id         pay_input_values_f.input_value_id%type;
 
   -- DT API Out Variables
   lb_correction             boolean;                      
   lb_update                 boolean;                      
   lb_upover                 boolean;                        
   lb_upchin                 boolean;
   --  
   lc_element_name           pay_element_types_f.element_name%type := 'AK Element';
   lc_emp_num                per_all_people_f.employee_number := '123425';
   lc_dt_mode                varchar2(20);

begin

   --
   savepoint sv_update;
   --
 
   select pee.element_entry_id
         ,pee.object_version_number
     into ln_element_entry_id
         ,ln_object_version_number
     from per_all_people_f        per
         ,per_all_assignments_f   paf
         ,pay_element_entries_f   pee
         ,pay_element_types_f_tl  petl
         ,pay_element_types_f     pet
    where per.employee_number = lc_emp_num
      and trunc(sysdate) between per.effective_start_date and per.effective_end_date
      and paf.person_id       = per.person_id
      and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
      and pee.assignment_id   = paf.assignment_id
      and pee.element_type_id = pet.element_type_id
      and trunc(sysdate) between pee.effective_start_date and pee.effective_end_date
      and petl.element_name   = lc_element_name
      and petl.language       = 'US'
      and pet.element_type_id = petl.element_type_id;
     
      --Determine the Date Track Mode for Update..
 
   dt_api.find_dt_upd_modes
      ( p_effective_date        =>  trunc(sysdate)
      , p_base_table_name       =>  'PAY_ELEMENT_ENTRIES_F'
      , p_base_key_column       =>  'ELEMENT_ENTRY_ID'
      , p_base_key_value        =>  ln_element_entry_id
      , p_correction            =>  lb_correction
      , p_update                =>  lb_update
      , p_update_override       =>  lb_upover
      , p_update_change_insert  =>  lb_upchin
      );
                               
   if lb_upover or lb_upchin then
      lc_dt_mode := 'UPDATE_OVERRIDE';
   --elsif lb_upchin then
   --   p_dt_mode := 'UPDATE_CHANGE_INSERT';
   elsif lb_update then
      lc_dt_mode := 'UPDATE';
   elsif lb_correction then
      lc_dt_mode := 'CORRECTION';      
   end if;
 
   -- Get input value ids
   for rec_input_name in cur_input_name(lc_element_name) loop

      select piv.input_value_id
        into ln_input_value_id
        from pay_input_values_f    piv
       where piv.element_type_id = rec_input_name.element_type_id
         and piv.name            = rec_input_name.name
        ;
      if rec_input_name.display_sequence = 1 then  -- AK Value
     
         p_input_value_id1 := ln_input_value_id;
     
      elsif rec_input_name.display_sequence = 2 then  -- Employee Rate
     
         p_input_value_id2 := ln_input_value_id;    
   
      end if;
 
   end loop;
 
   -- Update Element Entry
   -- ------------------------------
   pay_element_entry_api.update_element_entry
     (     -- Input data elements
           -- -----------------------------
           p_validate                           => false, --true
           p_datetrack_update_mode              => lc_dt_mode,
           p_effective_date    => to_date('25-JUN-2012','DD-MON-YYYY'),
           p_business_group_id  => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
           p_element_entry_id                   => ln_element_entry_id,
           p_object_version_number              => ln_object_version_number,
           p_input_value_id1                    => ln_input_value_id1,
           p_entry_value1                       => null,
           p_input_value_id2                    => ln_input_value_id2,
           p_entry_value2                       => 10,
           -- Output data elements
           -- --------------------------------
           p_effective_start_date               => ld_effective_start_date,
           p_effective_end_date                 => ld_effective_end_date,              
           p_update_warning                     => lb_update_warning
     );
 
  dbms_output.put_line( '  API: pay_element_entry_api.update_element_entry successfull - Element Entry Id: ' );

--
commit;
--
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
      rollback to sv_update;
end;
--

File Transfer to Windows Server

# This script will show you how to connect to the Database and windows server connection

#                      #!/bin/ksh
#
# ***********************************************************
# Default Applications Object Library specific Parameters
# ***********************************************************
orauser_pwd=${1}
user_id=${2}
user_name=${3}
request_id=${4}
#
# ***********************************************************
# Program Parameters
# ***********************************************************

P_HOST=$5    #Windows Server ip/host
P_USER=$6    #Windows Server User
P_PASS=$7    #Windows Server Password
P_DIR1=$8    #File Dir
P_FILE=$9    #File Name
#
# ***********************************************************
# Local variables
# ***********************************************************
lc_inst='AKPROD'      

# ***********************************************************
# Connect to the Database and get the File Name/ Instance
# ***********************************************************
lc_sql_rec=`sqlplus -s $orauser_pwd <<+ENDOFSQL+
set echo off
set pages 0
set heading off
set termout off
set feed off
set trimspool on
select substr('$P_FILE',instr('$P_FILE','-',-1)+2)
       ||'#'||(select instance_name FROM v\\$instance)
  from dual;
exit
+ENDOFSQL+`


lc_file_name=`echo $lc_sql_rec | cut -f1 -d'#'`
echo "File Name>"
echo $lc_file_name
echo " "
lc_inst_name=`echo $lc_sql_rec | cut -f2 -d'#'`
echo "Instance>"
echo $lc_inst_name
echo " "


#Start the transfer procedure

#Go to the file directory
cd $P_DIR1

# ***********************************************************
#  Check if it is Prod instance
# ***********************************************************

if [ "$lc_inst_name" = "$lc_inst" ]; then
   lc_windows_dir='/AKFiles/LIVE'
   lc_file_name_win='LIVE_'$lc_file_name
else
   lc_windows_dir='/AKFiles/TEST'
   lc_file_name_win='TEST_'$lc_file_name
fi

echo "Windows File Name>"
echo $lc_file_name_win
echo " "

# ***********************************************************
#  Connect to the Windows Server
# ***********************************************************

ftp -n $P_HOST <<END_SCRIPT
quote USER $P_USER
quote PASS $P_PASS
cd $lc_windows_dir
ascii
put $lc_file_name $lc_file_name_win
get $lc_file_name_win $lc_file_name_win
bye
END_SCRIPT

if [ -f $lc_file_name ]; then

  echo "FTP successful"
  rm $lc_file_name_win

  exit 0

else

  echo "FTP failed"
  exit 1
fi

#*******************End of FTP *****************************

Wrapping Utility - Part 2

The DBMS_DDL package wraps a single PL/SQL unit, such as a package specification, package body, function, procedure, type specification, or type body.

It contains WRAP function and the CREATE_WRAPPED procedure.

Example: WRAP Function

If pl/sql unit is small, you can directly embed PL/SQL code into another PL/SQL code to wrap and compile it

Execute below script and you will same result as we have seen in part 1.

declare
   sql_text_t   dbms_sql.varchar2a;
   sql_wrap_t   dbms_sql.varchar2a;
begin
   -- Store the pl/sql code in the array or pl/sql table
   -- Each line of code will go into a new row
   sql_text_t (1) := 'CREATE OR REPLACE FUNCTION get_sysdate RETURN VARCHAR2 AS ';
   sql_text_t (2) := 'BEGIN ';
   sql_text_t (3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
   sql_text_t (4) := 'END get_sysdate;';

   -- now compile and wrap the code
   sql_wrap_t := sys.dbms_ddl.wrap (ddl => sql_text_t,
                                    lb  => 1,
                                    ub  => sql_text_t.count
                                   );

   -- display each line of the wrapped code
   for i in 1 .. sql_wrap_t.count
   loop
      dbms_output.put_line (sql_wrap_t (i));
   end loop;
exception
   when others then
         dbms_output.put_line('Error: '||sqlerrm);
end;
/

Example: CREATE_WRAPPED procedure

Now if the PL/SQL unit is large having several thousand lines, in that case it is better to compile the code into the database first and then execute a PL/SQL code to wrap and compile it. 

Suppose you have a packaged procedure name XXAK_WRAP_TEST_PKG, already compiled in DB.

Describe this package

select text
  from all_source
 where name = 'XXAK_WRAP_TEST_PKG'
   and type = 'PACKAGE BODY'
   and owner = 'APPS';

Now use the CREATE_WRAPPED procedure to wrap this program unit.

Execute the below script to do the job

--
declare
   sql_text_t      dbms_sql.varchar2a;
   v_object_type   varchar2 (40);
   v_object_name   varchar2 (60)      := 'XXAK_WRAP_TEST_PKG';

   cursor c_package_body (v_package_name varchar2)
   is
      select text
        from all_source
       where name = v_package_name
         and type = 'PACKAGE BODY'
         and owner = 'APPS';

   cursor c_procedure (v_procedure varchar2)
   is
      select text
        from all_source
       where name = v_procedure
         and type = 'PROCEDURE'
         and owner = 'APPS';
begin
   begin
      select object_type
        into v_object_type
        from all_objects
       where object_name = v_object_name
         and status = 'VALID';
   exception
      when too_many_rows then
         -- It is recommended to wrap only package body, so that other user can see the specification and use the public part of it
         -- if it is more than one row object is having Specification and Body both
         v_object_type := 'PACKAGE BODY';
      when others then
         dbms_output.put_line('Error while Checking Object Type: '||sqlerrm);
   end;

   if (v_object_type = 'PROCEDURE' or v_object_type = 'FUNCTION') then

      open c_procedure (v_object_name);

      -- get each line of code into each array row, i.e. pl/sql table
      fetch c_procedure
      bulk collect into sql_text_t;

      close c_procedure;

   elsif v_object_type = 'PACKAGE BODY' then

      open c_package_body (v_object_name);

      fetch c_package_body
      bulk collect into sql_text_t;

      close c_package_body;

   end if;

   if sql_text_t.count > 0 then
      -- Code stored in the database does not contain the ddl text, create or replace, we have to add this explicitly
      sql_text_t (1) := 'CREATE OR REPLACE ' || sql_text_t (1);

      -- This will compile the PL/SQL unit again and finally wrap it
      dbms_ddl.create_wrapped (ddl  => sql_text_t,
                                   lb   => 1,
                                   ub   => sql_text_t.count
                                 );
   end if;
   dbms_output.put_line(v_object_name||' is wrapped now.');
exception
   when others then
      dbms_output.put_line('Error while wrapping: '||sqlerrm);
end;
/

Describe the package XXAK_WRAP_TEST_PKG again.

Done! :)

Wrapping Utility - Part 1

Wrapping is the process of hiding PL/SQL source code. It helps to protect your source code from others who might misuse it :)

Oracle has provided the WRAP utility to encrypt source code for this purpose. The WRAP utility can be invoked on the operating system, like Windows, command line as it comes with the Oracle client. It can also be invoked within the database using DBMS_DDL package (Part 2)

Normally PL/SQL code, such as a package specification, package body, function, procedure, type specification, or type body is wrapped using the wrap utility given by Oracle. 

Note: It does not wrap PL/SQL content in anonymous blocks or triggers or non-PL/SQL code 

This is a command line tool and syntax is:

wrap iname=input_file [oname=output_file]

input_file is the name of the file which is containing your source code, it can be with extension or without extension.

wrap iname=/mydir/myfile

wrap iname=/mydir/myfile.sql

output_file is the name of the wrapped file that is created. The defaults extension of output file  is .plb.

wrap iname=/mydir/myfile 

>  Output file will be myfile.plb

wrap iname=/mydir/myfile oname=/yourdir/yourfile.out

>Test: Create a test function Get_Sysdate and store it with name Get_sysdate.sql in your hard drive

Here, I have stored it in directory: D:\Wrapping Utility

Open Command Prompt > Start - Run – cmd - Enter

Goto the directory where you have stored your source file

 cd  D:\Wrap Utility

run below command to wrap the file

 wrap iname=Get_Sysdate

It will wrap the source code and create a new file Get_Sysdate with default extension .plb

Open the file, you can see the wrapped code.

Now compile the wrapped file

Now check the source code in DB

  select *

    from all_source

   where name = 'GET_SYSDATE'

     and owner = 'APPS';

Now execute the function

select get_sysdate

  from dual;

Next : 'Wrapping Utility - Part 2'

OTL- Employee's Time Card Details

select hts.resource_id                      "Person id"

      ,to_char(hts.start_time,'Mon-DD-YYYY') "Start Date"

      ,to_char(hts.stop_time,'Mon-DD-YYYY')  "End Date"

      ,htb2.measure                          "Hours"

      ,petf.element_name                     "Hours Type"

      ,hta.attribute12                       "Description"

      ,htb.comment_text                      "Comments"

  from hxc_time_building_blocks  htb,

       hxc_time_building_blocks  htb1,

       hxc_time_building_blocks  htb2,

       hxc_time_attribute_usages htau,

       hxc_time_attributes       hta,

       hxc_timecard_summary      hts,

       pay_element_types_f       petf

 where htb1.parent_building_block_id= htb.time_building_block_id

   and htb1.parent_building_block_ovn = htb.object_version_number

   and htb.date_to  = hr_general.end_of_time

   and htb.scope    = 'TIMECARD'

   and htb1.scope   = 'DAY'

   and htb1.date_to = hr_general.end_of_time

   and htb2.parent_building_block_id= htb1.time_building_block_id

   and htb2.parent_building_block_ovn= htb1.object_version_number

   and htb2.scope   = 'DETAIL'

   and htb2.date_to = hr_general.end_of_time

   and htau.time_building_block_id  = htb2.time_building_block_id

   and htau.time_building_block_ovn = htb2.object_version_number

   and htau.time_attribute_id       = hta.time_attribute_id

   and hts.start_time  = htb.start_time

   and hts.resource_id = htb.resource_id

   and to_char(petf.element_type_id) = (substr(hta.attribute_category,11,length(hta.attribute_category)))

   and hts.resource_id                       = :p_person_id

   and to_char(hts.start_time,'DD-MON-YYYY') = :p_start_date

   and hta.attribute_category is not null

   order by htb1.start_time;

-- More Specific with element name

select *

  from (select tt.resource_id                 "Person id"

              ,rt.status_name                 "Status"

              ,tt.start_date                  "Start Date"

              ,tt.stop_date                   "Stop Date"

              ,round (tt.hours_worked, 2)     "Hours"

              ,round (tt.hours_worked, 2) as hours

              ,td.detail_attribute12          "Description"

              ,substr(rt.timecard_comment,40) "Comments"

              ,(select alias_value_name

                  from hxc_alias_values_v

                 where alias_definition_id = 1051 -->>

                   and attribute1 =

                       substr (td.detail_attribute_category,11,length(td.detail_attribute_category)))

                       as ot_type

         from hxc_resource_total_time_v tt,

              hxc_resource_timecards_v rt,

              hxc_timecard_details_v td

        where tt.time_id = rt.timecard_id

          and tt.detail_id = td.detail_timecard_id

          and td.detail_bld_blk_info_type_id = 1

          and tt.resource_id   = :p_person_id  

          and to_char(tt.start_date,'DD-MON-YYYY') = :p_start_date

          and tt.start_date >=

              trunc((last_day (add_months (sysdate, -3)) + 1))) pivot                          (sum (hours) 

             for ot_type 

             in ('01. Half Time Hours (0.5)' "Half Time"

                ,'02. Single Time Hours (1.0)' "Single Time"

                ,'03. Time and a Quarter (1.25)' "Time And a Quarter"

                ,'04. Time and a Half (1.5)' "Time And a Half"

                ,'05. Double Time Hours (2.0)' "Double Time"

                ,'06. Time and three quarters (1.75)' "Time And Three Quarters"

                ,'07. EW - Premium @ 1/3 Third of Time' "Premium Third"

                ,'08. EW - Premium @ 1/2 Half of Time'  "Premium Half"

                ,'09. EW - Premium @ Single'  "Uremium Single"

                ,'10. EW - Unsocial Hours @ 1/5 Fifth of Time' "Unsocial"

               ));