How to group numbers into ranges?

Requirement:

I have table with a numerical ID field. The values in this column may or may not be consecutive. For example

Data is

1, 2,3,4,5,8,9,10,15,16,17,18

Group should happen like –

Range1-5 count 5

 Range 6-7 count 0 because it’s missing

8-10 count 3

 11-14  0

 15-18 4

 

Test Table Design:

create table xxtest

(number1 number)

 

All numbers inserted in table:

Query:

SELECT   lb || '-' || ub RANGE, COUNT
    FROM (SELECT number1 + 1 AS lb,
                 LEAD (number1) OVER (ORDER BY number1) - 1 AS ub, 0 COUNT
            FROM xxtest)
   WHERE lb <= ub
UNION
SELECT   MIN (number1) || '-' || MAX (number1) number_range,
         MAX (number1) - MIN (number1) + 1 COUNT
    FROM (SELECT number1, number1 - ROW_NUMBER () OVER (ORDER BY number1) rn
            FROM xxtest)
GROUP BY rn
ORDER BY 1;

RANGE COUNT
1-5       5
11-14   0
15-18   4
6-7       0
8-10     3

 

 

Inserting a sequence number/counter into a table’s rows in SQL Loader program

Eg: Create Table T1
(seqCol number(4),
col1 varchar2(15),
col2 varchar2(12))
Create SQL Loader

LOAD DATA INFILE 'data_file_path_name/datafile.dat' APPEND
INTO TABLE T1
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
( seqCol SEQUENCE(1,1) ,
col1 CHAR "LTRIM(RTRIM(:col1))",
col2 CHAR "LTRIM(RTRIM(:col2))" )

The sequence can be as you wish to be started, such as;
SEQUENCE(n,i) – Sequence starts with n(integer) value and increment by i.
SEQUENCE(COUNT,i) – Sequence starts with the number of rows already in the table and increment by i.
SEQUENCE(MAX,i) – Sequence starts with the current maximum value for the column and increment by i.

Oracle User Hook Registration & Deletion handy Scripts

-- To Delete OAB User Hook
declare
    l_api_hook_call_id number;
    l_object_version_number number;
begin
    --
    select  api_hook_call_id
    ,       object_version_number
    into    l_api_hook_call_id
    ,       l_object_version_number
    from    hr_api_hook_calls
    where   call_package='XX_OAB_USER_HOOK_PKG'
    and call_procedure='CREATE_PRTT_ENRT_RESULT_A';
    --
    hr_api_hook_call_api.delete_api_hook_call(false,l_api_hook_call_id,l_object_version_number);
    --
    dbms_output.put_line('Delete User Hook '||sqlcode||'-'||sqlerrm);
    --
exception when others then
    dbms_output.put_line('Error Deleting User Hook '||sqlcode||' - '||sqlerrm);   
end;

declare
  ln_api_module_id  number;
begin
  --
  begin
      select api_module_id
      into   ln_api_module_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API MODULE ID '||ln_api_module_id);
      --
  exception when others then
     ln_api_module_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_module_id);
  end; 
  if ln_api_module_id <> 0 then 
      --
      -- Create all hook package body source code for one API module
      --
      hr_api_user_hooks_utility.create_hooks_one_module(ln_api_module_id);
      --
      -- Build the report text
      --
      hr_api_user_hooks_utility.write_one_errors_report(ln_api_module_id);
      --
      dbms_output.put_line('User Hook Pre-Process Registration Success');
      --   
  else
     --
     dbms_output.put_line('User Hook Pre-Process Registration Failed');
     --        
  end if;  
  -- 
end;

--To Register the usershook

declare
ln_api_hook_id              hr_api_hooks.api_hook_id%type;      
ln_api_hook_call_id         number;
ln_object_version_number    number;
begin
--
  begin
      select api_hook_id
      into   ln_api_hook_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API HOOK ID '||ln_api_hook_id);
      --
  exception when others then
     ln_api_hook_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_hook_id);
  end; 
--
hr_api_hook_call_api.create_api_hook_call
  (p_validate                     => false,
   p_effective_date               => trunc(sysdate),
   p_api_hook_id                  => ln_api_hook_id, --1390,
   p_api_hook_call_type           => 'PP',
   p_sequence                     => 3000,
   p_enabled_flag                 => 'Y',
   p_call_package                 => 'XXS_OAB_USER_HOOK_PKG',
   p_call_procedure               => 'CREATE_PRTT_ENRT_RESULT_A',
   p_api_hook_call_id             => ln_api_hook_call_id,
   p_object_version_number        => ln_object_version_number);
 --
 dbms_output.put_line('p_api_hook_call_id '||ln_api_hook_call_id);
 dbms_output.put_line('p_object_version_number '||ln_object_version_number);
 --
 commit;
 --
exception when others then
    dbms_output.put_line('API Excepiton '||sqlcode||sqlerrm);
