[Home] [Help]
631: sysdate arrival_date,
632: round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
633: 1000,l_distance_uom),1) distance,
634: hl.geometry,
635: decode(sign(hz_timezone_pub.convert_datetime(
636: l_server_timezone_id,
637: cpp.timezone_id,
638: sysdate)-nvl(cocv.start_time,sysdate-1))+
639: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
635: decode(sign(hz_timezone_pub.convert_datetime(
636: l_server_timezone_id,
637: cpp.timezone_id,
638: sysdate)-nvl(cocv.start_time,sysdate-1))+
639: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
640: l_server_timezone_id,
641: cpp.timezone_id,
642: sysdate)),2,'OPEN','CLOSED')
643: from
656: and hl.location_id (+) = cpp.hz_location_id
657: and (cpp.organization_id <> l_organization_id
658: or nvl(cpp.secondary_inventory,'-1') <> l_subinventory_code)
659: and cocv.calendar_id(+) = cpp.calendar_id
660: and decode(sign(hz_timezone_pub.convert_datetime(
661: l_server_timezone_id,
662: cpp.timezone_id,
663: sysdate)-nvl(cocv.start_time,sysdate-1))+
664: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
660: and decode(sign(hz_timezone_pub.convert_datetime(
661: l_server_timezone_id,
662: cpp.timezone_id,
663: sysdate)-nvl(cocv.start_time,sysdate-1))+
664: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
665: l_server_timezone_id,
666: cpp.timezone_id,
667: sysdate)),2,'OPEN','CLOSED')
668: in ('OPEN',l_open_or_closed);
728: null arrival_date,
729: round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
730: 1000,l_distance_uom),1),
731: hl.geometry,
732: decode(sign(hz_timezone_pub.convert_datetime(
733: l_server_timezone_id,
734: cpp.timezone_id,
735: sysdate)-nvl(cocv.start_time,sysdate-1))+
736: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
732: decode(sign(hz_timezone_pub.convert_datetime(
733: l_server_timezone_id,
734: cpp.timezone_id,
735: sysdate)-nvl(cocv.start_time,sysdate-1))+
736: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
737: l_server_timezone_id,
738: cpp.timezone_id,
739: sysdate)),2,'OPEN','CLOSED')
740: from
760: where moq.organization_id = cpp.organization_id
761: and moq.subinventory_code = nvl(cpp.secondary_inventory,
762: moq.subinventory_code)
763: and moq.inventory_item_id = crpt.alternate_item_id)
764: and decode(sign(hz_timezone_pub.convert_datetime(
765: l_server_timezone_id,
766: cpp.timezone_id,
767: sysdate)-nvl(cocv.start_time,sysdate-1))+
768: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
764: and decode(sign(hz_timezone_pub.convert_datetime(
765: l_server_timezone_id,
766: cpp.timezone_id,
767: sysdate)-nvl(cocv.start_time,sysdate-1))+
768: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
769: l_server_timezone_id,
770: cpp.timezone_id,
771: sysdate)),2,'OPEN','CLOSED')
772: in ('OPEN',l_open_or_closed);
823: null arrival_date,
824: round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
825: 1000,l_distance_uom),1),
826: hl.geometry,
827: decode(sign(hz_timezone_pub.convert_datetime(
828: l_server_timezone_id,
829: cpp.timezone_id,
830: sysdate)-nvl(cocv.start_time,sysdate-1))+
831: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
827: decode(sign(hz_timezone_pub.convert_datetime(
828: l_server_timezone_id,
829: cpp.timezone_id,
830: sysdate)-nvl(cocv.start_time,sysdate-1))+
831: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
832: l_server_timezone_id,
833: cpp.timezone_id,
834: sysdate)),2,'OPEN','CLOSED')
835: from
848: l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
849: and nvl(cpp.secondary_inventory,'-1') =
850: nvl(p_search_params.source_subinventory,'-1')
851: and cocv.calendar_id(+) = cpp.calendar_id
852: and decode(sign(hz_timezone_pub.convert_datetime(
853: l_server_timezone_id,
854: cpp.timezone_id,
855: sysdate)-nvl(cocv.start_time,sysdate-1))+
856: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
852: and decode(sign(hz_timezone_pub.convert_datetime(
853: l_server_timezone_id,
854: cpp.timezone_id,
855: sysdate)-nvl(cocv.start_time,sysdate-1))+
856: sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
857: l_server_timezone_id,
858: cpp.timezone_id,
859: sysdate)),2,'OPEN','CLOSED')
860: in ('OPEN',l_open_or_closed);
1935: log('get_arrival_time',
1936: 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1937:
1938: if p_cutoff is not null then
1939: l_cutoff := hz_timezone_pub.convert_datetime( p_cutoff_tz,
1940: p_server_timezone_id,
1941: p_cutoff);
1942: if (sysdate-trunc(sysdate)) > (l_cutoff-trunc(l_cutoff)) then
1943: l_arrival_date := sysdate+1;
1959: log('get_arrival_time',
1960: 'l_delivery_time before:'||to_char(l_delivery_time,'dd-mon-yyyy hh24:mi'));
1961: log('get_arrival_time','p_location_id:'||p_location_id||
1962: 'p_location_source:'||p_location_source);
1963: l_delivery_time := hz_timezone_pub.convert_datetime(
1964: nvl(get_ship_to_tz(p_location_id,p_location_source),
1965: p_client_timezone_id),
1966: p_server_timezone_id,
1967: p_delivery_time);
2063:
2064: log('get_ship_to_tz', 'l_ship_to_tz:' || l_ship_to_tz);
2065:
2066: if l_ship_to_tz = -9999 or l_ship_to_tz = fnd_api.g_miss_num then
2067: hz_timezone_pub.get_timezone_id (
2068: p_api_version => 1.0,
2069: p_init_msg_list => fnd_api.g_false,
2070: p_postal_code => l_postal_code,
2071: p_city => l_city,
2175: log('get_cutoff_time', 'p_cutoff=' || to_char(p_cutoff, 'DD-MON-YYYY HH24:MI:SS'));
2176: log('get_cutoff_time','p_cutoff_tz=' || p_cutoff_tz);
2177: log('get_cutoff_time','p_server_timezone_id=' || p_server_timezone_id);
2178:
2179: l_cutoff := hz_timezone_pub.convert_datetime( p_cutoff_tz,
2180: p_server_timezone_id,
2181: p_cutoff);
2182:
2183: if (sysdate-trunc(sysdate)) > (l_cutoff-trunc(l_cutoff)) then