eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_TRB_API_PROCEDURES dependencies on OTA_TIMEZONE_UTIL

Line 474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))

470:
471: cursor chk_dates_tsr(crs_start_date date,crs_end_date date,crs_timezone varchar2, crs_start_time varchar2, crs_end_time varchar2) is
472: select 1
473: from dual
474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);

Line 475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)

471: cursor chk_dates_tsr(crs_start_date date,crs_end_date date,crs_timezone varchar2, crs_start_time varchar2, crs_end_time varchar2) is
472: select 1
473: from dual
474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
479:

Line 476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)

472: select 1
473: from dual
474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
479:
480:

Line 477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))

473: from dual
474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
479:
480:
481: l_start_date date;

Line 478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);

474: where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
475: >= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
476: --to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
477: and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
478: >= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
479:
480:
481: l_start_date date;
482: l_end_date date;

Line 555: and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)

551: cursor chk_dates_tsr is
552: select 1
553: from ota_suppliable_resources
554: where supplied_resource_id = p_supplied_resource_id
555: and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
556: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
557: >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
558:
559:

Line 556: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)

552: select 1
553: from ota_suppliable_resources
554: where supplied_resource_id = p_supplied_resource_id
555: and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
556: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
557: >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
558:
559:
560: /* commented for bug6078493

Line 557: >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);

553: from ota_suppliable_resources
554: where supplied_resource_id = p_supplied_resource_id
555: and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
556: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
557: >= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
558:
559:
560: /* commented for bug6078493
561: cursor chk_dates_tsr is

Line 565: and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)

561: cursor chk_dates_tsr is
562: select 1
563: from ota_suppliable_resources
564: where supplied_resource_id = p_supplied_resource_id
565: and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
566: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
567: >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
568: */
569: /*

Line 566: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)

562: select 1
563: from ota_suppliable_resources
564: where supplied_resource_id = p_supplied_resource_id
565: and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
566: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
567: >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
568: */
569: /*
570: cursor chk_dates_tsr is

Line 567: >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);

563: from ota_suppliable_resources
564: where supplied_resource_id = p_supplied_resource_id
565: and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
566: and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
567: >= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
568: */
569: /*
570: cursor chk_dates_tsr is
571: select 1

Line 917: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))

913: select 1
914: from ota_resource_bookings trb
915: where trb.supplied_resource_id = p_supplied_resource_id
916: and (
917: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
918: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
919: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921: )

Line 918: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

914: from ota_resource_bookings trb
915: where trb.supplied_resource_id = p_supplied_resource_id
916: and (
917: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
918: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
919: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921: )
922: /*or

Line 919: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

915: where trb.supplied_resource_id = p_supplied_resource_id
916: and (
917: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
918: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
919: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921: )
922: /*or
923: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

916: and (
917: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
918: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
919: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921: )
922: /*or
923: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
924: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

Line 924: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

920: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
921: )
922: /*or
923: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
924: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
925: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
926: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
927: )*/)
928: and (p_resource_booking_id is null

Line 926: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

922: /*or
923: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
924: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
925: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
926: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
927: )*/)
928: and (p_resource_booking_id is null
929: or (p_resource_booking_id is not null
930: and p_resource_booking_id <> trb.resource_booking_id));

Line 941: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