end;

declare
  ln_api_module_id  number;
begin
  --
  begin
      select api_module_id
      into   ln_api_module_id
      from   hr_api_hooks
      where  hook_package like '%BEN_PRTT_ENRT_RESULT_BK1%'
      and    api_hook_type='AP';
      --
      dbms_output.put_line('VALID API MODULE ID '||ln_api_module_id);
      --
  exception when others then
     ln_api_module_id := 0;
     dbms_output.put_line(' NOT A VALID API HOOK ID '||ln_api_module_id);
  end; 
  if ln_api_module_id <> 0 then 
      --
      -- Create all hook package body source code for one API module
      --
      hr_api_user_hooks_utility.create_hooks_one_module(ln_api_module_id);
      --
      -- Build the report text
      --
      hr_api_user_hooks_utility.write_one_errors_report(ln_api_module_id);
      --
      dbms_output.put_line('User Hook Pre-Processor Registration Success');
      --   
  else
     --
     dbms_output.put_line('User Hook Pre-Processor Registration Failed');
     --        
  end if;  
  -- 
end;

Holiday Calendar no List of Values (LOV) for Earnings Type - Oracle Time and Labor(OTL)

Symptoms

Create Holiday Calendar > No List of Values (LOV) Available Under Earning Type Field

Errors - APP-HXT-39163: FRM-41830: List of values contains no entries

Cause

 The element used to pay holiday pay needs to be set up in the Element Time Information form HXTAE100

Solution

The Element Time Information > Earning Category must be defined as Absence Earning



Note Ref - 123702.1

Oracle Web ADI Pump Tables (Spreadsheet Interface)

select * from HR_PUMP_BATCH_EXCEPTIONS
   
select * from HR_PUMP_BATCH_LINES

"Out of Memory" error when viewing data from table with a lot of columns and rows - TOAD

Description

Error received when viewing data in Schema Browser | Tables | Data tab or from SQL Editor | Data Grid tab from a table that contains a large amount of columns and rows:

"Out of Memory"

This error does not occur when the number of column or rows displayed are limited

Resolution

WORKAROUND 1:
1. Select View | (Toad) Options | Oracle | General.
2. Decrease the OCI array buffer size to a smaller amount or to Oracle's default value of 25.

WORKAROUND 2:
View | Toad Options | Data Grid | Data - increase 'stop data fetches when available memory reaches __ MB' to 128mb.

WORKAROUND 3:
Use F9 (Execute as Statement) instead of F5 (Execute as Script) so that Toad will only fetch about 500 records rather than all the records.

WORKAROUND 4:
Limit rows returned to required data using 'WHERE' condition

 

HRMS Benefits query to fetch the beneficiaries - Oracle Advanced Benefits

Query to Fetch Beneficiaries

SELECT emp_papf.full_name employee_name,
       emp_papf.national_identifier employee_ssn,
       ppf.full_name beneficiary_name,
       ppf.national_identifier beneficiary_ssn
  FROM per_all_people_f ppf,
       per_contact_relationships pcr,
       ben_prtt_enrt_rslt_f pen,
       ben_pl_bnf_f pbn,
       per_all_people_f emp_papf
 WHERE 1 = 1
   AND pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
   AND pen.prtt_enrt_rslt_stat_cd IS NULL
   AND pen.enrt_cvg_thru_dt = hr_general.end_of_time
  -- AND pcr.personal_flag(+) = 'Y'
   AND pbn.bnf_person_id = pcr.contact_person_id(+)
   AND pbn.bnf_person_id = ppf.person_id(+)
   AND emp_papf.person_id = pcr.person_id
   AND :p_report_end_date BETWEEN pbn.effective_start_date
                              AND pbn.effective_end_date
   AND :p_report_end_date BETWEEN NVL (ppf.effective_start_date,
                                       :p_report_end_date
                                      )
                              AND NVL (ppf.effective_end_date,
                                       :p_report_end_date
                                      )
   AND :p_report_end_date BETWEEN NVL (emp_papf.effective_start_date,
                                       :p_report_end_date
                                      )
                              AND NVL (emp_papf.effective_end_date,
                                       :p_report_end_date
                                      )
   AND :p_report_end_date BETWEEN pen.effective_start_date
                              AND pen.effective_end_date     

Approve Salary Proposal Profile Option - Salary Administration Oracle Payroll

Add the function Salary Administration: Approve to the menu of responsibilities that should be able to approve salary proposals. Without this function, users can enter salary proposals but they cannot approve them.

Program and Plan Enrollment Requirement ad-hoc Queries - Oracle Advanced Benefits(OAB)

Program Enrollment Requirement Setup Query

