PL/SQL API time.calc_win

Summary

• procedure calc_win(wpart in varchar2, wname in varchar2, ts in out timestamp with time zone, mts in timestamp with time zone) (Unsupported)
• function calc_win(wpart in varchar2, wname in varchar2, ts in out timestamp with time zone, mts in timestamp with time zone) return timestamp with time zone (Unsupported)

Calculate the first time a time window opens after a given start datetime.

note

The function always stops at 24 months in the future from ts. mts can be used to further restrict this period.

Examples

Using time.calc_win
       create or replace script "IS_WINDOW_OPEN"

       ( "P_BUSINESS_DATE" in out timestamp with time zone
                           default             from_tz(systimestamp, to_char(systimestamp, 'TZR'))
       , "P_TIME_PART"     in varchar2(30)     not null
                           constraint          "C_TIME_PART"
                           prequery            0 rows
                           check               (select name into P_TIME_PART from rs_partitions)
       , "P_TIME_WINDOW"   in varchar2(30)     not null
                           constraint          "C_TIME_WINDOW"
                           prequery            0 rows
                           check               (select name into P_TIME_WINDOW from rs_time_windows where partition=P_TIME_PART)
       )
       format              "SYSJCS"."GENERIC"
       no verify
       as declare
         is_open timestamp with time zone;
       begin
         is_open := p_business_date;
         sysjcs."TIME".calc_win ( P_TIME_PART
                                , P_TIME_WINDOW
                                , is_open
                                );
         if to_char(is_open, 'DD-MM-YYYY') = to_char(p_business_date, 'DD-MM-YYYY')
         then
           jcs_out.put_line('Requested date: '||to_char(p_business_date, 'DD-MM-YYYY'));
           jcs_out.put_line('Time window '||P_TIME_PART||'.'||P_TIME_WINDOW||' is open at: '||to_char(is_open, 'DD-MM-YYYY'));
           jcs_out.put_line('DATE IS OK');
         else
           jcs_out.put_line('Requested date: '||to_char(p_business_date, 'DD-MM-YYYY'));
           jcs_out.put_line('Time window '||P_TIME_PART||'.'||P_TIME_WINDOW||' is open at: '||to_char(is_open, 'DD-MM-YYYY'));
           jcs_out.put_line('DATE IS OUTSIDE WINDOW '||P_TIME_PART||'.'||P_TIME_WINDOW);
         end if;
       end;

Using time.calc_win

plsqlTopic

onsiteTopic