937: from ota_resource_bookings trb
938: where trb.supplied_resource_id = p_supplied_resource_id
939: and (
940: (p_required_date_from between
941: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
942: and
943: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
944: or
945: (p_required_date_to between

Line 943: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

939: and (
940: (p_required_date_from between
941: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
942: and
943: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
944: or
945: (p_required_date_to between
946: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
947: and

Line 946: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

942: and
943: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
944: or
945: (p_required_date_to between
946: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
947: and
948: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
949: or
950: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

Line 948: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

944: or
945: (p_required_date_to between
946: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
947: and
948: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
949: or
950: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
951: and
952: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

Line 950: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

946: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
947: and
948: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
949: or
950: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
951: and
952: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
953: )
954: --and trb.status = 'C'

Line 952: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

948: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
949: or
950: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
951: and
952: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
953: )
954: --and trb.status = 'C'
955: and (p_resource_booking_id is null
956: or (p_resource_booking_id is not null

Line 1003: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

999: and (rec.required_date_from <> rec.required_date_to)) then
1000: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1001: --check time overlap
1002: if (
1003: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1004: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1005: or
1006: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1007: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1004: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

1000: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1001: --check time overlap
1002: if (
1003: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1004: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1005: or
1006: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1007: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1008: ) then

Line 1006: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1002: if (
1003: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1004: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1005: or
1006: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1007: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1008: ) then
1009:
1010: l_return_value :='N';

Line 1007: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1003: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1004: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1005: or
1006: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1007: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1008: ) then
1009:
1010: l_return_value :='N';
1011: --return l_return_value;

Line 1027: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and

1023:
1024: --since first cursor didn't give problem this means new and old record dates cannot be equal
1025: --and time
1026: if(
1027: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1028: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1029: or
1030: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1031: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

Line 1028: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1024: --since first cursor didn't give problem this means new and old record dates cannot be equal
1025: --and time
1026: if(
1027: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1028: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1029: or
1030: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1031: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1032: )

Line 1030: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and

1026: if(
1027: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1028: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1029: or
1030: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1031: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1032: )
1033: ) then
1034:

Line 1031: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1027: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1028: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1029: or
1030: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1031: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1032: )
1033: ) then
1034:
1035: l_return_value :='N';

Line 1045: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1041: return l_return_value;
1042: end if;
1043: --bug 5116223
1044: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1045: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1046: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1047: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1048: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1049: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1046: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then

1042: end if;
1043: --bug 5116223
1044: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1045: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1046: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1047: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1048: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1049: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1050: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1048: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1044: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1045: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1046: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1047: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1048: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1049: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1050: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1051: ) then
1052: l_return_value :='Y';

Line 1050: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1046: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1047: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1048: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1049: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1050: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1051: ) then
1052: l_return_value :='Y';
1053: return l_return_value;
1054: end if;

Line 1117: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))

1113: select 1
1114: from ota_resource_bookings trb
1115: where trb.supplied_resource_id = p_supplied_resource_id
1116: and (
1117: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1118: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1119: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121: )

Line 1118: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1114: from ota_resource_bookings trb
1115: where trb.supplied_resource_id = p_supplied_resource_id
1116: and (
1117: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1118: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1119: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121: )
1122: /*or

Line 1119: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1115: where trb.supplied_resource_id = p_supplied_resource_id
1116: and (
1117: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1118: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1119: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121: )
1122: /*or
1123: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

1116: and (
1117: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1118: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1119: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121: )
1122: /*or
1123: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1124: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

Line 1124: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1120: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1121: )
1122: /*or
1123: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1124: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1125: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1126: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1127: )*/)
1128: and trb.resource_booking_id = p_target_resource_booking_id;

Line 1126: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

1122: /*or
1123: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1124: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1125: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1126: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1127: )*/)
1128: and trb.resource_booking_id = p_target_resource_booking_id;
1129:
1130:

Line 1139: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1135: from ota_resource_bookings trb
1136: where trb.supplied_resource_id = p_supplied_resource_id
1137: and (
1138: (p_required_date_from between
1139: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1140: and
1141: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1142: or
1143: (p_required_date_to between

Line 1141: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

1137: and (
1138: (p_required_date_from between
1139: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1140: and
1141: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1142: or
1143: (p_required_date_to between
1144: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1145: and

Line 1144: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1140: and
1141: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1142: or
1143: (p_required_date_to between
1144: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1145: and
1146: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1147: or
1148: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

Line 1146: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

1142: or
1143: (p_required_date_to between
1144: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1145: and
1146: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1147: or
1148: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1149: and
1150: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

Line 1148: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

1144: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1145: and
1146: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1147: or
1148: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1149: and
1150: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1151: )
1152: and trb.resource_booking_id = p_target_resource_booking_id;

Line 1150: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

1146: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1147: or
1148: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1149: and
1150: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1151: )
1152: and trb.resource_booking_id = p_target_resource_booking_id;
1153:
1154:

Line 1198: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

1194: and (rec.required_date_from <> rec.required_date_to)) then
1195: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1196: --check time overlap
1197: if (
1198: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1199: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1200: or
1201: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1202: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1199: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

1195: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1196: --check time overlap
1197: if (
1198: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1199: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1200: or
1201: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1202: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1203: ) then

Line 1201: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1197: if (
1198: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1199: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1200: or
1201: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1202: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1203: ) then
1204:
1205: l_return_value :='N';

Line 1202: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1198: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1199: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1200: or
1201: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1202: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1203: ) then
1204:
1205: l_return_value :='N';
1206: --return l_return_value;

Line 1223: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and

1219:
1220: --since first cursor didn't give problem this means new and old record dates cannot be equal
1221: --and time
1222: if(
1223: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1224: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1225: or
1226: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1227: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

Line 1224: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1220: --since first cursor didn't give problem this means new and old record dates cannot be equal
1221: --and time
1222: if(
1223: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1224: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1225: or
1226: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1227: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1228: )

Line 1226: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and

1222: if(
1223: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1224: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1225: or
1226: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1227: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1228: )
1229: ) then
1230:

Line 1227: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1223: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1224: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1225: or
1226: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1227: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1228: )
1229: ) then
1230:
1231: l_return_value :='N';

Line 1241: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1237: return l_return_value;
1238: end if;
1239: --bug 5116223
1240: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to )and rec.timezone_code <> p_timezone) then
1241: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1242: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1243: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1244: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1245: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1242: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then

1238: end if;
1239: --bug 5116223
1240: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to )and rec.timezone_code <> p_timezone) then
1241: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1242: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1243: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1244: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1245: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1246: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1244: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1240: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to )and rec.timezone_code <> p_timezone) then
1241: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1242: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1243: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1244: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1245: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1246: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1247: ) then
1248: l_return_value :='Y';