SELECT bpf.NAME, blf.NAME life_event_name,
       blr.dys_aftr_end_to_dflt_num days_after_enrt_prd_for_dflt,
       hr_general.decode_lookup
                      ('BEN_CLS_ENRT_DT_TO_USE',
                       blr.cls_enrt_dt_to_use_cd
                      ) close_enrollment_date_to_use,
       hr_general.decode_lookup
                           ('BEN_ENRT_CVG_STRT',
                            blr.enrt_cvg_strt_dt_cd
                           ) enrollment_cvg_start_date,
       hr_general.decode_lookup
                              ('BEN_ENRT_CVG_END',
                               blr.enrt_cvg_end_dt_cd
                              ) enrollment_cvg_end_date,
       hr_general.decode_lookup ('BEN_RT_STRT',
                                 blr.rt_strt_dt_cd
                                ) rate_start_date,
       hr_general.decode_lookup ('BEN_RT_END',
                                 blr.rt_end_dt_cd) rate_end_date,
       hr_general.decode_lookup
                       ('BEN_ENRT_PERD_STRT',
                        blr.enrt_perd_strt_dt_cd
                       ) enrollment_period_start_date,
       hr_general.decode_lookup
                          ('BEN_ENRT_PERD_END',
                           blr.enrt_perd_end_dt_cd
                          ) enrollment_period_end_date
  FROM ben_lee_rsn_f blr,
       ben_popl_enrt_typ_cycl_f bpe,
       ben_pgm_f bpf,
       ben_ler_f blf
 WHERE bpe.popl_enrt_typ_cycl_id = blr.popl_enrt_typ_cycl_id
   AND bpf.pgm_id = bpe.pgm_id
   AND blf.ler_id = blr.ler_id;

Plan Enrollment Requirement Setup Query
  
SELECT bplf.NAME, blf.NAME life_event_name,
       blr.dys_aftr_end_to_dflt_num days_after_enrt_prd_for_dflt,
       hr_general.decode_lookup
                      ('BEN_CLS_ENRT_DT_TO_USE',
                       blr.cls_enrt_dt_to_use_cd
                      ) close_enrollment_date_to_use,
       hr_general.decode_lookup
                           ('BEN_ENRT_CVG_STRT',
                            blr.enrt_cvg_strt_dt_cd
                           ) enrollment_cvg_start_date,
       hr_general.decode_lookup
                              ('BEN_ENRT_CVG_END',
                               blr.enrt_cvg_end_dt_cd
                              ) enrollment_cvg_end_date,
       hr_general.decode_lookup ('BEN_RT_STRT',
                                 blr.rt_strt_dt_cd
                                ) rate_start_date,
       hr_general.decode_lookup ('BEN_RT_END',
                                 blr.rt_end_dt_cd) rate_end_date,
       hr_general.decode_lookup
                       ('BEN_ENRT_PERD_STRT',
                        blr.enrt_perd_strt_dt_cd
                       ) enrollment_period_start_date,
       hr_general.decode_lookup
                          ('BEN_ENRT_PERD_END',
                           blr.enrt_perd_end_dt_cd
                          ) enrollment_period_end_date,
       formula_name
  FROM ben_lee_rsn_f blr,
       ff_formulas_f fff,
       ben_popl_enrt_typ_cycl_f bpe,
       ben_pl_f bplf,
       ben_ler_f blf
 WHERE bpe.popl_enrt_typ_cycl_id = blr.popl_enrt_typ_cycl_id
   AND bplf.pl_id = bpe.pl_id
   AND fff.formula_id(+) = blr.enrt_cvg_end_dt_rl
   AND blf.ler_id = blr.ler_id

Delete Approved Salary Proposal - Oracle Payroll

DECLARE
     -- Start of Variable declarations, Initialize Variables with appropriate values to test the script
     -- VARCHAR2 size is set to 2000 by default, please enter target table name to retrieve the exact limit
     -- Input Variables
     V_PAY_PROPOSAL_ID               NUMBER;
     V_BUSINESS_GROUP_ID             NUMBER;
     V_OBJECT_VERSION_NUMBER         NUMBER;
     V_VALIDATE                      BOOLEAN;
     -- Output Variables
     V_SALARY_WARNING                BOOLEAN;
BEGIN
     -- Calling API HR_MAINTAIN_PROPOSAL_API.DELETE_SALARY_PROPOSAL
     HR_MAINTAIN_PROPOSAL_API.DELETE_SALARY_PROPOSAL(P_PAY_PROPOSAL_ID              => V_PAY_PROPOSAL_ID
                                                    ,P_BUSINESS_GROUP_ID            => V_BUSINESS_GROUP_ID
                                                    ,P_OBJECT_VERSION_NUMBER        => V_OBJECT_VERSION_NUMBER
                                                    ,P_VALIDATE                     => V_VALIDATE
                                                    ,P_SALARY_WARNING               => V_SALARY_WARNING
                                                    );
exception when others then;
     dbms_output.put_line.put_line('error : ' || sqlerrm);
END;