SQL Developer: Set default date format to date and time

SQL Developer default display date field data without time:

We can set this default date format to date and time:

• Open SQL Developer, Open Menu Tools => Preferences
• From the Preferences dialog, Select option Database=> Click NLS
• NLS paremeters will display right side
• Update the "Date Format" parameter value to "DD-MON-RR HH24:MI:SS"
• Click "Ok" button to save and close the preference dialog

You have done, Now run the select statement and see the date filed values.

Terminate Employees using API

Following API's are used to terminating employees:

hr_ex_employee_api.actual_termination_emp
hr_ex_employee_api.update_term_details_emp
hr_ex_employee_api.final_process_emp

The API's have to be executed in the above order:


Example Code:

Declare
   Cursor c_emp_cur Is
         Select ppos.period_of_service_id
               ,ppos.object_version_number
               ,papf.person_type_id
               ,a.end_date
         From per_all_people_f papf
             ,per_periods_of_service ppos
             ,xxhr_resign_people_interface a
         Where papf.person_id = ppos.person_id
         And sysdate Between papf.effective_start_date and papf.effective_end_date
         And sysdate between ppos.date_start And coalesce(ppos.projected_termination_date,actual_termination_date,sysdate)
         And papf.business_group_id = 700
         And a.person_id = papf.person_id
         And a.end_date < '01-apr-2013';
   l_validate Boolean := False;
   l_period_of_service_id Number;
   l_object_version_number Number;
   l_actual_notice_period_date Date;
   l_effective_date date;
   l_supervisor_warning Boolean;
   l_event_warning Boolean;
   l_interview_warning Boolean;
   l_review_warning Boolean;
   l_recruiter_warning Boolean;
   l_asg_future_changes_warning Boolean;
   l_f_asg_future_changes_warning Boolean;
   l_pay_proposal_warning Boolean;
   l_dod_warning Boolean;
   l_final_process_date Date;
   l_org_now_no_manager_warning Boolean;
   l_entries_changed_warning Varchar2(255);
   l_f_entries_changed_warning Varchar2(255);
   l_alu_change_warning Varchar2(255);
   l_person_type_id Number;
   l_last_std_process_date_out Date;
For c_emp_rec In c_emp_cur
  Loop
     l_period_of_service_id := c_emp_rec.period_of_service_id;
     l_object_version_number := c_emp_rec.object_version_number;
     l_actual_notice_period_date := c_emp_rec.end_date;
     l_effective_date := c_emp_rec.end_date;
     l_person_type_id := c_emp_rec.person_type_id;
     Begin
     hr_ex_employee_api.actual_termination_emp
           (p_validate => l_validate
           ,p_effective_date => l_effective_date
           ,p_period_of_service_id => l_period_of_service_id
           ,p_object_version_number => l_object_version_number
           ,p_actual_termination_date => l_actual_notice_period_date
           ,p_last_standard_process_date => l_actual_notice_period_date
     --      ,p_person_type_id => l_person_type_id
           ,p_leaving_reason => 'RESS'
           ,p_last_std_process_date_out => l_last_std_process_date_out
           ,p_supervisor_warning => l_supervisor_warning
           ,p_event_warning => l_event_warning
           ,p_interview_warning => l_interview_warning
           ,p_review_warning => l_review_warning
           ,p_recruiter_warning => l_recruiter_warning
           ,p_asg_future_changes_warning => l_asg_future_changes_warning
           ,p_entries_changed_warning => l_entries_changed_warning
           ,p_pay_proposal_warning => l_pay_proposal_warning
           ,p_dod_warning => l_dod_warning
           ,p_alu_change_warning => l_alu_change_warning
           );
     hr_ex_employee_api.update_term_details_emp
          (p_validate    => l_validate
          ,p_effective_date          => l_effective_date
          ,p_period_of_service_id        => l_period_of_service_id
          ,p_object_version_number      => l_object_version_number
          ,p_accepted_termination_date  => l_effective_date
          ,p_leaving_reason           => 'RESS'
          ,p_notified_termination_date   => l_effective_date
          ,p_projected_termination_date  => l_effective_date
          );
     l_final_process_date := l_effective_date;

 hr_ex_employee_api.final_process_emp

         (p_validate  => l_validate

         ,p_period_of_service_id  => l_period_of_service_id

         ,p_object_version_number => l_object_version_number

         ,p_final_process_date    => l_final_process_date

         ,p_org_now_no_manager_warning => l_org_now_no_manager_warning

         ,p_asg_future_changes_warning => l_f_asg_future_changes_warning

         ,p_entries_changed_warning => l_f_entries_changed_warning

         );

     Exception

        When Others Then null;

     End;

  End Loop;

  commit;

End;

Cleaning FND_NODES Tables

Cleaning FND_NODES table

To clean the FND_NODES table use the following:

1. Shutdown all the services.
2. EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

run autoconfig on all the nodes starting with DB node.

Display the message in the status bar - Oracle Forms

Display the message in the status bar - Oracle Forms

To display the message in the status bar in Oracle Forms below mentioned command can be used,
Message('Message you want to display',NO_ACKNOWLEDGE);

Excel Unmerge Cells and copy the values to each cells

This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range

Sub UnMergeFill()
Dim cell As Range, joinedCells As Range
Application.ScreenUpdating = False
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

Application.ScreenUpdating = True
End Sub