Line 1246: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1242: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1243: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1244: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1245: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1246: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1247: ) then
1248: l_return_value :='Y';
1249: return l_return_value;
1250: end if;

Line 1335: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))

1331: select 1
1332: from ota_resource_bookings trb
1333: where trb.supplied_resource_id = p_supplied_resource_id
1334: and (
1335: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1336: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1337: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339: )

Line 1336: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1332: from ota_resource_bookings trb
1333: where trb.supplied_resource_id = p_supplied_resource_id
1334: and (
1335: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1336: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1337: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339: )
1340: /*or

Line 1337: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1333: where trb.supplied_resource_id = p_supplied_resource_id
1334: and (
1335: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1336: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1337: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339: )
1340: /*or
1341: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

1334: and (
1335: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1336: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1337: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339: )
1340: /*or
1341: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1342: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

Line 1342: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1338: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1339: )
1340: /*or
1341: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1342: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1343: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1344: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1345: )*/)
1346: and trb.status = 'C'

Line 1344: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)

1340: /*or
1341: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1342: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1343: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1344: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
1345: )*/)
1346: and trb.status = 'C'
1347: and (p_resource_booking_id is null
1348: or (p_resource_booking_id is not null

Line 1359: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))

1355: select 1
1356: from ota_resource_bookings trb
1357: where trb.supplied_resource_id = p_supplied_resource_id
1358: and (
1359: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1360: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363: /*or

Line 1360: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1356: from ota_resource_bookings trb
1357: where trb.supplied_resource_id = p_supplied_resource_id
1358: and (
1359: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1360: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363: /*or
1364: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1357: where trb.supplied_resource_id = p_supplied_resource_id
1358: and (
1359: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1360: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363: /*or
1364: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1365: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

Line 1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1358: and (
1359: (p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
1360: and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363: /*or
1364: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1365: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1366: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1365: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)

1361: and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1362: and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1363: /*or
1364: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1365: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1366: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1367: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1368: */)
1369: and trb.status = 'C'

Line 1367: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1363: /*or
1364: (to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1365: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
1366: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1367: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1368: */)
1369: and trb.status = 'C'
1370: and trb.chat_id is null
1371: and trb.forum_id is null

Line 1384: and p_required_date_from <= trunc (ota_timezone_util.convert_date

1380: select Book_entire_period_flag,required_end_time,required_start_time,
1381: required_date_from,required_date_to,timezone_code
1382: from ota_resource_bookings trb
1383: where trb.supplied_resource_id = p_supplied_resource_id
1384: and p_required_date_from <= trunc (ota_timezone_util.convert_date
1385: (trb.required_date_to, nvl (trb.required_end_time, '23:59'),
1386: trb.timezone_code, p_timezone))
1387: and p_required_date_to >= trunc (ota_timezone_util.convert_date
1388: (trb.required_date_from, nvl (trb.required_start_time, '00:00'),

Line 1387: and p_required_date_to >= trunc (ota_timezone_util.convert_date

1383: where trb.supplied_resource_id = p_supplied_resource_id
1384: and p_required_date_from <= trunc (ota_timezone_util.convert_date
1385: (trb.required_date_to, nvl (trb.required_end_time, '23:59'),
1386: trb.timezone_code, p_timezone))
1387: and p_required_date_to >= trunc (ota_timezone_util.convert_date
1388: (trb.required_date_from, nvl (trb.required_start_time, '00:00'),
1389: trb.timezone_code, p_timezone))
1390: and trb.status = 'C'
1391: and (p_resource_booking_id is null

Line 1405: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1401: from ota_resource_bookings trb
1402: where trb.supplied_resource_id = p_supplied_resource_id
1403: and (
1404: (p_required_date_from between
1405: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1406: and
1407: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1408: or
1409: (p_required_date_to between

Line 1407: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

1403: and (
1404: (p_required_date_from between
1405: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1406: and
1407: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1408: or
1409: (p_required_date_to between
1410: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1411: and

Line 1410: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))

1406: and
1407: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1408: or
1409: (p_required_date_to between
1410: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1411: and
1412: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1413: or
1414: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

Line 1412: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))

1408: or
1409: (p_required_date_to between
1410: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1411: and
1412: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1413: or
1414: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1415: and
1416: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

Line 1414: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))

1410: trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
1411: and
1412: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1413: or
1414: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1415: and
1416: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1417: )
1418: and trb.status = 'C'

Line 1416: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))

1412: trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
1413: or
1414: ((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
1415: and
1416: (p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
1417: )
1418: and trb.status = 'C'
1419: and trb.chat_id is null
1420: and trb.forum_id is null

Line 1465: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))

1461: and (trainer_rec.required_date_from <> trainer_rec.required_date_to)) then
1462: if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1463: --check time overlap
1464: if (
1465: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1466: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1467: or
1468: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1469: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

Line 1466: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))

1462: if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1463: --check time overlap
1464: if (
1465: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1466: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1467: or
1468: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1469: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1470: ) then

Line 1468: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

1464: if (
1465: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1466: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1467: or
1468: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1469: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1470: ) then
1471:
1472: return false;

Line 1469: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

1465: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1466: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
1467: or
1468: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1469: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1470: ) then
1471:
1472: return false;
1473: else

Line 1487: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and

1483:
1484: --since first cursor didn't give problem this means new and old record dates cannot be equal
1485: --and time
1486: if(
1487: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
1488: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1489: or
1490: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
1491: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)

Line 1488: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

1484: --since first cursor didn't give problem this means new and old record dates cannot be equal
1485: --and time
1486: if(
1487: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
1488: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1489: or
1490: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
1491: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1492: )

Line 1490: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and

1486: if(
1487: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
1488: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1489: or
1490: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
1491: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1492: )
1493: ) then
1494:

Line 1491: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)

1487: (p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
1488: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1489: or
1490: (p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
1491: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1492: )
1493: ) then
1494:
1495: return false;

Line 1503: if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

1499: return True;
1500: end if;
1501: --bug 5116223
1502: elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
1503: if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1504: <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
1505: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1506: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1507: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1504: <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then

1500: end if;
1501: --bug 5116223
1502: elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
1503: if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1504: <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
1505: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1506: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1507: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1508: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

Line 1506: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)

1502: elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
1503: if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1504: <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
1505: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1506: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1507: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1508: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1509: ) then
1510: return true;

Line 1508: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))

1504: <> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
1505: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1506: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
1507: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1508: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
1509: ) then
1510: return true;
1511: end if;
1512: end if;

Line 1551: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

1547: and (rec.required_date_from <> rec.required_date_to)) then*/
1548: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1549: --check time overlap
1550: if (
1551: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1552: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1553: or
1554: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1555: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1552: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))

1548: if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
1549: --check time overlap
1550: if (
1551: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1552: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1553: or
1554: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1555: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1556: ) then

Line 1554: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1550: if (
1551: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1552: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1553: or
1554: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1555: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1556: ) then
1557:
1558: return false;

Line 1555: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1551: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1552: and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
1553: or
1554: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1555: and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1556: ) then
1557:
1558: return false;
1559: else

Line 1571: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and

1567:
1568: --since first cursor didn't give problem this means new and old record dates cannot be equal
1569: --and time
1570: if(
1571: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1572: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1573: or
1574: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1575: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

Line 1572: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1568: --since first cursor didn't give problem this means new and old record dates cannot be equal
1569: --and time
1570: if(
1571: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1572: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1573: or
1574: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1575: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1576: )

Line 1574: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and

1570: if(
1571: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1572: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1573: or
1574: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1575: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1576: )
1577: ) then
1578:

Line 1575: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1571: (p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
1572: nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1573: or
1574: (p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
1575: nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1576: )
1577: ) then
1578:
1579: return false;

Line 1587: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1583: return True;
1584: end if;
1585: --bug 5116223
1586: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1587: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1588: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1589: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1590: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1591: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '

Line 1588: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then

1584: end if;
1585: --bug 5116223
1586: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1587: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1588: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1589: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1590: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1591: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1592: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

Line 1590: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)

1586: elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
1587: if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1588: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1589: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1590: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1591: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1592: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1593: ) then
1594: return true;

Line 1592: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))

1588: <> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
1589: if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1590: || nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
1591: and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
1592: || nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
1593: ) then
1594: return true;
1595: end if;
1596: end if;